新版excel优化

chenjiahe

2023-10-08 8b3e27cbc244bc5e09ea1acd8a0a0546927e063d

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

@@ -1,6 +1,5 @@
package com.hx.util;

import com.alibaba.fastjson.JSON;
import com.hx.exception.TipsException;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
@@ -679,9 +678,6 @@
      return null;
   }




   /**读取excel文件，兼容2003和2007
    * 通过流读取Excel文件
    * @return
@@ -778,9 +774,99 @@
      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 数据
    */
@@ -795,60 +881,9 @@
         //open InputStream或者XLSX格式的File(必须)
         Workbook book = StreamingReader.builder()
               .rowCacheSize(100)
               .bufferSize(4096)
               .bufferSize(10240)
               .open(new FileInputStream(file));

         //是否存在数据
         boolean isData;
         List<String> arrayString;
         short format;
         Date d;
         DateFormat formater;
         Sheet sheet;
         //遍历所有的sheet
         for(int i=0;i<book.getNumberOfSheets();i++){
            sheet = book.getSheetAt(i);
            //遍历所有的行
            for (Row row : sheet) {
               isData = false;
               arrayString = new ArrayList<>();
               //遍历所有的列
               for (Cell cell : row) {
                  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);
               }
            }
         }
         listData =readhandle(book);
      }catch (Exception e){
         e.printStackTrace();
         throw new RuntimeException(e.getMessage());
@@ -858,6 +893,7 @@

   /**
    * 新版读取Excel，只支持2007以上版本，也就是xslx格式
    * 支持大数据量
    * @param file 文件
    * @return 数据
    */
@@ -872,63 +908,108 @@
         //open InputStream或者XLSX格式的File(必须)
         Workbook book = StreamingReader.builder()
               .rowCacheSize(100)
               .bufferSize(4096)
               .bufferSize(10240)
               .open(new BufferedInputStream(file.getInputStream()));

         //是否存在数据
         boolean isData;
         List<String> arrayString;
         short format;
         Date d;
         DateFormat formater;
         Sheet sheet;
         //遍历所有的sheet
         for(int i=0;i<book.getNumberOfSheets();i++){
            sheet = book.getSheetAt(i);
            //遍历所有的行
            for (Row row : sheet) {
               isData = false;
               arrayString = new ArrayList<>();
               //遍历所有的列
               for (Cell cell : row) {
                  if (cell == null) {
                     arrayString.add("");
                  } else if (cell.getCellType() == CellType.NUMERIC) {
         listData =readhandle(book);
      }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){
                     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)) {
                     } 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()){
                        if (CellType.STRING == cell.getCellType()) {
                           arrayString.add(cell.getStringCellValue());
                        }else if(CellType.FORMULA==cell.getCellType()){
                        } else if (CellType.FORMULA == cell.getCellType()) {
                           arrayString.add(cell.getCellFormula());
                        }else{
                        } else {
                           arrayString.add(cell.getStringCellValue().trim());
                        }
                     }
                  } else if(cell.getCellType() == CellType.BLANK){
                  } else if (cell.getCellType() == CellType.BLANK) {
                     arrayString.add("");
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     isData = true;
                     arrayString.add(cell.getStringCellValue().trim());
                  }
               }
               if(isData){
                  listData.add(arrayString);
               }
            }
            if (isData) {
               listData.add(arrayString);
            }
         }
      }catch (Exception e){
         e.printStackTrace();
         throw new RuntimeException(e.getMessage());
      }
      return listData;
   }