springboot中jxl生成excel报表 - 借鉴

雨点打透心脏的1/2处 2022-01-29 01:37 522阅读 0赞

controller层：

@ApiOperation(value="下载问卷报表", notes="下载问卷报表", position = 4)
        @GetMapping("/download/{surveyId}")
        public void download(@PathVariable("surveyId") String surveyId,
                             HttpServletRequest request, HttpServletResponse response) {
    
            ExcelUtilsMProtal.exportCourseContent(request,
                                                  response,
                                                  surveyService.download(surveyId));
        }

import jxl.CellView;
    import jxl.format.Alignment;
    import jxl.format.Colour;
    import jxl.format.UnderlineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.write.*;
    import lombok.extern.slf4j.Slf4j;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    
    @Slf4j
    public class ExcelUtilsMProtal {
    
        public static void exportCourseContent(HttpServletRequest request,
                                               HttpServletResponse response,
                                               SurveyDownloadDto surveyDownloadDto){
            WritableWorkbook wwb = null;
            OutputStream os = null;
            String surveyName = surveyDownloadDto.getSurveyName();
            List<String> header = surveyDownloadDto.getColumnName();
            List<SurveyData> data = surveyDownloadDto.getDataList();
    
            try {
                os = response.getOutputStream();// 取得输出流
                response.reset();// 清空输出流
                // excel 文件的 MIME 类型
                response.setContentType("application/vnd.ms-excel");
                //在导出前对名称根据浏览器做下处理
                String agent = request.getHeader("USER-AGENT").toLowerCase();
                String fileName = new SimpleDateFormat("yyyy-MM-dd ").format(new Date()) + surveyName;//文件名中文乱码
                String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
                if (agent.contains("firefox")) {
                    response.setCharacterEncoding("utf-8");
                    response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
                } else {
                    response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
                }
    
                wwb = jxl.Workbook.createWorkbook(os);
                WritableSheet ws = wwb.createSheet(surveyName, 10); // 创建一个工作表
    
                // 设置标题单元格的文字格式
                WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 12,
                        WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat titleFontFormat = new WritableCellFormat(titleFont);
                titleFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
                titleFontFormat.setAlignment(Alignment.CENTRE);
                titleFontFormat.setBackground(Colour.LIGHT_TURQUOISE);
    
    
                // 设置内容数据单元格的文字格式
                WritableFont cellFont = new WritableFont(WritableFont.createFont("宋体"), 12,
                        WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
                WritableCellFormat cellFontFormat = new WritableCellFormat(cellFont);
                cellFontFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
                cellFontFormat.setAlignment(Alignment.LEFT);//文字对齐方式
    
                ws.getSettings().setDefaultColumnWidth(18);
                ws.getSettings().setDefaultRowHeight(300);
    
                CellView cellView = new CellView();
                cellView.setAutosize(true); //设置自动大小
    //            /*======= 设置列宽 =====*/
    //            ws.setColumnView(0, 30);//根据内容自动设置列宽
    //            ws.setColumnView(1, 30);
    //            ws.setColumnView(2, 30);
    //            ws.setColumnView(3, 24);
    //            ws.setColumnView(4, 24);
    //            ws.setColumnView(5, 24);
    
                /*======= 填充标题 =====*/
                for(int i = 0; i < header.size(); i++) {
    
                    ws.setColumnView(i, cellView);
                    ws.addCell(new Label(i, 0, header.get(i), titleFontFormat));
                }
    
                for(int i = 1; i <= data.size(); i++) {
                    //======= 填充内容 =====
                    SurveyData surveyData = data.get(i - 1);
                    ws.addCell(new Label(0, i, surveyData.getNum(), cellFontFormat));
                    ws.addCell(new Label(1, i, surveyData.getSurveyId(), cellFontFormat));
                    ws.addCell(new Label(2, i, surveyData.getSurveyName(), cellFontFormat));
                    ws.addCell(new Label(3, i, surveyData.getUserName(), cellFontFormat));
                    ws.addCell(new Label(4, i, surveyData.getUserId(), cellFontFormat));
                    ws.addCell(new Label(5, i, surveyData.getPhone(), cellFontFormat));
                    ws.addCell(new Label(6, i, surveyData.getDeptName(), cellFontFormat));
                    ws.addCell(new Label(7, i, surveyData.getBranch(), cellFontFormat));
                    int j = 8;
                    for(String item : surveyData.getItemList()) {
                        ws.addCell(new Label(j++, i, item, cellFontFormat));
                    }
                    ws.addCell(new Label(j, i, surveyData.getSurveyRecordTime() + "", cellFontFormat));
                }
    
                ws.toString();
                wwb.write();
            } catch (Exception e) {
                log.error(e.getMessage());
            } finally {
                try {
                    if (wwb != null) {
                        wwb.close();
                    }
                    if (os != null) {
                        os.close();
                    }
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
        }
    }

pom

<dependency>
                <groupId>net.sourceforge.jexcelapi</groupId>
                <artifactId>jxl</artifactId>
            </dependency>