利用jxl实现下载打印

最近做的一个项目很多地方需要使用下载打印，不管是excel的还是doc的一大批，格式五花八门，搞得头昏眼花的。现在项目已经结束了，就下载打印总结一下经验。

1.使用模板写下载打印

相信做过下载打印的朋友们都写过通过模板的下载打印方式，先把要把下载打印的格式设计好，然后通过一个xml文件将其所需要的值灌入进去，如：

<?xml version="1.0" encoding="UTF-8"?>
<documents type="excel">
 <document id="propertyShift" name="小区信息归集统计" class="com.ccb.refms.mf.query.total.vo.TotalVO" template="propTotal.xls">
  <sheet type="single" name="小区信息归集统计" readonly="true" union="1">
  <block type="field" row="0" >
    <row><col colnum="0" property="headerValue">小区名称</col></row>
  </block>
  <block type="field" row="1" >
    <row><col colnum="1" property="dateTime">时间</col></row>
  </block>
   <block type="table" row="3" list="dataList" class="java.util.ArrayList" itemclass="com.ccb.refms.mf.query.total.vo.DataVO">
     <col colnum="0" property="code">序号</col>
     <col colnum="1" property="bld_name">楼幢名称</col>
     <col colnum="2" property="stairs">单元数量</col>
     <col colnum="3" property="doors">房屋数量</col>
     <col colnum="4" property="people">业主数量</col>
     <col colnum="5" property="bal" pattern="money" property-type="array">余额</col>
     <col colnum="6" property="sum_area">面积</col>
     <col colnum="7" property="total" pattern="money" property-type="array">售房款</col>
     <col colnum="8" property="first_pay" pattern="money" property-type="array">首缴额度</col>
     <col colnum="9" property="total_pay_amt" pattern="money" property-type="array">累计缴款</col>
     <col colnum="10" property="tbusi_part" pattern="money" property-type="array">累计收益</col>
     <col colnum="11" property="total_ref_amt" pattern="money" property-type="array">累计退款</col>
   </block>
   
  </sheet>
 </document>
</documents>

这样做开始很顺手，做起来也很简单，遍历的这个集合也不需要怎么控制，很方便。但是，最后客户提出了一个需求，他们说要将制表人的姓名添加在打印的后面而不是在前面，并且不能有任何样式。这样一来，使用模板的下载打印方式在这里就行不通了，因为集合的size()根本就无法控制。这里我们就导入了第二种下载打印方式。

二、靠手写

1.首先我声明了三个常量

private final static int TYPE0 = 0;
 private final static int TYPE1 = 1;
 private final static int TYPE2 = 2;

2.写一个方法，根据传进的常量的值的不同调用不同的方法

 //参数设置匹配
 private WritableCellFormat getStyle(int type) {
  switch (type) {
  case TYPE0:
   return applyStyle0();
  case TYPE1:
   return applyStyle1();
  case TYPE2:
   return applyStyle2();
  default:
   return applyStyle1();
  }
 }

3.完成这些方法

