文件解析工具

前言

对Excel & CSV 文件解析

package com.wind.bird.Utils;

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import org.apache.commons.validator.Var;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @Author windBird
 * @Date 2023/11/25 21:28
 * @ClassName FileParseUtil
 * coding ^_^ every Day
 * @Desc  excel & csv  parse
 */
public class FileParseUtil {

    private static final Logger log = LoggerFactory.getLogger(FileParseUtil.class);


    /**
     * @param file
     * @return
     */
    public static List<Object[]> readFileToList(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        String suffixName = fileName.substring(fileName.lastIndexOf("."));
        try {
            switch (suffixName) {
                case FileTypeEnum.CSV:
                    return parseFileWithCsv(file);
                case FileTypeEnum.XLS:
                    return readExcelWithXLs(file);
                case FileTypeEnum.XLSX:
                    return parseExcelWithXlsx(file);
                default:
                    return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ArrayList<Object[]>();
    }

    private static List<Object[]> parseExcelWithXlsx(MultipartFile file) throws IOException {
        XSSFWorkbook xssfWorkbook = null;
        List<Object[]> list = new ArrayList<Object[]>();
        InputStream inputStream = file.getInputStream();
        try {
             xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            XSSFRow xssfRow = null;
            XSSFCell xssfCell = null;
            Object cellValue = null;
            for (int i = 1; i <= xssfSheet.getPhysicalNumberOfRows(); i++) {
                xssfRow = xssfSheet.getRow(i);
                if (null == xssfRow) {
                    continue;
                }

                Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
                for (int j = 0; j <= xssfRow.getLastCellNum(); j++) {
                    xssfCell = xssfRow.getCell(j);
                    if (null == xssfCell) {
                        continue;
                    }
                    switch (xssfCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cellValue = xssfCell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(xssfCell)) {

                                cellValue = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
                                break;
                            } else {
                                cellValue = NumberToTextConverter.toText(xssfCell.getNumericCellValue());
                                break;
                            }
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = xssfCell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cellValue = xssfCell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            cellValue = "";
                            break;
                        default:
                            cellValue = cellValue.toString();
                    }
                    values[j] = cellValue;
                }
                list.add(values);
            }
            return list;
        } catch (Exception e) {
            log.error("文件格式有误，请检查：{}", e);
            return null;
        } finally {
            if (null != inputStream) inputStream.close();
            if (null != xssfWorkbook) xssfWorkbook.close();
        }
    }

    private static List<Object[]> readExcelWithXLs(MultipartFile file) throws IOException {
        HSSFWorkbook hssfWorkbook = null;
        List<Object[]> list = new ArrayList<Object[]>();
        InputStream inputStream = file.getInputStream();
        try {
            hssfWorkbook = new HSSFWorkbook(inputStream);
            HSSFSheet xssfSheet = hssfWorkbook.getSheetAt(0);
            HSSFRow hssfRow = null;
            HSSFCell hssfCell = null;
            Object cellValue = null;
            for (int i = 1; i <= xssfSheet.getPhysicalNumberOfRows(); i++) {
                hssfRow = xssfSheet.getRow(i);
                if (null == hssfRow) {
                    continue;
                }
                Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
                for (int j = 0; j <= hssfRow.getLastCellNum(); j++) {
                    hssfCell = hssfRow.getCell(j);
                    if (null == hssfCell) {
                        continue;
                    }
                    switch (hssfCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cellValue = hssfCell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(hssfCell)) {
                                cellValue = DateUtil.getJavaDate(hssfCell.getNumericCellValue());
                                break;
                            } else {
                                cellValue = NumberToTextConverter.toText(hssfCell.getNumericCellValue());
                                break;
                            }
                        case Cell.CELL_TYPE_FORMULA:
                            cellValue = hssfCell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cellValue = hssfCell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            cellValue = "";
                            break;
                        default:
                            cellValue = cellValue.toString();
                    }
                    values[j] = cellValue;
                }
                list.add(values);
            }
            return list;
        } catch (Exception e) {
            log.error("文件格式有误，请检查：{}", e);
            return null;
        } finally {
            if (null != inputStream) inputStream.close();

            if (null != hssfWorkbook) hssfWorkbook.close();

        }
    }

    private static List<Object[]> parseFileWithCsv(MultipartFile file) {
        List<Object[]> list = new ArrayList<>();
        int i = 0;
        try {
            CSVReader csvReader = new CSVReaderBuilder(
                    new BufferedReader(
                            new InputStreamReader(file.getInputStream()))).build();
            Iterator<String[]> iterator = csvReader.iterator();
            while (iterator.hasNext()) {
                String[] next = iterator.next();
                //去除第一行的表头，从第二行开始
                if (i >= 1) {
                    list.add(next);
                }
                i++;
            }
            return list;
        } catch (Exception e) {
            log.error("CSV文件读取异常");
            return list;
        }
    }


    public interface FileTypeEnum {
        String CSV = ".csv";
        String XLS = ".xls";
        String XLSX = ".xlsx";
    }
}