java JXL 导出表格

依赖

<dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

 

源码：

//----前一日录像丢失检查导出----start
    @RequestMapping(value = "/yesterdayExport")
    public void yesterdayExport(RecordQuery query, HttpServletResponse response) throws Exception {
        query.setPageSize(50000);
        query.setPageNumber(1);
        PageInfo<RecordStatusVo> result = recordService.getYesterdayRecordStatus(query);
        JSONArray list = JSONArray.fromObject(result.getList());
        Label label = null;
        WritableSheet sheet = null;
        WritableWorkbook workBook = null;
        WorkbookSettings settings = new WorkbookSettings();
        settings.setWriteAccess(null);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        InputStream is = null;
        OutputStream out = null;
        try {
            workBook = Workbook.createWorkbook(os, settings);
            sheet = workBook.createSheet("定时录像", 0);
            for (int i = 0; i < 11; i++) {
                //设置Excel的宽度
                sheet.setColumnView(i, 20);
            }
            //向Excel中添加数据
            sheet = this.addRecordTitleInfoToLabelByYesterdayExport(label, sheet, query.getRecordDate());
            this.addContentInfoToLabelByYesterdayExport(label, sheet, list);
            workBook.write();
        } catch (IOException e) {
            LOG.error("", e);
        } finally {
            workBook.close();
        }
        try {
            is = new ByteArrayInputStream(os.toByteArray());
            out = response.getOutputStream();
            // 设置输出文件信息
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String(("前一日录像丢失检查报表" + DateTimeUtils.getTime("YYYYMMddHHmmssSSS") + ".xls").getBytes("gb2312"), "ISO8859-1"));

            // 写文件流
            byte[] buffer = new byte[6 * 1024];
            int len = 0;
            while ((len = is.read(buffer, 0, buffer.length)) != -1) {
                out.write(buffer, 0, len);
            }
        } catch (IOException e) {
            LOG.error("", e);
        }finally{
            is.close();
            out.close();
        }
    }

    /**
     * 给Excel导出增加标题
     *
     * @param label
     * @param sheet
     * @param recordDate
     * @return WritableSheet
     * @throws WriteException
     * @throws RowsExceededException
     */
    private WritableSheet addRecordTitleInfoToLabelByYesterdayExport(Label label, WritableSheet sheet, String recordDate) throws RowsExceededException, WriteException {
        String format = DateUtil.format(DateUtil.parse(recordDate), "YYYY年MM月dd日");

        //设置表头样式
        WritableFont wf = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD, false);
        WritableCellFormat wcf = new WritableCellFormat(wf);
//        wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        wcf.setAlignment(Alignment.CENTRE);// 对齐方式
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);// 对齐方式

        //第一行
        label = new Label(0, 0, format + "录像丢失检查报表", wcf);
        sheet.addCell(label);
        //合并单元格，参数依次为：列索引、行索引、列索引+需要合并的列的个数、行索引+需要合并的行的个数
        sheet.mergeCells(0, 0, 7, 0);

        label = new Label(0, 1, "序号");
        sheet.addCell(label);
        label = new Label(1, 1, "组织机构");
        sheet.addCell(label);
        label = new Label(2, 1, "通道名称");
        sheet.addCell(label);
        label = new Label(3, 1, "通道号");
        sheet.addCell(label);
        label = new Label(4, 1, "设备名称");
        sheet.addCell(label);
        label = new Label(5, 1, "设备ip");
        sheet.addCell(label);
        label = new Label(6, 1, "定时录像状态");
        sheet.addCell(label);
        label = new Label(7, 1, "丢失时长");
        sheet.addCell(label);
        return sheet;
    }

    /**
     * 往Excel中添加内容
     * @param label
     * @param sheet
     * @throws Exception
     */
    private void addContentInfoToLabelByYesterdayExport(Label label, WritableSheet sheet, JSONArray list) throws Exception {
        for (int i = 0; i < list.size(); i++) {

            int count = 0;
            JSONObject info = list.getJSONObject(i);
            label = new Label(count, i + 2, String.valueOf(i + 1));
            sheet.addCell(label);
            count++;

            label = new Label(count, i + 2, info.getString("pathName"));
            sheet.addCell(label);
            count++;

            label = new Label(count, i + 2, info.getString("chnnName"));
            sheet.addCell(label);
            count++;
            label = new Label(count, i + 2, info.getString("chnnCode"));
            sheet.addCell(label);
            count++;
            label = new Label(count, i + 2, info.getString("dvsName"));
            sheet.addCell(label);
            count++;
            label = new Label(count, i + 2, info.getString("netAddress"));
            sheet.addCell(label);
            count++;
            label = new Label(count, i + 2, info.getString("timeRecord").equals("0") ? "正常" : "异常");
            sheet.addCell(label);
            count++;
            Long loseTime = StringUtils.isBlank(info.getString("loseTime")) ? 0L : info.getLong("loseTime");
            String loseTimeStr = loseTime / 3600 + "时" + loseTime % 3600 / 60 + "分" + loseTime % 3600 % 60 % 60 + "秒";
            label = new Label(count, i + 2, loseTimeStr);
            sheet.addCell(label);
            count++;
        }
    }

    //----前一日录像丢失检查导出----end

 

效果：