All Downloads are FREE. Search and download functionalities are using the official Maven repository.
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.gomcarter.frameworks.xmlexcel.utils.ExcelUtils Maven / Gradle / Ivy
package com.gomcarter.frameworks.xmlexcel.utils;
import jxl.CellView;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;
/**
* @author gomcarter 2017年12月2日 08:10:35
*/
public class ExcelUtils {
protected static List> upload(InputStream is, String fileName) {
String fileSuffix = getFileSuffix(fileName);
if (!"xls".contains(fileSuffix)) {
throw new RuntimeException("excel解析失败，请上传excel2003格式的文档！");
}
List> dataList = null;
try {
dataList = ExcelUtils.readWithHeader(is);
} catch (Exception e1) {
throw new RuntimeException("excel解析失败，请上传excel2003格式的文档！", e1);
}
if (dataList == null || dataList.size() == 0) {
throw new RuntimeException("上传的文件无内容或有误！");
}
return dataList;
}
public static List> readWithHeader(File file, Integer sheetNumber) throws BiffException, IOException {
Workbook book = Workbook.getWorkbook(file);
Sheet sheet = book.getSheet(sheetNumber);
return _readSheet(sheet);
}
public static List> readWithHeader(File file, String sheetName) throws BiffException, IOException {
Workbook book = Workbook.getWorkbook(file);
for (Sheet sheet : book.getSheets()) {
if (sheetName.equals(sheet.getName().trim())) {
return _readSheet(sheet);
}
}
return null;
}
private static List> _readSheet(Sheet sheet) {
int rows = sheet.getRows();
int cos = sheet.getColumns();
List> result = new ArrayList>();
Map indexMap = new HashMap();
for (int j = 0; j < cos; j++) {
String header = sheet.getCell(j, 0).getContents();
if (StringUtils.isNotBlank(header)) {
indexMap.put(j, StringUtils.trim(header));
}
}
boolean hasMerged = false;
Map mergedValue = new HashMap();
Range[] ranges = sheet.getMergedCells();
if (ranges != null) {
for (Range space : ranges) {
int rf = space.getTopLeft().getRow();
int rt = space.getBottomRight().getRow();
int cf = space.getTopLeft().getColumn();
int ct = space.getBottomRight().getColumn();
String spaceValue = space.getTopLeft().getContents();
if (StringUtils.isNotBlank(spaceValue)) {
hasMerged = true;
for (int m = rf; m <= rt; m++) {
for (int n = cf; n <= ct; n++) {
mergedValue.put(m + "-" + n, spaceValue.trim());
}
}
}
}
}
for (int i = 1; i < rows; i++) {
Map rowMap = new LinkedHashMap();
boolean add = false;
for (int j = 0; j < cos; j++) {
String header = indexMap.get(j);
if (header != null) {
String cellValue = StringUtils.trim(sheet.getCell(j, i).getContents());
if (StringUtils.isNotBlank(cellValue)) {
rowMap.put(header, cellValue);
add = true;
} else {
if (hasMerged) {
String key = i + "-" + j;
String value = mergedValue.get(key);
rowMap.put(header, value);
add = true;
} else {
rowMap.put(header, null);
}
}
}
}
if (add/* !rowMap.isEmpty() */) {
result.add(rowMap);
}
}
return result;
}
public static List> readWithHeader(File file) throws BiffException, IOException {
Workbook book = Workbook.getWorkbook(file);
Sheet sheet = book.getSheet(0);
return _readSheet(sheet);
}
public static List> readWithHeader(InputStream is) throws IOException, BiffException {
Workbook book = Workbook.getWorkbook(is);
Sheet sheet = book.getSheet(0);
return _readSheet(sheet);
}
public static void write(OutputStream out, String[][] cells) throws IOException, RowsExceededException, WriteException {
WritableWorkbook book = Workbook.createWorkbook(out);
WritableSheet sheet = book.createSheet("sheet1", 0);
for (int i = 0; i < cells.length; i++) {
for (int j = 0; j < cells[i].length; j++) {
Label label = new Label(j, i, cells[i][j]);
sheet.addCell(label);
}
}
book.write();
book.close();
}
public static void write(OutputStream out, List> dataList) throws IOException, RowsExceededException, WriteException {
WritableWorkbook book = Workbook.createWorkbook(out);
WritableSheet sheet = book.createSheet("sheet1", 0);
if (dataList != null && !dataList.isEmpty()) {
//取列数最大的那一行,用于初始化列头
int maxRow = 0;
int maxCols = 0;
int current = 0;
for (Map rowMap : dataList) {
if (rowMap.keySet().size() > maxCols) {
maxCols = rowMap.keySet().size();
maxRow = current;
}
current++;
}
//初始化header,
int index = 0;
Map headerMap = new HashMap();
for (String header : dataList.get(maxRow).keySet()) {
Label label = new Label(index, 0, header);
headerMap.put(header, index);
sheet.addCell(label);
index++;
}
//行
int rowIndex = 1;
for (Map rowMap : dataList) {
for (String header : rowMap.keySet()) {
Label label = new Label(headerMap.get(header), rowIndex, rowMap.get(header));
sheet.addCell(label);
}
rowIndex++;
}
for (Integer col : headerMap.values()) {
CellView view = new CellView();
view.setAutosize(true);
sheet.setColumnView(col, view);
}
}
book.write();
book.close();
}
public static void writeObject(OutputStream out, List> dataList) throws IOException, RowsExceededException, WriteException {
WritableWorkbook book = Workbook.createWorkbook(out);
WritableSheet sheet = book.createSheet("sheet1", 0);
//sheet.mergeCells(arg0, arg1, arg2, arg3);
if (dataList != null && !dataList.isEmpty()) {
//取列数最大的那一行,用于初始化列头
int maxRow = 0;
int maxCols = 0;
int current = 0;
for (Map rowMap : dataList) {
if (rowMap.keySet().size() > maxCols) {
maxCols = rowMap.keySet().size();
maxRow = current;
}
current++;
}
//初始化header,
int index = 0;
Map headerMap = new HashMap();
for (String header : dataList.get(maxRow).keySet()) {
Label label = new Label(index, 0, header);
headerMap.put(header, index);
sheet.addCell(label);
index++;
}
//行
int rowIndex = 1;
for (Map rowMap : dataList) {
for (String header : rowMap.keySet()) {
Object value = rowMap.get(header);
if (value != null) {
WritableCell cell = null;
if (value instanceof Number) {
cell = new jxl.write.Number(headerMap.get(header), rowIndex, Double.valueOf(value.toString()));
} else if (value instanceof Date) {
cell = new jxl.write.DateTime(headerMap.get(header), rowIndex, (Date) value);
} else {
cell = new Label(headerMap.get(header), rowIndex, value.toString().trim());
}
sheet.addCell(cell);
}
}
rowIndex++;
}
for (Integer col : headerMap.values()) {
CellView view = new CellView();
view.setAutosize(true);
sheet.setColumnView(col, view);
}
}
book.write();
book.close();
}
/**
* 获取文件拓展名
*
* @param filename file name
* @return the suffix
*/
private static String getFileSuffix(String filename) {
if ((filename != null) && (filename.length() > 0)) {
int dot = filename.lastIndexOf('.');
if ((dot > -1) && (dot < (filename.length() - 1))) {
return filename.substring(dot + 1);
}
}
return filename;
}
}
