Java生成并下载Excel文件

在Java中生成Excel文件并提供下载功能,通常可以使用Apache POI或EasyExcel等库，以下是几种常见实现方式：

使用Apache POI生成Excel并下载

基本实现（Servlet环境）

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
public class ExcelDownloadServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        // 1. 创建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2. 创建工作表
        Sheet sheet = workbook.createSheet("员工信息");
        // 3. 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("姓名");
        headerRow.createCell(1).setCellValue("年龄");
        headerRow.createCell(2).setCellValue("部门");
        // 4. 添加数据
        Row dataRow1 = sheet.createRow(1);
        dataRow1.createCell(0).setCellValue("张三");
        dataRow1.createCell(1).setCellValue(28);
        dataRow1.createCell(2).setCellValue("技术部");
        Row dataRow2 = sheet.createRow(2);
        dataRow2.createCell(0).setCellValue("李四");
        dataRow2.createCell(1).setCellValue(32);
        dataRow2.createCell(2).setCellValue("市场部");
        // 5. 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=employee.xlsx");
        // 6. 写入输出流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

Spring Boot环境实现

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
@RestController
public class ExcelController {
    @GetMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws IOException {
        // 1. 创建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2. 创建工作表
        Sheet sheet = workbook.createSheet("员工信息");
        // 3. 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("姓名");
        headerRow.createCell(1).setCellValue("年龄");
        headerRow.createCell(2).setCellValue("部门");
        // 4. 添加数据（这里使用模拟数据）
        List<Employee> employees = getMockEmployees();
        int rowNum = 1;
        for (Employee employee : employees) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(employee.getName());
            row.createCell(1).setCellValue(employee.getAge());
            row.createCell(2).setCellValue(employee.getDepartment());
        }
        // 5. 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=employee.xlsx");
        // 6. 写入输出流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();
        } finally {
            workbook.close();
        }
    }
    private List<Employee> getMockEmployees() {
        List<Employee> employees = new ArrayList<>();
        employees.add(new Employee("张三", 28, "技术部"));
        employees.add(new Employee("李四", 32, "市场部"));
        employees.add(new Employee("王五", 25, "财务部"));
        return employees;
    }
    static class Employee {
        private String name;
        private int age;
        private String department;
        // 构造方法、getter和setter省略...
    }
}

使用EasyExcel生成Excel并下载

EasyExcel是阿里巴巴开源的Excel处理库,性能更好，内存占用更低。

Maven依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

Spring Boot环境实现

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
@RestController
public class EasyExcelController {
    @GetMapping("/downloadEasyExcel")
    public void downloadEasyExcel(HttpServletResponse response) throws IOException {
        // 1. 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=employee_easy.xlsx");
        // 2. 准备数据
        List<Employee> employees = getMockEmployees();
        // 3. 写入Excel
        try (OutputStream out = response.getOutputStream()) {
            EasyExcel.write(out, Employee.class)
                    .sheet("员工信息")
                    .doWrite(employees);
        }
    }
    private List<Employee> getMockEmployees() {
        List<Employee> employees = new ArrayList<>();
        employees.add(new Employee("张三", 28, "技术部"));
        employees.add(new Employee("李四", 32, "市场部"));
        employees.add(new Employee("王五", 25, "财务部"));
        return employees;
    }
    static class Employee {
        @ExcelProperty("姓名")
        private String name;
        @ExcelProperty("年龄")
        private int age;
        @ExcelProperty("部门")
        private String department;
        // 构造方法、getter和setter省略...
    }
}

高级功能：大数据量处理

对于大数据量Excel,建议使用EasyExcel的SXSSFWorkbook模式：

import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
@RestController
public class LargeExcelController {
    @GetMapping("/downloadLargeExcel")
    public void downloadLargeExcel(HttpServletResponse response) throws IOException {
        // 1. 创建SXSSFWorkbook（用于大数据量）
        Workbook workbook = new SXSSFWorkbook(100); // 内存中保留100行，其余写入临时文件
        // 2. 创建工作表
        Sheet sheet = workbook.createSheet("大数据量");
        // 3. 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("名称");
        headerRow.createCell(2).setCellValue("描述");
        // 4. 添加大量数据
        for (int i = 1; i <= 100000; i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue("项目" + i);
            row.createCell(2).setCellValue("这是第" + i + "个项目的描述");
        }
        // 5. 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=large_data.xlsx");
        // 6. 写入输出流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();
        } finally {
            // 清理临时文件
            if (workbook instanceof SXSSFWorkbook) {
                ((SXSSFWorkbook) workbook).dispose();
            }
            workbook.close();
        }
    }
}

注意事项

1. 内存管理：大数据量处理时，使用SXSSFWorkbook或EasyExcel的流式API避免内存溢出
2. 文件名编码：中文文件名需要正确编码，可以使用URLEncoder.encode()
3. 异常处理：确保资源被正确关闭，可以使用try-with-resources
4. 浏览器兼容性：设置正确的Content-Type
5. 性能考虑：EasyExcel在内存使用上通常优于Apache POI

代码提供了从简单到复杂的Excel生成和下载方案,您可以根据实际需求选择合适的实现方式。