com.gitee.cliveyuan.tools.data.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.data;
import com.gitee.cliveyuan.tools.Assert;
import com.gitee.cliveyuan.tools.CollectionTools;
import com.gitee.cliveyuan.tools.FileTools;
import com.gitee.cliveyuan.tools.StringTools;
import com.gitee.cliveyuan.tools.exception.ExcelException;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
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.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.stream.Collectors;
import static com.gitee.cliveyuan.tools.enums.ExcelType.XLS;
import static com.gitee.cliveyuan.tools.enums.ExcelType.XLSM;
import static com.gitee.cliveyuan.tools.enums.ExcelType.XLSX;
/**
* @author clive
* Created on 2018/08/01
* @since 1.0
*/
public class ExcelTools {
private static final Logger logger = LoggerFactory.getLogger(ExcelTools.class);
private ExcelTools(){}
/**
* 读入excel文件，解析后返回
* key: sheet名, value每行数据
*
* @param absoluteFilePath 文件绝对路径
* @param sheetNames 指定的sheet名
*/
public static Map> readExcel(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);
if (Objects.isNull(workbook)) throw ExcelException.excelReadError();
// 仅读取第一个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;
}
return cellValue;
}
}