解析Excel问题

fwq

2023-10-23 9020811b517b3a417231e0558d740804ca6fb5b2

 src/main/java/com/hx/util/ExcelUtil.java

@@ -1,24 +1,30 @@
package com.hx.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import com.hx.exception.TipsException;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
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 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;

import com.hx.exception.TipsException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.web.multipart.MultipartFile;


/**
 * 
 *
 * @author hjr
 */
public final class ExcelUtil {
@@ -37,8 +43,8 @@
    * @throws Exception
    */
   public static String createExcel(String outPath, String excelName,
         String[] headList, String[] fieldList,
         List<Map<String, Object>> dataList) throws Exception {
                            String[] headList, String[] fieldList,
                            List<Map<String, Object>> dataList) throws Exception {

      String filePath = null;
      // 创建新的Excel 工作簿
@@ -51,27 +57,30 @@
      // 在索引0的位置创建行（最顶端的行）
      HSSFRow row = sheet.createRow(0);
      // ===============================================================
      for (int i = 0; i < headList.length; i++) {

      HSSFCell cell;
      for (int i = 0; i < headList.length; i++) {
         // 在索引0的位置创建单元格（左上端）
         HSSFCell cell = row.createCell(i);
         cell = row.createCell(i);
         // 定义单元格为字符串类型
         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
         //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
         // 在单元格中输入一些内容
         cell.setCellValue(headList[i]);
      }
      // ===============================================================
      if (dataList != null) {
         HSSFRow row_value;
         Map<String, Object> dataMap;
         for (int n = 0; n < dataList.size(); n++) {
            // 在索引1的位置创建行
            HSSFRow row_value = sheet.createRow(n + 1);
            Map<String, Object> dataMap = dataList.get(n);
            row_value = sheet.createRow(n + 1);
            dataMap = dataList.get(n);
            // ===============================================================
            for (int i = 0; i < fieldList.length; i++) {
               // 在索引0的位置创建单元格（左上端）
               HSSFCell cell = row_value.createCell(i);
               cell = row_value.createCell(i);
               // 定义单元格为字符串类型
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               // 在单元格中输入一些内容
               cell.setCellValue(objToString(dataMap.get(fieldList[i])));
            }
@@ -80,7 +89,7 @@
      }

      // 新建一输出文件流
      File file = SimpleTool.createFile(outPath,excelName);
      File file = SimpleTool.createFile(outPath, excelName);
      FileOutputStream fOut = new FileOutputStream(file);
      // 把相应的Excel 工作簿存盘
      workbook.write(fOut);
@@ -106,8 +115,8 @@
    * @throws Exception
    */
   public static File createExcel(String[] headList, String[] fieldList,
                            List<Map<String, Object>> dataList) throws Exception {
      File file = File.createTempFile("temp", ".xls");
                           List<Map<String, Object>> dataList) throws Exception {
      File file = File.createTempFile("temp", ".xlsx");
      try{
         // 创建新的Excel 工作簿
         HSSFWorkbook workbook = new HSSFWorkbook();
@@ -119,29 +128,210 @@
         // 在索引0的位置创建行（最顶端的行）
         HSSFRow row = sheet.createRow(0);
         // ===============================================================
         HSSFCell cell;
         for (int i = 0; i < headList.length; i++) {

            // 在索引0的位置创建单元格（左上端）
            HSSFCell cell = row.createCell(i);
            cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(headList[i]);
         }
         // ===============================================================
         if (dataList != null) {
            HSSFRow row_value;
            Map<String, Object> dataMap;
            for (int n = 0; n < dataList.size(); n++) {
               // 在索引1的位置创建行
               HSSFRow row_value = sheet.createRow(n + 1);
               Map<String, Object> dataMap = dataList.get(n);
               row_value = sheet.createRow(n + 1);
               dataMap = dataList.get(n);
               // ===============================================================
               for (int i = 0; i < fieldList.length; i++) {
                  // 在索引0的位置创建单元格（左上端）
                  HSSFCell cell = row_value.createCell(i);
                  cell = row_value.createCell(i);
                  // 定义单元格为字符串类型
                  cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                  //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<Map<String, Object>> 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(VerticalAlignment.CENTER);
         boderStyle.setAlignment(HorizontalAlignment.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<String, Object> 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<Map<String, Object>> 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(VerticalAlignment.CENTER);
         boderStyle.setAlignment(HorizontalAlignment.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<String, Object> 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);
               }
               // ===============================================================
            }
@@ -171,10 +361,34 @@
            return (String) obj;
         } else if (obj instanceof Date) {
            return null;// DateUtil.dateToString((Date)
                     // obj,DateUtil.DATESTYLE_SHORT_EX);
            // obj,DateUtil.DATESTYLE_SHORT_EX);
         } else {
            return obj.toString();
         }
      }
   }

   /**
    * 读取Excel数据
    * @param file
    * @param header
    * @return
    * @throws Exception
    */
   public static List<List<String>> 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);
      }
   }

@@ -219,9 +433,9 @@
                  HSSFCell cell = hssfrow.getCell(i);
                  if (cell == null) {
                     arrayString.add("");
                  } else if (cell.getCellType() == 0) {
                  } else if (cell.getCellType() == CellType.NUMERIC) {
                     // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                     if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                     if (CellType.NUMERIC == cell.getCellType()) {
                        short format = cell.getCellStyle().getDataFormat();
                        if(format == 14 || format == 31 || format == 57 || format == 58){
                           //日期（中文时间格式的）
@@ -237,24 +451,28 @@
                           arrayString.add(formater.format(d));
                           //arrayString[i] = formater.format(d);
                        } else {
                           if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                           if(CellType.STRING == cell.getCellType()){
                              arrayString.add(cell.getStringCellValue());
                              //arrayString[i] =cell.getStringCellValue();
                           }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                           }else if(CellType.FORMULA==cell.getCellType()){
                              arrayString.add(cell.getCellFormula());
                              //arrayString[i] =cell.getCellFormula();
                           }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
                           }else if(CellType.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){
                  } else if(cell.getCellType() == CellType.BLANK){
                     arrayString.add("");
                     //arrayString[i] = "";
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     arrayString.add(cell.getStringCellValue().trim());
                     if(cell.getCellType() != CellType.BOOLEAN){
                        arrayString.add(cell.getStringCellValue().trim());
                     }else{
                        arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
                     }
                     //arrayString[i] = cell.getStringCellValue().trim();
                  }
               }
@@ -265,4 +483,648 @@
      return list;
   }

}
   /**
    * 读取 Excel文件内容
    *
    * @param file
    * @param header 是否包括表头
    * @return
    * @throws Exception
    */
   public static List<List<String>> 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<String>> list = new ArrayList<>();

      XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());

      // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
      for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
         XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
         int col = 0;
         // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
         for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
            XSSFRow xssfrow = xssfSheet.getRow(j);
            if(xssfrow!=null){
               if(j == 0) {
                  col = xssfrow.getPhysicalNumberOfCells();
                  if(!header) {
                     //不包括表头
                     continue;
                  }
               }
               // 单行数据
               List<String> arrayString = new ArrayList<>();
               for (int i = 0; i < col; i++) {
                  XSSFCell cell = xssfrow.getCell(i);
                  if (cell == null) {
                     arrayString.add("");
                  } else if (cell.getCellType() == CellType.NUMERIC) {
                     // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                     if (CellType.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(CellType.STRING == cell.getCellType()){
                              arrayString.add(cell.getStringCellValue());
                              //arrayString[i] =cell.getStringCellValue();
                           }else if(CellType.FORMULA==cell.getCellType()){
                              arrayString.add(cell.getCellFormula());
                              //arrayString[i] =cell.getCellFormula();
                           }else if(CellType.NUMERIC== cell.getCellType()){
                              HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                              arrayString.add(dataFormatter.formatCellValue(cell));
                              //arrayString[i] =dataFormatter.formatCellValue(cell);
                           }
                        }
                     }
                  } else if(cell.getCellType() == CellType.BLANK){
                     arrayString.add("");
                     //arrayString[i] = "";
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     if(cell.getCellType() != CellType.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 IOException {
      //这样写  excel 能兼容03和07
      return WorkbookFactory.create(inp);
   }

   /**读取excel文件，兼容2003和2007
    * 通过流读取Excel文件
    * @return
    * @throws Exception
    */
   public static List<List<String>> 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<String>> list = new ArrayList<>();
         Workbook book = create(new BufferedInputStream(new FileInputStream(file)));

         // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
         Sheet hssfsheet;
         Row hssfrow;
         List<String> arrayString;
         Cell cell;
         short format;
         Date d;
         DateFormat formater;
         HSSFDataFormatter dataFormatter;
         for(int s=0;s<book.getNumberOfSheets();s++) {
            hssfsheet = book.getSheetAt(s);
            int col = 0;
            // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
            for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
               hssfrow = hssfsheet.getRow(j);
               if(hssfrow!=null){
                  if(j == 0) {
                     col = hssfrow.getPhysicalNumberOfCells();
                     if(!header) {
                        //不包括表头
                        continue;
                     }
                  }
                  // 单行数据
                  arrayString = new ArrayList<>();
                  for (int i = 0; i < col; i++) {
                     cell = hssfrow.getCell(i);
                     if (cell == null) {
                        arrayString.add("");
                     } else if (cell.getCellType() == CellType.NUMERIC) {
                        // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                        if (CellType.NUMERIC == cell.getCellType()) {
                           format = cell.getCellStyle().getDataFormat();
                           if(format == 14 || format == 31 || format == 57 || format == 58){
                              //日期（中文时间格式的）
                               d = cell.getDateCellValue();
                               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)) {
                              d = cell.getDateCellValue();
                              //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
                              formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                              arrayString.add(formater.format(d));
                              //arrayString[i] = formater.format(d);
                           } else {
                              if(CellType.STRING == cell.getCellType()){
                                 arrayString.add(cell.getStringCellValue());
                                 //arrayString[i] =cell.getStringCellValue();
                              }else if(CellType.FORMULA==cell.getCellType()){
                                 arrayString.add(cell.getCellFormula());
                                 //arrayString[i] =cell.getCellFormula();
                              }else if(CellType.NUMERIC== cell.getCellType()){
                                 dataFormatter = new HSSFDataFormatter();
                                 arrayString.add(dataFormatter.formatCellValue(cell));
                                 //arrayString[i] =dataFormatter.formatCellValue(cell);
                              }
                           }
                        }
                     } else if(cell.getCellType() == CellType.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<List<String>> 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<String>> list = new ArrayList<>();
         Workbook book = create(new BufferedInputStream(file.getInputStream()));

         Sheet hssfsheet;
         Row hssfrow;
         List<String> arrayString;
         Cell cell;
         short format;
         Date d;
         DateFormat formater;
         HSSFDataFormatter dataFormatter;
         // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
         for(int s=0;s<book.getNumberOfSheets();s++) {
            hssfsheet = book.getSheetAt(s);
            int col = 0;
            // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
            for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
               hssfrow = hssfsheet.getRow(j);
               if(hssfrow!=null){
                  if(j == 0) {
                     col = hssfrow.getPhysicalNumberOfCells();
                     if(!header) {
                        //不包括表头
                        continue;
                     }
                  }
                  // 单行数据
                  arrayString = new ArrayList<>();
                  for (int i = 0; i < col; i++) {
                     cell = hssfrow.getCell(i);
                     if (cell == null) {
                        arrayString.add("");
                     } else if (cell.getCellType() == CellType.NUMERIC) {
                        // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                        if (CellType.NUMERIC == cell.getCellType()) {
                           format = cell.getCellStyle().getDataFormat();
                           if(format == 14 || format == 31 || format == 57 || format == 58){
                              //日期（中文时间格式的）
                              d = cell.getDateCellValue();
                              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)) {
                              d = cell.getDateCellValue();
                              //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
                              formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                              arrayString.add(formater.format(d));
                              //arrayString[i] = formater.format(d);
                           } else {
                              if(CellType.STRING == cell.getCellType()){
                                 arrayString.add(cell.getStringCellValue());
                                 //arrayString[i] =cell.getStringCellValue();
                              }else if(CellType.FORMULA==cell.getCellType()){
                                 arrayString.add(cell.getCellFormula());
                                 //arrayString[i] =cell.getCellFormula();
                              }else if(CellType.NUMERIC== cell.getCellType()){
                                 dataFormatter = new HSSFDataFormatter();
                                 arrayString.add(dataFormatter.formatCellValue(cell));
                                 //arrayString[i] =dataFormatter.formatCellValue(cell);
                              }
                           }
                        }
                     } else if(cell.getCellType() == CellType.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<List<String>> getExcelDataCompatible(InputStream inputStream,boolean header) throws Exception {
      try {
         // 结果集
         List<List<String>> list = new ArrayList<>();
         Workbook book = create(new BufferedInputStream(inputStream));

         Sheet hssfsheet;
         Row hssfrow;
         List<String> arrayString;
         Cell cell;
         short format;
         Date d;
         DateFormat formater;
         HSSFDataFormatter dataFormatter;
         // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
         for(int s=0;s<book.getNumberOfSheets();s++) {
            hssfsheet = book.getSheetAt(s);
            int col = 0;
            // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
            for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
               hssfrow = hssfsheet.getRow(j);
               if(hssfrow!=null){
                  if(j == 0) {
                     col = hssfrow.getPhysicalNumberOfCells();
                     if(!header) {
                        //不包括表头
                        continue;
                     }
                  }
                  // 单行数据
                  arrayString = new ArrayList<>();
                  for (int i = 0; i < col; i++) {
                     cell = hssfrow.getCell(i);
                     if (cell == null) {
                        arrayString.add("");
                     } else if (cell.getCellType() == CellType.NUMERIC) {
                        // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                        if (CellType.NUMERIC == cell.getCellType()) {
                           format = cell.getCellStyle().getDataFormat();
                           if(format == 14 || format == 31 || format == 57 || format == 58){
                              //日期（中文时间格式的）
                              d = cell.getDateCellValue();
                              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)) {
                              d = cell.getDateCellValue();
                              //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
                              formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                              arrayString.add(formater.format(d));
                              //arrayString[i] = formater.format(d);
                           } else {
                              if(CellType.STRING == cell.getCellType()){
                                 arrayString.add(cell.getStringCellValue());
                                 //arrayString[i] =cell.getStringCellValue();
                              }else if(CellType.FORMULA==cell.getCellType()){
                                 arrayString.add(cell.getCellFormula());
                                 //arrayString[i] =cell.getCellFormula();
                              }else if(CellType.NUMERIC== cell.getCellType()){
                                 dataFormatter = new HSSFDataFormatter();
                                 arrayString.add(dataFormatter.formatCellValue(cell));
                                 //arrayString[i] =dataFormatter.formatCellValue(cell);
                              }
                           }
                        }
                     } else if(cell.getCellType() == CellType.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，只支持2007以上版本，也就是xslx格式
    * 支持大数据量
    * @param file 文件
    * @return 数据
    */
   public static List<List<String>> readExcelData(File file){
      if(!isExcel(file)){
         throw new TipsException("请上传excel的文件格式！");
      }
      List<List<String>> listData = new ArrayList<>();
      try{
         //rowCacheSize 缓存到内存中的行数(默认是10)
         //bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
         //open InputStream或者XLSX格式的File(必须)
         Workbook book = StreamingReader.builder()
               .rowCacheSize(100)
               .bufferSize(10240)
               .open(new FileInputStream(file));
         listData =readhandle(book);
      }catch (OLE2NotOfficeXmlFileException ex){
         ex.printStackTrace();
         throw new RuntimeException("excel版本不为2007及以上");
      } catch (Exception e){
         e.printStackTrace();
         throw new RuntimeException(e.getMessage());
      }
      return listData;
   }

   /**
    * 新版读取Excel，只支持2007以上版本，也就是xslx格式
    * 支持大数据量
    * @param file 文件
    * @return 数据
    */
   public static List<List<String>> readExcelData(MultipartFile file){
      if(!isExcel(file)){
         throw new TipsException("请上传excel的文件格式！");
      }
      List<List<String>> listData = new ArrayList<>();
      try{
         //rowCacheSize 缓存到内存中的行数(默认是10)
         //bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
         //open InputStream或者XLSX格式的File(必须)
         Workbook book = StreamingReader.builder()
               .rowCacheSize(100)
               .bufferSize(10240)
               .open(new BufferedInputStream(file.getInputStream()));

         listData =readhandle(book);
      }catch (OLE2NotOfficeXmlFileException ex){
         ex.printStackTrace();
         throw new RuntimeException("excel版本不为2007及以上");
      } catch (Exception e){
         e.printStackTrace();
         throw new RuntimeException(e.getMessage());
      }
      return listData;
   }

   /**
    * 新版读取Excel，只支持2007以上版本，也就是xslx格式
    * 支持大数据量
    * @param file 文件
    * @return 数据
    */
   public static List<List<String>> readExcelData(InputStream file){
      List<List<String>> listData = new ArrayList<>();
      try{
         //rowCacheSize 缓存到内存中的行数(默认是10)
         //bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
         //open InputStream或者XLSX格式的File(必须)
         Workbook book = StreamingReader.builder()
               .rowCacheSize(100)
               .bufferSize(10240)
               .open(file);

         listData =readhandle(book);
      }catch (Exception e){
         e.printStackTrace();
         throw new RuntimeException(e.getMessage());
      }
      return listData;
   }

   /**处理数据*/
   public static List<List<String>> readhandle(Workbook book){
      List<List<String>> listData = new ArrayList<>();

      //是否存在数据
      boolean isData;
      List<String> arrayString;
      short format;
      Date d;
      DateFormat formater;
      Sheet sheet;
      Cell cell;
      //遍历所有的sheet
      for(int i=0;i<book.getNumberOfSheets();i++) {
         sheet = book.getSheetAt(i);
         //列数
         Integer arrange = null;
         //遍历所有的行
         for (Row row : sheet) {
            if(row == null){
               continue;
            }
            isData = false;
            arrayString = new ArrayList<>();
            if(arrange == null){
               arrange = row.getPhysicalNumberOfCells();
            }
            //遍历所有的列
            for (int j = 0;j<arrange;j++) {
               cell = row.getCell(j);
               if (cell == null) {
                  arrayString.add("");
               }else{
                  if (cell.getCellType() == CellType.NUMERIC) {
                     isData = true;
                     format = cell.getCellStyle().getDataFormat();
                     if (format == 14 || format == 31 || format == 57 || format == 58) {
                        //日期（中文时间格式的）
                        d = cell.getDateCellValue();
                        formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                        arrayString.add(formater.format(d));
                     } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        d = cell.getDateCellValue();
                        formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                        arrayString.add(formater.format(d));
                     } else {
                        if (CellType.STRING == cell.getCellType()) {
                           arrayString.add(cell.getStringCellValue());
                        } else if (CellType.FORMULA == cell.getCellType()) {
                           arrayString.add(cell.getCellFormula());
                        } else {
                           arrayString.add(cell.getStringCellValue().trim());
                        }
                     }
                  } else if (cell.getCellType() == CellType.BLANK) {
                     arrayString.add("");
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     isData = true;
                     arrayString.add(cell.getStringCellValue().trim());
                  }
               }
            }
            if (isData) {
               listData.add(arrayString);
            }
         }
      }
      return listData;
   }


   /**
    * p判断是否excel文件
    * @param file
    * @return
    */
   public static boolean isExcel(MultipartFile file){
      return isExcel(file.getOriginalFilename());
   }


   /**
    * p判断是否excel文件
    * @param file
    * @return
    */
   public static boolean isExcel(File file){
      return isExcel(file.getName());
   }


   /**判断文件格式是不是excel*/
   public static boolean isExcel(String fileName){
      if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
         return false;
      }
      return true;
   }

   public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> 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(VerticalAlignment.CENTER);
         boderStyle.setAlignment(HorizontalAlignment.CENTER);
         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.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<String, Object> dataMap = (Map)dataList.get(n);

               for(int i = 0; i < fieldList.length; ++i) {
                  sheet.setColumnWidth(i, width);
                  cell = row_value.createCell(i);
                  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(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                        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;
   }

}