读取excel文件版本兼容

ChenJiaHe

2021-02-24 cac339cb773b12721bc2c9886d5ab7ed9753f245

读取excel文件版本兼容

 hx-common.iml

@@ -19,8 +19,16 @@
    </content>
    <orderEntry type="inheritedJdk" />
    <orderEntry type="sourceFolder" forTests="false" />
    <orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.32" level="project" />
    <orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.24" level="project" />
    <orderEntry type="library" name="Maven: javax.xml.bind:jaxb-api:2.3.1" level="project" />
    <orderEntry type="library" name="Maven: javax.activation:javax.activation-api:1.2.0" level="project" />
    <orderEntry type="library" name="Maven: org.apache.poi:poi-ooxml:3.9" level="project" />
    <orderEntry type="library" name="Maven: org.apache.poi:poi-ooxml-schemas:3.9" level="project" />
    <orderEntry type="library" name="Maven: org.apache.xmlbeans:xmlbeans:2.3.0" level="project" />
    <orderEntry type="library" name="Maven: stax:stax-api:1.0.1" level="project" />
    <orderEntry type="library" name="Maven: dom4j:dom4j:2.1.3" level="project" />
    <orderEntry type="library" name="Maven: xml-apis:xml-apis:1.4.01" level="project" />
    <orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.32" level="project" />
    <orderEntry type="library" name="Maven: org.slf4j:slf4j-api:1.7.25" level="project" />
    <orderEntry type="library" name="Maven: commons-codec:commons-codec:1.11" level="project" />
    <orderEntry type="library" name="Maven: joda-time:joda-time:2.9.9" level="project" />

 pom.xml

@@ -21,6 +21,11 @@
            <artifactId>jaxb-api</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <!--腾讯云 cos-->
        <dependency>

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

@@ -1,19 +1,20 @@
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;


@@ -350,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 "";
    }

}