使用Java上传Excel解析工具类(基于springboot)

上传excel分为.xls和.xlsx两种类型的文件

pom.xml

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

excel解析工具类

package com.abke.bi.utils;

import lombok.extern.slf4j.Slf4j;
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.CellType;
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.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: XXXX
 * @Time: 2020/5/28 0028
 * @Description: 解析excel工具类
 */
@Slf4j
public class ReadExcelUtil {

    /**
     * sheet中总行数
     */
    private int totalRows;

    /**
     * 每一行总单元格数
     */
    private static int totalCells;

    /**
     * read the Excel .xlsx,.xls
     *
     * @param file 上传的excel文件
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readExcel(MultipartFile file) {
        if (file == null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())) {
            return null;
        } else {
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            if (!ExcelUtil.EMPTY.equals(postfix)) {
                if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(file);
                } else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(file);
                } else {
                    return null;
                }
            }
        }
        return null;
    }

    /**
     * read the Excel 2010 .xlsx
     *
     * @param file
     * @return
     * @throws IOException
     */
    @SuppressWarnings("deprecation")
    public List<ArrayList<String>> readXlsx(MultipartFile file) {
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        ArrayList<String> columeList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            // 读取第一个sheet(页)
            XSSFSheet xssfSheet = wb.getSheetAt(0);
            totalRows = xssfSheet.getLastRowNum();

            // 控制
            if (totalRows > 4999) {
                totalRows = 4999;
            }
            // 获取第一行表头信息
            XSSFRow xssfRow1 = xssfSheet.getRow(0);
            if (xssfRow1 != null) {
                columeList = new ArrayList<String>();
                totalCells = xssfRow1.getLastCellNum();
                // 读取列，从第一列开始
                for (int c = 0; c <= totalCells + 1; c++) {
                    XSSFCell cell = xssfRow1.getCell(c);
                    if (cell == null || "".equals(cell)) {
                        break;
                    }
                    cell.setCellType(CellType.STRING);
                    columeList.add("1");
                }
            }
            // 读取Row,从第1行开始
            for (int rowNum = 0; rowNum <= totalRows; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null) {
                    break;
                }

                if (xssfRow != null) {
                    rowList = new ArrayList<String>();
                    // 读取列，从第一列开始
                    for (int c = 0; c <= columeList.size() - 1; c++) {
                        XSSFCell cell = xssfRow.getCell(c);
                        if (cell == null || "".equals(cell)) {
                            rowList.add("");
                            continue;
                        }
                        // cell.setCellType(CellType.STRING);
                        rowList.add(ExcelUtil.getXValue(cell).trim());
                    }
                    list.add(rowList);
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }

    /**
     * read the Excel 2003-2007 .xls
     *
     * @param file
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readXls(MultipartFile file) {
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        ArrayList<String> columeList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页)
            HSSFSheet hssfSheet = wb.getSheetAt(0);
            totalRows = hssfSheet.getLastRowNum();

            // 控制excel行数
            if (totalRows > 4999) {
                totalRows = 4999;
            }

            // 获取第一行表头信息数量
            HSSFRow row = hssfSheet.getRow(0);
            totalCells = row.getLastCellNum();
            columeList = new ArrayList<String>();
            for (int i = 0; i <= totalCells + 1; i++) {
                HSSFCell cell = row.getCell(i);
                if (cell == null || "".equals(cell)) {
                    break;
                }
                cell.setCellType(CellType.STRING);
                columeList.add("1");
            }


            //读取Row,从第1行开始
            for (int rowNum = 0; rowNum <= totalRows; rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    rowList = new ArrayList<String>();
                    //读取列，从第一列开始
                    for (short c = 0; c <= columeList.size() - 1; c++) {
                        HSSFCell cell = hssfRow.getCell(c);
                        if (cell == null || "".equals(cell)) {
                            rowList.add("");
                            continue;
                        }
                        // cell.setCellType(CellType.STRING);
                        rowList.add(ExcelUtil.getHValue(cell).trim());

                    }
                    list.add(rowList);
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

ExcelUtil

package com.abke.bi.utils;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author ＸＸＸ
 * @Date 2020/5/27 22:48
 * @Description Excel文件解析工具类
 */
@Component
@Slf4j
public class ExcelUtil {


    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    /**
     * 获得path的后缀名
     *
     * @param path
     * @return
     */
    public static String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }

    /**
     * 单元格格式
     *
     * @param hssfCell
     * @return
     */
    public static String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            String cellValue = "";
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 单元格格式
     *
     * @param xssfCell
     * @return
     */
    public static String getXValue(XSSFCell xssfCell) {
        if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            String cellValue = "";
            if (XSSFDateUtil.isCellDateFormatted(xssfCell)) {
                Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    /**
     * 自定义xssf日期工具类
     */
    static class XSSFDateUtil extends DateUtil {
        protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
            return DateUtil.absoluteDay(cal, use1904windowing);
        }
    } 
}