Spring Boot利用POI生成导出excel文档

首先pom中添加依赖

<</span>dependency>
    <</span>groupId>org.apache.poi</</span>groupId>
    <</span>artifactId>poi-ooxml-schemas</</span>artifactId>
    <</span>version>3.17</</span>version>
</</span>dependency>
<</span>dependency>
    <</span>groupId>org.apache.poi</</span>groupId>
    <</span>artifactId>poi</</span>artifactId>
    <</span>version>RELEASE</</span>version>
</</span>dependency>
<</span>dependency>
    <</span>groupId>org.apache.poi</</span>groupId>
    <</span>artifactId>poi-ooxml</</span>artifactId>
    <</span>version>RELEASE</</span>version>
</</span>dependency>

然后创建表头

public void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){
    HSSFRow row = sheet.createRow(0);
    //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
    for (int i=0;i<</span>12;i++)
        sheet.setColumnWidth(i,18*256);

    //设置为居中加粗
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBold(true);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(font);

    HSSFCell cell;
    cell = row.createCell(0);
    cell.setCellValue("订单号");
    cell.setCellStyle(style);


    cell = row.createCell(1);
    cell.setCellValue("BOM编码");
    cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue("BOM名称");
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("BOM类别");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("BOM封装");
    cell.setCellStyle(style);


    cell = row.createCell(5);
    cell.setCellValue("元件值");
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("供应商");
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellValue("型号");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("LotNo");
    cell.setCellStyle(style);


    cell = row.createCell(9);
    cell.setCellValue("ZQ");
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellValue("操作者");
    cell.setCellStyle(style);

    cell = row.createCell(11);
    cell.setCellValue("操作时间");
    cell.setCellStyle(style);
}

添加表内容：

public void addSheetContent(HSSFSheet sheet , HSSFWorkbook workbook, List bomresultlist)
{
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBold(true);
    //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    style.setAlignment(HorizontalAlignment.CENTER);
    int rowNum=1;
    for(int i=0;i
        HSSFRow row = sheet.createRow(rowNum);
        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue(bomresultlist.get(i).getBomencode());
        cell.setCellStyle(style);
        cell = row.createCell(1);

        cell.setCellValue(bomresultlist.get(i).getBomname());
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue(bomresultlist.get(i).getBomcategory());
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue(bomresultlist.get(i).getBompackage());
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue(bomresultlist.get(i).getBomvalue());
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue(bomresultlist.get(i).getBomgys());
        cell.setCellStyle(style);
        cell = row.createCell(6);
        cell.setCellValue(bomresultlist.get(i).getBomxh());
        cell.setCellStyle(style);
        cell = row.createCell(7);
        cell.setCellValue(bomresultlist.get(i).getBomlot());
        cell.setCellStyle(style);
        cell = row.createCell(8);
        cell.setCellValue(bomresultlist.get(i).getBomzq());
        cell.setCellStyle(style);
        cell = row.createCell(9);
        cell.setCellValue(bomresultlist.get(i).getBomuser());
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue(bomresultlist.get(i).getBomtime());
        cell.setCellStyle(style);
        rowNum++;
    }
}

输出文档供下载：

protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
    OutputStream outputStream = response.getOutputStream();
    workbook.write(outputStream);
    outputStream.flush();
    outputStream.close();
}

生成excel文档

protected void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{

    FileOutputStream fos = new FileOutputStream(filename);
    workbook.write(fos);
    fos.flush();
    fos.close();
}

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("BOM");

createTitle(workbook,sheet);

addSheetContent(sheet,workbook,bomresultlist);

  buildExcelFile(fileName, workbook);
   buildExcelDocument(fileName,workbook,response);