Java导出数据到Excel分3步处理
1、构建Workbook 数据
2、设置Workbook 格式
3、导出到Excel

1、构建Workbook 数据

public static void buildData(Workbook wb, List<Person> list) {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());}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 = 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

public static void writeExcel(Workbook workbook) throws Exception {ExcelUtils.deleteOldExcelFile(OUT_PATH);ExcelUtils.makeExcelFile(OUT_PATH);FileOutputStream fileOutputStream = new FileOutputStream(genExcelName(OUT_PATH), false);workbook.write(fileOutputStream);fileOutputStream.close();
}
public static void writeExcel2(Workbook workbook) throws Exception {ExcelUtils.deleteOldExcelFile(OUT_PATH);ExcelUtils.makeExcelFile(OUT_PATH);OutputStream outputStream = new FileOutputStream(genExcelName(OUT_PATH));workbook.write(outputStream);outputStream.close();
}private static String genExcelName(String path) {String postName = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHMMSS"));return path + "/输出" + postName + ".xlsx";
}

4、UT

@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); // 导出数据
}

5、结果展示

6、核心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;

7、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><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.11.0</version>
</dependency>
<!--FileUtils--><!--日志-->
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-api</artifactId><version>2.15.0</version>
</dependency>
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.15.0</version>
</dependency>
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-slf4j-impl</artifactId><version>2.15.0</version>
</dependency>