IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

Java导出Excel案例怎么编写

wen java案例 22

本文目录导读：

Java导出Excel案例怎么编写

  1. 使用Apache POI（推荐）
  2. 使用EasyExcel（阿里开源的简单库）
  3. 工具类封装
  4. 注意事项

我来为您提供Java导出Excel的几种常见实现方式，包括使用Apache POI和EasyExcel两种主流库。

使用Apache POI（推荐）

Maven依赖

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

案例1：简单Excel导出

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class ExcelExportExample {
    public static void main(String[] args) {
        // 准备数据
        List<User> userList = new ArrayList<>();
        userList.add(new User(1, "张三", "zhangsan@example.com", 25));
        userList.add(new User(2, "李四", "lisi@example.com", 30));
        userList.add(new User(3, "王五", "wangwu@example.com", 28));
        // 导出Excel
        exportToExcel(userList, "users.xlsx");
    }
    public static void exportToExcel(List<User> userList, String fileName) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("用户信息");
        // 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建字体
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 12);
        headerStyle.setFont(headerFont);
        // 创建表头
        String[] headers = {"ID", "姓名", "邮箱", "年龄"};
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        // 填充数据
        for (int i = 0; i < userList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            User user = userList.get(i);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getEmail());
            row.createCell(3).setCellValue(user.getAge());
        }
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(fileName)) {
            workbook.write(fos);
            System.out.println("Excel文件导出成功：" + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    // 用户实体类
    static class User {
        private int id;
        private String name;
        private String email;
        private int age;
        public User(int id, String name, String email, int age) {
            this.id = id;
            this.name = name;
            this.email = email;
            this.age = age;
        }
        public int getId() { return id; }
        public String getName() { return name; }
        public String getEmail() { return email; }
        public int getAge() { return age; }
    }
}

案例2：带样式的复杂Excel导出

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class AdvancedExcelExport {
    public static void main(String[] args) {
        exportAdvancedExcel("advanced_report.xlsx");
    }
    public static void exportAdvancedExcel(String fileName) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("销售报告");
        // 1. 创建标题行（合并单元格）
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("2024年销售报告");
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        // 设置标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 18);
        titleStyle.setFont(titleFont);
        titleCell.setCellStyle(titleStyle);
        // 2. 创建信息行
        Row infoRow = sheet.createRow(1);
        Cell infoCell = infoRow.createCell(0);
        infoCell.setCellValue("导出时间：" + 
            LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
        // 3. 创建表头
        String[] headers = {"日期", "产品名称", "销量", "单价", "总金额"};
        Row headerRow = sheet.createRow(2);
        CellStyle headerStyle = createHeaderStyle(workbook);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        // 4. 填充示例数据
        Object[][] data = {
            {"2024-01-01", "产品A", 100, 99.9},
            {"2024-01-02", "产品B", 150, 199.9},
            {"2024-01-03", "产品C", 80, 299.9}
        };
        CellStyle dataStyle = createDataStyle(workbook);
        CellStyle currencyStyle = createCurrencyStyle(workbook);
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 3);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                } else if (data[i][j] instanceof Double) {
                    cell.setCellValue((Double) data[i][j]);
                }
                cell.setCellStyle(dataStyle);
            }
            // 计算总金额
            int quantity = (int) data[i][2];
            double price = (double) data[i][3];
            Cell totalCell = row.createCell(4);
            totalCell.setCellValue(quantity * price);
            totalCell.setCellStyle(currencyStyle);
        }
        // 5. 自动调整列宽
        for (int i = 0; i < 5; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10); // 增加宽度
        }
        // 保存文件
        try (FileOutputStream fos = new FileOutputStream(fileName)) {
            workbook.write(fos);
            System.out.println("高级Excel导出成功：" + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    private static CellStyle createCurrencyStyle(Workbook workbook) {
        CellStyle style = createDataStyle(workbook);
        style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
        return style;
    }
}

使用EasyExcel（阿里开源的简单库）

Maven依赖

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

案例3：EasyExcel导出

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.style.*;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.Data;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelExportExample {
    public static void main(String[] args) {
        exportWithEasyExcel();
    }
    public static void exportWithEasyExcel() {
        // 准备数据
        List<ExportData> dataList = new ArrayList<>();
        dataList.add(new ExportData(1, "张三", 25, "北京", 8000.50));
        dataList.add(new ExportData(2, "李四", 30, "上海", 12000.00));
        dataList.add(new ExportData(3, "王五", 28, "广州", 9500.75));
        // 导出Excel
        String fileName = "easyexcel_export.xlsx";
        EasyExcel.write(new File(fileName), ExportData.class)
                .sheet("员工信息")
                .doWrite(dataList);
        System.out.println("EasyExcel导出成功：" + fileName);
    }
    @Data
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 22)
    @HeadFontStyle(fontHeightInPoints = 12)
    public static class ExportData {
        @ExcelProperty("编号")
        private Integer id;
        @ExcelProperty("姓名")
        private String name;
        @ExcelProperty("年龄")
        private Integer age;
        @ExcelProperty("城市")
        private String city;
        @ExcelProperty("工资")
        @ContentStyle(dataFormat = 4) // 设置数字格式
        private Double salary;
        public ExportData(Integer id, String name, Integer age, String city, Double salary) {
            this.id = id;
            this.name = name;
            this.age = age;
            this.city = city;
            this.salary = salary;
        }
    }
}

