jxl 方式导出excel

 

   List<EnergyEntityBean> energyList = ee.getEnergyName();// 获得所有的能源名称
   List<EnterpriseEnergyEntityBean> list = ee.getEnergyConsumeSWZBByCond(
     reportNo, areaId + "");//获得数据
   
   String fileName=Excels.toUtf8String("消费量汇总");
   response.addHeader("content-type", "application/shlnd.ms-excel;charset=utf-8");
   response.addHeader("content-disposition", "attachment; filename="+fileName+".xls");  
   
   WritableWorkbook workbook=null;
   workbook=jxl.Workbook.createWorkbook(response.getOutputStream());
  
   jxl.write.WritableSheet sheet=workbook.createSheet("消费量汇总", 0);
   
   jxl.write.WritableCellFormat cellFormat=new WritableCellFormat();
   cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
   cellFormat.setAlignment(Alignment.CENTRE);
   cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
   cellFormat.setWrap(true);//设置自动换行
   jxl.write.WritableFont font=new WritableFont(WritableFont.createFont("宋体"),12);
   cellFormat.setFont(font);
   jxl.write.Label lbl=null;  

   sheet.setColumnView(0, 15);//设置列宽
   sheet.setColumnView(1, 15);
   sheet.setColumnView(2, 15);
   lbl=new Label(0,0,"消费量汇总",cellFormat);
   sheet.addCell(lbl);
   sheet.mergeCells(0, 0, energyList.size()+2, 0);
   lbl = new Label(0,1,"单位名称",cellFormat);
   sheet.addCell(lbl);
   sheet.mergeCells(0, 1, 1, 2);
   lbl = new Label(2,1,"消费量",cellFormat);
   sheet.addCell(lbl);
   sheet.mergeCells(2, 1, 2, 2);
//   表头信息
   for (int i =0;i< energyList.size();i++) {
    EnergyEntityBean eeb = energyList.get(i);
    lbl = new Label(i+3,1,eeb.getEnergyName(),cellFormat);
    sheet.addCell(lbl);
    lbl = new Label(i+3,2,eeb.getEnergyUnit(),cellFormat);
    sheet.addCell(lbl);
    sheet.setColumnView(i+3, 15);;
   }
//   数据信息
   
//          循环判断当前对象中是否有相应的能源，如果不存在相应的能源，则添加0
   int r = 3;//控制最外层的行，从第三行开始
   for (EnterpriseEnergyEntityBean ee : list) {
    int i=0;
    int j=0;
//    预算量
    lbl = new Label(0,r,ee.getAreaName(),cellFormat);
    sheet.addCell(lbl);
    sheet.mergeCells(0,r,0,r+1);
    lbl = new Label(1,r,"预算量",cellFormat);
    sheet.addCell(lbl);
    lbl = new Label(2,r,"--",cellFormat);
    sheet.addCell(lbl);
    for (EnergyEntityBean eeb : energyList) {     
     if (!ee.getEnergySW().containsKey(eeb.getEnergyTypeId())) {
      ee.getEnergySW().put(eeb.getEnergyTypeId(), "0");
     }
     lbl = new Label(i+3,r,ee.getEnergySW().get(eeb.getEnergyTypeId()),cellFormat);
     sheet.addCell(lbl);
     i++;
    }
//    实际消费量
    lbl = new Label(1,r+1,"实际消费量",cellFormat);
    sheet.addCell(lbl);
    lbl = new Label(2,r+1,ee.getZhxf(),cellFormat);
    sheet.addCell(lbl);
    for (EnergyEntityBean eeb : energyList) {     
     if (!ee.getEnergyZB().containsKey(eeb.getEnergyTypeId())) {
      ee.getEnergyZB().put(eeb.getEnergyTypeId(), "0");
     }
     lbl = new Label(j+3,r+1,ee.getEnergyZB().get(eeb.getEnergyTypeId()),cellFormat);
     sheet.addCell(lbl);      
     j++;
    }
    r+=2;//由于一个地市包含预算量和实际消费量两行，故此时加2
   }
   workbook.write();
   workbook.close();
   response.getOutputStream().close();