ExcelUtils EXCEL文档解析工具类

import com.example.demo.util.idmake.KeyGenerator;
import org.apache.commons.lang.StringUtils;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * EXCEL文档解析工具类
 * 该工具能将EXCEL文档中的表解析为由JAVA基础类构成的数据集合
 * 整个EXCEL表由多个行组成,每行用一个LIST表示.
 * EXCEL中的行由一个LIST表示,各列的数据索引从0开始一一对齐存放在这个LIST中; 多个行构成整个表,由一个LIST存放多个行.
 */
public class ExcelUtils {

    private Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    private HSSFWorkbook workbook;

    /**
     * 文件输入流方式获取文件
     *
     * @param excelFile
     * @throws IOException
     */
    public ExcelUtils(File excelFile) throws IOException {
        workbook = new HSSFWorkbook(new FileInputStream(excelFile));
    }

    /**
     * 纯输入流方式获取文件
     *
     * @param is
     * @throws IOException
     */
    public ExcelUtils(InputStream is) throws IOException {
        workbook = new HSSFWorkbook(is);
    }

    /**
     * 获得表中的数据
     *
     * @param sheetNum 表格索引 从0开始
     * @param isAddId  是否自动增加id
     * @return
     * @throws IOException
     */
    public List<String[]> getDatasFromSheet(int sheetNum, boolean isAddId) throws IOException {
        // 表格数据对象
        List<String[]> resultList = new ArrayList<String[]>();
        // 获得指定的表
        HSSFSheet sheet = workbook.getSheetAt(sheetNum);
        // 获得数据总行数
        int rowCount = sheet.getLastRowNum();
        logger.info("excel表总行数: " + rowCount);
        if (rowCount < 1) {
            return null;
        }
        // 逐行读取数据
        for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            HSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            // 本行单元格的个数
            int columnCount = row.getLastCellNum();
            if (isAddId) {
                columnCount = row.getLastCellNum() + 1;
            }
            String[] rowData = getRowDatas(row, columnCount, isAddId);
            if (rowData != null) {
                resultList.add(rowData);
            }
        }
        return resultList;
    }

    /**
     * 获得表中的数据
     *
     * @param sheetNum  表格索引 从0开始
     * @param columnNum 每行数据个数
     * @param isAddId   是否自动增加id
     * @return
     * @throws IOException
     */
    public List<String[]> getDatasFromSheet(int sheetNum, int columnNum, boolean isAddId) throws IOException {
        // 表格数据对象
        List<String[]> resultList = new ArrayList<String[]>();
        // 获得指定的表
        HSSFSheet sheet = workbook.getSheetAt(sheetNum);
        // 获得数据总行数
        int rowCount = sheet.getLastRowNum();
        logger.info("excel表总行数: " + rowCount);
        if (rowCount < 1) {
            return null;
        }
        if (isAddId) {
            columnNum++;
        }
        // 逐行读取数据
        for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            HSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            String[] rowData = getRowDatas(row, columnNum, isAddId);
            if (rowData != null) {
                resultList.add(rowData);
            }
        }
        return resultList;
    }

    /**
     * 获得表中的数据
     *
     * @param sheetNumber 表格索引 从0开始
     * @param flag        flag=0 不增加id,flag=1自动增加id
     * @return
     * @throws IOException
     */
    public List<Object[]> getNewDatasInSheet(int sheetNumber, int flag) throws IOException {
        List<Object[]> result = new ArrayList<Object[]>();
        // 获得指定的表
        HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        // 获得数据总行数
        int rowCount = sheet.getLastRowNum();
        //int rowCount=this.getRightRows(sheetAs);
        int sjrsCols = 0;
        logger.info("found excel rows count: " + rowCount);
        if (rowCount < 1) {
            return null;
        }
        // 逐行读取数据
        for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
            // 获得行对象
            HSSFRow row = sheet.getRow(rowIndex);
            if (row != null) {
                // 获得本行中单元格的个数
                int columnCount = 0;
                if (flag == 0) {
                    columnCount = row.getLastCellNum();
                } else {
                    columnCount = row.getLastCellNum() + 1;
                }
                Object[] rowData = new Object[columnCount];
                // 获得本行中各单元格中的数据
                if (flag == 0) {
                    for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                        HSSFCell cell = row.getCell(columnIndex);
                        // 获得指定单元格中数据
                        Object cellStr = this.getCellString(cell);
                        if (cellStr != null) {
                            if (cellStr.toString().trim().length() > 0) {
                                rowData[columnIndex] = cellStr.toString().trim();
                                sjrsCols++;
                            }
                        }
                    }
                } else {
                    for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                        if (columnIndex == 0) {
                            rowData[0] = KeyGenerator.getUUID();
                        } else {
                            // 获得指定单元格中数据
                            HSSFCell cell = row.getCell(columnIndex - 1);
                            Object cellStr = this.getCellString(cell);
                            if (cellStr != null) {
                                if (cellStr.toString().trim().length() > 0) {
                                    rowData[columnIndex] = cellStr.toString().trim();
                                    sjrsCols++;
                                }
                            }
                        }
                    }
                }
                if (sjrsCols != 0) {
                    result.add(rowData);
                }
                sjrsCols = 0;
            }
        }
        return result;
    }

    /**
     * 获取单行单元格数据
     * @param row
     * @param columnNum
     * @param isAddId
     * @return
     */
    private String[] getRowDatas(HSSFRow row, int columnNum, boolean isAddId) {
        String[] rowData = new String[columnNum];
        boolean hasData = false;
        for (int i = 0; i < columnNum; i++) {
            int index = i;
            if (isAddId) {
                index = i - 1;
                if (i == 0) {
                    rowData[0] = KeyGenerator.getUUID();
                }
            }
            HSSFCell cell = row.getCell(index);
            // 获得指定单元格中数据
            String cellStr = this.getCellString(cell);
            if (StringUtils.isNotBlank(cellStr)) {
                rowData[i] = cellStr;
                hasData = true;
            }
        }
        if (hasData) {
            return rowData;
        } else {
            return null;
        }
    }

    /**
     * 获得单元格中的内容
     *
     * @param cell 单元格对象
     * @return
     */
    protected String getCellString(HSSFCell cell) {
        String result = null;
        if (cell != null) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cell.getRichStringCellValue().getString();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    DecimalFormat df = new DecimalFormat("#");
                    result = df.format(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    result = null;
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue() ? "true" : "false";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    result = null;
                    break;
            }
            if (result != null) {
                result = result.trim();
            }
        }
        return result;
    }
}

文章永久链接：https://tech.souyunku.com/25021