工具类封装

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.function.Function;
import java.util.stream.IntStream;
public class ExcelExportUtil {
    /**
     * 泛型Excel导出工具方法
     * @param dataList 数据列表
     * @param headers 表头
     * @param fieldGetters 字段获取方法
     * @param fileName 文件名
     * @param <T> 数据类型
     */
    public static <T> void exportExcel(
            List<T> dataList,
            String[] headers,
            List<Function<T, Object>> fieldGetters,
            String fileName) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        // 创建表头
        Row headerRow = sheet.createRow(0);
        CellStyle headerStyle = createDefaultHeaderStyle(workbook);
        IntStream.range(0, headers.length).forEach(i -> {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        });
        // 填充数据
        CellStyle dataStyle = createDefaultDataStyle(workbook);
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            T data = dataList.get(i);
            for (int j = 0; j < fieldGetters.size(); j++) {
                Cell cell = row.createCell(j);
                Object value = fieldGetters.get(j).apply(data);
                setCellValue(cell, value);
                cell.setCellStyle(dataStyle);
            }
        }
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        // 写入文件
        try (OutputStream os = new FileOutputStream(fileName)) {
            workbook.write(os);
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            try {
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }
    private static CellStyle createDefaultHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }
    private static CellStyle createDefaultDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }
}

使用工具类示例

public class UtilExample {
    public static void main(String[] args) {
        List<User> users = new ArrayList<>();
        users.add(new User(1, "张三", "北京", 25));
        String[] headers = {"ID", "姓名", "城市", "年龄"};
        List<Function<User, Object>> fieldGetters = Arrays.asList(
            User::getId,
            User::getName,
            User::getCity,
            User::getAge
        );
        ExcelExportUtil.exportExcel(users, headers, fieldGetters, "users.xlsx");
    }
}

注意事项

  1. 内存管理：大数据量导出时注意内存溢出，建议使用分页或SXSSFWorkbook
  2. 版本兼容：.xls使用HSSFWorkbook，.xlsx使用XSSFWorkbook
  3. 性能优化：大数据量使用SXSSFWorkbook实现流式写入
  4. 样式控制：避免过多样式导致性能下降
  5. 异常处理：完善异常处理和资源释放

这些示例涵盖了Java导出Excel的常见场景,您可以根据实际需求选择合适的实现方式。

上一篇Java导入Excel案例如何实操

下一篇Java数据权限案例如何开发

相关文章

抱歉，评论功能暂时关闭!