打开Excel模板
编写Java代码实现数据导出
public void getAllMonitorDataAndExport(MonitorDataQueryVM monitorDataQueryVM, HttpServletResponse response) throws IOException {
monitorDataQueryVM.setPageNumber(0);
monitorDataQueryVM.setPageSize(Integer.MAX_VALUE);
Page<MonitorDataDTO> monitorDataDTOPage = getAllMonitorData(monitorDataQueryVM);
File file = File.createTempFile("监测数据预警", ".xlsx");
try {
// 数据映射
List<Map<String, Object>> mapList = new ArrayList<>();
if (!monitorDataDTOPage.getContent().isEmpty()) {
// 查询工点
ProjectDTO site = projectService.findOne(monitorDataQueryVM.getProjectId()).orElse(null);
// 查询工区
ProjectDTO area = projectService.findOne(site.getParentId()).orElse(null);
for (MonitorDataDTO monitorDataDTO : monitorDataDTOPage.getContent()) {
Map<String, Object> row = new HashMap<>();
row.put("area", area.getProjectName());
row.put("site", site.getProjectName());
if (!ObjectUtils.isEmpty(monitorDataDTO.getCollectTime())) {
row.put("collectTime", InstantUtil.instantToStr(monitorDataDTO.getCollectTime(), "yyyy/MM/dd"));
} else {
row.put("collectTime", "");
}
if (!StringUtils.isEmpty(monitorDataDTO.getPointType())) {
row.put("pointType", MonitorPointType.getByCode(monitorDataDTO.getPointType()).getName());
} else {
row.put("pointType", "");
}
row.put("pointCode", monitorDataDTO.getPointCode());
if (!StringUtils.isEmpty(monitorDataDTO.getAlarmLevel())) {
row.put("alarmLevel", MonitorAlarmLevelEnum.getByCode(monitorDataDTO.getAlarmLevel()).getName());
} else {
row.put("alarmLevel", "");
}
row.put("currentValue", monitorDataDTO.getCurrentValue());
if ("1".equals(monitorDataDTO.getIsEliminate())) {
row.put("isEliminate", "已消警");
} else {
row.put("isEliminate", "未消警");
}
mapList.add(row);
}
// 设置表头
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("headers", new String[]{
"工区", "工点", "预警发生日期", "测点类型", "测点名称", "预警级别", "累计值", "是否消警"
});
// 模板路径
ClassPathResource classPathResource = new ClassPathResource("/templates/MonitorDataTemplate.xlsx");
try (InputStream inputStream = classPathResource.getInputStream()) {
// 创建 Excel 文件
if (!AsposeCellUtil.createExcel(dataMap, mapList, inputStream, file.getAbsolutePath())) {
throw new RuntimeException("Excel 文件生成失败");
}
}
// 将文件输出到浏览器
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("监测数据预警.xlsx", "UTF-8"));
try (InputStream is = new FileInputStream(file.getAbsolutePath());
OutputStream os = response.getOutputStream()) {
IOUtils.copy(is, os);
}
}
} catch (Exception e) {
log.error("监测数据预警导出错误！", e);
throw new BadRequestAlertException("监测数据预警导出错误！", "", HttpStatus.UNAUTHORIZED.getReasonPhrase());
} finally {
if (file.exists()) {
file.delete();
}
}
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
}AsposeCellUtil.java
public class AsposeCellUtil {
private static License license = null;
private final static Logger log = LoggerFactory.getLogger(AsposeCellUtil.class);
private static InputStream getWordLicense() {
try {
InputStream is = AsposeCellUtil.class.
getClassLoader().getResourceAsStream("license/license.xml");
return is;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static boolean getLicense() {
boolean result = true;
try {
InputStream is = getWordLicense();
if (license == null && is != null) {
license = new License();
license.setLicense(is);
}
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/*public static boolean createExcel(List<Map> mapList, String modelPath, String savePath) throws Exception {
Map<String, List<Map>> dataMap = new HashMap();
dataMap.put("data",mapList);
return createExcel(dataMap,modelPath,savePath);
}*/
public static boolean createExcel(Map<String, Object> dataMap,List<Map<String,Object>> mapList, String modelPath, String savePath) throws Exception {
if (!getLicense()) {//必须要验证, 否则有水印
return false;
}
File modelFile = new File(modelPath);
if (!modelFile.exists()) {
log.debug("--模板文件不存在--");
return false;
}
//读取工作簿
Workbook wb = new Workbook(modelPath);
//创建Aspose.Cells WorkbookDesigner 的对象
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(wb);
//设置DataTable对象
if (mapList!=null) {
designer.setDataSource("data", new MapCellsTable(mapList));
}
//设置普通参数
if (dataMap!=null){
for (Map.Entry<String,Object> mm : dataMap.entrySet()) {
designer.setDataSource(mm.getKey(),mm.getValue());
}
}
//根据数据源处理生成报表内容
designer.process();
//将查询数据写入到文件中
wb.save(savePath,wb.getFileFormat());
return true;
}
public static boolean createExcel(Map<String, Object> dataMap,List<Map<String,Object>> mapList, InputStream modelFile, String savePath) throws Exception {
if (!getLicense()) {//必须要验证, 否则有水印
return false;
}
if (modelFile == null) {
log.debug("--模板文件不存在--");
return false;
}
//读取工作簿
Workbook wb = new Workbook(modelFile);
//创建Aspose.Cells WorkbookDesigner 的对象
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(wb);
//设置普通参数
// if (dataMap!=null){
// for (Map.Entry<String,Object> mm : dataMap.entrySet()) {
// if("columnDataList".equals(mm.getKey())){
// //动态列数据集合
// designer.setDataSource("columns", (Object[])dataMap.get("columnDataList"));
// }else{
// designer.setDataSource(mm.getKey(),mm.getValue());
// }
// }
// }
if (dataMap != null) {
for (Map.Entry<String, Object> mm : dataMap.entrySet()) {
designer.setDataSource(mm.getKey(), mm.getValue());
}
}
//设置DataTable对象
if (mapList!=null) {
designer.setDataSource("data", new MapCellsTable(mapList));
}
//设置动态列行数据
// /*
// * Set the Variable Array marker to a cell. You may also place this Smart Marker into a template file manually using Excel
// * and then open this file via WorkbookDesigner
// */
// sheet.getCells().get("A1").putValue("&=$VariableArray");
//
// // Set the data source for the marker(s)
// report.setDataSource("VariableArray", new String[] { "English", "Arabic", "Hindi", "Urdu", "French" });
//
// // Set the CallBack property
// report.setCallBack(new SmartMarkerCallBack(report.getWorkbook()));
//根据数据源处理生成报表内容
designer.process();
//将查询数据写入到文件中
wb.save(savePath,wb.getFileFormat());
return true;
}
public static boolean createExcel(Map<String, Object> dataMap,List<Map<String,Object>> mapList, InputStream modelFile, String savePath,int[] mergeColumns) throws Exception {
if (!getLicense()) {//必须要验证, 否则有水印
return false;
}
if (modelFile == null) {
log.debug("--模板文件不存在--");
return false;
}
//读取工作簿
Workbook wb = new Workbook(modelFile);
//创建Aspose.Cells WorkbookDesigner 的对象
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(wb);
//设置普通参数
// if (dataMap!=null){
// for (Map.Entry<String,Object> mm : dataMap.entrySet()) {
// if("columnDataList".equals(mm.getKey())){
// //动态列数据集合
// designer.setDataSource("columns", (Object[])dataMap.get("columnDataList"));
// }else{
// designer.setDataSource(mm.getKey(),mm.getValue());
// }
// }
// }
if (dataMap != null) {
for (Map.Entry<String, Object> mm : dataMap.entrySet()) {
designer.setDataSource(mm.getKey(), mm.getValue());
}
}
//设置DataTable对象
if (mapList!=null) {
designer.setDataSource("data", new MapCellsTable(mapList));
}
//设置动态列行数据
// /*
// * Set the Variable Array marker to a cell. You may also place this Smart Marker into a template file manually using Excel
// * and then open this file via WorkbookDesigner
// */
// sheet.getCells().get("A1").putValue("&=$VariableArray");
//
// // Set the data source for the marker(s)
// report.setDataSource("VariableArray", new String[] { "English", "Arabic", "Hindi", "Urdu", "French" });
//
// // Set the CallBack property
// report.setCallBack(new SmartMarkerCallBack(report.getWorkbook()));
//根据数据源处理生成报表内容
designer.process();
//合并单元格
Worksheet sheet = wb.getWorksheets().get(0);
for (int j=0; j < mergeColumns.length; j++) {
for (int i = 2; i <= mapList.size(); i++) {
Cell cell = sheet.getCells().get(i, mergeColumns[j]); // 获取当前单元格
Cell aboveCell = sheet.getCells().get(i - 1, mergeColumns[j]); // 获取上一个单元格
// 检查当前单元格是否为空
if (cell.getStringValue().isEmpty() && !aboveCell.getStringValue().isEmpty()) {
// 如果为空，则合并当前单元格和上一个单元格
sheet.getCells().merge(i-1, mergeColumns[j], 2, 1);
}
}
}
//将查询数据写入到文件中
wb.save(savePath,wb.getFileFormat());
return true;
}
/**
* 如果有动态列头（需动态生成列头以及对应每行的表格数据）
* 参考模板（当行动态表头；删除列）：target\classes\templates\import\主要物资月度需用量计划表.xlsx
* 参考模板（多行动态表头；最后一列为动态数据; 合并单元格）： target\classes\templates\import\分工号主要物资设计数量明细表
* dynamic_cell_C-4 动态列头（dataMap中key为dynamic_cell_开头，C-4代表单元格C4；值为 List<String> 标识多列的数据）
* dynamic_cell_noinsert_C-4 动态列头不插入列，满足特定模板（dataMap中key为dynamic_cell_noinsert开头，C-4代表单元格C4；值为 List<String> 标识多列的数据）
* dynamic_cell_data_C-7-RIGHT 动态列头对应的数据绑定列（dataMap中key为dynamic_cell_data_开头,C-7代表单元格C7,RIGHT 表示文字居右对齐，CENTER表示居左对齐，默认dynamic_cell_data_C-7表示居左对齐）
* merge_row_cell_title 合并单元格。 开始行索引：beginRowIndex ； 开始列索引：beginCellIndex ；合并总行数：totalRows；合并总列数：totalColumns
* @param dataMap
* @param mapList
* @param modelFile
* @param savePath
* @return
* @throws Exception
*/
public static boolean createExcelDynamicColumns(Map<String, Object> dataMap,List<Map<String,Object>> mapList, InputStream modelFile, String savePath) throws Exception {
if (!getLicense()) {//必须要验证, 否则有水印
return false;
}
if (modelFile == null) {
System.out.println("--模板文件不存在--");
log.error("模板文件不存在");
return false;
}
//读取工作簿
Workbook wb = new Workbook(modelFile);
Worksheet sheet = wb.getWorksheets().get(0);
//创建Aspose.Cells WorkbookDesigner 的对象
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(wb);
//设置普通参数
if (dataMap!=null){
//设置变量
for (Map.Entry<String,Object> mm : dataMap.entrySet()) {
if(mm.getKey().indexOf("dynamic_cell_") != -1 || mm.getKey().indexOf("remove_cell_") != -1){
continue;
}
designer.setDataSource(mm.getKey(),mm.getValue());
}
//删除列
List<Map.Entry<String,Object>> removeCellList = dataMap.entrySet().stream().filter(w->w.getKey().indexOf("remove_cell_") != -1).collect(Collectors.toList());
//设置动态列变量
List<Map.Entry<String,Object>> dynamicCellList = dataMap.entrySet().stream().filter(w->w.getKey().indexOf("dynamic_cell_") != -1).collect(Collectors.toList());
List<Integer> insertColumnIndexList = new ArrayList<>();
List<Map.Entry<String,Object>> noInsertColList = dynamicCellList.stream().filter(w-> w.getKey().indexOf("dynamic_cell_noinsert_") != -1).collect(Collectors.toList());//不插入动态列索引，直接写入列（含添加了动态数据后的列）
List<Map.Entry<String,Object>> selDynamicCellList = dynamicCellList.stream().filter(w-> w.getKey().indexOf("dynamic_cell_noinsert_") == -1).collect(Collectors.toList());
Map<String,Integer> noInsertList = noInsertColList.stream().collect(Collectors.toMap(w->(w.getKey().replaceAll("dynamic_cell_noinsert_","").replaceAll("-","").toUpperCase()),b->(Integer) b.getValue()));
for (Map.Entry<String,Object> mm :selDynamicCellList) {
List<String> cellList = (List<String>)mm.getValue();
String columnKey = mm.getKey();
boolean isRowData = columnKey.indexOf("dynamic_cell_data_") != -1;
String keyDataStr = mm.getKey().replaceAll("dynamic_cell_data_","");//行数据
String keyTitleStr = mm.getKey().replaceAll("dynamic_cell_","");//标题数据
String[] keys = isRowData ? keyDataStr.split("-") : keyTitleStr.split("-");//G-6格式，G-6/G-6-CENTER/G-6-RIGHT,居左/居中/居右对齐
String key = keys[0].toUpperCase();
Integer cellNum = Integer.parseInt(keys[1]);
String cellKey = key + cellNum;
String align = keys.length > 2 ? keys[2] : "";//默认居左对齐
Cell cell = sheet.getCells().get(cellKey);
int rowCount = cellNum;//行索引
int cellIndex = cell.getColumn();//列索引
int rowIndex = rowCount - 1;
boolean isNoInsert = noInsertList.containsKey(cellKey);//是否不需要插入列，在现有列写入
Integer noInsertColIndex = isNoInsert ? (Integer)noInsertList.get(cellKey) : -1;//是否不需要插入列，在现有列写入索引
for (String cellName:cellList) {
if(!isNoInsert || (isNoInsert && noInsertColIndex > cellIndex)){
if (!insertColumnIndexList.contains(cellIndex)) {
sheet.getCells().insertColumn(cellIndex);//插入动态列
insertColumnIndexList.add(cellIndex);
//扩大删除列
List<Map.Entry<String, Object>> selRemoveColList = removeCellList.stream().filter(w -> (int) w.getValue() >= cellNum.intValue()).collect(Collectors.toList());
if (selRemoveColList != null && selRemoveColList.size() > 0) {
for (int i = 0; i < selRemoveColList.size(); i++) {
int removeCellNum = (int) selRemoveColList.get(i).getValue();
selRemoveColList.get(i).setValue(++removeCellNum);
}
}
}
}
if(isRowData){
cell = sheet.getCells().get(rowIndex,cellIndex);
if(!StringUtils.isEmpty(align) && align.toUpperCase().equals("RIGHT")){
Style style = cell.getStyle();
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
cell.setStyle(style);
}
else if(!StringUtils.isEmpty(align) && align.toUpperCase().equals("CENTER")){
Style style = cell.getStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
cell.setStyle(style);
}
cell.putValue("&=data."+cellName.replaceAll("\\.","-"));//. 会影响绑定数据，所以替换特殊字符.为其他字符
}else{
Cell titleCell = sheet.getCells().get(rowIndex,cellIndex);
titleCell.putValue(cellName);
}
cellIndex++;
}
}
//删除需要删除的列(删除列需要按小到大顺序添加)
if(removeCellList != null && removeCellList.size() > 0){
for(int i = 0; i < removeCellList.size();i++){
int cellNum = (int)removeCellList.get(i).getValue();
sheet.getCells().deleteColumn(cellNum - i);
}
}
}
//设置DataTable对象
if (mapList!=null) {
designer.setDataSource("data", new MapCellsTable(mapList));
}
//设置动态列行数据
// /*
// * Set the Variable Array marker to a cell. You may also place this Smart Marker into a template file manually using Excel
// * and then open this file via WorkbookDesigner
// */
// sheet.getCells().get("A1").putValue("&=$VariableArray");
//
// // Set the data source for the marker(s)
// report.setDataSource("VariableArray", new String[] { "English", "Arabic", "Hindi", "Urdu", "French" });
//
// // Set the CallBack property
// report.setCallBack(new SmartMarkerCallBack(report.getWorkbook()));
// //自适应行高
// sheet.autoFitRows();
//根据数据源处理生成报表内容
designer.process();
//合并单元格
List<Map.Entry<String,Object>> mergeCellList = dataMap.entrySet().stream().filter(w->w.getKey().indexOf("merge_row_cell_") != -1).collect(Collectors.toList());
for (Map.Entry<String,Object> mm :mergeCellList) {
Map<String,Integer> mergeDatas = (Map<String,Integer>)mm.getValue();
Integer beginRowIndex = mergeDatas.get("beginRowIndex");//开始合并行
Integer totalRows = mergeDatas.get("totalRows");//合并结束行
Integer beginCellIndex = mergeDatas.get("beginCellIndex");//开始合并列
Integer totalColumns = mergeDatas.get("totalColumns");//合并结束列
sheet.getCells().merge(beginRowIndex,beginCellIndex,totalRows,totalColumns,true);
}
//将查询数据写入到文件中
wb.save(savePath,wb.getFileFormat());
return true;
}
/**
* 读取excel
* @param file 文件
* @return
* @param <T>
* @throws Exception
*/
public static <T extends ExcelColumn> JSONArray readExcel(MultipartFile file) throws Exception {
return readExcel(file, new ArrayList<>());
}
/**
* 读取excel<br>
* excel的行列开始下标都是 0<br>
* columns的配，例如：
* <table>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据1</td>
* <td colspan="2" style="text-align: center; border: 1px solid;">数据2</td>
* </tr>
* <tr>
* <td rowspan="2" style="text-align: center; border: 1px solid;">数据3</td>
* <td style="text-align: center; border: 1px solid;">数据4</td>
* <td style="text-align: center; border: 1px solid;">数据5</td>
* </tr>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据6</td>
* <td style="text-align: center; border: 1px solid;">数据7</td>
* </tr>
* </table>
* 这样的表格需要配置的 columns 就是:<br>
* { columnName: 数据1-数据3, columnKey: xxx-1, columnType: yyy-1 }<br>
* { columnName: 数据2-数据4-数据6, columnKey: xxx-2, columnType: yyy-2 }<br>
* { columnName: 数据2-数据5-数据7, columnKey: xxx-3, columnType: yyy-3 }
* @param file 文件
* @param columns 列配置
* @return
* @param <T>
* @throws Exception
*/
public static <T extends ExcelColumn> JSONArray readExcel(MultipartFile file, List<T> columns) throws Exception {
return readExcel(file, columns, 0, 0, 0, "-", 1);
}
/**
* 格式化表头以最小粒度的列为基准<br>
* excel的行列开始下标都是 0<br>
* columns的配，例如：
* <table>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据1</td>
* <td colspan="2" style="text-align: center; border: 1px solid;">数据2</td>
* </tr>
* <tr>
* <td rowspan="2" style="text-align: center; border: 1px solid;">数据3</td>
* <td style="text-align: center; border: 1px solid;">数据4</td>
* <td style="text-align: center; border: 1px solid;">数据5</td>
* </tr>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据6</td>
* <td style="text-align: center; border: 1px solid;">数据7</td>
* </tr>
* </table>
* 这样的表格需要配置的 columns 就是:<br>
* { columnName: 数据1-数据3, columnKey: xxx-1, columnType: yyy-1 }<br>
* { columnName: 数据2-数据4-数据6, columnKey: xxx-2, columnType: yyy-2 }<br>
* { columnName: 数据2-数据5-数据7, columnKey: xxx-3, columnType: yyy-3 }
* @param worksheet 工作表
* @param columns excel列的参数
* @param headerStartRow 表头开始的行
* @param headerEndRow 表头结束的行
* @param headerSplitChar 复杂表头的分隔符
* @return
* @param <T> excel列的类型
*/
private static <T extends ExcelColumn> List<ExcelColumn> formatHeaders(Worksheet worksheet, List<T> columns, int headerStartRow, int headerEndRow, String headerSplitChar) {
// 处理配置项里的表头和字段对应关系
Map<String, T> columnMap = columns.stream().collect(Collectors.toMap(T::getColumnName, Function.identity()));
// 获取表头行
List<ExcelColumn> realColumns = new ArrayList<>();
ExcelColumn ec;
int maxColumn = worksheet.getCells().getMaxDataColumn();
List<String> headers = new ArrayList<>();
// 处理表头，如果表头有配置，则取配置中的key，如果没有则取表头
for (int column = 0; column <= maxColumn; column++) {
headers = new ArrayList<>();
// 遍历每一行到指定的头部行数
for (int row = headerStartRow; row <= headerEndRow; row++) {
Cell cell = worksheet.getCells().get(row, column);
if (cell.isMerged()) {
// 当前行 为合并行的第一行，取他的值为表头
if (cell.getMergedRange().getFirstRow() == row) {
headers.add(worksheet.getCells().get(cell.getMergedRange().getFirstRow(), cell.getMergedRange().getFirstColumn()).getStringValue());
}
} else {
headers.add(cell.getStringValue());
}
}
String columnName = String.join(headerSplitChar, headers);
ec = new ExcelColumn();
if (columnMap.containsKey(columnName) && StringUtils.hasText(columnMap.get(columnName).getColumnKey())) {
ec.setColumnKey(columnMap.get(columnName).getColumnKey());
ec.setColumnType(columnMap.get(columnName).getColumnType());
} else {
ec.setColumnKey(columnName);
}
realColumns.add(ec);
}
return realColumns;
}
/**
* 读取excel<br>
* excel的行列开始下标都是 0<br>
* columns的配，例如：
* <table>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据1</td>
* <td colspan="2" style="text-align: center; border: 1px solid;">数据2</td>
* </tr>
* <tr>
* <td rowspan="2" style="text-align: center; border: 1px solid;">数据3</td>
* <td style="text-align: center; border: 1px solid;">数据4</td>
* <td style="text-align: center; border: 1px solid;">数据5</td>
* </tr>
* <tr>
* <td style="text-align: center; border: 1px solid;">数据6</td>
* <td style="text-align: center; border: 1px solid;">数据7</td>
* </tr>
* </table>
* 这样的表格需要配置的 columns 就是:<br>
* { columnName: 数据1-数据3, columnKey: xxx-1, columnType: yyy-1 }<br>
* { columnName: 数据2-数据4-数据6, columnKey: xxx-2, columnType: yyy-2 }<br>
* { columnName: 数据2-数据5-数据7, columnKey: xxx-3, columnType: yyy-3 }
* @param file 文件
* @param columns 列配置
* @param sheetIndex 第几个工作表
* @param headerStartRow 表头开始的行
* @param headerEndRow 表头结束的行
* @param headerSplitChar 复杂表头之间的拼接符
* @param bodyStartRow 表内容开始行
* @return
* @param <T>
* @throws Exception
*/
public static <T extends ExcelColumn> JSONArray readExcel(MultipartFile file, List<T> columns, int sheetIndex, int headerStartRow, int headerEndRow, String headerSplitChar, int bodyStartRow) throws Exception {
JSONArray result = new JSONArray();
InputStream is = file.getInputStream();
Workbook workbook = new Workbook(is);
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
// 处理表头
List<ExcelColumn> realColumns = formatHeaders(worksheet, columns, headerStartRow, headerEndRow, headerSplitChar);
JSONObject object;
ExcelColumn realColumn;
for (int row = bodyStartRow; row <= worksheet.getCells().getMaxDataRow(); row++) {
object = new JSONObject();
try {
// 在行处捕捉异常，失败则整行失败
for (int col = 0; col <= worksheet.getCells().getMaxDataColumn(); col++) {
if (realColumns.size() > col) {
realColumn = realColumns.get(col);
// 获取单元格
Cell cell = worksheet.getCells().get(row, col);
// 根据配置不同的类型单独处理，默认是文本
switch (realColumn.getColumnType() == null ? "" : realColumn.getColumnType()) {
case "Double":
object.put(realColumn.getColumnKey(), cell.getDoubleValue());
break;
case "Integer":
object.put(realColumn.getColumnKey(), cell.getIntValue());
break;
case "Boolean":
object.put(realColumn.getColumnKey(), cell.getBoolValue());
break;
case "DateTime":
object.put(realColumn.getColumnKey(), cell.getDateTimeValue());
break;
case "Float":
object.put(realColumn.getColumnKey(), cell.getFloatValue());
break;
case "BigDecimal":
Object cellValue = cell.getValue();
BigDecimal bigDecimalValue;
if (cellValue instanceof Double) {
bigDecimalValue = BigDecimal.valueOf((Double) cellValue);
} else if (cellValue instanceof Integer) {
bigDecimalValue = BigDecimal.valueOf((Integer) cellValue);
} else if (cellValue instanceof String) {
bigDecimalValue = new BigDecimal((String) cellValue);
} else {
bigDecimalValue = new BigDecimal("0");
}
object.put(realColumn.getColumnKey(), bigDecimalValue);
break;
case "Instant":
if (cell.getType() == CellValueType.IS_STRING) {
String stringValue = cell.getStringValue();
Instant instantValue = DateUtil.parseInstant(stringValue);
object.put(realColumn.getColumnKey(), instantValue);
} else if (cell.getType() == CellValueType.IS_DATE_TIME) {
if (cell.getDateTimeValue() != null) {
Instant instantValue = cell.getDateTimeValue().toUniversalTime().j().toInstant();
object.put(realColumn.getColumnKey(), instantValue);
} else {
object.put(realColumn.getColumnKey(), null);
}
} else if (cell.getType() == CellValueType.IS_NUMERIC) {
Instant instantValue = Instant.ofEpochMilli((long)cell.getValue());
object.put(realColumn.getColumnKey(), instantValue);
}
break;
case "Percent":
String value = cell.getStringValue();
if (StringUtils.isEmpty(value)) {
object.put(realColumn.getColumnKey(), 0);
} else {
try {
value = value.replace("%", "");
object.put(realColumn.getColumnKey(), Double.parseDouble(value));
} catch (Exception e) {
object.put(realColumn.getColumnKey(), 0);
}
}
break;
default:
object.put(realColumn.getColumnKey(), cell.getStringValue());
break;
}
}
}
result.add(object);
} catch (Exception e) {
log.error("read excel row: {} error: {}", row, e.getMessage());
throw e;
}
}
return result;
}
}