超简单读取Excel使用Apache POI java api包括被忽略的空单元格处理与空行

快速读取分析excel文件 .xls .xlsx, 使用Apache POI java api

程序过程：读取文件，遍历excel文件的每个sheet(单页excel)，然后是遍历每个行列，比SAXParser解析简单很多

需要注意的是

1. excel的单元格不同类型取数据问题
2. 2.获取的行列总数是有效数据的行列总数，统计总数不包括空行与空单元格 (可能是由于excel保存文件内容的格式，只保存了有数据行的数据与行id，有效节约空间)

SAXParser解析excel可以看这篇
秒懂POI解析excel，SAXParser解析大xlsx，XSSFReader处理包括被忽略的空单元格处理

idea建立空maven项目

//pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dddd</groupId>
    <artifactId>apache-poi</artifactId>
    <version>1.0</version>

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;


@Slf4j
public class ExcelUtil {

    /**
     * excel的二进制读取数据
     * @param data
     * @return
     */
    public static List<Map<String,List<List<Object>>>> getData(byte[] data){
        List<Map<String,List<List<Object>>>> excelData = new ArrayList<>();
        try {
            Workbook workbook =   WorkbookFactory.create(new ByteArrayInputStream(data));
            excelData = getData(workbook);
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        return excelData;
    }

    /**
     * excel文件读取数据
     * @param fileName
     * @return
     */
    public static List<Map<String,List<List<Object>>>> getData(String fileName){
        List<Map<String,List<List<Object>>>> excelData = new ArrayList<>();
        try {
            Workbook workbook =   WorkbookFactory.create(new File(fileName)); // 自动判断格式
            excelData = getData(workbook);
        } catch (Exception e) {
            log.error("error xls:{}",fileName);
            e.printStackTrace();
        }
        return excelData;
    }

    /**
     * excel内容转list，支持多个工作页sheet
     * map就是工作页名称与数据
     * List<List<Object>> 就是单页的行列数据
     * @param workbook workbook
     * @return
     */
    private static List<Map<String,List<List<Object>>>> getData(Workbook workbook){
        List<Map<String,List<List<Object>>>> excelData = new ArrayList<>();
        Iterator<Sheet> sheetItr = workbook.sheetIterator();
        while (sheetItr.hasNext()){
            Sheet oneSheet = sheetItr.next();
            // excel单个页的最大尺寸，行数，列数，防止空行与单元格
            int rows = oneSheet.getLastRowNum(); //最大行数， 从0开始的，所以长度加1，但是
            int cols = 0; //最大列数
            for (int i = 0; i < rows + 1; i++) {
                if (oneSheet.getRow(i) == null){
                    continue;
                }
                int curCols = oneSheet.getRow(i).getLastCellNum(); //已经加1了
                cols = Math.max(cols,curCols);
            }
            if (rows > 0) rows += 1; // 0表示这页没有内容，加1会出错
            log.info("sheet size:{} {}",rows,cols);
            String oneSheetName = oneSheet.getSheetName();
            Map<String,List<List<Object>>> sheetDataMap = new HashMap<>();
            // 单页没有数据
            if (rows == 0){
                sheetDataMap.put(oneSheetName,null);
                excelData.add(sheetDataMap);
                continue;
            }
            List<List<Object>> sheetData = new ArrayList<>();
            sheetDataMap.put(oneSheetName,sheetData);
            excelData.add(sheetDataMap);
            for (int i = 0; i < rows; i++) {
                List<Object> rowData = new ArrayList<>();
                Row oneRow = oneSheet.getRow(i);
                if ( oneRow == null){
                    sheetData.add(fakeEmptyRow(cols));
                    continue;
                }
                sheetData.add(rowData);
                for (int j = 0; j < cols; j++) {
                    Cell oneCell = oneRow.getCell(j);
                    if (oneCell == null || oneCell.getCellTypeEnum() == CellType.BLANK
                            || (oneCell.getCellTypeEnum() == CellType.STRING && oneCell.getStringCellValue().length() == 0)){
                        if (oneCell == null){
                            rowData.add(null);
                        }else if(oneCell.getCellTypeEnum() == CellType.BLANK){ //空单元格
//                                rowData.add("blank"); // debug
                            rowData.add(null);
                        }else if(oneCell.getCellTypeEnum() == CellType.STRING && oneCell.getStringCellValue().length() == 0){ //空字符
//                                rowData.add("nullStr"); // debug
                            rowData.add(null);
                        }
                    }else{
                        rowData.add(getCellValue(oneCell));
                    }
                }
            }
        }
        return excelData;
    }

    private static Object getCellValue(Cell oneCell){
        Object cellValue = null;
        switch (oneCell.getCellTypeEnum()) {
            case NUMERIC:
                cellValue = oneCell.getNumericCellValue();
                break;
            case STRING:
                cellValue = oneCell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(oneCell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = oneCell.getCellFormula();
                break;
            case BLANK:
                cellValue = null;
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static List<Object> fakeEmptyRow(int cols){
        List<Object> fakeRow = new ArrayList<>();
        for (int i = 0; i < cols; i++) {
            fakeRow.add(null);
        }
        return fakeRow;
    }

    /**
     * 生成excel的二进制数据
     * @param excelData
     * @return
     */
    public static byte[] toExcel(List<Map<String,List<List<Object>>>> excelData){
        if (excelData == null) return null;
        Workbook workbook = new XSSFWorkbook();
        for(Map<String,List<List<Object>>> sheet:excelData){
            String sheetName = sheet.entrySet().iterator().next().getKey();
            Sheet oneSheet = workbook.createSheet(sheetName);
            List<List<Object>> sheetData = sheet.get(sheetName);
            if (sheetData == null) continue;
            int rows = sheetData.size();
            int cols = 0;
            for(List<Object> oneRow:sheetData){
                if (oneRow == null) continue;
                int curCols = oneRow.size();
                cols = Math.max(cols,curCols);
            }
            for (int i = 0; i < rows; i++) {
                List<Object> rowData = sheetData.get(i);
                if (rowData == null) continue;
                Row oneRow = oneSheet.createRow(i);
                for (int j = 0; j < cols && j < rowData.size(); j++) {
                    if (rowData.get(j) == null) continue;
                    Cell oneCell = oneRow.createCell(j);
                    setCellValue(oneCell,rowData.get(j));
                }
            }
        }
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return bos.toByteArray();
    }

    private static void setCellValue(Cell cell, Object obj){
        if(obj == null){
        }else if(obj instanceof String){
            cell.setCellValue((String) obj);
        }else if(obj instanceof Integer){
            cell.setCellValue((Integer) obj);
        }else if(obj instanceof Double){
            cell.setCellValue((Double) obj);
        }else if(obj instanceof Float){
            cell.setCellValue((Float) obj);
        }else{
            cell.setCellValue(obj.toString());
        }
    }

    /**
     * 二进制存入excel文件
     * @param fileName
     * @param data
     */
    public static void toFile(String fileName, byte[] data){
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(fileName));
            fos.write(data);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException ioe) {
                System.out.println("Error while closing stream: " + ioe);
            }
        }
    }
}

测试用例

import org.junit.jupiter.api.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.*;

class ExcelUtilTest {

