jxl后台生成表格

记录一下

//导出和下载
public void depthReportRateExp(List<Map> _list){
    String uuid=UUID.randomUUID().toString();
    String exportPah="C:\\sirm\\depthreport\\"+uuid+".xls";
    OutputStream _outputStream = null;
    try {
        File _file = new File(exportPah);
        if (!_file.getParentFile().exists()) {
            _file.getParentFile().mkdirs();
        }

        if (_file.exists() && _file.isFile()) {
            logger.info("已删除存在的文件,路径【" + exportPah + "】");
            _file.delete(); //如果该文件已存在，则删除之
        }

        if (!_file.createNewFile()) {
            String _msg = "创建文件[" + exportPah + "]失败，无法导出！";
            logger.error(_msg);
        }
        _outputStream = new FileOutputStream(exportPah);
        if (_outputStream == null) {
            String _msg = "无法获取输出流，无法导出！";
            logger.error(_msg);
        }
        WritableWorkbook book = Workbook.createWorkbook(_outputStream);
        WritableSheet sheet = book.createSheet("Sheet1", 0);
        addStksToSheet(sheet, _list);
        book.write();
        book.close();

        _outputStream.flush();
        _outputStream.close();
        logger.info("成功生成批量打分表格,路径【" + exportPah + "】");
        FileInputStream fis = new FileInputStream("C:\\sirm\\depthreport\\"+uuid+".xls");
        HttpUtils.download(response, "深度报告批量打分模板.xls", fis);
        fis.close();
        logger.info("成功下载批量打分表格");
        _file.delete();
        logger.info("成功删除批量打分表格，路径【" + exportPah + "】");
    } catch (Exception e) {
        StringWriter sw = new StringWriter();
        e.printStackTrace(new PrintWriter(sw));
        logger.error(sw.toString());
    }
}
//添加表到xls
private void addStksToSheet(WritableSheet sheet, List<Map> _list) {
    List<Map> ratePeopleList=getUserList();
    try {
        int row = 0, col = 0;
        WritableCellFormat format = new WritableCellFormat();
        format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        format.setAlignment(Alignment.CENTRE);
        format.setBackground(Colour.GREY_25_PERCENT);

        Label label = new Label(col++, row, "报告类型", format);
        sheet.addCell(label);
        label = new Label(col++, row, "报告名称", format);
        sheet.addCell(label);
        label = new Label(col++, row, "撰写人", format);
        sheet.addCell(label);
        label = new Label(col++, row, "开题申请日期", format);
        sheet.addCell(label);
        label = new Label(col++, row, "批准撰写日期", format);
        sheet.addCell(label);
        label = new Label(col++, row, "报告完成日期", format);
        sheet.addCell(label);
        label = new Label(col++, row, "汇报安排日期", format);
        sheet.addCell(label);
        label = new Label(col++, row, "报告得分", format);
        sheet.addCell(label);
        for(int i=0;i<ratePeopleList.size();i++){
            label = new Label(col++, row,MapUtils.getString(ratePeopleList.get(i),"name")+"("+MapUtils.getString(ratePeopleList.get(i),"username")+")", format);
            sheet.addCell(label);
        }
        row++;
        sheet.setColumnView( 0 , 16 );
        sheet.setColumnView( 1 , 60 );
        sheet.setColumnView( 2 , 20 );
        for(int i=3;i<col;i++){
            sheet.setColumnView( i , 15 );
        }

        //输出表数据
        col = 0;
        format = new WritableCellFormat();
        format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        if(_list!=null && _list.size()!=0){
            for (Map _map : _list) {
                String reportTypeStr="";
                int reporttype =MapUtils.getInteger(_map,"reporttype",-1);
                if(reporttype!=-1){
                    ISirmEnum enumName = EnumUtils.getSirmEnumName("INNERREPORT", "depthreport",reporttype);
                    if(enumName!=null){
                        reportTypeStr=enumName.getName();
                    }
                }
                String reporttitle = _map.get("reporttitle") == null ? "" : _map.get("reporttitle").toString();

                //替换作者名称
                IOrgService orgServic= OrgServiceFactory.getOrgService();
                String author=MapUtils.getString(_map,"author","");
                String[] authors=author.split(",");
                author="";
                for(int i=0;i<authors.length;i++){
                    String orgname=orgServic.getEmployeeById(authors[i]).getEmpName();
                    author=author+orgname;
                    author=author+",";
                }
                author=author.substring(0,author.length()-1);

                String applydate=MapUtils.getString(_map,"applydate").substring(0,10);
                String approvedate=MapUtils.getString(_map,"approvedate").substring(0,10);
                String finishdate=MapUtils.getString(_map,"finishdate").substring(0,10);
                String reportdate=MapUtils.getString(_map,"reportdate").substring(0,10);

                label = new Label(col++, row, reportTypeStr , format);//报告类型
                sheet.addCell(label);
                label = new Label(col++, row, reporttitle, format); //报告标题
                sheet.addCell(label);
                label = new Label(col++, row, author, format); //撰写人
                sheet.addCell(label);
                label = new Label(col++, row, applydate, format); //开题申请日期
                sheet.addCell(label);
                label = new Label(col++, row, approvedate, format); //批准撰写日期
                sheet.addCell(label);
                label = new Label(col++, row, finishdate, format); //报告完成日期
                sheet.addCell(label);
                label = new Label(col, row++, reportdate, format); //汇报安排日期
                sheet.addCell(label);
                col = 0;
            }
        }

    } catch (WriteException ex) {
        StringWriter sw = new StringWriter();
        ex.printStackTrace(new PrintWriter(sw));
        logger.error(sw.toString());
    }
}