Java Excel导出方案

Java Excel导出方案

Java接口实现excel导出,本地制作生成Excel文件,在通过http请求将Excel传输到客户端,最后删除本地生成的excel防止占用服务器硬盘空间,同时也防止了如果Excel文件比较大，可能会导致内存占用过高。为防止内存溢出.

以下是使用Apache POI 建单Demo

 import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;

public class ExcelExportServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        // 创建一个Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Sheet1");

        // 填充表格数据
        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue("Cell-" + i + "-" + j);
            }
        }

        // 将Excel工作簿写入到一个临时文件
        File tempFile = File.createTempFile("excel-export-", ".xlsx");
        try (FileOutputStream fos = new FileOutputStream(tempFile)) {
            workbook.write(fos);
        }

        // 设置响应头信息
        resp.setContentType("application/vnd.ms-excel");
        resp.setHeader("Content-Disposition", "attachment;filename=" + "exported-file.xlsx");

        // 读取临时文件并将其写入到HTTP响应输出流
        try (FileInputStream fis = new FileInputStream(tempFile);
             ServletOutputStream os = resp.getOutputStream()) {
            byte[] buffer = new byte[1024];
            int len;
            while ((len = fis.read(buffer)) != -1) {
                os.write(buffer, 0, len);
            }
        }

        // 删除临时文件
        Files.delete(tempFile.toPath());
    }
}

Apache POI的Maven依赖

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