Java POI实现excel导出

Java POI实现excel导出

一、依赖

<!-- excel导入导出 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

二、自定义工具类

ExcelUtil.java

public class ExcelUtil {

    /**
     * 导出excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     */
    public static HSSFWorkbook getHssfWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook workbook) {
        //第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        //第二步，在workbook中添加一个sheet，对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置列宽
        sheet.setDefaultColumnWidth(15);
        //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，并设置值表头，设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        //样式
        style.setAlignment(HorizontalAlignment.CENTER);//创建一个居中格式
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i+1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return workbook;
    }
}

三、业务部分

 /**
     * 导出xx项目excel
     * contents：具体内容
     * fileName：文件名
     * title：列头
     * sheetName：sheet名
     */
    private HSSFWorkbook excelOutProject(List<ProjectInfoTable> contents, String[] title, String sheetName) {
        String [][] content = new String[contents.size()][];
        //遍历内容
        for (int i=0; i < contents.size(); i++) {
            content[i] = new String[title.length];
            ProjectInfoTable info = contents.get(i);
            content[i][0] = info.getCategory();
            content[i][1] = info.getName()；
        }
        //创建HSSFWorkbook
        HSSFWorkbook workbook = ExcelUtil.getHssfWorkbook(sheetName, title, content, null);
        return workbook;
    }

四、controller

//报表导出
@GetMapping("/excel")
@Override
public void excelExport(HttpServletResponse response){

    HSSFWorkbook workbook = officeService.excelExport(sort);
    String fileName = "xxx项目" + System.currentTimeMillis() + ".xls";

    //响应到客户端
    try {
        //设置响应头
        this.setResponseHeader(response, fileName);
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        //刷新流
        outputStream.flush();
        //关闭流
        outputStream.close();
    }catch (Exception e){
        e.printStackTrace();
    }
}

 //发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
    try {
        try {
            fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}