分享一个我手写的导出Excel代码片段,方便以后现抄
@PostMapping("/export-excel")
public void exportProductSampleExcel(@Valid ProductSamplePageReqVO pageReqVO, HttpServletResponse response){
try {
List<ProductSampleDO> list = productSampleService.getProductSampleList(pageReqVO);
String fileName="产品样品信息";
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(fileName);
// 合并标题行
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
// 设置数据样式
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置默认列宽度为15个字符宽度
sheet.setDefaultColumnWidth(15);
// 创建标题行
Row headerRow1 = sheet.createRow(0);
headerRow1.createCell(0).setCellValue(fileName);
// 设置单元格样式为居中
headerRow1.getCell(0).setCellStyle(dataStyle);
// 创建标题行
Row headerRow = sheet.createRow(1);
headerRow.createCell(0).setCellValue("样品编号");
headerRow.createCell(1).setCellValue("产品名称");
headerRow.createCell(2).setCellValue("产品品类");
headerRow.createCell(3).setCellValue("生产者");
headerRow.createCell(4).setCellValue("采集地点");
headerRow.createCell(5).setCellValue("产品批次");
headerRow.createCell(6).setCellValue("采集时间");
headerRow.createCell(7).setCellValue("采集量");
headerRow.createCell(8).setCellValue("状态");
// 设置单元格样式为居中
for(int i=0; i<9; i++){
headerRow.getCell(i).setCellStyle(dataStyle);
}
// 填充数据
int rowNum = 2;
if (list != null && !list.isEmpty()) {
for (ProductSampleDO companyDetail : list) {
Row row = sheet.createRow(rowNum++);
if (companyDetail.getSampleNo() != null) {
row.createCell(0).setCellValue(companyDetail.getSampleNo());
}
if (companyDetail.getProductName() != null) {
row.createCell(1).setCellValue(companyDetail.getProductName());
}
if (companyDetail.getProductType() != null) {
row.createCell(2).setCellValue(companyDetail.getProductType());
}
if (companyDetail.getProducer() != null) {
row.createCell(3).setCellValue(companyDetail.getProducer());
}
if (companyDetail.getCollectionSite() != null) {
row.createCell(4).setCellValue(companyDetail.getCollectionSite());
}
if (companyDetail.getBatchNo() != null) {
row.createCell(5).setCellValue(companyDetail.getBatchNo());
}
if (companyDetail.getCollectionTime() != null) {
row.createCell(6).setCellValue(companyDetail.getCollectionTime());
}
if (companyDetail.getCollectionQuantity() != null) {
row.createCell(7).setCellValue(String.valueOf(companyDetail.getCollectionQuantity()));
}
if (companyDetail.getStatus2() != null) {
row.createCell(8).setCellValue(companyDetail.getStatus2());
}
}
}
// 设置响应头
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+fileName+".xlsx");
// 将工作簿写入响应流中
workbook.write(response.getOutputStream());
workbook.close();
} catch (Exception e) {
e.printStackTrace();
// 返回适当的错误信息
response.setContentType("text/plain");
try {
response.getWriter().write("导出Excel文件时发生错误");
} catch (IOException ex) {
ex.printStackTrace();
}
}
}