1.封装工具
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public final class ExcelReader {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReader.class);
private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("#.##");
private ExcelReader() {
}
public static ExcelReader.ExcelType getExcelType(String fileName) {
if (StringUtils.isBlank(fileName)) {
return null;
} else {
return fileName.toLowerCase(Locale.CHINESE).endsWith("xlsx") ? ExcelReader.ExcelType.XLSX : ExcelReader.ExcelType.XLS;
}
}
public static List<Map<String, Object>> readToMapList(File file) {
return readToMapList((File)file, (String[])null);
}
public static List<Map<String, Object>> readToMapList(InputStream in, ExcelReader.ExcelType excelType) {
return readToMapList((InputStream)in, (ExcelReader.ExcelType)excelType, (String[])null);
}
public static List<Map<String, Object>> readToMapList(InputStream in, ExcelReader.ExcelType excelType, String[] fields) {
return readToMapList(in, excelType, fields, 0);
}
public static List<Map<String, Object>> readToMapList(File file, String[] fields) {
return readToMapList(file, fields, 0);
}
public static List<Map<String, Object>> readToMapList(File file, String[] fields, Integer sheetIndex) {
ArrayList dataList = new ArrayList();
try {
ExcelReader.ExcelType type = ExcelReader.ExcelType.XLS;
if (file.getName().toLowerCase(Locale.CHINESE).endsWith("xlsx")) {
type = ExcelReader.ExcelType.XLSX;
}
return readToMapList(new FileInputStream(file), type, fields, sheetIndex);
} catch (FileNotFoundException var5) {
LOGGER.error("file not found", var5);
return dataList;
}
}
public static List<Map<String, Object>> readToMapList(InputStream inputStream, ExcelReader.ExcelType excelType, String[] fields, Integer sheetIndex) {
ArrayList dataList = new ArrayList();
try {
Object wb = ExcelReader.ExcelType.XLSX.equals(excelType) ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
try {
int rows;
HashMap fieldIndex;
short headerCellCount;
int i;
String cellValue;
int c;
HashMap dataMap;
if (ExcelReader.ExcelType.XLSX.equals(excelType) && wb instanceof XSSFWorkbook) {
XSSFSheet sheet = ((XSSFWorkbook)wb).getSheetAt(sheetIndex);
rows = sheet.getLastRowNum();
XSSFRow headerRow = sheet.getRow(0);
fieldIndex = new HashMap();
headerCellCount = headerRow.getLastCellNum();
i = 0;
label119:
while(true) {
if (i >= headerCellCount) {
i = 1;
while(true) {
if (i > rows) {
break label119;
}
XSSFRow row = sheet.getRow(i);
if (row != null) {
dataMap = new HashMap();
for(c = 0; c < headerCellCount; ++c) {
if (fieldIndex.get(c) != null) {
XSSFCell cell = row.getCell(c);
if (cell != null) {
dataMap.put((String)fieldIndex.get(c), getValue(cell));
}
}
}
dataList.add(dataMap);
}
++i;
}
}
XSSFCell cell = headerRow.getCell(i);
if (cell != null && CellType.STRING.equals(cell.getCellType())) {
cellValue = cell.getStringCellValue();
if (fields == null || fields.length == 0 || ArrayUtils.contains(fields, cellValue)) {
fieldIndex.put(i, cellValue);
}
}
++i;
}
} else if (wb instanceof HSSFWorkbook) {
HSSFSheet sheet = ((HSSFWorkbook)wb).getSheetAt(sheetIndex);
rows = sheet.getLastRowNum();
HSSFRow headerRow = sheet.getRow(0);
fieldIndex = new HashMap();
headerCellCount = headerRow.getLastCellNum();
i = 0;
label149:
while(true) {
if (i >= headerCellCount) {
i = 1;
while(true) {
if (i > rows) {
break label149;
}
HSSFRow row = sheet.getRow(i);
if (row != null) {
dataMap = new HashMap();
for(c = 0; c < headerCellCount; ++c) {
if (fieldIndex.get(c) != null) {
HSSFCell cell = row.getCell(c);
if (cell != null) {
dataMap.put((String)fieldIndex.get(c), getValue(cell));
}
}
}
dataList.add(dataMap);
}
++i;
}
}
HSSFCell cell = headerRow.getCell(i);
if (cell != null && CellType.STRING.equals(cell.getCellType())) {
cellValue = cell.getStringCellValue();
if (fields == null || fields.length == 0 || ArrayUtils.contains(fields, cellValue)) {
fieldIndex.put(i, cellValue);
}
}
++i;
}
}
} catch (Throwable var17) {
if (wb != null) {
try {
((Workbook)wb).close();
} catch (Throwable var16) {
var17.addSuppressed(var16);
}
}
throw var17;
}
if (wb != null) {
((Workbook)wb).close();
}
} catch (IOException var18) {
LOGGER.error(var18.getLocalizedMessage(), var18);
}
return dataList;
}
private static Object getValue(Cell cell) {
CellType cellType = cell.getCellType();
if (CellType.FORMULA.equals(cellType)) {
return cell.getCellFormula();
} else if (CellType.NUMERIC.equals(cellType) && DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else if (CellType.NUMERIC.equals(cellType)) {
return DECIMAL_FORMAT.format(cell.getNumericCellValue());
} else {
return CellType.STRING.equals(cellType) ? cell.getStringCellValue() : null;
}
}
public static String cutZero(String v) {
if (v.contains(".")) {
while(true) {
if (v.lastIndexOf(48) != v.length() - 1) {
if (v.lastIndexOf(46) == v.length() - 1) {
v = v.substring(0, v.lastIndexOf(46));
}
break;
}
v = v.substring(0, v.lastIndexOf(48));
}
}
return v;
}
public static enum ExcelType {
XLS,
XLSX;
private ExcelType() {
}
}
}
2.读取excel 例子：
image.png
3.解析代码
ExcelReader.ExcelType type = ExcelReader.ExcelType.XLS;
if (serviceFile.getName().toLowerCase().endsWith("xlsx")) {
type = ExcelReader.ExcelType.XLSX;
}
try (InputStream inputStream = serviceFile.openInputStream()) {
List<Map<String, Object>> maps = ExcelReader.readToMapList(inputStream, type, null, 0);
task.setTotals(maps.size());
int index = 0;
for (Map<String, Object> item : maps) {
index++;
task.setProgress(index);
Entity entity = new Entity(ReportDetails.ID);
String report = (String) item.get("所属报表");
if (StringUtils.isBlank(report)) {
task.println("第" + index + "行数据【所属报表】为空");
continue;
}
entity.setProperty("report", report);
String columnName = (String) item.get("列名");
if (StringUtils.isBlank(columnName)) {
task.println("第" + index + "行数据【列名】为空");
continue;
}
entity.setProperty("columnName", columnName);
String numberOfLines = (String) item.get("行数");
if (StringUtils.isBlank(numberOfLines)) {
task.println("第" + index + "行数据【行数】为空");
continue;
}
entity.setProperty("numberOfLines", Convert.toInt(numberOfLines));
String sequenceNumber = (String) item.get("顺序号");
if (StringUtils.isBlank(sequenceNumber)) {
task.println("第" + index + "行数据【顺序号】为空");
continue;
}
entity.setProperty("sequenceNumber", Convert.toInt(sequenceNumber));
String rowSpan = (String) item.get("跨行");
if (StringUtils.isNotBlank(rowSpan)) {
entity.setProperty("rowSpan", Convert.toInt(rowSpan));
}
String colSpan = (String) item.get("跨列");
if (StringUtils.isNotBlank(colSpan)) {
entity.setProperty("colSpan", Convert.toInt(colSpan));
}
String columnKey = (String) item.get("取值key");
if (StringUtils.isNotBlank(columnKey)) {
entity.setProperty("columnKey", columnKey);
}
this.entityService.create(entity);
}
task.println("读取excels数据条数 ( " + maps.size() + " )");
} catch (Exception e) {
log.error(e.getMessage(), e);
}