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.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; 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.springframework.web.multipart.MultipartFile; 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); // =============================================================== for (int i = 0; i < headList.length; i++) { // 在索引0的位置创建单元格(左上端) HSSFCell cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); } // =============================================================== if (dataList != null) { for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 HSSFRow row_value = sheet.createRow(n + 1); Map dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) HSSFCell 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", ".xls"); try{ // 创建新的Excel 工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); // 在Excel工作簿中建一工作表,其名为缺省值 // 如要新建一名为"效益指标"的工作表,其语句为: // HSSFSheet sheet = workbook.createSheet("效益指标"); HSSFSheet sheet = workbook.createSheet(); // 在索引0的位置创建行(最顶端的行) HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.length; i++) { // 在索引0的位置创建单元格(左上端) HSSFCell cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); } // =============================================================== if (dataList != null) { for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 HSSFRow row_value = sheet.createRow(n + 1); Map dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) HSSFCell 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); // =============================================================== for (int i = 0; i < headList.length; i++) { //高度 row.setHeight(height.shortValue()); sheet.setColumnWidth(i,width); // 在索引0的位置创建单元格(左上端) HSSFCell cell = row.createCell(i); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 在单元格中输入一些内容 cell.setCellValue(headList[i]); cell.setCellStyle(boderStyle); } // =============================================================== if (dataList != null) { for (int n = 0; n < dataList.size(); n++) { // 在索引1的位置创建行 HSSFRow row_value = sheet.createRow(n + 1); row_value.setHeight(height.shortValue()); Map dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 在索引0的位置创建单元格(左上端) sheet.setColumnWidth(i,width); HSSFCell 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; } 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 ""; } }