com.gitee.cliveyuan.tools.ExcelTools Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of java-tools Show documentation
Show all versions of java-tools Show documentation
Some commonly used methods in java
package com.gitee.cliveyuan.tools;
import com.gitee.cliveyuan.tools.bean.excel.ExcelGenerateParam;
import com.gitee.cliveyuan.tools.bean.excel.SheetData;
import com.gitee.cliveyuan.tools.bean.excel.SheetInfo;
import com.gitee.cliveyuan.tools.data.TableUtils;
import com.gitee.cliveyuan.tools.data.excel.ExcelReader;
import com.gitee.cliveyuan.tools.data.excel.ExcelWriter;
import com.gitee.cliveyuan.tools.exception.ExcelException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
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.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;
import static com.gitee.cliveyuan.tools.enums.ExcelType.*;
/**
* @author clive
* Created on 2018/08/01
* @since 1.0
*/
@Slf4j
public class ExcelTools {
private static final Logger logger = LoggerFactory.getLogger(ExcelTools.class);
private ExcelTools() {
}
/**
* 读取Excel
*
* @param excelReader excelReader
* @return
*/
public static List read(ExcelReader excelReader) {
if (Objects.isNull(excelReader)) {
return Lists.newArrayList();
}
Assert.notNull(excelReader, "excelReader is required");
List resultList = Lists.newArrayList();
List sheetInfoList = excelReader.getSheetInfoList();
Assert.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 = ExcelTools.readRawExcel(excelReader.getExcelPath(), 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.getSkipRowNo(), dataArray, sheetInfo.getClazz()));
resultList.add(sheetData);
}
);
return resultList;
}
/**
* 生成Excel
*
* @param excelWriter excelWriter
* @return
*/
public static File write(ExcelWriter excelWriter) {
Assert.notNull(excelWriter, "excelWriter is required");
ExcelGenerateParam param = ExcelGenerateParam.builder()
.filePath(excelWriter.getFilePath())
.excelType(excelWriter.getExcelType())
.sheetContentList(excelWriter.getSheetContentList())
.build();
return TableUtils.generate(param);
}
/**
* 读入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) {
Assert.notBlank(absoluteFilePath, "excel file path is empty");
File file = new File(absoluteFilePath);
Assert.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 = Maps.newHashMap();
try {
// 检查文件
// 获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
// 仅读取第一个sheet, 如需读取其他sheet, 需自行封装方法 获取sheet数量:workbook.getNumberOfSheets()
// 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
int numberOfSheets = workbook.getNumberOfSheets();
List sheets = Lists.newArrayList();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (Objects.isNull(sheet)) {
throw ExcelException.failToReadSheet();
}
sheets.add(sheet);
}
if (CollectionTools.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 = Lists.newArrayList();
// 获得当前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;
}
//endregion
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy