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();