Springboot前后端分离excel下载

心已赠人 2023-07-24 11:45 2阅读 0赞

现在公司的技术栈是springboot作为后端,前端是vue, 现在要做excel的导出功能, 之前没做过,写一下记录下.
    springboot版本是2.0.6  poi 3.14 ,jdk1.8
    
    类上面的注解是: @RestController

/**
         * 导出excel
         * 
         */
        @GetMapping("export")
        public void exportExcel() {
            XSSFWorkbook workbook = placeStatService.exportExcel();
            // 设置生成的Excel的文件名，并以中文进行编码
            String fileName = null;
            try {
                fileName = URLEncoder.encode("房间预约使用统计表" + ".xlsx", "utf-8").replaceAll("\\+", "%20");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            // 响应类型,编码
            response.setContentType("application/octet-stream;charset=UTF-8");
            try {
                // 形成输出流
                OutputStream osOut = response.getOutputStream();
                // 将指定的字节写入此输出流
                workbook.write(osOut);
                // 刷新此输出流并强制将所有缓冲的输出字节被写出
                osOut.flush();
                // 关闭流
                osOut.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

@Override
        public XSSFWorkbook exportExcel) {
            List<RoomOrderDetailModel> roomOrdersList = getRoomOrderList();
            XSSFWorkbook data = ExcelUtil.setExcelData(roomOrdersList);
            return data;
        }

package com.util;
    
    import com.curefun.place.model.RoomOrderDetailModel;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author excel 工具类. 导出功能
     */
    public class ExcelUtil {
    
    
        /**
         * 数据导出, 获取一个excel对象
         *
         * @param
         */
        public static XSSFWorkbook setExcelData(List<RoomOrderDetailModel> orderDetailModels) {
            //创建一个book，对应一个Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook();
            //在book中添加一个sheet,对应Excel文件中的sheet
            XSSFSheet sheet = workbook.createSheet("教室预约使用记录");
            //设置六列的宽度
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 3000);
            sheet.setColumnWidth(2, 3800);
            sheet.setColumnWidth(3, 2800);
            sheet.setColumnWidth(4, 3200);
            sheet.setColumnWidth(5, 3600);
            sheet.setColumnWidth(6, 2850);
    
            //居中的样式
            XSSFCellStyle centerStyle = getCenterStyle(workbook);
    
            // 第三步，在sheet中添加表头第0行
            XSSFRow row0 = sheet.createRow(0);
            setFirstRow(centerStyle, row0);
    
            int rowNum = 1;
            for (RoomOrderDetailModel model : orderDetailModels) {
                XSSFRow row = sheet.createRow(rowNum);
                row.createCell(0).setCellValue(rowNum);
                rowNum++;
                row.createCell(1).setCellValue(model.getBuildingName());
                row.createCell(2).setCellValue(model.getRoomNo());
                row.createCell(3).setCellValue(model.getRoomName());
                row.createCell(4).setCellValue(model.getEventType());
                row.createCell(5).setCellValue(model.getEventName());
                row.createCell(6).setCellValue(model.getUserRealName());
            }
            return workbook;
        }
    
    
    
        /**
         * 获取居中的样式.
         *
         * @param workbook
         * @return
         */
        private static XSSFCellStyle getCenterStyle(XSSFWorkbook workbook) {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            //设置水平对齐的样式为居中对齐;
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            return cellStyle;
        }
    
    
        /**
         * 设置第一行的表头
         *
         * @param centerStyle
         * @param row
         */
        private static void setFirstRow(XSSFCellStyle centerStyle, XSSFRow row) {
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellValue("序号");
            cell0.setCellStyle(centerStyle);
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue("楼栋信息");
            cell1.setCellStyle(centerStyle);
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue("房号");
            cell2.setCellStyle(centerStyle);
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue("房间名称");
            cell3.setCellStyle(centerStyle);
            XSSFCell cell4 = row.createCell(4);
            cell4.setCellValue("活动类型");
            cell4.setCellStyle(centerStyle);
            XSSFCell cell5 = row.createCell(5);
            cell5.setCellValue("活动名称");
            cell5.setCellStyle(centerStyle);
            XSSFCell cell6 = row.createCell(6);
            cell6.setCellValue("使用人");
            cell6.setCellStyle(centerStyle);
    
    /**
    其实完全使用这种方式, 会更加的简单,便于修改
    List<String> title = Stream.of("序号", "专业", "班级", "课程名称", "课程内容", "授课教师", "授课时长", "授课时间", "学分", "授课房间")
                    .collect(Collectors.toList());
            for (int i = 0; i < title.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(title.get(i));
                cell.setCellStyle(centerStyle);
            }
    */
        }
    
    }

其实使用很简单, 就是excel的文件名需要进行编码, 这个需要注意, 其他没啥的了.

5.1 放假回来了, 真的没啥心思上班, 只想放假休息, 唉