java jxl 导出excel_Java_JXL 实现导出excel

1 @PostMapping("/inform/export2")2 public @ResponseBody Reobj export2 (@Valid @RequestBody SearchParamsearchParam) {3 Reobj reobj = new Reobj();4 Reobj> propertyInform = (Reobj>) this.getExport(searchParam);5 List list =propertyInform.getData();6 List qualified = new ArrayList();7 for(PropertyContractInform propertyContractInform : list) {8 propertyContractInform.setChargingPeriod(DateUtil.getStartOrEndDayOfQuarter(true)+"--"+DateUtil.getStartOrEndDayOfQuarter(false));9 qualified.add(propertyContractInform);10 }11 if(qualified.size() > 0) {12 reobj.setRecode("200");13 reobj.setMessage("导出成功");14 Date date=newDate();15 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");16 String now =sdf.format(date);17 //String filename = "收费通知"+now+".xls";

18 String filename="收费通知.xls";19 reobj.setData(filename);20 File file2 = new File(unqualifiedPath + "/" +filename);21 try{22 //打开文件

23 WritableWorkbook workbook =Workbook.createWorkbook(file2);24 WritableSheet sheet = workbook.createSheet("收费通知单", 0);25

26 WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),//字体

27 15,//WritableFont.DEFAULT_POINT_SIZE,//字号

28 WritableFont.BOLD, //粗体

29 false, //斜体

30 UnderlineStyle.NO_UNDERLINE, //下划线

31 Colour.BLACK, //字体颜色

32 ScriptStyle.NORMAL_SCRIPT);33

34 WritableFont wf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),//字体

35 10,//WritableFont.DEFAULT_POINT_SIZE,//字号

36 WritableFont.NO_BOLD, //粗体

37 false, //斜体

38 UnderlineStyle.NO_UNDERLINE, //下划线

39 Colour.BLACK, //字体颜色

40 ScriptStyle.NORMAL_SCRIPT);41

42 WritableFont Wf_explain = new WritableFont(WritableFont.createFont("仿宋_GB2312"),//字体

43 10,//WritableFont.DEFAULT_POINT_SIZE,//字号

44 WritableFont.BOLD, //粗体

45 false, //斜体

46 UnderlineStyle.NO_UNDERLINE, //下划线

47 Colour.BLACK, //字体颜色

48 ScriptStyle.NORMAL_SCRIPT);49 WritableCellFormat wcfTitle = new WritableCellFormat(titleWf); //单元格定义

50 try{51 wcfTitle.setBackground(jxl.format.Colour.WHITE);52 wcfTitle.setAlignment(jxl.format.Alignment.CENTRE); //设置对齐方式

53 wcfTitle.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);54 } catch(WriteException e1) {55 e1.printStackTrace();56 }57

58

59 WritableFont font1 = new WritableFont(WritableFont.COURIER, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);60 WritableCellFormat cellFormat2 = newWritableCellFormat(font1);61 cellFormat2.setBorder(Border.ALL,BorderLineStyle.DASH_DOT);62 try{63 cellFormat2.setAlignment(Alignment.CENTRE);64 cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);65 cellFormat2.setBackground(Colour.WHITE);66 cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);67 cellFormat2.setWrap(true);68 } catch(WriteException e) {69 e.printStackTrace();70 }71 int n = 2;72 if (qualified != null && qualified.size() > 0) {73 sheet.getSettings().setDefaultColumnWidth(20);74 String[] array = {""};75 String explain = "";76 String formName = "";77 String formFoot="";78 String formCut="--------------------------------------------------------------------------------------";79 String sign = " ";80 String sign2 = " ";81 String formPropertyName = "";82

83 /**

84 换行 \n85 */

86

87 int unitColumn = 13;88 for(int index = 0; index < qualified.size(); index++){89 PropertyContractInform propertyContractInform =qualified.get(index);90 //标题栏

91 String propertyFeeInfos [] ={propertyContractInform.getContractId(),propertyContractInform.getPartyB(), propertyContractInform.getPosition(),92 propertyContractInform.getPropertyFee(),propertyContractInform.getRentalArea(), propertyContractInform.getDays(),93 propertyContractInform.getSumPropertyFee(),propertyContractInform.getElectricityFees(),propertyContractInform.getPublicFee(),propertyContractInform.getAirFee()94 ,propertyContractInform.getAirRent(),propertyContractInform.getSumFees(),propertyContractInform.getChargingPeriod(),95 };96

97 //测试

98 List arrayList = newArrayList();99 for (int i=0;i

103 List propertyFeeInfosList = newArrayList();104 for (int i = 0;i

108

109 if(propertyContractInform.getAirRent().equals("0")){110 arrayList.remove("空调费单价");111 arrayList.remove("空调费");112 propertyFeeInfosList.remove(propertyContractInform.getAirFee());113 propertyFeeInfosList.remove(propertyContractInform.getAirRent());114 }115

116 int unitCol = arrayList.size()-1;117

118 for (int i = 0; i < arrayList.size(); i++) {119

120 WritableCellFormat wcf = newWritableCellFormat(titleWf);121 //wcf.setBackground(Colour.GRAY_25);

122 wcf.setAlignment(jxl.format.Alignment.CENTRE);123 wcf.setVerticalAlignment(VerticalAlignment.CENTRE);124 wcf.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);125 wcf.setWrap(true);126

127 WritableCellFormat wcf2 = newWritableCellFormat(wf);128 wcf2.setAlignment(jxl.format.Alignment.CENTRE);129 wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);130 wcf2.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);131 wcf2.setWrap(true);132

133 WritableCellFormat wcf3 = newWritableCellFormat(wf);134 wcf3.setAlignment(Alignment.LEFT);135 wcf3.setWrap(true);136 wcf3.setVerticalAlignment(VerticalAlignment.CENTRE);137 wcf3.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);138

139 WritableCellFormat wcf4 = newWritableCellFormat(Wf_explain);140 wcf4.setAlignment(Alignment.LEFT);141 wcf4.setWrap(true);142 wcf4.setVerticalAlignment(VerticalAlignment.CENTRE);143 wcf4.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);144

145 WritableCellFormat wcf5 = newWritableCellFormat(wf);146 wcf5.setAlignment(Alignment.RIGHT);147 wcf5.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);148 wcf5.setWrap(true);149

150 //表名

151 sheet.addCell(new Label(0, index * unitColumn + 1,formName,wcf));152 sheet.mergeCells(0, index * unitColumn + 1,unitCol, index * unitColumn + 1);153

154

155 WritableCellFormat cellFormat1 = newWritableCellFormat(font1);156 cellFormat1.setWrap(true);157

158 //物业名称

159 sheet.addCell(new Label(0, index * unitColumn + 2, formPropertyName,wcf));160 sheet.mergeCells(0, index * unitColumn + 2, unitCol, index * unitColumn + 2);161

162

163 //表头

164 sheet.addCell(new Label(i, index * unitColumn + 3, arrayList.get(i), wcf2));165

166 //数据

167 sheet.addCell( new Label(i, index * unitColumn + 4, propertyFeeInfosList.get(i), wcf2));168 //签收

169 sheet.addCell(new Label(0, index * unitColumn + 5, sign,wcf3));170 sheet.mergeCells(0, index * unitColumn + 5, unitCol, index * unitColumn + 5);171

172

173 //分隔

174 sheet.addCell(new Label(0, index * unitColumn + 6, formCut,wcf5));175 sheet.mergeCells(0, index * unitColumn + 6, unitCol, index * unitColumn + 6);176

177 //下面的表格部分178

179 //表名

180 sheet.addCell(new Label(0, index * unitColumn + 7, formName, wcf));181 sheet.mergeCells(0, index * unitColumn + 7, unitCol, index * unitColumn + 7);182

183 //物业名称

184 sheet.addCell(new Label(0, index * unitColumn + 8, formPropertyName, wcf));185 sheet.mergeCells(0, index * unitColumn + 8, unitCol, index * unitColumn + 8);186 //表头

187 sheet.addCell(new Label(i, index * unitColumn + 9, arrayList.get(i), wcf2));188 //数据

189 sheet.addCell(new Label(i, index * unitColumn + 10, propertyFeeInfosList.get(i), wcf2));190 //备注

191 sheet.addCell(new Label(0, index * unitColumn + 11, explain, wcf4));192 sheet.mergeCells(0, index * unitColumn + 11, unitCol, index * unitColumn + 11);193 //物业账户信息

194 sheet.addCell(new Label(0, index * unitColumn + 12, formFoot, wcf3));195 sheet.mergeCells(0, index * unitColumn + 12, unitCol, index * unitColumn + 12);196

197 //落款

198 sheet.addCell(new Label(0, index * unitColumn + 13, sign2, wcf3));199 sheet.mergeCells(0, index * unitColumn + 13, unitCol, index * unitColumn + 13);200

201

202 sheet.setRowView(index * unitColumn + 1,550);203 sheet.setRowView(index * unitColumn + 2,550);204 sheet.setRowView(index * unitColumn + 3,500);205 sheet.setRowView(index * unitColumn + 4,500);206 sheet.setRowView(index * unitColumn + 5,400);207 sheet.setRowView(index * unitColumn + 6,400);208

209 sheet.setRowView(index * unitColumn + 7,550);210 sheet.setRowView(index * unitColumn + 8,550);211 sheet.setRowView(index * unitColumn + 9,500);212 sheet.setRowView(index * unitColumn + 10,500);213 sheet.setRowView(index * unitColumn + 11,2200);214 sheet.setRowView(index * unitColumn + 12,1500);215 sheet.setRowView(index * unitColumn + 13,500);216 sheet.setColumnView(0,13);217 sheet.setColumnView(1,12);218 sheet.setColumnView(2,10);219 sheet.setColumnView(3,10);220 sheet.setColumnView(4,10);221 sheet.setColumnView(5,5);222 sheet.setColumnView(6,10);223 sheet.setColumnView(7,10);224 sheet.setColumnView(8,10);225 sheet.setColumnView(9,10);226 sheet.setColumnView(10,10);227 sheet.setColumnView(11,9);228 sheet.setColumnView(12,12);229

230 }231

232 //测试

233 arrayList.add("空调费单价");234 arrayList.add("空调费");235 propertyFeeInfosList.add(propertyContractInform.getAirFee());236 propertyFeeInfosList.add(propertyContractInform.getAirRent());237

238 n++;239 }240 workbook.write();241 try{242 workbook.close();243 } catch(WriteException e) {244 e.printStackTrace();245 }246 }247 } catch(IOException e) {248 e.printStackTrace();249 } catch(RowsExceededException e) {250 e.printStackTrace();251 } catch(WriteException e) {252 e.printStackTrace();253 } catch(ParseException e) {254 e.printStackTrace();255 }256

257 }258 returnreobj;259 }