处理大量数据的Excel报表

处理大量数据的Excel报表

1. 报表导出

1.1 需求

我们需要导出包含数百万行数据的Excel报表，同时确保在处理大数据量时不会出现内存溢出问题。

1.2 解决方案

1.2.1 思路

传统的Excel导出方式通常会将所有数据一次性加载到内存中，这可能导致内存占用激增。为了解决这个问题，我们采用了Apache POI库中的SXSSFWorkbook，它可以用于处理大规模数据量的Excel报表导出。

1.2.2 原理

SXSSFWorkbook允许将数据对象分块写入磁盘，以避免内存溢出。当内存中的对象数量达到指定值时，数据将被写入磁盘（以XML格式存储），然后从内存中释放。

1.3 代码示例

在代码中，首先构建需要导出的数据，然后创建一个SXSSFWorkbook对象。接着逐行创建表格数据并将其写入Excel文件。最后，设置响应头，以便用户下载生成的Excel文件。

// 构建数据
List dataList = TestService.findByReport(testId, month + "%");

// 创建工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();

// 创建工作表和表头
String[] columnHeaders = {"编号", "姓名", "手机", /*...其他列名...*/};
Sheet sheet = workbook.createSheet();
Row headerRow = sheet.createRow(0);

for (int i = 0; i < columnHeaders.length; i++) {
    Cell cell = headerRow.createCell(i);
    cell.setCellValue(columnHeaders[i]);
}

// 逐行写入数据
for (int i = 0; i < dataList.size(); i++) {
    Row dataRow = sheet.createRow(i + 1);
    TestDataResult report = dataList.get(i);
    // 将数据写入单元格
    // ...
}

// 导出Excel文件
// 设置响应头
String fileName = URLEncoder.encode(month + "人员信息.xlsx", "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

// 将工作簿写入响应流
workbook.write(response.getOutputStream());

2. 报表导入

2.1 需求

在处理大量数据时，我们需要能够从Excel文件中导入数据，同时避免内存溢出问题。

2.2 解决方案

2.2.1 思路

传统的Excel读取方式通常将整个文件加载到内存中，然后逐一解析每个单元格。对于大数据量的Excel文件，这可能会导致内存不足或OOM异常。为了解决这个问题，我们采用了事件驱动的SAX解析方式，它逐行扫描文档并逐行解析，无需将整个文件存储在内存中。

2.2.2 原理

在事件模式下，Excel文件会被逐行扫描和解析，只有在读取数据时才会加载到内存中，因此适用于大型文档。我们使用自定义的SheetHandler来处理每一行的数据，并将其封装成实体对象。

2.3 代码示例

PoiEntity类

public class PoiEntity {
    private String id;
    private String breast;
    private String adipocytes;
    private String negative;
    private String staining;
    private String supportive;

    // 各属性的getters和setters
}

自定义处理器SheetHandler类

import cn.itcast.poi.entity.PoiEntity;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    private PoiEntity entity;

    @Override
    public void startRow(int i) {
        if (i > 0) {
            entity = new PoiEntity();
        }
    }

    @Override
    public void endRow(int i) {
        // 使用实体对象进行业务操作，例如存储到数据库或进行其他处理
        System.out.println(entity);
    }

    @Override
    public void cell(String cellReference, String value, XSSFComment xssfComment) {
        if (entity != null) {
            String col = cellReference.substring(0, 1);
            switch (col) {
                case "A":
                    entity.setId(value);
                    break;
                case "B":
                    entity.setBreast(value);
                    break;
                case "C":
                    entity.setAdipocytes(value);
                    break;
                case "D":
                    entity.setNegative(value);
                    break;
                case "E":
                    entity.setStaining(value);
                    break;
                case "F":
                    entity.setSupportive(value);
                    break;
                default:
                    break;
            }
        }
    }
}

ExcelParser类

public class ExcelParser {
    public void parse(String path) throws Exception {
        // 打开Excel文件
        OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);

        try {
            // 创建XSSFReader对象
            XSSFReader reader = new XSSFReader(pkg);

            // 获取SharedStringsTable对象
            SharedStringsTable sst = reader.getSharedStringsTable();

            // 获取StylesTable对象
            StylesTable styles = reader.getStylesTable();

            // 创建SAX的XmlReader对象
            XMLReader parser = XMLReaderFactory.createXMLReader();

            // 设置事件处理器
            parser.setContentHandler(new XSSFSheetXMLHandler(styles, sst, new SheetHandler(), false));

            // 获取所有Sheet并逐行读取
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
            while (sheets.hasNext()) {
                InputStream sheetStream = sheets.next();
                InputSource sheetSource = new InputSource(sheetStream);
                try {
                    // 开始解析
                    parser.parse(sheetSource);
                } finally {
                    sheetStream.close();
                }
            }
        } finally {
            pkg.close();
        }
    }
}