java 下载Excel

最新推荐文章于 2024-08-29 03:49:18 发布

Roc.Y

最新推荐文章于 2024-08-29 03:49:18 发布

阅读量185

点赞数

文章标签：下载excel

本文链接：https://blog.csdn.net/i_yuanpeng/article/details/100099607

版权

需求：点击按钮后，向后台传入查询条件，然后根据查询条件获取满足条件的记录通过excel下载。

模板样式文件下载：模板样式下载地址

后台代码：如下

@RequestMapping("export_exl.do")    
private void exportExemptionExl(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<TStudentInfo> studentInfoList = examService.findStudents(request);
        String suffix = DateConverUtil.dd2(new Date(), "yyyyMMddHHmmss");
        String fname = "文档名称" + suffix + ".xls";
        response.reset();
        response.setContentType("text/html; charset=UTF-8");
        response.setContentType("application/x-msdownload");// 设置response的编码方式
        String agent = request.getHeader("User-Agent");
        String supName = URLEncoder.encode(fname, "utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8'zh_cn'" + supName);
        studentService.exportExemptionExl(studentInfoList, response.getOutputStream());

    }

public void exportExemptionExl(List<TStudentInfo> studentInfoList, ServletOutputStream outputStream) {
        InputStream is = null;
        try {
            is = new BufferedInputStream(this.getClass().getResourceAsStream("/com/当前模板文件地址/util/" + EXCEL_TEMPLATE_NAME));
            // 读取模板文件中样式
            HSSFWorkbook wb = new HSSFWorkbook(is);
            Sheet tplSheet = wb.getSheet("template2");
            int width = tplSheet.getColumnWidth(2);
            int widthDjh = tplSheet.getColumnWidth(5);
            short tdHeight = tplSheet.getRow(3).getHeight();
            CellStyle titleCs = tplSheet.getRow(0).getCell(0).getCellStyle();
            CellStyle dateCs = tplSheet.getRow(1).getCell(1).getCellStyle();
            CellStyle tdCs = tplSheet.getRow(3).getCell(3).getCellStyle();
            CellStyle thCs = tplSheet.getRow(10).getCell(10).getCellStyle();

            String title = "title 名称";
            Sheet sheet = wb.createSheet(title);
            createCell(titleCs, sheet, 0, 0, "首行显示");
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
            sheet.setColumnWidth(0, widthDjh);
            sheet.setColumnWidth(1, 3 * width);
            int rows = 1;
            createCell(thCs, sheet, rows, 0, "测试一");
            createCell(thCs, sheet, rows, 1, "测试二");
            createCell(thCs, sheet, rows, 2, "测试三");
            for (int rownum = 0; rownum < studentInfoList.size(); rownum++) {
                TStudentInfo result = studentInfoList.get(rownum);
                String studentState = result.stState2Html();
                createCell(thCs, sheet, rows + 1, 0, result.数据一);
                createCell(thCs, sheet, rows + 1, 1, result.数据二);
                createCell(thCs, sheet, rows + 1, 2, result.数据三);
                rows += 1;
            }
            // 删除模板sheet
            wb.removeSheetAt(wb.getSheetIndex(wb.getSheet("template")));
            wb.removeSheetAt(wb.getSheetIndex(wb.getSheet("template2")));
            wb.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }