Java导出数据到Excel

Java 导出数据到Excel

XSSFWorkbook wb = new XSSFWorkbook();
第1步、构建Workbook 数据
buildData(wb, persons);
第2步、设置Workbook 格式
buildStyle(wb);
第3步、导出到Excel
writeExcel(wb);

第1步、构建Workbook 数据

public static void buildData(Workbook wb, List<Person> list) {
    // 创建sheet页
    Sheet sheetName = wb.createSheet("sheetName");
    
    // 填充表头
    Row row = sheetName.createRow(0);
    row.createCell(0).setCellValue("Id");
    row.createCell(1).setCellValue("Name");
    row.createCell(2).setCellValue("Age");
    row.createCell(3).setCellValue("NickName");
    
    // 填充内容
    for (int i = 0; i < list.size(); i++) {
        Person person = list.get(i);
        row = sheetName.createRow(i + 1);
        row.createCell(0).setCellValue(person.getId());
        row.createCell(1).setCellValue(person.getName());
        row.createCell(2).setCellValue(person.getAge());
        row.createCell(3).setCellValue(person.getNickName());
    }
    
    // sheet页重命名
    wb.setSheetName(0, "sheet");
}

第2步、设置Workbook 格式

public static void buildStyle(Workbook wb) {
    Sheet sheet;
    Row row;
    for (int s = 0; s < wb.getNumberOfSheets(); s++) { // 遍历Sheet页
        sheet = wb.getSheetAt(s);
        for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { // 遍历所有行
            row = sheet.getRow(r);
            for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { // 遍历每一样所有单元格
                if (r == 0) { // 表头
                    row.getCell(c).setCellStyle(ExcelStyleUtils.headerStyle(wb)); // 设置格式
                    sheet.autoSizeColumn(c); // 列自动伸缩
                } else { // 数据
                    row.getCell(c).setCellStyle(ExcelStyleUtils.cellsStyle(wb)); // 设置格式
                    if (sheet.getColumnWidth(c) > 50 * 256) {
                        sheet.setColumnWidth(c, 50 * 256); // 设置列宽
                    }
                }
            }
        }
    }
}

第3步、导出到Excel

# FileOutputStream
public static void writeExcel(Workbook workbook) throws Exception {
    FileIUtils.deleteDirAndFiles(OUT_PATH);
    FileIUtils.makeSureDirExist(OUT_PATH);
    
    String exportPath = FileIUtils.genFileName(OUT_PATH, "文件名", ".xlsx");
    try (FileOutputStream fos = new FileOutputStream(exportPath, false)) {
        workbook.write(fos);
    }
}

# OutputStream 
public static void writeExcel2(Workbook workbook) throws Exception {
    FileIUtils.deleteDirAndFiles(OUT_PATH);
    FileIUtils.makeSureDirExist(OUT_PATH);
    
    String exportPath = FileIUtils.genFileName(OUT_PATH, "文件名", ".xlsx");
    try (OutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }
}

Further Reading ： FileIUtils 共通方法最佳实践

测试

@Test
public void testWriteExcel2() throws Exception {
    List<Person> persons = new ArrayList<>();
    persons.add(new Person(10, "John", 11, "nickName"));
    persons.add(new Person(11, "John1", 11, "nickName1"));
    persons.add(new Person(12, "John2", 11, "nickName2"));
    persons.add(new Person(13, "John3", 11, "nickName3"));
    persons.add(new Person(14, "John4", 11, "nickName4"));
    
    XSSFWorkbook wb = new XSSFWorkbook();
    buildData(wb, persons); // 构建数据
    
    buildStyle(wb); // 设置格式
    
	writeExcel(wb); 或 writeExcel2(wb); // 导出数据
}

结果展示

---

核心import

import model.Person;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

maven 依赖

<!--读取excel文件-->
<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>

拓展

Java读取excel中的数据
Java导出数据到Excel
Java给Excel设置单元格格式