统计业务

chenjiahe

2022-07-27 386c2a4bfe398298db827607a32c1aa81cf7d554

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

@@ -370,7 +370,11 @@
                     arrayString.add("");
                     //arrayString[i] = "";
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     arrayString.add(cell.getStringCellValue().trim());
                     if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
                        arrayString.add(cell.getStringCellValue().trim());
                     }else{
                        arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
                     }
                     //arrayString[i] = cell.getStringCellValue().trim();
                  }
               }
@@ -424,7 +428,7 @@
                     arrayString.add("");
                  } else if (cell.getCellType() == 0) {
                     // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                     if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                     if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        short format = cell.getCellStyle().getDataFormat();
                        if(format == 14 || format == 31 || format == 57 || format == 58){
                           //日期（中文时间格式的）
@@ -440,13 +444,13 @@
                           arrayString.add(formater.format(d));
                           //arrayString[i] = formater.format(d);
                        } else {
                           if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                           if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                              arrayString.add(cell.getStringCellValue());
                              //arrayString[i] =cell.getStringCellValue();
                           }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                           }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                              arrayString.add(cell.getCellFormula());
                              //arrayString[i] =cell.getCellFormula();
                           }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
                           }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
                              HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                              arrayString.add(dataFormatter.formatCellValue(cell));
                              //arrayString[i] =dataFormatter.formatCellValue(cell);
@@ -457,7 +461,13 @@
                     arrayString.add("");
                     //arrayString[i] = "";
                  } else { // 如果EXCEL表格中的数据类型为字符串型
                     arrayString.add(cell.getStringCellValue().trim());
                     if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
                        arrayString.add(cell.getStringCellValue().trim());
                     }else{
                        arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
                     }


                     //arrayString[i] = cell.getStringCellValue().trim();
                  }
               }
@@ -488,6 +498,97 @@
    * @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表示工作表的总数
         for(int s=0;s<book.getNumberOfSheets();s++) {
            Sheet hssfsheet = book.getSheetAt(s);
            int col = 0;
            // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
            for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
               Row hssfrow = hssfsheet.getRow(j);
               if(hssfrow!=null){
                  if(j == 0) {
                     col = hssfrow.getPhysicalNumberOfCells();
                     if(!header) {
                        //不包括表头
                        continue;
                     }
                  }
                  // 单行数据
                  List<String> 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<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception {
      try {