使用Apache POI生成基本Excel
Apache POI是一个强大的Java库,用来处理Microsoft Office文件。对于Excel文件(.xls和.xlsx)处理,提供有HSSF
(.xls)和XSSF
(.xlsx)等API。
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
public void createExcelTemplate(HttpServletResponse response) {
Workbook workbook = new XSSFWorkbook(); // 创建一个工作簿
Sheet sheet = workbook.createSheet("Template"); // 创建一个工作表
// 创建表头行
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("ID");
headerRow.createCell(1).setCellValue("Name");
headerRow.createCell(2).setCellValue("Email");
// 设置响应头信息
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=ExportTemplate.xlsx");
try (OutputStream outputStream = response.getOutputStream()) {
workbook.write(outputStream); // 写入输出流
workbook.close(); // 关闭工作簿
} catch (IOException e) {
throw new RuntimeException("Error while writing Excel file", e);
}
}
使用Apache POI进行复杂的格式设定
随着需求的复杂化,可能需要设置单元格样式、数据格式、合并单元格等。
import org.apache.poi.ss.usermodel.*; // 引入所有需要的包
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
public void createComplexExcelTemplate(HttpServletResponse response) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("ComplexTemplate");
// 创建样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("No.");
headerRow.createCell(1).setCellValue("Name");
// 应用样式
for (Cell cell : headerRow) {
cell.setCellStyle(headerStyle);
}
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=ComplexExportTemplate.xlsx");
try (OutputStream outputStream = response.getOutputStream()) {
workbook.write(outputStream);
workbook.close();
} catch (IOException e) {
throw new RuntimeException("Error writing Excel file", e);
}
}
使用EasyExcel处理大数据
对于大数据和大规模用户下载场景,Apache POI在内存消耗和效率上存在短板。这时,可以使用针对Java的高效Excel读写库EasyExcel。
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
// 创建Excel数据模型
public static class Data {
private String id; // User ID
private String name; // User Name
private String email; // Email
// getters and setters
}
public ResponseEntity<ByteArrayResource> createExcelTemplateEasyExcel() {
// 创建测试数据
List<Data> data = new ArrayList<>();
// 添加数据到list
ByteArrayOutputStream os = new ByteArrayOutputStream();
EasyExcel.write(os, Data.class).sheet("Template")
.doWrite(data); // 写入数据
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", "DataTemplate.xlsx");
// 这里使用ByteArrayResource是因为我们不需要流暴露在外部
return ResponseEntity.ok().headers(headers).contentLength(os.size())
.body(new ByteArrayResource(os.toByteArray()));
}