新版excel

chenjiahe

2023-09-01 2e06d10f39d8bbdeae8a8da99f3d94ed027df24c

新版excel

 pom.xml

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

        <dependency>
      <!--  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
@@ -31,7 +31,7 @@
                    <groupId>dom4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        </dependency>-->

        <!--腾讯云 cos-->
        <dependency>
@@ -117,11 +117,6 @@
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
        </dependency>
        <!-- excel2003使用的jar -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>redis.clients</groupId>
@@ -295,6 +290,13 @@
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer -->
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
        </dependency>

    </dependencies>

    <build>

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

@@ -1,8 +1,9 @@
package com.hx.util;

import com.hx.exception.TipsException;
import org.apache.poi.POIXMLDocument;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
@@ -61,7 +62,7 @@
            // 在索引0的位置创建单元格（左上端）
            cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(headList[i]);
        }
@@ -78,7 +79,7 @@
                    // 在索引0的位置创建单元格（左上端）
                    cell = row_value.createCell(i);
                    // 定义单元格为字符串类型
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    // 在单元格中输入一些内容
                    cell.setCellValue(objToString(dataMap.get(fieldList[i])));
                }
@@ -131,7 +132,7 @@
                // 在索引0的位置创建单元格（左上端）
                cell = row.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(headList[i]);
            }
@@ -148,7 +149,7 @@
                        // 在索引0的位置创建单元格（左上端）
                        cell = row_value.createCell(i);
                        // 定义单元格为字符串类型
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        // 在单元格中输入一些内容
                        cell.setCellValue(objToString(dataMap.get(fieldList[i])));
                    }
@@ -197,8 +198,8 @@
            //合并的单元格样式
            HSSFCellStyle boderStyle = workbook.createCellStyle();
            //垂直居中
            boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

            // 在Excel工作簿中建一工作表，其名为缺省值
            // 如要新建一名为"效益指标"的工作表，其语句为：
@@ -215,7 +216,7 @@
                // 在索引0的位置创建单元格（左上端）
                cell = row.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(headList[i]);
                cell.setCellStyle(boderStyle);
@@ -235,7 +236,7 @@
                        sheet.setColumnWidth(i,width);
                        cell = row_value.createCell(i);
                        // 定义单元格为字符串类型
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        // 在单元格中输入一些内容
                        cell.setCellValue(objToString(dataMap.get(fieldList[i])));
                        cell.setCellStyle(boderStyle);
@@ -288,8 +289,8 @@
            //合并的单元格样式
            CellStyle boderStyle = workbook.createCellStyle();
            //垂直居中
            boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            boderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

            // 在Excel工作簿中建一工作表，其名为缺省值
            // 如要新建一名为"效益指标"的工作表，其语句为：
@@ -306,7 +307,7 @@
                // 在索引0的位置创建单元格（左上端）
                cell = row.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 在单元格中输入一些内容
                cell.setCellValue(headList[i]);
                cell.setCellStyle(boderStyle);
@@ -326,7 +327,7 @@
                        sheet.setColumnWidth(i,width);
                        cell = row_value.createCell(i);
                        // 定义单元格为字符串类型
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        // 在单元格中输入一些内容
                        cell.setCellValue(objToString(dataMap.get(fieldList[i])));
                        cell.setCellStyle(boderStyle);
