基于若依框架得excel导出自定义的多sheet工具类

ゝ一纸荒年。 2024-03-17 21:48 178阅读 0赞

下面这个代码是在若依框架中修改的excel工具类，接着我上篇文章excel注解来完成的excel导入导出工具类，在原有的基础上，可以自定义导出的sheet，可以有多个sheet，每个sheet的数据可以自定义。

package com.ruoyi.leave.utils;
 
 import java.io.IOException;
 import java.io.InputStream;
 import java.lang.reflect.Field;
 import java.lang.reflect.Method;
 import java.math.BigDecimal;
 import java.net.URLEncoder;
 import java.text.NumberFormat;
 import java.text.SimpleDateFormat;
 import java.time.LocalDateTime;
 import java.time.ZoneId;
 import java.time.format.DateTimeFormatter;
 import java.util.*;
 
 import javax.servlet.http.HttpServletResponse;
 
 import com.ruoyi.common.core.domain.AjaxResult;
 import com.ruoyi.common.utils.reflect.ReflectUtils;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 import org.apache.poi.ss.usermodel.BorderStyle;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
 import org.apache.poi.ss.usermodel.CellType;
 import org.apache.poi.ss.usermodel.DataFormat;
 import org.apache.poi.ss.usermodel.FillPatternType;
 import org.apache.poi.ss.usermodel.Font;
 import org.apache.poi.ss.usermodel.HorizontalAlignment;
 import org.apache.poi.ss.usermodel.IndexedColors;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.VerticalAlignment;
 import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.xssf.streaming.SXSSFSheet;
 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 import org.apache.poi.xssf.usermodel.XSSFSheet;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.web.multipart.MultipartFile;
 
 import static com.ruoyi.common.utils.reflect.ReflectUtils.convertReflectionExceptionToUnchecked;
 
 public class ExcelKit {
 private static final Logger logger = LoggerFactory.getLogger(ExcelKit.class);
 
 /* 工作簿 */
 private Workbook workbook;
 /* 工作表-导出 */
 private SXSSFSheet sheet;
 /* 工作表-导入 */
 private XSSFSheet importSheet;
 /* 默认工作表名称 */
 private String defaultSheetName = "Sheet1";
 /* 样式 */
 private Map<String, CellStyle> cellStyleMap = new HashMap<>();
 /* 行号 */
 private int rownum = 0;
 /* 规定数据从第3行开始填充(第一行表头,第二行标题) */
 private int firstRowNum = 2;
 /* 表头 */
 private String header;
 /* 列标题 */
 private List<String> titleList = new ArrayList<>();
 /* 导出类型 */
 /* 注解 - new Object[] { Excel, Field或Method } */
 private List<Object[]> annotationList = new ArrayList<>();
 /* 表格数据 */
 private List<?> dataList = new ArrayList<>();
 /* 多sheet的表格数据 */
 private Map<String,List> dataListMap = new HashMap<String,List>();
 /* 客户端响应对象 */
 private HttpServletResponse response;
 
 /*******
 * 
 * 描述： 【Excel导出】
 * 
 *
 * @param header 表头
 * @param _class 要导出的类
 * @param map 数据列表, string--sheet名，list--数据
 * @param response 响应客户端
 */
 public ExcelKit(String header, Map<String, List> map, Class<?> _class, HttpServletResponse response) {
 
 this.header = StringUtils.isBlank(header) ? "" : header;
 this.dataListMap = map;
 this.response = response;
 this.handleAnnotationExport(_class);
 this.handleTitleList();
 }
 
 /*******
 * 
 * 描述： 【Excel导入】
 * 
 *
 * @param multipartFile 要导入的文件, xlsx格式
 * @throws IOException
 */
 public ExcelKit(MultipartFile multipartFile) throws Exception {
 // 文件名
 String fileName = multipartFile.getOriginalFilename();
 InputStream inputStream = multipartFile.getInputStream();
 if (StringUtils.isBlank(fileName)) {
 throw new Exception("没有文件名");
 } else if (!fileName.toLowerCase().endsWith("xlsx")) {
 throw new Exception("错误类型");
 }
 this.workbook = new XSSFWorkbook(inputStream);
 this.importSheet = (XSSFSheet) workbook.getSheet(defaultSheetName);
 }
 
 
 /*******
 * 
 * 描述： 【导出】
 * 
 */
 public AjaxResult export() throws Exception {
 try {
 // 创建工作簿, 支持大文件
 this.workbook = new SXSSFWorkbook();
 int i =0;
 // 遍历需要导出的数据集合
 for (String key:dataListMap.keySet()){
 
 // 创建工作表
 SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("Sheet" + (i+1));
 //工作表的名称
 workbook.setSheetName(i, key);
 
 // 自定义样式
 this.createCellStyle();
 rownum = 0;
 // 首行表头
 this.createHeaderRowBySheet(sheet);
 
 // 下一行标题
 this.createTitleRowByList(sheet);
 
 // 下一行开始数据填充
 this.createContentRowByList(dataListMap.get(key),sheet);
 
 i++;
 }
 
 // 输出到客户端
 this.write();
 return AjaxResult.success();
 } catch (Exception e) {
 logger.error("ExcelKit.export-error:{}", e.getMessage());
 throw new Exception("导出错误");
 } finally {
 try {
 workbook.close();
 } catch (IOException e) {
 logger.error("workbook.close-error:{}", e.getMessage());
 }
 }
 }
 
 
 /*******
 * 
 * 描述： 【导入时解析数据】
 * 
 *
 * @throws IllegalAccessException
 * @throws InstantiationException
 */
 public <E> List<E> getDataList(Class<E> _class) throws InstantiationException, IllegalAccessException {
 this.handleAnnotationImport(_class);
 List<E> importDataList = new ArrayList<>();
 E e = null;
 Row row = null;
 Object value = null;
 int colnum = 0;
 for (int i = firstRowNum; i < this.getImportLastRowNum(); i++) {
 e = _class.newInstance();
 colnum = 0;
 row = this.importSheet.getRow(i);
 for (Object[] object : annotationList) {
 value = this.getCellValue(row, colnum++);
 if (value != null) {
 // Excel excel = (Excel) object[0];
 Class<?> valueType = Class.class;
 if (object[1] instanceof Field) {
 valueType = ((Field) object[1]).getType();
 } else if (object[1] instanceof Method) {
 Method method = ((Method) object[1]);
 if ("get".equals(method.getName().substring(0, 3))) {
 valueType = method.getReturnType();
 } else if ("set".equals(method.getName().substring(0, 3))) {
 valueType = ((Method) object[1]).getParameterTypes()[0];
 }
 }
 try {
 if (valueType == String.class) {
 String s = String.valueOf(value.toString());
 if (StringUtils.endsWith(s, ".0")) {
 value = StringUtils.substringBefore(s, ".0");
 } else {
 value = String.valueOf(value.toString());
 }
 } else if (valueType == Integer.class) {
 value = Double.valueOf(value.toString()).intValue();
 } else if (valueType == Long.class) {
 value = Double.valueOf(value.toString()).longValue();
 } else if (valueType == Double.class) {
 value = Double.valueOf(value.toString());
 } else if (valueType == Float.class) {
 value = Float.valueOf(value.toString());
 } else if (valueType == Date.class) {
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
 value = sdf.parse(value.toString());
 } else if (valueType == LocalDateTime.class) {
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
 value = LocalDateTime.ofInstant((sdf.parse(value.toString())).toInstant(), ZoneId.systemDefault());
 } else {
 value = Class
 .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
 "fieldtype." + valueType.getSimpleName() + "Type"))
 .getMethod("getValue", String.class).invoke(null, value.toString());
 }
 } catch (Exception ex) {
 logger.warn("excel导入,第{}行第{}列解析失败:{}", i, colnum, ex.getMessage());
 value = null;
 }
 if (object[1] instanceof Field) {
 ReflectUtils.invokeSetter(e, ((Field) object[1]).getName(), value);
 } else if (object[1] instanceof Method) {
 String mthodName = ((Method) object[1]).getName();
 if ("get".equals(mthodName.substring(0, 3))) {
 mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
 }
 ReflectUtils.invokeMethod(e, mthodName, new Class[]{valueType}, new Object[]{value});
 }
 }
 }
 importDataList.add(e);
 }
 return importDataList;
 }
 
 /*******
 * 
 * 描述： 【根据Class处理对应的注解-导出】
 * 
 *
 * @param _class 必须传入带Excel注解的类
 */
 private void handleAnnotationExport(Class<?> _class) {
 // 字段
 Field[] fieldArr = _class.getDeclaredFields();
 for (Field field : fieldArr) {
 ExcelUtils excel = field.getAnnotation(ExcelUtils.class);
 if (excel != null && (excel.type() == ExcelUtils.TypeEnum.EXPORT_AND_IMPORT || excel.type() == ExcelUtils.TypeEnum.ONLY_EXPORT)) {
 annotationList.add(new Object[]{excel, field});
 }
 }
 // 方法
 Method[] methodArr = _class.getDeclaredMethods();
 for (Method method : methodArr) {
 ExcelUtils excel = method.getAnnotation(ExcelUtils.class);
 if (excel != null && (excel.type() == ExcelUtils.TypeEnum.EXPORT_AND_IMPORT || excel.type() == ExcelUtils.TypeEnum.ONLY_EXPORT)) {
 annotationList.add(new Object[]{excel, method});
 }
 }
 // 排序
 Collections.sort(annotationList, new Comparator<Object[]>() {
 public int compare(Object[] o1, Object[] o2) {
 return new Integer(((ExcelUtils) o1[0]).sort()).compareTo(new Integer(((ExcelUtils) o2[0]).sort()));
 }
 
 ;
 });
 }
 
 /*******
 * 
 * 描述： 【根据Class处理对应的注解-导入】
 * 
 *
 * @param _class 必须传入带Excel注解的类
 */
 private void handleAnnotationImport(Class<?> _class) {
 // 字段
 Field[] fieldArr = _class.getDeclaredFields();
 for (Field field : fieldArr) {
 ExcelUtils excel = field.getAnnotation(ExcelUtils.class);
 if (excel != null && (excel.type() == ExcelUtils.TypeEnum.EXPORT_AND_IMPORT || excel.type() == ExcelUtils.TypeEnum.ONLY_IMPORT)) {
 annotationList.add(new Object[]{excel, field});
 }
 }
 // 方法
 Method[] methodArr = _class.getDeclaredMethods();
 for (Method method : methodArr) {
 ExcelUtils excel = method.getAnnotation(ExcelUtils.class);
 if (excel != null && (excel.type() == ExcelUtils.TypeEnum.EXPORT_AND_IMPORT || excel.type() == ExcelUtils.TypeEnum.ONLY_IMPORT)) {
 annotationList.add(new Object[]{excel, method});
 }
 }
 // 排序
 Collections.sort(annotationList, new Comparator<Object[]>() {
 public int compare(Object[] o1, Object[] o2) {
 return new Integer(((ExcelUtils) o1[0]).sort()).compareTo(new Integer(((ExcelUtils) o2[0]).sort()));
 }
 
 ;
 });
 }
 
 /*******
 * 
 * 描述： 【获取列标题列表, 必须在handleAnnotation之后调用】
 * 
 */
 private void handleTitleList() {
 for (Object[] object : annotationList) {
 titleList.add(((ExcelUtils) object[0]).title());
 }
 }
 
 /*******
 * 
 * 描述： 【创建单元格样式】
 * 
 */
 private void createCellStyle() {
 // 表头
 CellStyle cellStyle = workbook.createCellStyle();
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 Font headerFont = workbook.createFont();
 headerFont.setFontName("宋体");
 headerFont.setColor(IndexedColors.WHITE.getIndex());
 headerFont.setFontHeightInPoints((short) 16);
 headerFont.setBold(true);
 cellStyle.setFont(headerFont);
 cellStyleMap.put("header", cellStyle);
 
 // 标题
 cellStyle = workbook.createCellStyle();
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 cellStyle.setBorderRight(BorderStyle.THIN);
 cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
 cellStyle.setBorderLeft(BorderStyle.THIN);
 cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
 cellStyle.setBorderTop(BorderStyle.DOUBLE);
 cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
 cellStyle.setBorderBottom(BorderStyle.THIN);
 cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
 cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 Font titleFont = workbook.createFont();
 titleFont.setFontName("宋体");
 titleFont.setColor(IndexedColors.WHITE.getIndex());
 titleFont.setFontHeightInPoints((short) 12);
 titleFont.setBold(true);
 cellStyle.setFont(titleFont);
 cellStyleMap.put("title", cellStyle);
 
 // 内容通用
 cellStyle = workbook.createCellStyle();
 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 cellStyle.setWrapText(true);
 // cellStyle.setBorderRight(BorderStyle.THIN);
 // cellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
 // cellStyle.setBorderLeft(BorderStyle.THIN);
 // cellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
 // cellStyle.setBorderTop(BorderStyle.THIN);
 // cellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
 // cellStyle.setBorderBottom(BorderStyle.THIN);
 // cellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
 Font dataFont = workbook.createFont();
 dataFont.setFontName("仿宋");
 dataFont.setFontHeightInPoints((short) 12);
 cellStyle.setFont(dataFont);
 cellStyleMap.put("common", cellStyle);
 
 // 左对齐
 cellStyle = workbook.createCellStyle();
 cellStyle.cloneStyleFrom(cellStyleMap.get("common"));
 cellStyle.setAlignment(HorizontalAlignment.LEFT);
 cellStyleMap.put("left", cellStyle);
 
 // 居中齐
 cellStyle = workbook.createCellStyle();
 cellStyle.cloneStyleFrom(cellStyleMap.get("common"));
 cellStyle.setAlignment(HorizontalAlignment.CENTER);
 cellStyleMap.put("center", cellStyle);
 
 // 右对齐
 cellStyle = workbook.createCellStyle();
 cellStyle.cloneStyleFrom(cellStyleMap.get("common"));
 cellStyle.setAlignment(HorizontalAlignment.RIGHT);
 cellStyleMap.put("right", cellStyle);
 }
 
 /*******
 * 
 * 描述： 【创建表头】
 * 
 */
 private void createHeaderRow() {
 Row headerRow = this.createRow();
 headerRow.setZeroHeight(false);
 headerRow.setHeightInPoints(37.5F);
 
 Cell headerCell = headerRow.createCell(0);
 headerCell.setCellStyle(cellStyleMap.get("header"));
 headerCell.setCellValue(header);
 // 合并单元格
 sheet.addMergedRegion(new CellRangeAddress(headerRow.getRowNum(), headerRow.getRowNum(), headerRow.getRowNum(),
 titleList.size() - 1));
 }
 
 private void createHeaderRowBySheet(SXSSFSheet sheet) {
 Row headerRow = this.createRowBySheet(sheet);
 headerRow.setZeroHeight(false);
 headerRow.setHeightInPoints(37.5F);
 
 Cell headerCell = headerRow.createCell(0);
 headerCell.setCellStyle(cellStyleMap.get("header"));
 headerCell.setCellValue(header);
 // 合并单元格
 sheet.addMergedRegion(new CellRangeAddress(headerRow.getRowNum(), headerRow.getRowNum(), headerRow.getRowNum(),
 titleList.size() - 1));
 }
 
 /*******
 * 
 * 描述： 【创建标题】
 * 
 */
 private void createTitleRow() {
 Row titleRow = this.createRow();
 titleRow.setZeroHeight(false);
 titleRow.setHeightInPoints(19F);
 for (int i = 0; i < titleList.size(); i++) {
 Cell cell = titleRow.createCell(i);
 cell.setCellStyle(cellStyleMap.get("title"));
 cell.setCellValue(titleList.get(i));
 }
 }
 
 private void createTitleRowByList(SXSSFSheet sheet) {
 Row titleRow = this.createRowBySheet(sheet);
 titleRow.setZeroHeight(false);
 titleRow.setHeightInPoints(19F);
 for (int i = 0; i < titleList.size(); i++) {
 Cell cell = titleRow.createCell(i);
 cell.setCellStyle(cellStyleMap.get("title"));
 cell.setCellValue(titleList.get(i));
 }
 }
 
 /*******
 * 
 * 描述： 【创建内容】
 * 
 */
 private void createContentRow() {
 for (Object dataObject : dataList) {
 Row contentRow = this.createRow();
 contentRow.setZeroHeight(false);
 contentRow.setHeightInPoints(18F);
 int column = 0;
 for (Object[] annotation : annotationList) {
 ExcelUtils excel = (ExcelUtils) annotation[0];
 Object value = null;
 try {
 if (annotation[1] instanceof Field) {
 value = ReflectUtils.invokeGetter(dataObject, ((Field) annotation[1]).getName());
 } else if (annotation[1] instanceof Method) {
 value = ReflectUtils.invokeMethod(dataObject, ((Method) annotation[1]).getName(), new Class[]{},
 new Object[]{});
 }
 } catch (Exception e) {
 logger.error("createContentRow-error:{}", e.getMessage());
 value = "";
 }
 if (StringUtils.isNotBlank(excel.append())) {
 value = String.valueOf(value) + excel.append();
 }
 String replaceStr = excel.replace();
 if (StringUtils.isNotBlank(replaceStr)) {
 value = String.valueOf(value);
 try {
 String[] keyValuesArr = replaceStr.split(",");
 for (String keyValueStr : keyValuesArr) {
 String[] keyValueArr = keyValueStr.split(":");
 if (value.equals(keyValueArr[0])) {
 value = keyValueArr[1];
 break;
 }
 }
 } catch (Exception e) {
 logger.warn("createContentRow.replace-error:{}", e.getMessage());
 }
 }
 this.createCell(contentRow, column++, value, excel.align());
 }
 }
 // 所有内容填充完, 重新调整列宽
 sheet.trackAllColumnsForAutoSizing();
 for (int i = 0; i < titleList.size(); i++) {
 sheet.autoSizeColumn(i);
 }
 }
 
 private void createContentRowByList(List<?> dataList,SXSSFSheet sheet) {
 for (Object dataObject : dataList) {
 Row contentRow = this.createRowBySheet(sheet);
 contentRow.setZeroHeight(false);
 contentRow.setHeightInPoints(18F);
 int column = 0;
 for (Object[] annotation : annotationList) {
 ExcelUtils excel = (ExcelUtils) annotation[0];
 Object value = null;
 try {
 if (annotation[1] instanceof Field) {
 value = ReflectUtils.invokeGetter(dataObject, ((Field) annotation[1]).getName());
 } else if (annotation[1] instanceof Method) {
 value = ReflectUtils.invokeMethod(dataObject, ((Method) annotation[1]).getName(), new Class[]{},
 new Object[]{});
 }
 } catch (Exception e) {
 logger.error("createContentRow-error:{}", e.getMessage());
 value = "";
 }
 if (StringUtils.isNotBlank(excel.append())) {
 value = String.valueOf(value) + excel.append();
 }
 String replaceStr = excel.replace();
 if (StringUtils.isNotBlank(replaceStr)) {
 value = String.valueOf(value);
 try {
 String[] keyValuesArr = replaceStr.split(",");
 for (String keyValueStr : keyValuesArr) {
 String[] keyValueArr = keyValueStr.split(":");
 if (value.equals(keyValueArr[0])) {
 value = keyValueArr[1];
 break;
 }
 }
 } catch (Exception e) {
 logger.warn("createContentRow.replace-error:{}", e.getMessage());
 }
 }
 this.createCell(contentRow, column++, value, excel.align());
 }
 }
 //如果为空，合并数据格式，填写暂无数据
 if(dataList!=null && dataList.size()==0){
 Row endRow = this.createRowBySheet(sheet);
 Cell headerCell = endRow.createCell(0);
 headerCell.setCellStyle(cellStyleMap.get("header"));
 headerCell.setCellValue("暂无数据");
 // 合并单元格
 sheet.addMergedRegion(new CellRangeAddress(endRow.getRowNum(), endRow.getRowNum(), 0,
 titleList.size() - 1));
 }
 // 所有内容填充完, 重新调整列宽
 sheet.trackAllColumnsForAutoSizing();
 for (int i = 0; i < titleList.size(); i++) {
 sheet.autoSizeColumn(i);
 }
 }
 
 /*******
 * 
 * 描述： 【创建行】
 * 
 */
 private Row createRow() {
 return sheet.createRow(rownum++);
 }
 
 private Row createRowBySheet(SXSSFSheet sheet) {
 return sheet.createRow(rownum++);
 }
 
 /*******
 * 
 * 描述： 【创建单元格】
 * 
 */
 private void createCell(Row row, int column, Object value, ExcelUtils.AlignEnum align) {
 Cell cell = row.createCell(column);
 CellStyle cellStyle = null;
 if (ExcelUtils.AlignEnum.LEFT == align) {
 cellStyle = cellStyleMap.get("left");
 } else if (ExcelUtils.AlignEnum.CENTER == align) {
 cellStyle = cellStyleMap.get("center");
 } else if (ExcelUtils.AlignEnum.RIGHT == align) {
 cellStyle = cellStyleMap.get("right");
 } else {
 cellStyle = cellStyleMap.get("common");
 }
 try {
 if (value == null) {
 cell.setCellValue("");
 } else if (value instanceof String) {
 if(((String) value).startsWith("http")){//如果字符串是以http开头的 自动转成超链接格子
 cell.setCellFormula("HYPERLINK(\"" +value + "\",\""+ value +"\")");
 }else {
 cell.setCellValue((String) value);
 }
 } else if (value instanceof Integer) {
 cell.setCellValue((Integer) value + "");
 } else if (value instanceof Long) {
 cell.setCellValue((Long) value + "");
 } else if (value instanceof Double) {
 cell.setCellValue((Double) value + "");
 } else if (value instanceof Float) {
 cell.setCellValue((Float) value + "");
 } else if (value instanceof Date) {
 DataFormat format = workbook.createDataFormat();
 cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
 cell.setCellValue((Date) value);
 } else if (value instanceof LocalDateTime) {
 DataFormat format = workbook.createDataFormat();
 cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
 cell.setCellValue(Date.from(((LocalDateTime) value).atZone(ZoneId.systemDefault()).toInstant()));
 } else if (value instanceof BigDecimal) {
 double doubleVal = ((BigDecimal) value).doubleValue();
 cell.setCellValue(doubleVal + "");
 } else {
 cell.setCellValue((String) Class
 .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
 "fieldtype." + value.getClass().getSimpleName() + "Type"))
 .getMethod("setValue", Object.class).invoke(null, value));
 }
 } catch (Exception e) {
 logger.error("createCell-error:{}", e.getMessage());
 cell.setCellValue(value.toString());
 }
 cell.setCellStyle(cellStyle);
 }
 
 /*******
 * 
 * 描述： 【输出到客户端】
 * 
 */
 private void write() throws IOException {
 //response.reset();
 //response.setContentType("application/octet-stream; charset=utf-8");
 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
 response.setHeader("Content-Disposition",
 "attachment; filename=" + handleFilename(URLEncoder.encode(header, "UTF-8")));
 workbook.write(response.getOutputStream());
 }
 
 /*******
 * 
 * 描述： 【文件名处理】
 * 
 *
 * @param filename 文件名
 */
 private String handleFilename(String filename) {
 StringBuffer filenameSb = new StringBuffer();
 filenameSb.append(filename).append("_").append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))).append(".xlsx");
 return filenameSb.toString();
 }
 
 /*******
 * 
 * 描述： 【最后行号】
 * 
 */
 private int getImportLastRowNum() {
 return this.importSheet.getLastRowNum() + firstRowNum - 1;
 }
 
 /*******
 * 
 * 描述： 【获取单元格值】
 * 
 *
 * @param row 行
 * @param column 列号
 */
 private Object getCellValue(Row row, int column) {
 Object val = "";
 try {
 Cell cell = row.getCell(column);
 if (cell != null) {
 if (cell.getCellType() == CellType.NUMERIC) {
 if (HSSFDateUtil.isCellDateFormatted(cell)) {
 double d = cell.getNumericCellValue();
 Date date = HSSFDateUtil.getJavaDate(d);
 SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
 val = dformat.format(date);
 } else {
 NumberFormat nf = NumberFormat.getInstance();
 nf.setGroupingUsed(false);// true时的格式：1,234,567,890
 val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换
 }
 } else if (cell.getCellType() == CellType.STRING) {
 val = cell.getStringCellValue();
 } else if (cell.getCellType() == CellType.FORMULA) {
 val = cell.getCellFormula();
 } else if (cell.getCellType() == CellType.BOOLEAN) {
 val = cell.getBooleanCellValue();
 } else if (cell.getCellType() == CellType.ERROR) {
 val = cell.getErrorCellValue();
 }
 }
 } catch (Exception e) {
 return val;
 }
 return val;
 }
 
 
 
 }

注意其中poi的包是4.1.2版本

<poi.version>4.1.2</poi.version>