cn.cliveyuan.tools.poi.inner.ExcelInnerHelper Maven / Gradle / Ivy
package cn.cliveyuan.tools.poi.inner;
import cn.cliveyuan.tools.common.ArrayTools;
import cn.cliveyuan.tools.common.AssertTools;
import cn.cliveyuan.tools.common.FileTools;
import cn.cliveyuan.tools.common.StringTools;
import cn.cliveyuan.tools.common.TableUtils;
import cn.cliveyuan.tools.poi.bean.ExcelReader;
import cn.cliveyuan.tools.poi.bean.ExcelType;
import cn.cliveyuan.tools.poi.bean.ExcelWriter;
import cn.cliveyuan.tools.poi.bean.SheetContent;
import cn.cliveyuan.tools.poi.bean.SheetData;
import cn.cliveyuan.tools.poi.bean.SheetInfo;
import cn.cliveyuan.tools.poi.exception.ExcelException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.UUID;
import java.util.function.Function;
import java.util.stream.Collectors;
import static cn.cliveyuan.tools.poi.bean.ExcelType.XLS;
import static cn.cliveyuan.tools.poi.bean.ExcelType.XLSM;
import static cn.cliveyuan.tools.poi.bean.ExcelType.XLSX;
/**
* @author clive
* Created on 2018/08/01
* @since 1.0
*/
@Slf4j
public class ExcelInnerHelper {
private static final Logger logger = LoggerFactory.getLogger(ExcelInnerHelper.class);
private ExcelInnerHelper() {
}
/**
* 读取Excel
*
* @param excelReader excelReader
* @return
*/
public static List read(ExcelReader excelReader) {
if (Objects.isNull(excelReader)) {
return new ArrayList<>();
}
AssertTools.notNull(excelReader, "excelReader is required");
List resultList = new ArrayList<>();
List sheetInfoList = excelReader.getSheetInfoList();
AssertTools.notEmpty(sheetInfoList, "sheetInfoList can't be empty");
String[] sheetNames = sheetInfoList.stream().map(SheetInfo::getSheetName).toArray(String[]::new);
Map sheetInfoMap = sheetInfoList.stream().collect(Collectors.toMap(SheetInfo::getSheetName, Function.identity()));
Map> sheetDataMap = ExcelInnerHelper.readRawExcel(excelReader.getPathname(), sheetNames);
sheetDataMap.forEach((sheetName, dataArray) -> {
SheetInfo sheetInfo = sheetInfoMap.get(sheetName);
if (Objects.isNull(sheetInfo)) {
log.warn("can't find sheetInfo of sheetName " + sheetName);
throw ExcelException.dataConvertError();
}
SheetData sheetData = new SheetData(sheetName, TableUtils.dataToObject(excelReader.getStartRowNo(), dataArray, sheetInfo.getClazz()));
resultList.add(sheetData);
}
);
return resultList;
}
/**
* 生成Excel
*
* @param excelWriter excelWriter
* @return
*/
public static File write(ExcelWriter excelWriter) {
AssertTools.notNull(excelWriter, "excelWriter is required");
return generate(excelWriter);
}
/**
* 读入excel文件，解析后返回
* key: sheet名, value每行数据
*
* @param absoluteFilePath 文件绝对路径
* @param sheetNames 指定的sheet名
*/
public static Map> readRawExcel(String absoluteFilePath, String... sheetNames) {
return doReadRawExcel(absoluteFilePath, sheetNames);
}
//region private methods
private static Map> doReadRawExcel(String absoluteFilePath, String... sheetNames) {
AssertTools.notBlank(absoluteFilePath, "excel file path is empty");
File file = new File(absoluteFilePath);
AssertTools.isTrue(file.exists(), "excel file not exists");
String extension = FileTools.getExtension(absoluteFilePath);
if (!XLS.name().equalsIgnoreCase(extension)
&& !XLSX.name().equalsIgnoreCase(extension)
&& !XLSM.name().equalsIgnoreCase(extension)
) {
throw ExcelException.notSupport(extension);
}
Map> map = new HashMap();
try {
// 检查文件
// 获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
// 仅读取第一个sheet, 如需读取其他sheet, 需自行封装方法 获取sheet数量:workbook.getNumberOfSheets()
// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
int numberOfSheets = workbook.getNumberOfSheets();
List sheets = new ArrayList<>();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (Objects.isNull(sheet)) {
throw ExcelException.failToReadSheet();
}
sheets.add(sheet);
}
if (ArrayTools.isNotEmpty(sheetNames)) {
List sheetNameList = Arrays.asList(sheetNames);
List collect = sheets.stream().filter(x -> sheetNameList.contains(x.getSheetName())).collect(Collectors.toList());
sheets.clear();
sheets.addAll(collect);
}
for (Sheet sheet : sheets) {
List list = new ArrayList<>();
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
int blankCol = 0;
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
String cellStr = getCellValue(cell);
if (StringTools.isBlank(cellStr)) {
blankCol++;
}
cells[cellNum] = cellStr;
}
if (blankCol < lastCellNum) {
list.add(cells);
}
}
map.put(sheet.getSheetName(), list);
}
} catch (ExcelException e) {
throw e;
} catch (Exception e) {
logger.error("readExcel ", e);
throw ExcelException.failToParseExcel();
}
return map;
}
private static Workbook getWorkBook(File file) throws IOException {
// 获得文件名
String fileName = file.getName();
// 创建Workbook工作薄对象，表示整个excel
Workbook workbook;
// 获取excel文件的io流
InputStream is = new FileInputStream(file);
// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith(XLS.name().toLowerCase())
|| fileName.endsWith(XLS.name())) {
// 2003
workbook = new HSSFWorkbook(is);
} else {
// 2007
workbook = new XSSFWorkbook(is);
}
return workbook;
}
private static String getCellValue(Cell cell) {
String cellValue = StringTools.EMPTY;
if (Objects.isNull(cell)) {
return cellValue;
}
switch (cell.getCellTypeEnum()) {
case NUMERIC:
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
break;
case STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case _NONE:
cellValue = "[未知类型]";
break;
case ERROR:
cellValue = "[非法字符]";
break;
default:
}
return cellValue;
}
private static File generate(ExcelWriter param) {
String filePath = param.getFilePath();
ExcelType excelType = param.getFileType();
AssertTools.notBlank(filePath, "file path can't be empty");
AssertTools.notNull(excelType, "excel type is required");
AssertTools.notEmpty(param.getSheetContentList(), "sheetContentList can't be empty");
File file = new File(filePath);
if (!file.exists()) {
boolean result = file.mkdirs();
if (!result) throw ExcelException.failToMkdirs();
}
String name = param.getFileName();
if (StringTools.isBlank(name)) {
name = UUID.randomUUID().toString();
}
String fileName = name + "." + excelType.name().toLowerCase();
if (!filePath.endsWith("/")) filePath = filePath + "/";
file = new File(filePath + fileName);
try (FileOutputStream fos = new FileOutputStream(file)) {
Workbook wb;
if (excelType.equals(XLS)) wb = new HSSFWorkbook();
else wb = new XSSFWorkbook();
for (SheetContent sheetContent : param.getSheetContentList()) {
String[] headers = sheetContent.getHeaders();
Collection data = sheetContent.getData();
AssertTools.isTrue(ArrayTools.isNotEmpty(headers), "headers can't be empty");
AssertTools.isTrue(CollectionUtils.isNotEmpty(data), "data can't be empty");
Sheet sheet = wb.createSheet(sheetContent.getSheetName());
Row firstRow = sheet.createRow(0);
//创建表头
for (int i = 0; i < headers.length; i++) {
Cell cell = firstRow.createCell(i);
cell.setCellValue(headers[i]);
}
//处理数据
Iterator it = data.iterator();
int index = 0;
while (it.hasNext()) {
index++;
Row row = sheet.createRow(index);
Object t = it.next();
//反射
Class clazz = t.getClass();
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
Method getMethod = pd.getReadMethod();
if (getMethod != null) {
Object invoke = getMethod.invoke(t);
String value = StringTools.EMPTY;
if (invoke != null) {
if (invoke instanceof Date) {
value = TableUtils.SDF.format((Date) invoke);
} else value = invoke.toString();
}
Cell cell = row.createCell(i);
cell.setCellValue(value);
}
}
}
}
wb.write(fos);
return file;
} catch (Exception e) {
log.error("excel generate exception", e);
throw ExcelException.failToGenerateExcel();
}
}
//endregion
}