private WritableCellFormat applyStyle0() {
  WritableFont font = new WritableFont(WritableFont.ARIAL, 20,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                Colour.BLACK);
  WritableCellFormat format = new WritableCellFormat(font);
  try {
   format.setVerticalAlignment(VerticalAlignment.TOP);//字体向上  CENTER表示上线居中
   format.setAlignment(Alignment.CENTRE);//表示左右居中
   format.setWrap(true);//是否自动换行  
   format.setBorder(Border.TOP, BorderLineStyle.NONE);
   format.setBorder(Border.LEFT, BorderLineStyle.NONE);
   format.setBorder(Border.RIGHT, BorderLineStyle.NONE);
   //  format.setBackground(Colour.YELLOW);// 黄色背景
  } catch (Exception e) {
   e.printStackTrace();
  }
  return format;
 }
 
  private WritableCellFormat applyStyle1() {
  WritableFont font = new WritableFont(WritableFont.TIMES, 10,
    WritableFont.NO_BOLD);
  WritableCellFormat format = new WritableCellFormat(font);
  try {
   format.setVerticalAlignment(VerticalAlignment.TOP);
   format.setAlignment(Alignment.CENTRE);
   format.setWrap(true);//是否自动换行  
   format.setBorder(Border.ALL, BorderLineStyle.THIN);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return format;
 }
 
 private WritableCellFormat applyStyle2() {
  WritableFont font = new WritableFont(WritableFont.TIMES, 10,
    WritableFont.BOLD);
  WritableCellFormat format = new WritableCellFormat(font);
  try {
   format.setVerticalAlignment(VerticalAlignment.TOP);
   format.setAlignment(Alignment.CENTRE);
   format.setWrap(true);//是否自动换行  
   format.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return format;
 }
这些方法主要是设置显示边框线、字体、背景色，控制单元的显示格式。

4.完成下载打印的方法

@SuppressWarnings("unchecked")
 public void printProp() throws ServiceException, IOException{
  WritableWorkbook workbook = null;
  ByteArrayOutputStream result = new ByteArrayOutputStream();
  try {
  workbook = Workbook.createWorkbook(result);
  WritableSheet sheet = workbook.createSheet("小区信息归集统计", 0);
  String name=vo.getHeaderValue()+"小区信息归集统计";
  sheet.setColumnView(0, 8);
  sheet.setColumnView(1, 22);
  sheet.setColumnView(2, 11);
  sheet.setColumnView(3, 11);
  sheet.setColumnView(4, 11);
  sheet.setColumnView(5, 11);
  sheet.setColumnView(6, 13);
  sheet.setColumnView(7, 13);
  sheet.setColumnView(8, 13);
  sheet.setColumnView(9, 13);
  sheet.setColumnView(10, 13);
  sheet.setColumnView(11, 13);
  // 设置标题
  sheet.mergeCells((short) 0, 0, (short) 11, 0);
  sheet.addCell(new Label(0, 0, name, getStyle(TYPE0)));
  sheet.setRowView(0, 550);
  sheet.getSettings().setDefaultRowHeight(400);

  // 合并抬头
  sheet.mergeCells((short) 0, 1, (short) 0, 1);
  vo.setDateTime(CommonUtils.timeStamp2String(new Timestamp(new Date().getTime())));
  sheet.addCell(new Label(0, 1, "日期：", getStyle(TYPE2)));
  sheet.mergeCells((short) 1, 1, (short) 1, 1);
  sheet.addCell(new Label(1, 1, vo.getDateTime(),getStyle(TYPE2)));
  
  // 行游标
  int rowIndex = 2;
  sheet.addCell(new Label(0, rowIndex, "编号", getStyle(TYPE1)));
  sheet.addCell(new Label(1, rowIndex, "楼幢名称", getStyle(TYPE1)));
  sheet.addCell(new Label(2, rowIndex, "单元数量", getStyle(TYPE1)));
  sheet.addCell(new Label(3, rowIndex, "房屋数量", getStyle(TYPE1)));
  sheet.addCell(new Label(4, rowIndex, "业主数量", getStyle(TYPE1)));
  sheet.addCell(new Label(5, rowIndex, "余额", getStyle(TYPE1)));
  sheet.addCell(new Label(6, rowIndex, "面积", getStyle(TYPE1)));
  sheet.addCell(new Label(7, rowIndex, "售房款", getStyle(TYPE1)));
  sheet.addCell(new Label(8, rowIndex, "首缴额度", getStyle(TYPE1)));
  sheet.addCell(new Label(9, rowIndex, "累计缴款", getStyle(TYPE1)));
  sheet.addCell(new Label(10, rowIndex, "累计收益", getStyle(TYPE1)));
  sheet.addCell(new Label(11, rowIndex, "累计退款", getStyle(TYPE1)));
  rowIndex++;
  List dataList = vo.getDataList();
  NumberFormat format = new DecimalFormat("#,###.##");//保留两位小数
  for (int i = 0; i < dataList.size(); i++) {
   DataVO bvo = (DataVO) dataList.get(i);
   if(bvo.getCode()!=null){
    sheet.addCell(new Label(0, rowIndex, String.valueOf(bvo.getCode()),getStyle(TYPE1)));
   }else{
    sheet.addCell(new Label(0, rowIndex, "",getStyle(TYPE1)));
   }
   sheet.addCell(new Label(1, rowIndex, bvo.getBld_name(),getStyle(TYPE1)));
   sheet.addCell(new Label(2, rowIndex, String.valueOf(bvo.getStairs()),getStyle(TYPE1)));
   sheet.addCell(new Label(3, rowIndex, String.valueOf(bvo.getDoors()),getStyle(TYPE1)));
   sheet.addCell(new Label(4, rowIndex, String.valueOf(bvo.getPeople()),getStyle(TYPE1)));
   sheet.addCell(new Label(5, rowIndex, bvo.getBal(),getStyle(TYPE1)));
   sheet.addCell(new Label(6, rowIndex, String.valueOf(format.format(bvo.getSum_area())),getStyle(TYPE1)));
   sheet.addCell(new Label(7, rowIndex, String.valueOf(format.format(bvo.getTotal())),getStyle(TYPE1)));
   sheet.addCell(new Label(8, rowIndex, String.valueOf(format.format(bvo.getFirst_pay())),getStyle(TYPE1)));
   sheet.addCell(new Label(9, rowIndex, String.valueOf(format.format(bvo.getTotal_pay_amt())),getStyle(TYPE1)));
   sheet.addCell(new Label(10, rowIndex, String.valueOf(format.format(bvo.getTbusi_part())),getStyle(TYPE1)));
   sheet.addCell(new Label(11, rowIndex, String.valueOf(format.format(bvo.getTotal_ref_amt())),getStyle(TYPE1)));
   rowIndex++;
  }
   String name1=SecurityUtils.getCurrentUserBO().getUserName();
   sheet.addCell(new Label(1, rowIndex, "制表人："+name1, getStyle(TYPE2)));
   sheet.addCell(new Label(2, rowIndex, "复核人：", getStyle(TYPE2)));
   sheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);
   sheet.getSettings().setScaleFactor(85);
   workbook.write();
   workbook.close();
   FacesContext ctx = FacesContext.getCurrentInstance();
   if (!ctx.getResponseComplete()) {
    HttpServletResponse response = (HttpServletResponse) ctx
      .getExternalContext().getResponse();
    response.setContentType("APPLICATION/VND.MS-EXCEL");
    response.setHeader("Content-Disposition",
      "attachment; filename="+ new String("小区信息归集统计.xls".getBytes("GBK"),"ISO8859_1"));
    ServletOutputStream output = response.getOutputStream();
    output.write(result.toByteArray());
    output.flush();
    output.close();
    result.close();
    ctx.responseComplete();
    DialogService.saveView();
  }
  } catch (RowsExceededException e) {
   e.printStackTrace();
  } catch (WriteException e) {
   e.printStackTrace();
  }
 }
在jsp页面中只要调用这个下载打印的方法即可。

总结：

使用模板实现下载打印相对来说简单，但是局限性很大，不能很好的控制格式。使用手写的方式，虽然可以灵活的控制下载打印的格式，但是相对来说比较麻烦，开发的速度相对较慢。总之，开发人员根据当时的开发情况而选择下载打印的方式即可。