@@ -431,9 +432,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){
                                    //日期（中文时间格式的）
@@ -449,24 +450,24 @@
                                    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表格中的数据类型为字符串型
                            if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
                            if(cell.getCellType() != CellType.BOOLEAN){
                                arrayString.add(cell.getStringCellValue().trim());
                            }else{
                                arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -522,9 +523,9 @@
                        XSSFCell cell = xssfrow.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 (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                            if (CellType.NUMERIC == cell.getCellType()) {
                                short format = cell.getCellStyle().getDataFormat();
                                if(format == 14 || format == 31 || format == 57 || format == 58){
                                    //日期（中文时间格式的）
@@ -540,24 +541,24 @@
                                    arrayString.add(formater.format(d));
                                    //arrayString[i] = formater.format(d);
                                } else {
                                    if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                                    if(CellType.STRING == cell.getCellType()){
                                        arrayString.add(cell.getStringCellValue());
                                        //arrayString[i] =cell.getStringCellValue();
                                    }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                                    }else if(CellType.FORMULA==cell.getCellType()){
                                        arrayString.add(cell.getCellFormula());
                                        //arrayString[i] =cell.getCellFormula();
                                    }else if(XSSFCell.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表格中的数据类型为字符串型
                            if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
                            if(cell.getCellType() != CellType.BOOLEAN){
                                arrayString.add(cell.getStringCellValue().trim());
                            }else{
                                arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -576,17 +577,9 @@


    /**判断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解析不了");
    public static Workbook create(InputStream inp) throws IOException {
        //这样写  excel 能兼容03和07
        return WorkbookFactory.create(inp);
    }

    /**读取excel文件，兼容2003和2007
@@ -607,12 +600,20 @@
            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++) {
                Sheet hssfsheet = book.getSheetAt(s);
                hssfsheet = book.getSheetAt(s);
                int col = 0;
                // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
                for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
                    Row hssfrow = hssfsheet.getRow(j);
                    hssfrow = hssfsheet.getRow(j);
                    if(hssfrow!=null){
                        if(j == 0) {
                            col = hssfrow.getPhysicalNumberOfCells();
@@ -622,43 +623,43 @@
                            }
                        }
                        // 单行数据
                        List<String> arrayString = new ArrayList<>();
                        arrayString = new ArrayList<>();
                        for (int i = 0; i < col; i++) {
                            Cell cell = hssfrow.getCell(i);
                            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()) {
                                    short format = cell.getCellStyle().getDataFormat();
                                if (CellType.NUMERIC == cell.getCellType()) {
                                    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");
                                         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)) {
                                        Date d = cell.getDateCellValue();
                                        d = cell.getDateCellValue();
                                        //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
                                        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(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()){
                                            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                                        }else if(CellType.NUMERIC== cell.getCellType()){
                                            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表格中的数据类型为字符串型
@@ -676,6 +677,7 @@
        }
        return null;
    }




@@ -696,7 +698,105 @@
            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>> getExcelDataCompatibleCheckEmpty(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表示工作表的总数
            //不为空
            boolean isNotEmpty = false;
            for(int s=0;s<book.getNumberOfSheets();s++) {
                Sheet hssfsheet = book.getSheetAt(s);
                int col = 0;
@@ -717,9 +817,9 @@
                            Cell 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){
                                        //日期（中文时间格式的）
@@ -735,28 +835,32 @@
                                        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){
                                isNotEmpty = true;
                            } else if(cell.getCellType() == CellType.BLANK){
                                arrayString.add("");
                                //arrayString[i] = "";
                            } else { // 如果EXCEL表格中的数据类型为字符串型
                                arrayString.add(cell.getStringCellValue().trim());
                                //arrayString[i] = cell.getStringCellValue().trim();
                                isNotEmpty = true;
                            }
                        }
                        if (isNotEmpty){
                        list.add(arrayString);
                        }
                    }
                }
            }
@@ -768,13 +872,109 @@
    }


    private static String getCellVal(Cell cell) {
    /**读取excel文件，兼容2003和2007
     * 通过流读取Excel文件
     * @return
     * @throws Exception
     */
    public static List<List<String>> getExcelDataCompatibleCheckEmpty(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()));

            // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
            //不为空
            boolean isNotEmpty = false;
            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++) {
                    isNotEmpty = false;
                    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() == 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);
                                        }
                                    }
                                }
                                isNotEmpty = true;
                            } else if(cell.getCellType() == CellType.BLANK){
                                arrayString.add("");
                                //arrayString[i] = "";
                            } else { // 如果EXCEL表格中的数据类型为字符串型
                                arrayString.add(cell.getStringCellValue().trim());
                                isNotEmpty = true;
                                //arrayString[i] = cell.getStringCellValue().trim();
                            }
                        }
                        if (isNotEmpty){
                            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:
            case CellType.NUMERIC:
                // 日期格式的处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@@ -782,24 +982,24 @@
                }
                return String.valueOf(cell.getNumericCellValue());
            // 字符串
            case HSSFCell.CELL_TYPE_STRING:
            case CellType.STRING:
                return cell.getStringCellValue();
            // 公式
            case HSSFCell.CELL_TYPE_FORMULA:
            case CellType.FORMULA:
                return cell.getCellFormula();
            // 空白
            case HSSFCell.CELL_TYPE_BLANK:
            case CellType.BLANK:
                return "";
            case HSSFCell.CELL_TYPE_BOOLEAN:
            case CellType.BOOLEAN:
                return cell.getBooleanCellValue() + "";
            // 错误类型
            case HSSFCell.CELL_TYPE_ERROR:
            case CellType.ERROR:
                return cell.getErrorCellValue() + "";
            default:
                break;
        }
        return "";
    }
    }*/

    /**
     * p判断是否excel文件
@@ -835,8 +1035,8 @@

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFCellStyle boderStyle = workbook.createCellStyle();
            boderStyle.setVerticalAlignment((short)1);
            boderStyle.setAlignment((short)2);
            boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            boderStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFSheet sheet = workbook.createSheet();
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFRow row = sheet.createRow(0);
@@ -846,7 +1046,6 @@
                row.setHeight(height.shortValue());
                sheet.setColumnWidth(i, width);
                anchor = row.createCell(i);
                anchor.setCellType(1);
                anchor.setCellValue(headList[i]);
                anchor.setCellStyle(boderStyle);
            }
@@ -863,7 +1062,6 @@
                    for(int i = 0; i < fieldList.length; ++i) {
                        sheet.setColumnWidth(i, width);
                        cell = row_value.createCell(i);
                        cell.setCellType(1);
                        Object value = dataMap.get(fieldList[i]);
                        if (value != null && "class java.io.File".equals(value.getClass().toString())) {
                            File file2 = (File)value;
@@ -873,7 +1071,7 @@
                                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(0);
                                anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                                patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
                            }
                        } else {
@@ -896,4 +1094,25 @@

        return file;
    }
    public static void main(String[] args) throws Exception {
        //File file = new File("E:\\360\\a701aae8-9d4b-49f1-8999-1456fa53ef03 (1).xlsx");
        File file = new File("E:\\360\\tttt (1).xls");
        //Workbook wk = create(new FileInputStream(file));
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)    // 缓存到内存中的行数(默认是10)
                .bufferSize(4096)     // 读取资源时，缓存到内存的字节大小(默认是1024)
                .open(new FileInputStream(file));            // InputStream或者XLSX格式的File(必须)

        Sheet sheet = wk.getSheetAt(0);
        //遍历所有的行
        for (Row row : sheet) {
            System.out.println("开始遍历第" + row.getRowNum() + "行数据：");
            //遍历所有的列
            for (Cell cell : row) {
                System.out.print(cell.getStringCellValue() + " ");
            }
            System.out.println(" ");
        }
    }

}

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

@@ -43,6 +43,7 @@
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.cglib.beans.BeanMap;

public class SimpleTool {
@@ -802,7 +803,7 @@
                HSSFCell cell = hssfrow.getCell(i);
                if (cell == null) {
                    arrayString[i] = "";
                } else if (cell.getCellType() == 0) {
                } else if (cell.getCellType() == CellType.NUMERIC) {
                    // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
                     short format = cell.getCellStyle().getDataFormat();  
                        SimpleDateFormat sdf = null;  
@@ -812,7 +813,7 @@
                        }else if (format == 20 || format == 32) {  
                            //时间  
                            sdf = new SimpleDateFormat("HH:mm");  
                        }else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { 
                        }else if (CellType.NUMERIC == cell.getCellType()) {
                          if (HSSFDateUtil.isCellDateFormatted(cell)) {    
                            Date d = cell.getDateCellValue();    
                            DateFormat formater = new SimpleDateFormat("yyyy年"); 
@@ -868,9 +869,9 @@
                        HSSFCell cell = hssfrow.getCell(i);
                        if (cell == null) {
                            arrayString[i] = "";
                        } 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){  
                                    //日期（中文时间格式的）
@@ -884,17 +885,17 @@
                                    // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    arrayString[i] = formater.format(d);   
                                } else {    
                                    if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                                    if(CellType.STRING == cell.getCellType()){
                                        arrayString[i] =cell.getStringCellValue();
                                    }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                                    }else if(CellType.FORMULA==cell.getCellType()){
                                        arrayString[i] =cell.getCellFormula();
                                    }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
                                    }else if(CellType.NUMERIC== cell.getCellType()){
                                        HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                                        arrayString[i] =dataFormatter.formatCellValue(cell);
                                    }
                                }
                            }
                        } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
                        } else if(cell.getCellType() == CellType.BLANK){
                            arrayString[i] = "";
                        } else { // 如果EXCEL表格中的数据类型为字符串型
                            arrayString[i] = cell.getStringCellValue().trim();
@@ -939,9 +940,9 @@
                            HSSFCell cell = hssfrow.getCell(i);
                            if (cell == null) {
                                arrayString[i] = "";
                            } 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){  
                                        //日期（中文时间格式的）
@@ -955,20 +956,20 @@
                                        // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                        arrayString[i] = formater.format(d);   
                                    } else {    
                                        if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
                                        if(CellType.STRING == cell.getCellType()){
                                            arrayString[i] =cell.getStringCellValue();
                                        }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
                                        }else if(CellType.FORMULA==cell.getCellType()){
                                            arrayString[i] =cell.getCellFormula();
                                        }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
                                        }else if(CellType.NUMERIC== cell.getCellType()){
                                            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                                            arrayString[i] =dataFormatter.formatCellValue(cell);
                                        }
                                    }
                                }
                            } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
                            } else if(cell.getCellType() == CellType.BLANK){
                                arrayString[i] = "";
                            } else { // 如果EXCEL表格中的数据类型为字符串型
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cell.setCellType(CellType.STRING);
                                String name = cell.getStringCellValue().trim();
                                if(name.equals("-")) {
                                    arrayString[i] = "";