利用excel模板实现快速的导出

项目中利用的框架是springBoot,实现便捷开发。

下面是部分代码：从controller中导出功能代码

@RequestMapping(value = "/export")
public void exportByYjfx(HttpServletResponse response, HttpServletRequest request,String condition)
        throws FileNotFoundException {
    // 文件根路径
    Calendar calendar = new GregorianCalendar();
    String todayMonth = null; //当前月份
    String todayMonthLast = null; //当前月份最后一天
    Date dateCalendar = new Date(); //中转用dateCalendar,当前月份
    SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd" );
    if (condition==null){
        condition = sdf.format(new Date());
    }
    try {
        Date date = sdf.parse(condition);
        calendar.setTime(date);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        dateCalendar = calendar.getTime();
        todayMonth = sdf.format(dateCalendar);
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        todayMonthLast = sdf.format(calendar.getTime());

    } catch (ParseException e) {
        condition = sdf.format(new Date());
        calendar.setTime(new Date());
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        dateCalendar = calendar.getTime();
        todayMonth = sdf.format(dateCalendar);
        e.printStackTrace();
    }
    //String fileRootPath = request.getSession().getServletContext().getRealPath("/excel");
    File file = ResourceUtils.getFile("classpath:exprot.xls");
    String path = file.getPath();
    // 文件名称
   String fileName = "exprot.xls";
    // 内容
    Map<String, Object> bean = new HashMap<String, Object>();
    @SuppressWarnings("unchecked")
    List<TaxInformationStatistics> tax = taxInformationService.selectTaxStatisticList(condition,todayMonth,todayMonth);
    tax.stream().forEach(p ->
    {
        p.setRegistrationTypeName(ConstantFactory.me().getRegistrationTypeName(p.getRegistrationType()));
    });
    List<TaxInformationStatistics> taxInformationStatistics = (List<TaxInformationStatistics>)
            bean.put("topicMap",tax);
    String fileNameS;
    fileNameS = "税收信息表.xls";
    // 设置sheet页名称
    String sheetName = "税收信息表";
    downFile(response, bean, path, fileName, fileNameS, sheetName);
}

// 文件下载
private void downFile(final HttpServletResponse response, Map<String, Object> bean, String fileRootPath,
                      String fileName, String fileNameS, String sheetName)
        throws FileNotFoundException {
    if (bean != null) {
        XLSTransformer transformer = new XLSTransformer();
        InputStream in = new FileInputStream(new File(fileRootPath));
        HSSFWorkbook workbook;
        try {
            workbook = (HSSFWorkbook) transformer.transformXLS(in, bean);
            Sheet sheet = workbook.getSheetAt(0);
            // 设置sheet页名称
            workbook.setSheetName(0, sheetName);

            /**合并单元格需要的逻辑
             // 起始行
             int f = 1;
             // 结束行
             int g = 0;
             sheet.addMergedRegion(new CellRangeAddress(f, g, i, i));//合并单元格的函数,我把我的逻辑删掉了,按情况进行合并
             */
            try {
                response.setContentType("application ns.ms-excel");
                response.setHeader("Expires", "0");
                response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
                response.setHeader("Pragma", "public");
                response.setHeader("Content-disposition",
                        "attachment;filename=" + new String(fileNameS.getBytes("gb2312"), "ISO8859_1"));
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (ParsePropertyException e1) {
            e1.printStackTrace();
        } catch (InvalidFormatException e1) {
            // TODO 自动生成的 catch 块
            e1.printStackTrace();
        }
    }
}

传的参数condition是项目自动生成代码自动传的参数。项目是码云中流行的guns自动生成代码。其中重要代码为

List<TaxInformationStatistics> tax = taxInformationService.selectTaxStatisticList(condition,todayMonth,todayMonth); tax.stream().forEach(p -> { p.setRegistrationTypeName(ConstantFactory.me().getRegistrationTypeName(p.getRegistrationType())); }); List<TaxInformationStatistics> taxInformationStatistics = (List<TaxInformationStatistics>) bean.put("topicMap",tax);

改方法是查询返回结果。excel模板会有对结果进行遍历。