导出Excel下载实战

导出Excel下载实战

这里要实现一个简单的网页导出接口，点击接口，自动下载文件到该浏览器的默认文件保存位置。使用的是POI

直接上代码：

• Controller层

    /**
     * 导出用户答题记录
     * @param list
     */
    @ApiOperation("导出用户答题记录")
    @PostMapping("/excelExportRecord")
    @CheckToken(value = false)
    public void excelExportRecord( @RequestBody  List<AnYiNengTestResult> list){
        anyiNengOnlineService.exportExcel(response,request,list);
    }
    

• Service

/**
 * 导出某条记录
 * @param response
 * @param request
 * @param list
 */
void exportExcel(HttpServletResponse response, HttpServletRequest request, List<AnYiNengTestResult> list);

• ServiceImpl

 /**
     * 批量或单个导出答题记录
     * @param response
     * @param request
     * @param list
     */
    @Override
    public void exportExcel(HttpServletResponse response, HttpServletRequest request, List<AnYiNengTestResult> list) {
        if (list.size() == 0) {
            throw new ExpectedException("请先选择要导出的记录");
        }

        String fileName = "用户答题记录导出";
        String title = "测评编号,学校,班级,姓名,年龄段,安全知识指标得分,安全意识指标得分,安全能力指标得分,安意能分值,测评时间";

        HSSFWorkbook wb = new HSSFWorkbook();     //建立新HSSFWorkbook对象
        HSSFSheet sheet = wb.createSheet(fileName);    //建立sheet页
        Cell cell = null;

        CellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        style.setFont(font);

        //加边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setBorderTop(BorderStyle.THIN); //上边框

        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.YELLOW.index);

        style.setWrapText(true);//设置自动换行
        CellStyle stylec = wb.createCellStyle();
        HSSFFont fontc = wb.createFont();
        fontc.setFontHeightInPoints((short) 10);
        fontc.setFontName("宋体");
        stylec.setFont(font);
        fontc.setBold(false);//设置是否加粗
        stylec.setWrapText(true);//设置自动换行
        SimpleDateFormat sdf = new SimpleDateFormat(" yyyy-MM-dd HH:mm:ss ");


        //进行第一行表头的设置
        HSSFRow row = sheet.createRow((short) 0);
        for (int i = 0; i < title.split(",").length; i++) {
            sheet.setColumnWidth(i, 3500);第一个参数代表列(从0开始),
            sheet.setColumnWidth(6, 5000);
            cell = row.createCell(i);
            cell.setCellValue(title.split(",")[i]);
            cell.setCellStyle(style);
        }

        //进行表内容的设置
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);

            cell = row.createCell(0);
            cell.setCellValue(list.get(i).getId());

            cell = row.createCell(1);
            cell.setCellValue(list.get(i).getSchoolName());

            cell = row.createCell(2);
            cell.setCellValue(list.get(i).getClassName());

            cell = row.createCell(3);
            cell.setCellValue(list.get(i).getName());

            cell = row.createCell(4);
            cell.setCellValue(list.get(i).getLevel());

//            cell = row.createCell(6);
//            cell.setCellValue("1".equals(list.get(i).getUploadFlag()) ? "是" : "否");

            cell = row.createCell(5);
            cell.setCellValue(list.get(i).getSafeKnowlogy());


            cell = row.createCell(6);
            cell.setCellValue(list.get(i).getSafeAware());

            cell = row.createCell(7);
            cell.setCellValue(list.get(i).getSafeAblity());

            cell = row.createCell(8);
            cell.setCellValue(list.get(i).getScore());

            cell = row.createCell(9);
            cell.setCellValue(sdf.format(list.get(i).getTestTime()));

            cell.setCellStyle(stylec);
        }
   

        ServletOutputStream os = null;

        try {
            os = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-disposition", "attachment;filename=" + System.currentTimeMillis() + ".xls");
            wb.write(os);
            wb.close();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new ExpectedException("导出出错");
        }

    }

最终结果

Swagger里面点击导出

服用代码

• 这里的ServiceImpl里面的代码是可以完全复用的

//修改处1：
String fileName = "用户答题记录导出";
String title = "测评编号,学校,班级,姓名,年龄段,安全知识指标得分,安全意识指标得分,安全能力指标得分,安意能分值,测评时间";

这里的FileName就是最后别人下载下来的文件名称
title就是表头的第一行展示的东西
这里使用的是自己写表头的方法，不适用于类里面字段很多的，只能用于字段较少的类
    
    
//修改处2:

        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);

            cell = row.createCell(0);
            cell.setCellValue(list.get(i).getId());

            cell = row.createCell(1);
            cell.setCellValue(list.get(i).getSchoolName());

            cell = row.createCell(2);
            cell.setCellValue(list.get(i).getClassName());

            cell = row.createCell(3);
            cell.setCellValue(list.get(i).getName());

            cell = row.createCell(4);
            cell.setCellValue(list.get(i).getLevel());

//            cell = row.createCell(6);
//            cell.setCellValue("1".equals(list.get(i).getUploadFlag()) ? "是" : "否");

            cell = row.createCell(5);
            cell.setCellValue(list.get(i).getSafeKnowlogy());


            cell = row.createCell(6);
            cell.setCellValue(list.get(i).getSafeAware());

            cell = row.createCell(7);
            cell.setCellValue(list.get(i).getSafeAblity());

            cell = row.createCell(8);
            cell.setCellValue(list.get(i).getScore());

            cell = row.createCell(9);
            cell.setCellValue(sdf.format(list.get(i).getTestTime()));

            cell.setCellStyle(stylec);
        } 


这里要修改的就是cell.setCellValue(list.get(i).getId());
按照表头输入的顺序，（测评编号,学校,班级,姓名,年龄段,安全知识指标得分,安全意识指标得分,安全能力指标得分,安意能分值,测评时间）分别从第一个Cell里面填写编号到最后一个cell里面填写时间，一一对应就行。

补充

补充一下，这里在实际项目中导出Excel没有什么下载路径之说，都是用户用自己的浏览器访问我们的网址，点击导出Excel，然后我们在代码里面只要将Excel变成io流，最后加上io.write就行，这样就会自动下载Excel到浏览器默认的文件夹里面。