Java 导出 Excel Sheet 的完整指南
在 Java 中导出 Excel 文件（特别是操作 Sheet）有多种方式，以下是几种主流方法的详细说明和代码示例。
（图片来源网络，侵删）
使用 Apache POI (推荐)
Apache POI 是最流行的 Java 操作 Office 文件的库。
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>
基本示例
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelExportExample {
public static void main(String[] args) {
// 创建新的工作簿
Workbook workbook = new XSSFWorkbook();
// 创建新的Sheet
Sheet sheet = workbook.createSheet("员工数据");
// 创建标题行
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("ID");
headerRow.createCell(1).setCellValue("姓名");
headerRow.createCell(2).setCellValue("部门");
headerRow.createCell(3).setCellValue("薪资");
// 添加数据行
Object[][] data = {
{1, "张三", "技术部", 8000},
{2, "李四", "市场部", 7500},
{3, "王五", "财务部", 6000}
};
int rowNum = 1;
for (Object[] rowData : data) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : rowData) {
row.createCell(colNum++).setCellValue(field.toString());
}
}
// 自动调整列宽
for (int i = 0; i < 4; i++) {
sheet.autoSizeColumn(i);
}
// 写入文件
try (FileOutputStream fileOut = new FileOutputStream("employees.xlsx")) {
workbook.write(fileOut);
System.out.println("Excel文件已成功创建");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
高级功能示例
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class AdvancedExcelExport {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("高级示例");
// 创建样式
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 应用标题样式
Row headerRow = sheet.createRow(0);
String[] headers = {"ID", "姓名", "年龄", "入职日期"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
// 添加数据
Row dataRow = sheet.createRow(1);
dataRow.createCell(0).setCellValue(101);
dataRow.createCell(1).setCellValue("赵六");
dataRow.createCell(2).setCellValue(28);
// 设置日期格式
CellStyle dateStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));
Cell dateCell = dataRow.createCell(3);
dateCell.setCellValue(java.sql.Date.valueOf("2025-05-15"));
dateCell.setCellStyle(dateStyle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
Row mergeRow = sheet.createRow(2);
mergeRow.createCell(0).setCellValue(102);
// 冻结窗格
sheet.createFreezePane(1, 1); // 冻结第一行和第一列
// 写入文件
try (FileOutputStream fileOut = new FileOutputStream("advanced_example.xlsx")) {
workbook.write(fileOut);
} finally {
workbook.close();
}
}
}
使用 EasyExcel (阿里开源)
EasyExcel 是阿里巴巴开源的 Excel 处理框架，性能更好，内存占用更低。
Maven 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
示例代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelExample {
public static void main(String[] args) {
// 准备数据
List<User> dataList = new ArrayList<>();
dataList.add(new User(1, "张三", 25, "技术部"));
dataList.add(new User(2, "李四", 30, "市场部"));
dataList.add(new User(3, "王五", 28, "财务部"));
// 文件输出路径
String fileName = "easyexcel_example.xlsx";
// 头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 样式策略
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// EasyExcel写入
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, User.class)
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();
excelWriter.write(dataList, writeSheet);
System.out.println("Excel文件已成功创建");
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
// 实体类
public static class User {
private Integer id;
private String name;
private Integer age;
private String department;
// 构造方法、getter和setter省略...
public User(Integer id, String name, Integer age, String department) {
this.id = id;
this.name = name;
this.age = age;
this.department = department;
}
}
}
使用 JXL (较老但简单)
JXL 是一个较老的库，但API简单易用。
Maven 依赖
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
示例代码
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import java.io.File;
import java.io.IOException;
public class JxlExample {
public static void main(String[] args) {
try {
// 创建可写入的Excel工作簿
WritableWorkbook workbook = Workbook.createWorkbook(new File("jxl_example.xls"));
// 创建Sheet
WritableSheet sheet = workbook.createSheet("员工数据", 0);
// 添加标题
sheet.addCell(new Label(0, 0, "ID"));
sheet.addCell(new Label(1, 0, "姓名"));
sheet.addCell(new Label(2, 0, "部门"));
// 添加数据
sheet.addCell(new Label(0, 1, "1"));
sheet.addCell(new Label(1, 1, "张三"));
sheet.addCell(new Label(2, 1, "技术部"));
sheet.addCell(new Label(0, 2, "2"));
sheet.addCell(new Label(1, 2, "李四"));
sheet.addCell(new Label(2, 2, "市场部"));
// 写入文件
workbook.write();
workbook.close();
System.out.println("Excel文件已成功创建");
} catch (IOException | WriteException e) {
e.printStackTrace();
}
}
}
性能比较
| 库 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| Apache POI | 功能全面，支持所有Excel特性 | 内存占用高，大数据量时性能较差 | 需要复杂Excel操作，数据量不大 |
| EasyExcel | 内存占用低，性能好 | 功能相对POI较少 | 大数据量导出，对性能要求高 |
| JXL | API简单，学习成本低 | 功能有限，不支持新版本Excel | 简单导出需求，旧项目维护 |
最佳实践建议
- 数据量小：使用 Apache POI，功能最全面
- 数据量大：使用 EasyExcel，性能更好
- 简单导出：可以使用 JXL 或 EasyExcel
- 样式复杂：推荐使用 Apache POI
- Web应用导出：考虑使用 EasyExcel 或 POI SXSSF (流式API)
希望这些示例能帮助你根据需求选择合适的Excel导出方案！
（图片来源网络，侵删）
（图片来源网络，侵删）
