1.修改区分Excel不同格式读取数据问题

fhx

2021-07-19 c6eec82a790b6dc9c1d74556a52cd891a2017e8c

1.修改区分Excel不同格式读取数据问题

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

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

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.hx.exception.TipsException;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.*;
@@ -14,8 +9,19 @@
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

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;


/**
@@ -265,6 +271,30 @@
    }

    /**
     * 读取Excel数据
     * @param file
     * @param header
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {

        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new TipsException("上传文件格式不正确");
        }

        //判断不同格式处理方法不同
        if(fileName.matches("^.+\\.(?i)(xls)$")){
            //xls格式使用HSSF
            return readExcelByeFileData(file, header);
        }else{
            //xlsx格式使用XSSF
            return readExcelByeFileDataToXSSF(file, header);
        }
    }

    /**
     * 读取 Excel文件内容
     *
     * @param file
@@ -351,6 +381,93 @@
        return list;
    }

    /**
     * 读取 Excel文件内容
     *
     * @param file
     * @param header 是否包括表头
     * @return
     * @throws Exception
     */
    public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {

        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new TipsException("上传文件格式不正确");
        }

        // 结果集
        List<List<String>> list = new ArrayList<>();

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());

        // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
        for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
            int col = 0;
            // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题
            for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
                XSSFRow xssfrow = xssfSheet.getRow(j);
                if(xssfrow!=null){
                    if(j == 0) {
                        col = xssfrow.getPhysicalNumberOfCells();
                        if(!header) {
                            //不包括表头
                            continue;
                        }
                    }
                    // 单行数据
                    List<String> arrayString = new ArrayList<>();
                    for (int i = 0; i < col; i++) {
                        XSSFCell cell = xssfrow.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;
    }


    /**判断excel的版本*/
    public static Workbook create(InputStream inp) throws Exception {