springboot 实现excel表导出

柔情只为你懂 2022-05-16 03:39 254阅读 0赞

首先导入相应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][http_localhost_8080_excel_excelExport]

[http_localhost_8080_excel_excelExport]: http://localhost:8080/excel/excelExport