读取excel文件版本兼容

ChenJiaHe

2021-02-24 cac339cb773b12721bc2c9886d5ab7ed9753f245

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

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

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
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 java.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.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;


/**
 * 
 *
 * @author hjr
 */
public final class ExcelUtil {
@@ -37,8 +38,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 工作簿
@@ -106,7 +107,7 @@
    * @throws Exception
    */
   public static File createExcel(String[] headList, String[] fieldList,
                            List<Map<String, Object>> dataList) throws Exception {
                           List<Map<String, Object>> dataList) throws Exception {
      File file = File.createTempFile("temp", ".xls");
      try{
         // 创建新的Excel 工作簿
@@ -162,6 +163,91 @@
      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(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<String, Object> 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) {
@@ -171,7 +257,7 @@
            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();
         }
@@ -265,4 +351,142 @@
      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<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<>();

         Map<String, List<List<Map<Integer,String>>>> data = new HashMap<String, List<List<Map<Integer,String>>>>();
         Workbook book = create(new BufferedInputStream(file.getInputStream()));

         // 遍历该表格中所有的工作表，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;
   }

   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 "";
   }

}