新版excel

chenjiahe

2023-09-04 5b623ba0cf530207953c4ec708d6d3706634c139

新版excel

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

@@ -1,14 +1,15 @@
package com.hx.util;

import com.alibaba.fastjson.JSON;
import com.hx.exception.TipsException;
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.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
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 javax.imageio.ImageIO;
@@ -777,229 +778,161 @@
        return null;
    }

    /**读取excel文件，兼容2003和2007
     * 通过流读取Excel文件
     * @return
     * @throws Exception

    /**
     * 新版读取Excel，只支持2007以上版本，也就是xslx格式
     * @param file 文件
     * @return 数据
     */
    public static List<List<String>> getExcelDataCompatibleCheckEmpty(File file,boolean header) throws Exception {
        try {
    public static List<List<String>> readExcelData(File file){
        if(!isExcel(file)){
            throw new TipsException("请上传excel的文件格式！");
        }
        List<List<String>> listData = new ArrayList<>();
        try{
            //rowCacheSize 缓存到内存中的行数(默认是10)
            //bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
            //open InputStream或者XLSX格式的File(必须)
            Workbook book = StreamingReader.builder()
                    .rowCacheSize(100)
                    .bufferSize(4096)
                    .open(new FileInputStream(file));

            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;
                // 遍历该行所有的行,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() == 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);
                                        }
                                    }
            //是否存在数据
            boolean isData;
            List<String> arrayString;
            short format;
            Date d;
            DateFormat formater;
            Sheet sheet;
            //遍历所有的sheet
            for(int i=0;i<book.getNumberOfSheets();i++){
                sheet = book.getSheetAt(i);
                //遍历所有的行
                for (Row row : sheet) {
                    isData = false;
                    arrayString = new ArrayList<>();
                    //遍历所有的列
                    for (Cell cell : row) {
                        if (cell == null) {
                            arrayString.add("");
                        } else if (cell.getCellType() == CellType.NUMERIC) {
                            isData = true;
                            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");
                                arrayString.add(formater.format(d));
                            }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                d = cell.getDateCellValue();
                                formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                                arrayString.add(formater.format(d));
                            } else {
                                if(CellType.STRING == cell.getCellType()){
                                    arrayString.add(cell.getStringCellValue());
                                }else if(CellType.FORMULA==cell.getCellType()){
                                    arrayString.add(cell.getCellFormula());
                                }else{
                                    arrayString.add(cell.getStringCellValue().trim());
                                }
                                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;
                            }
                        } else if(cell.getCellType() == CellType.BLANK){
                            arrayString.add("");
                        } else { // 如果EXCEL表格中的数据类型为字符串型
                            isData = true;
                            arrayString.add(cell.getStringCellValue().trim());
                        }
                        if (isNotEmpty){
                            list.add(arrayString);
                        }
                    }
                    if(isData){
                        listData.add(arrayString);
                    }
                }
            }
            return list;
        } catch (Exception e) {
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return null;
        return listData;
    }


    /**读取excel文件，兼容2003和2007
     * 通过流读取Excel文件
     * @return
     * @throws Exception
    /**
     * 新版读取Excel，只支持2007以上版本，也就是xslx格式
     * @param file 文件
     * @return 数据
     */
    public static List<List<String>> getExcelDataCompatibleCheckEmpty(MultipartFile file,boolean header) throws Exception {
        try {
    public static List<List<String>> readExcelData(MultipartFile file){
        if(!isExcel(file)){
            throw new TipsException("请上传excel的文件格式！");
        }
        List<List<String>> listData = new ArrayList<>();
        try{
            //rowCacheSize 缓存到内存中的行数(默认是10)
            //bufferSize 读取资源时，缓存到内存的字节大小(默认是1024)
            //open InputStream或者XLSX格式的File(必须)
            Workbook book = StreamingReader.builder()
                    .rowCacheSize(100)
                    .bufferSize(4096)
                    .open(new BufferedInputStream(file.getInputStream()));

            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);
                                        }
                                    }
            //是否存在数据
            boolean isData;
            List<String> arrayString;
            short format;
            Date d;
            DateFormat formater;
            Sheet sheet;
            //遍历所有的sheet
            for(int i=0;i<book.getNumberOfSheets();i++){
                sheet = book.getSheetAt(i);
                //遍历所有的行
                for (Row row : sheet) {
                    isData = false;
                    arrayString = new ArrayList<>();
                    //遍历所有的列
                    for (Cell cell : row) {
                        if (cell == null) {
                            arrayString.add("");
                        } else if (cell.getCellType() == CellType.NUMERIC) {
                            isData = true;
                            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");
                                arrayString.add(formater.format(d));
                            }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                d = cell.getDateCellValue();
                                formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                                arrayString.add(formater.format(d));
                            } else {
                                if(CellType.STRING == cell.getCellType()){
                                    arrayString.add(cell.getStringCellValue());
                                }else if(CellType.FORMULA==cell.getCellType()){
                                    arrayString.add(cell.getCellFormula());
                                }else{
                                    arrayString.add(cell.getStringCellValue().trim());
                                }
                                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();
                            }
                        } else if(cell.getCellType() == CellType.BLANK){
                            arrayString.add("");
                        } else { // 如果EXCEL表格中的数据类型为字符串型
                            isData = true;
                            arrayString.add(cell.getStringCellValue().trim());
                        }
                        if (isNotEmpty){
                            list.add(arrayString);
                        }
                    }
                    if(isData){
                        listData.add(arrayString);
                    }
                }
            }
            return list;
        } catch (Exception e) {
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return null;
        return listData;
    }


    /*private static String getCellVal(Cell cell) {
        if (null == cell) {
            return "";
        }
        switch (cell.getCellType()) {
            // 数字
            case CellType.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 CellType.STRING:
                return cell.getStringCellValue();
            // 公式
            case CellType.FORMULA:
                return cell.getCellFormula();
            // 空白
            case CellType.BLANK:
                return "";
            case CellType.BOOLEAN:
                return cell.getBooleanCellValue() + "";
            // 错误类型
            case CellType.ERROR:
                return cell.getErrorCellValue() + "";
            default:
                break;
        }
        return "";
    }*/

    /**
     * p判断是否excel文件
@@ -1007,16 +940,27 @@
     * @return
     */
    public static boolean isExcel(MultipartFile file){
        String fileName = file.getOriginalFilename();
        return isExcel(file.getOriginalFilename());
    }


    /**
     * p判断是否excel文件
     * @param file
     * @return
     */
    public static boolean isExcel(File file){
        return isExcel(file.getName());
    }


    /**判断文件格式是不是excel*/
    public static boolean isExcel(String fileName){
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return false;
        }
        return true;
    }





    public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
        File file = File.createTempFile("temp", ".xls");
@@ -1093,26 +1037,6 @@
        }

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

}