package com.hx.util; import com.hx.exception.TipsException; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.*; import org.springframework.web.multipart.MultipartFile; import javax.imageio.ImageIO; import java.awt.image.BufferedImage; import java.io.*; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; /** * * @author hjr */ public final class ExcelUtil { /** * @param excelName * 文件名称 * @param outPath * 保存路径 * @param headList * Excel文件Head标题集合 * @param fieldList * Excel文件Field标题集合 根据field来寻找位置填充表格 * @param dataList * Excel文件数据内容部分 * @throws Exception */ public static String createExcel(String outPath, String excelName, String[] headList, String[] fieldList, List> dataList) throws Exception { String filePath = null; // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow(0); // =============================================================== HSSFCell cell; for (int i = 0; i < headList.length; i++) { // 在索引0的位置创建单元格(左上端) cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); } // =============================================================== if (dataList != null) { HSSFRow row_value; Map dataMap; for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 row_value = sheet.createRow(n + 1); dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) cell = row_value.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(objToString(dataMap.get(fieldList[i]))); } // =============================================================== } } // 新建一输出文件流 File file = SimpleTool.createFile(outPath, excelName); FileOutputStream fOut = new FileOutputStream(file); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); if(outPath.endsWith("/")){ filePath = outPath + excelName; }else{ filePath = outPath +"/"+ excelName; } return filePath; } /**生成临时文件 * @param headList * Excel文件Head标题集合 * @param fieldList * Excel文件Field标题集合 根据field来寻找位置填充表格 * @param dataList * Excel文件数据内容部分 * @throws Exception */ public static File createExcel(String[] headList, String[] fieldList, List> dataList) throws Exception { File file = File.createTempFile("temp", ".xlsx"); try{ // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow(0); // =============================================================== HSSFCell cell; for (int i = 0; i < headList.length; i++) { // 在索引0的位置创建单元格(左上端) cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); } // =============================================================== if (dataList != null) { HSSFRow row_value; Map dataMap; for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 row_value = sheet.createRow(n + 1); dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) cell = row_value.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(objToString(dataMap.get(fieldList[i]))); } // =============================================================== } } // 新建一输出文件流 FileOutputStream fOut = new FileOutputStream(file); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); }catch (Exception e){ }finally { file.deleteOnExit(); } return file; } /**生成临时文件 * @param headList * Excel文件Head标题集合 * @param fieldList * Excel文件Field标题集合 根据field来寻找位置填充表格 * @param dataList * Excel文件数据内容部分 * @throws Exception */ public static File createExcel(String[] headList, String[] fieldList, List> dataList ,Integer height,Integer width) throws Exception { File file = File.createTempFile("temp", ".xls"); try{ if(height == null){ height = 450; } if(width == null){ width = 5000; } // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //合并的单元格样式 HSSFCellStyle boderStyle = workbook.createCellStyle(); //垂直居中 boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow(0); // =============================================================== HSSFCell cell; for (int i = 0; i < headList.length; i++) { //高度 row.setHeight(height.shortValue()); sheet.setColumnWidth(i,width); // 在索引0的位置创建单元格(左上端) cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); cell.setCellStyle(boderStyle); } // =============================================================== if (dataList != null) { HSSFRow row_value; Map dataMap; for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 row_value = sheet.createRow(n + 1); row_value.setHeight(height.shortValue()); dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) sheet.setColumnWidth(i,width); cell = row_value.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(objToString(dataMap.get(fieldList[i]))); cell.setCellStyle(boderStyle); } // =============================================================== } } // 新建一输出文件流 FileOutputStream fOut = new FileOutputStream(file); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); }catch (Exception e){ }finally { file.deleteOnExit(); } return file; } /**无限制行数生成ecxel,生成临时文件 * @param headList * Excel文件Head标题集合 * @param fieldList * Excel文件Field标题集合 根据field来寻找位置填充表格 * @param dataList * Excel文件数据内容部分 * @param height 单元格高度,默认450 * @param width 单元格宽度,默认5000 * @throws Exception */ public static File createXSSExcel(String[] headList, String[] fieldList, List> dataList ,Integer height,Integer width) throws Exception { File file = File.createTempFile("temp", ".xls"); try{ if(height == null){ height = 450; } if(width == null){ width = 5000; } // 创建新的Excel 工作簿 //XSSFWorkbook workbook = new XSSFWorkbook(); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); //合并的单元格样式 CellStyle boderStyle = workbook.createCellStyle(); //垂直居中 boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); Sheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) Row row = sheet.createRow(0); // =============================================================== Cell cell; for (int i = 0; i < headList.length; i++) { //高度 row.setHeight(height.shortValue()); sheet.setColumnWidth(i,width); // 在索引0的位置创建单元格(左上端) cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); cell.setCellStyle(boderStyle); } // =============================================================== if (dataList != null) { Row row_value; Map dataMap; for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 row_value = sheet.createRow(n + 1); row_value.setHeight(height.shortValue()); dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) sheet.setColumnWidth(i,width); cell = row_value.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(objToString(dataMap.get(fieldList[i]))); cell.setCellStyle(boderStyle); } // =============================================================== } } // 新建一输出文件流 FileOutputStream fOut = new FileOutputStream(file); // 把相应的Excel 工作簿存盘 workbook.write(fOut); fOut.flush(); // 操作结束,关闭文件 fOut.close(); }catch (Exception e){ }finally { file.deleteOnExit(); } return file; } private static String objToString(Object obj) { if (obj == null) { return ""; } else { if (obj instanceof String) { return (String) obj; } else if (obj instanceof Date) { return null;// DateUtil.dateToString((Date) // obj,DateUtil.DATESTYLE_SHORT_EX); } else { return obj.toString(); } } } /** * 读取Excel数据 * @param file * @param header * @return * @throws Exception */ public static List> readExcelData(MultipartFile file, boolean header) throws Exception { String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } //判断不同格式处理方法不同 if(fileName.matches("^.+\\.(?i)(xls)$")){ //xls格式使用HSSF return readExcelByeFileData(file, header); }else{ //xlsx格式使用XSSF return readExcelByeFileDataToXSSF(file, header); } } /** * 读取 Excel文件内容 * * @param file * @param header 是否包括表头 * @return * @throws Exception */ public static List> readExcelByeFileData(MultipartFile file, boolean header) throws Exception { String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream()); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){ arrayString.add(cell.getStringCellValue().trim()); }else{ arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE"); } //arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } } return list; } /** * 读取 Excel文件内容 * * @param file * @param header 是否包括表头 * @return * @throws Exception */ public static List> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception { String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream()); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { XSSFCell cell = xssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){ arrayString.add(cell.getStringCellValue().trim()); }else{ arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE"); } //arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } } return list; } /**判断excel的版本*/ public static Workbook create(InputStream inp) throws Exception { if (!inp.markSupported()) { inp = new PushbackInputStream(inp, 8); } if (POIFSFileSystem.hasPOIFSHeader(inp)) { return new HSSFWorkbook(inp); } if (POIXMLDocument.hasOOXMLHeader(inp)) { return new XSSFWorkbook(OPCPackage.open(inp)); } throw new IllegalArgumentException("你的excel版本目前poi解析不了"); } /**读取excel文件,兼容2003和2007 * 通过流读取Excel文件 * @return * @throws Exception */ public static List> getExcelDataCompatible(File file,boolean header) throws Exception { try { String fileName = file.getName(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); Workbook book = create(new BufferedInputStream(new FileInputStream(file))); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { Cell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString.add(cell.getStringCellValue().trim()); //arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } /**读取excel文件,兼容2003和2007 * 通过流读取Excel文件 * @return * @throws Exception */ public static List> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception { try { String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); Workbook book = create(new BufferedInputStream(file.getInputStream())); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { Cell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString.add(cell.getStringCellValue().trim()); //arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } /**读取excel文件,兼容2003和2007 * 通过流读取Excel文件 * @return * @throws Exception */ public static List> getExcelDataCompatibleCheckEmpty(File file,boolean header) throws Exception { try { String fileName = file.getName(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); Workbook book = create(new BufferedInputStream(new FileInputStream(file))); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 //不为空 boolean isNotEmpty = false; for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { Cell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } isNotEmpty = true; } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString.add(cell.getStringCellValue().trim()); //arrayString[i] = cell.getStringCellValue().trim(); isNotEmpty = true; } } if (isNotEmpty){ list.add(arrayString); } } } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } /**读取excel文件,兼容2003和2007 * 通过流读取Excel文件 * @return * @throws Exception */ public static List> getExcelDataCompatibleCheckEmpty(MultipartFile file,boolean header) throws Exception { try { String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new TipsException("上传文件格式不正确"); } // 结果集 List> list = new ArrayList<>(); Workbook book = create(new BufferedInputStream(file.getInputStream())); // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 //不为空 boolean isNotEmpty = false; for(int s=0;s arrayString = new ArrayList<>(); for (int i = 0; i < col; i++) { Cell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { short format = cell.getCellStyle().getDataFormat(); if(format == 14 || format == 31 || format == 57 || format == 58){ //日期(中文时间格式的) Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); }else if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); arrayString.add(formater.format(d)); //arrayString[i] = formater.format(d); } else { if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ arrayString.add(cell.getStringCellValue()); //arrayString[i] =cell.getStringCellValue(); }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ arrayString.add(cell.getCellFormula()); //arrayString[i] =cell.getCellFormula(); }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); arrayString.add(dataFormatter.formatCellValue(cell)); //arrayString[i] =dataFormatter.formatCellValue(cell); } } } isNotEmpty = true; } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ arrayString.add(""); //arrayString[i] = ""; } else { // 如果EXCEL表格中的数据类型为字符串型 arrayString.add(cell.getStringCellValue().trim()); isNotEmpty = true; //arrayString[i] = cell.getStringCellValue().trim(); } } if (isNotEmpty){ list.add(arrayString); } } } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } private static String getCellVal(Cell cell) { if (null == cell) { return ""; } switch (cell.getCellType()) { // 数字 case HSSFCell.CELL_TYPE_NUMERIC: // 日期格式的处理 if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString(); } return String.valueOf(cell.getNumericCellValue()); // 字符串 case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); // 公式 case HSSFCell.CELL_TYPE_FORMULA: return cell.getCellFormula(); // 空白 case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() + ""; // 错误类型 case HSSFCell.CELL_TYPE_ERROR: return cell.getErrorCellValue() + ""; default: break; } return ""; } /** * p判断是否excel文件 * @param file * @return */ public static boolean isExcel(MultipartFile file){ String fileName = file.getOriginalFilename(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { return false; } return true; } public static File createExcelByImg(String[] headList, String[] fieldList, List> dataList, Integer height, Integer width) throws Exception { File file = File.createTempFile("temp", ".xls"); FileOutputStream fileOut = null; BufferedImage bufferImg = null; try { ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); if (height == null) { height = 450; } if (width == null) { width = 1000; } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle boderStyle = workbook.createCellStyle(); boderStyle.setVerticalAlignment((short)1); boderStyle.setAlignment((short)2); HSSFSheet sheet = workbook.createSheet(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFRow row = sheet.createRow(0); HSSFCell anchor; for(int i = 0; i < headList.length; ++i) { row.setHeight(height.shortValue()); sheet.setColumnWidth(i, width); anchor = row.createCell(i); anchor.setCellType(1); anchor.setCellValue(headList[i]); anchor.setCellStyle(boderStyle); } HSSFRow row_value = null; anchor = null; HSSFCell cell = null; if (dataList != null) { for(int n = 0; n < dataList.size(); ++n) { row_value = sheet.createRow(n + 1); row_value.setHeight(height.shortValue()); Map dataMap = (Map)dataList.get(n); for(int i = 0; i < fieldList.length; ++i) { sheet.setColumnWidth(i, width); cell = row_value.createCell(i); cell.setCellType(1); Object value = dataMap.get(fieldList[i]); if (value != null && "class java.io.File".equals(value.getClass().toString())) { File file2 = (File)value; if (file2 == null) { cell.setCellValue(""); } else { bufferImg = ImageIO.read(file2); ImageIO.write(bufferImg, "jpg", byteArrayOut); HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1); anchor1.setAnchorType(0); patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5)); } } else { cell.setCellValue(objToString(dataMap.get(fieldList[i]))); cell.setCellStyle(boderStyle); } } } } FileOutputStream fOut = new FileOutputStream(file); workbook.write(fOut); fOut.flush(); fOut.close(); } catch (Exception var25) { var25.printStackTrace(); } finally { file.deleteOnExit(); } return file; } }