    @Test
    void getData() {
        List<Map<String,List<List<Object>>>> excelData = ExcelUtil.getData("2020-02-26-1.xls");
        System.out.println(excelData);
    }

    @Test
    void toExcel() {
        List<Map<String,List<List<Object>>>> excelData1 = ExcelUtil.getData("one.xls");
        byte[] data = ExcelUtil.toExcel(excelData1);
        System.out.println(data);
        List<Map<String,List<List<Object>>>> excelData2 = ExcelUtil.getData(data);
        System.out.println(excelData2.size());

        // 自定义表格数据测试
        List<Object> row1 = new ArrayList<>();
        row1.add(null);
        row1.add(1234);
        row1.add(null);
        List<Object> row2 = new ArrayList<>();
        row2.add("abc");
        List<Object> row3 = new ArrayList<>();
        row3.add(null);
        row3.add(null);
        row3.add(null);
        List<List<Object>> sheetData = new ArrayList<>();
        sheetData.add(row1);
        sheetData.add(null);
        sheetData.add(row2);
        sheetData.add(row3);
        sheetData.add(null);
        sheetData.add(null);
        Map<String,List<List<Object>>> sheet = new HashMap<>();
        sheet.put("my excel我的表格999", sheetData);
        List<Map<String,List<List<Object>>>> excelDataCustom = new ArrayList<>();
        excelDataCustom.add(sheet);

        List<Map<String,List<List<Object>>>> excelData3 = ExcelUtil.getData(ExcelUtil.toExcel(excelDataCustom));
        ExcelUtil.toFile("excelDataCustom.xls",ExcelUtil.toExcel(excelDataCustom));
    }

    @Test
    void toFile() {
        List<Map<String,List<List<Object>>>> excelData = ExcelUtil.getData("one.xls");
        byte[] data = ExcelUtil.toExcel(excelData);
        ExcelUtil.toFile("test.xls",data);
        // 生成的excel再转生成一次，比对与第一次excel的差别，判断程序问题
        excelData = ExcelUtil.getData("test.xls");
        data = ExcelUtil.toExcel(excelData);
        ExcelUtil.toFile("test2.xls",data);
    }
}