package com.artfess.poi.util; import com.alibaba.fastjson.JSON; import com.artfess.base.annotation.Excel; import com.artfess.poi.DefaultExcelStyle; import com.artfess.poi.style.font.BoldWeight; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.usermodel.HSSFFont; 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.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.ResourceUtils; import org.springframework.util.StringUtils; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.ByteArrayOutputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.time.*; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Pattern; /** * Excel相关处理 * * @author min.wu */ public class ExcelUtils { public final static String EXCEL2003 = ".xls"; private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class); public Class clazz; public ExcelUtils(Class clazz) { this.clazz = clazz; } // 正则表达式,用于匹配整数或小数 private static final Pattern NUMBER_PATTERN = Pattern.compile("^[-+]?\\d+(\\.\\d+)?$"); public List importExcel(String sheetName, InputStream input,Integer startRow) throws Exception { List list = new ArrayList(); Workbook workbook = WorkbookFactory.create(input); Sheet sheet; if (!StringUtils.isEmpty(sheetName)) { sheet = workbook.getSheet(sheetName); // 如果指定sheet名,则取指定sheet中的内容. } else { sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet. } int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { // 有数据时才处理 Field[] allFields = clazz.getDeclaredFields(); // 得到类的所有field. Map fieldsMap = new HashMap(); // 定义一个map用于存放列的序号和field. for (Field field : allFields) { // 将有注解的field存放到map中. if (field.isAnnotationPresent(Excel.class)) { Excel attr = field.getAnnotation(Excel.class); int col = getExcelCol(attr.column());// 获得列号 field.setAccessible(true);// 设置类的私有字段属性可访问. fieldsMap.put(col, field); } } for (int i = startRow; i < rows; i++) { // 从第2行开始取数据,默认第一行是表头. Row row = sheet.getRow(i); if (null == row) { continue; } int cellNum = sheet.getRow(0).getPhysicalNumberOfCells(); T entity = null; for (int j = 0; j < cellNum; j++) { Cell cell = row.getCell(j); if (cell == null) { continue; } else { // 先设置Cell的类型,然后就可以把纯数字作为String类型读进来了 by zhuyangyong 20171228 row.getCell(j).setCellType(CellType.STRING); cell = row.getCell(j); } String c = cell.getStringCellValue(); if (StringUtils.isEmpty(c) || "NULL".equals(c)) { continue; } entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建. Field field = fieldsMap.get(j);// 从map中得到对应列的field. if (null == field) { continue; } // 取得类型,并根据对象类型设置值. Class fieldType = field.getType(); if (cell.getCellType() == CellType.STRING) { c = cell.getStringCellValue(); } else if (cell.getCellType() == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (LocalDate.class == fieldType) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); c = sdf.format(date); } else if (LocalDateTime.class == fieldType) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); c = sdf.format(date); } } else { if (LocalDate.class == fieldType) { // 处理数字格式的日期 double numericValue = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(numericValue); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); c = sdf.format(date); } else { c = String.valueOf(cell.getNumericCellValue()); } } } try { if (String.class == fieldType) { field.set(entity, c.trim()); } else if (Integer.TYPE == fieldType || Integer.class == fieldType) { if (!c.isEmpty()) { field.set(entity, Integer.parseInt(c)); } else { field.set(entity, 0); } } else if (Long.TYPE == fieldType || Long.class == fieldType) { if (!c.isEmpty()) { field.set(entity, Long.valueOf(c)); } else { field.set(entity, 0L); } } else if (Float.TYPE == fieldType || Float.class == fieldType) { if (!c.isEmpty()) { field.set(entity, Float.valueOf(c)); } else { field.set(entity, 0.0f); } } else if (Short.TYPE == fieldType || Short.class == fieldType) { if (!c.isEmpty()) { field.set(entity, Short.valueOf(c)); } else { field.set(entity, (short) 0); } } else if (Double.TYPE == fieldType || Double.class == fieldType) { if (!c.isEmpty()) { field.set(entity, Double.valueOf(c)); } else { field.set(entity, 0.0); } } else if (Character.TYPE == fieldType) { if (c != null && c.length() > 0) { field.set(entity, c.charAt(0)); } } else if (LocalDate.class == fieldType) { if (!c.isEmpty()) { boolean matches = NUMBER_PATTERN.matcher(c).matches(); LocalDate localDate; if (matches){ Double aDouble = Double.valueOf(c); // 将 Double 类型的时间戳转换为localDate localDate = convertExcelNumberToLocalDate(aDouble); }else { DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); localDate = LocalDate.parse(c, formatter); } field.set(entity, localDate); } } else if (LocalDateTime.class == fieldType) { if (!c.isEmpty()) { boolean matches = NUMBER_PATTERN.matcher(c).matches(); LocalDateTime localDateTime; if (matches){ Double aDouble = Double.valueOf(c); // 将 Double 类型的时间戳转换为localDateTime localDateTime=convertExcelNumberToLocalDateTime(aDouble); }else { DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); localDateTime = LocalDateTime.parse(c, formatter); } field.set(entity, localDateTime); } } else if (BigDecimal.class == fieldType) { if (!c.isEmpty()) { field.set(entity, new BigDecimal(c)); } else { field.set(entity, BigDecimal.ZERO); } } } catch (Exception e) { System.err.println("数据转换异常: " + e.getMessage()); } } if (entity != null) { try { list.add(entity); }catch (Exception e){ log.error("导入失败,异常信息:{},数据信息:{}", e.getMessage(), JSON.toJSON(entity)); } } } } return list; } // 将 Excel 数字时间转换为 LocalDateTime private static LocalDateTime convertExcelNumberToLocalDateTime(double excelValue) { // Excel 时间基准 (Windows 版本,1900 年 1 月 1 日) LocalDate baseDate = LocalDate.of(1899, 12, 30); // 注意 Excel 的 1900 年闰年错误 long days = (long) excelValue; double fraction = excelValue - days; // 计算日期部分 LocalDate date = baseDate.plusDays(days); // 计算时间部分(秒 = 24*60*60 * fraction) long seconds = (long) (24 * 60 * 60 * fraction); LocalTime time = LocalTime.ofSecondOfDay(seconds); return LocalDateTime.of(date, time); } // Excel 数字 → LocalDate(忽略时间部分) private static LocalDate convertExcelNumberToLocalDate(double excelValue) { // Excel 基准日期(Windows 版本,1900-01-01,但有 1900 闰年错误) LocalDate baseDate = LocalDate.of(1899, 12, 30); // 调整基准 long days = (long) excelValue; // 取整数部分(天数) return baseDate.plusDays(days); } /** * 对list数据源将其里面的数据导入到excel表单 * * @param sheetName 工作表的名称 */ public void exportExcel(HttpServletResponse response, HttpServletRequest request, List list, String sheetName) { try { HSSFWorkbook workbook = createHssfWorkbook(response, request, list, sheetName); String filename = encodingFilename(sheetName); //TODO 2021.11.26 LZ 此处不再进行输出流操作 // OutputStream out = new FileOutputStream(getfile() + filename); // workbook.write(out); // out.close(); this.downloadExcel(workbook, filename, response); } catch (Exception e) { log.error("关闭flush失败{}", e); } } private HSSFWorkbook createHssfWorkbook(HttpServletResponse response, HttpServletRequest request, List list, String sheetName){ // 得到所有定义字段 Field[] allFields = clazz.getDeclaredFields(); List fields = new ArrayList(); // 得到所有field并存放到一个list中. for (Field field : allFields) { if (field.isAnnotationPresent(Excel.class)) { fields.add(field); } } // 产生工作薄对象 HSSFWorkbook workbook = new HSSFWorkbook(); // excel2003中每个sheet中最多有65536行 int sheetSize = 65536; // 取出一共有多少个sheet. double sheetNo = Math.ceil(list.size() / sheetSize); for (int index = 0; index <= sheetNo; index++) { // 产生工作表对象 HSSFSheet sheet = workbook.createSheet(); // 冻结单元格 sheet.createFreezePane(0, 1, 0, 1); if (sheetNo == 0) { workbook.setSheetName(index, sheetName); } else { // 设置工作表的名称. workbook.setSheetName(index, sheetName + index); } // 产生一行 HSSFRow row = sheet.createRow(0); // 产生单元格 HSSFCell cell = row.createCell(0); // 写入各个字段的列头名称 for (int i = 0; i < fields.size(); i++) { Field field = fields.get(i); Excel attr = field.getAnnotation(Excel.class); // 获得列号 int col = getExcelCol(attr.column()); // 创建列 cell = row.createCell(col); // 设置列中写入内容为String类型 cell.setCellType(CellType.STRING); HSSFCellStyle cellStyle = workbook.createCellStyle(); //居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //垂直 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); if (attr.name().indexOf("注:") >= 0) { HSSFFont font = workbook.createFont(); font.setColor(HSSFFont.COLOR_RED); cellStyle.setFont(font); cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); sheet.setColumnWidth(i, 6000); } else { HSSFFont font = workbook.createFont(); font.setBold(BoldWeight.BOLD.getWeight()); // 选择需要用到的字体格式 cellStyle.setFont(font); cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); // 设置列宽 sheet.setColumnWidth(i, 3766); } DefaultExcelStyle defaultExcelStyle = new DefaultExcelStyle(); cellStyle.setFillPattern(defaultExcelStyle.getFillPattern()); cellStyle.setWrapText(true); cell.setCellStyle(cellStyle); // 写入列名 cell.setCellValue(attr.name()); // 如果设置了提示信息则鼠标放上去提示. if (!attr.prompt().trim().equals("")) { // 这里默认设了2-101列提示. setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col); } // 如果设置了combo属性则本列只能选择不能输入 if (attr.combo().length > 0) { // 这里默认设了2-101列只能选择不能输入. setHSSFValidation(sheet, attr.combo(), 1, 100, col, col); } } int startNo = index * sheetSize; int endNo = Math.min(startNo + sheetSize, list.size()); // 写入各条记录,每条记录对应excel表中的一行 HSSFCellStyle cs = workbook.createCellStyle(); // 居中 cs.setAlignment(HorizontalAlignment.CENTER); //垂直 cs.setVerticalAlignment(VerticalAlignment.CENTER); for (int i = startNo; i < endNo; i++) { row = sheet.createRow(i + 1 - startNo); // 得到导出对象. T vo = (T) list.get(i); for (int j = 0; j < fields.size(); j++) { // 获得field. Field field = fields.get(j); // 设置实体类私有属性可访问 field.setAccessible(true); Excel attr = field.getAnnotation(Excel.class); try { // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列. if (attr.isExport()) { // 创建cell cell = row.createCell(getExcelCol(attr.column())); cell.setCellStyle(cs); try { if (String.valueOf(field.get(vo)).length() > 10) { throw new Exception("长度超过10位就不用转数字了"); } // 如果可以转成数字则导出为数字类型 BigDecimal bc = new BigDecimal(String.valueOf(field.get(vo))); cell.setCellType(CellType.NUMERIC); cell.setCellType(CellType.STRING); cell.setCellValue(bc.doubleValue()); } catch (Exception e) { cell.setCellType(CellType.STRING); if (vo == null) { // 如果数据存在就填入,不存在填入空格. cell.setCellValue(""); } else { // 如果数据存在就填入,不存在填入空格. cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo))); } } } } catch (Exception e) { log.error("导出Excel失败{}", e); } } } } return workbook; } public void downloadExcel(HSSFWorkbook workBook, String fileName, HttpServletResponse response) throws IOException { String filedisplay = URLEncoder.encode(fileName, "utf-8"); response.setContentType("APPLICATION/OCTET-STREAM"); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay); response.addHeader("filename", filedisplay); OutputStream os = null; try { os = response.getOutputStream(); workBook.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (os != null){ os.close(); } } } /** * 将EXCEL中A,B,C,D,E列映射成0,1,2,3 * * @param col */ public static int getExcelCol(String col) { col = col.toUpperCase(); // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。 int count = -1; char[] cs = col.toCharArray(); for (int i = 0; i < cs.length; i++) { count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i); } return count; } /** * 设置单元格上提示 * * @param sheet 要设置的sheet. * @param promptTitle 标题 * @param promptContent 内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 * @return 设置好的sheet. */ public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) { // 构造constraint对象 DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1"); // 四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); // 数据有效性对象 HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint); data_validation_view.createPromptBox(promptTitle, promptContent); sheet.addValidationData(data_validation_view); return sheet; } /** * 设置某些列的值只能输入预制的数据,显示下拉框. * * @param sheet 要设置的sheet. * @param textlist 下拉框显示的内容 * @param firstRow 开始行 * @param endRow 结束行 * @param firstCol 开始列 * @param endCol 结束列 * @return 设置好的sheet. */ public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) { // 加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist); // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); // 数据有效性对象 HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint); sheet.addValidationData(data_validation_list); return sheet; } /** * 编码文件名 */ public String encodingFilename(String filename) { Date now = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss"); filename = filename + "_" + dateFormat.format(now) + EXCEL2003; return filename; } public String getfile() throws FileNotFoundException { return ResourceUtils.getURL("classpath:").getPath(); } public byte[] getByte(HttpServletResponse response, HttpServletRequest request, List list, String fileName) { HSSFWorkbook workbook = createHssfWorkbook(response, request, list, fileName); ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { workbook.write(bos); } catch (IOException e) { } finally { try { bos.close(); workbook.close(); } catch (IOException e) { } } byte[] bytes = bos.toByteArray(); return bytes; } public List importQuestionExcel(String sheetName, InputStream input) throws Exception { List list = new ArrayList(); Workbook workbook = WorkbookFactory.create(input); Sheet sheet; if (!StringUtils.isEmpty(sheetName)) { sheet = workbook.getSheet(sheetName); // 如果指定sheet名,则取指定sheet中的内容. } else { sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet. } int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { // 有数据时才处理 Field[] allFields = clazz.getDeclaredFields(); // 得到类的所有field. Map fieldsMap = new HashMap(); // 定义一个map用于存放列的序号和field. for (Field field : allFields) { // 将有注解的field存放到map中. if (field.isAnnotationPresent(Excel.class)) { Excel attr = field.getAnnotation(Excel.class); int col = getExcelCol(attr.column());// 获得列号 field.setAccessible(true);// 设置类的私有字段属性可访问. fieldsMap.put(col, field); } } for (int i = 2; i < rows; i++) { // 从第2行开始取数据,默认第一行是表头. Row row = sheet.getRow(i); if (null == row) { continue; } int cellNum = sheet.getRow(0).getPhysicalNumberOfCells(); T entity = null; for (int j = 0; j < cellNum; j++) { Cell cell = row.getCell(j); if (cell == null) { continue; } else { // 先设置Cell的类型,然后就可以把纯数字作为String类型读进来了 by zhuyangyong 20171228 row.getCell(j).setCellType(CellType.STRING); cell = row.getCell(j); } String c = cell.getStringCellValue(); if (StringUtils.isEmpty(c) || "NULL".equals(c)) { continue; } entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建. Field field = fieldsMap.get(j);// 从map中得到对应列的field. if (null == field) { continue; } // 取得类型,并根据对象类型设置值. Class fieldType = field.getType(); if (String.class == fieldType) { field.set(entity, String.valueOf(c).trim()); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { field.set(entity, Integer.parseInt(c)); } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) { field.set(entity, Long.valueOf(c)); } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) { field.set(entity, Float.valueOf(c)); } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) { field.set(entity, Short.valueOf(c)); } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { field.set(entity, Double.valueOf(c)); } else if (Character.TYPE == fieldType) { if ((c != null) && (c.length() > 0)) { field.set(entity, Character.valueOf(c.charAt(0))); } } else if (cell.getCellType() == CellType.NUMERIC) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cell.setCellValue(sdf.format(cell.getNumericCellValue())); c = sdf.format(cell.getNumericCellValue()); field.set(entity, LocalDate.parse(c)); } else if (BigDecimal.class == fieldType) { c = cell.getStringCellValue(); field.set(entity, new BigDecimal(c)); } } if (entity != null) { try { list.add(entity); }catch (Exception e){ log.error("导入失败,异常信息:{},数据信息:{}", e.getMessage(), JSON.toJSON(entity)); } } } } return list; } }