springboot 实现excel表导出

最新推荐文章于 2024-05-22 22:49:25 发布

原创最新推荐文章于 2024-05-22 22:49:25 发布 · 2.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#excel #导出 #springboot

本文介绍如何使用Apache POI库将数据封装为DataItem对象，并通过Java控制器将其导出为Excel文件。重点包括数据初始化、Excel表头设置、数据填充以及响应到客户端的过程。

首先导入相应jar包

pom:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.6</version>
</dependency>

数据封装要准备好，也就是我们说的domain或者pojo

package com.nework.course01.util;

public class DataItem {

    private String project;

    private String annualBudget;

    private String cumulativExecution;

    private String progress;

    private String progressDeviation;

    public DataItem(String project, String annualBudget, String cumulativExecution, String progress, String progressDeviation) {
        this.project = project;
        this.annualBudget = annualBudget;
        this.cumulativExecution = cumulativExecution;
        this.progress = progress;
        this.progressDeviation = progressDeviation;
    }

    public DataItem() {
    }

    public String getProject() {
        return project;
    }

    public void setProject(String project) {
        this.project = project;
    }

    public String getAnnualBudget() {
        return annualBudget;
    }

    public void setAnnualBudget(String annualBudget) {
        this.annualBudget = annualBudget;
    }

    public String getCumulativExecution() {
        return cumulativExecution;
    }

    public void setCumulativExecution(String cumulativExecution) {
        this.cumulativExecution = cumulativExecution;
    }

    public String getProgress() {
        return progress;
    }

    public void setProgress(String progress) {
        this.progress = progress;
    }

    public String getProgressDeviation() {
        return progressDeviation;
    }

    public void setProgressDeviation(String progressDeviation) {
        this.progressDeviation = progressDeviation;
    }
}

然后开始正题：

package com.nework.course01.controller;

import com.nework.course01.util.DataItem;
import com.nework.course01.util.PageData;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;


@RestController
@RequestMapping("/excel")
public class ExcelController {

    @RequestMapping("/excelExport")
    public void excelExport(HttpServletRequest request, HttpServletResponse response) throws Exception {



        //开始模拟获取数据，实际应该在数据库查出来

        List<DataItem> list = new ArrayList<>();
        list.add(new DataItem("教育局","123123","123","123","123"));
        list.add(new DataItem("民政局","123123","123","123","123"));
        list.add(new DataItem("邮政局","123123","123","123","123"));





//EXCEL表导出核心代码
        //   声明一个Excel
        HSSFWorkbook wb=null;

        //title代表的是你的excel表开头每列的名字
        String[] title =new String[]{"项目","年度预算","累计执行数","执行进度","进度偏差"};


        String name="测试";
        //excel文件名
        String fileName = name+".xls";

        //sheet名
        String sheetName = name+"表";

        //二维数组铺满整个Excel

        String[][] content = new String[list.size()][title.length];
        //--------------------------------------------------------------------------------------------

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);


        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //设置背景色
        style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置居右
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//水平居右
        style.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //设置字体
        HSSFFont font=wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 16);//设置字体大小
        HSSFFont font2=wb.createFont();
        font2.setFontName("仿宋_GB2312");
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        font2.setFontHeightInPoints((short) 12);

        style.setFont(font);//选择需要用到的字体格式

        //设置列宽
        sheet.setColumnWidth(0, 12000);//第一个参数代表列id（从0开始），第二个参数代表宽度值
        sheet.setColumnWidth(1, 4567);//第一个参数代表列id（从1开始），第二个参数代表宽度值
        sheet.setColumnWidth(2, 4567);//第一个参数代表列id（从2开始），第二个参数代表宽度值
        sheet.setColumnWidth(3, 4567);//第一个参数代表列id（从3开始），第二个参数代表宽度值
        sheet.setColumnWidth(4, 4567);//第一个参数代表列id（从4开始），第二个参数代表宽度值
        style.setWrapText(true);//设置自动换行

        //加边框
        HSSFCellStyle cellStyle=wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }





        //把list放进content里

        for (int i = 0; i < list.size(); i++) {
            content[i] = new String[title.length];
            DataItem obj = list.get(i);
            content[i][0] = obj.getProject();
            content[i][1] = obj.getAnnualBudget();
            content[i][2] = obj.getCumulativExecution();
            content[i][3] = obj.getProgress();
            content[i][4] = obj.getProgressDeviation();
        }
        //添加数据进入excel

        for(int i=0;i<content.length;i++){

            row = sheet.createRow(i + 1);

            for(int j=0;j<content[i].length;j++){

          //将内容按顺序赋给对应的列对象
                HSSFCell cel = row.createCell(j);
                cel.setCellValue(content[i][j]);

            } }


        //响应到客户端
        try {
            try {
                try {
                    fileName = new String(fileName.getBytes(),"ISO8859-1");
                } catch (UnsupportedEncodingException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                response.setContentType("application/octet-stream;charset=ISO8859-1");
                response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

最后地址栏输入：http://localhost:8080/excel/excelExport