1 /************2019-11-28 ******************/
2 /**
3 * 数据导出 2019-11-284 */
5 publicString exportExcel(String planguid) {6 //System.out.println(guid);
7 String kdname = "";8 String kdguid = "";9 if(flag) {10 kdname =kd.getKaodname();11 kdguid =kd.getRowguid();12 }13 returncreateExcel(kdname, planguid, kdguid);14 }15
16 publicString createExcel(String preName, String planguid, String kdguid) {17 //第一步,创建一个webbook,对应一个Excel文件
18 HSSFWorkbook wb = newHSSFWorkbook();19 //第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
20 HSSFSheet sheet = wb.createSheet("Sheet1");21 sheet.setDefaultColumnWidth(15);//默认列宽
22 sheet.setColumnWidth(0, 8*256);23 sheet.setColumnWidth(2, 50*256);24 sheet.setColumnWidth(5, 10*256);25 sheet.setColumnWidth(6, 10*256);26 //第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
27 HSSFRow row = sheet.createRow(0);28
29 //单元格样式
30 HSSFCellStyle cellStyle = wb.createCellStyle(); //单元格样式31 //字体样式
32 Font fontStyle =wb.createFont();33 fontStyle.setBold(false); //加粗
34 fontStyle.setFontName("黑体"); //字体
35 fontStyle.setFontHeightInPoints((short) 11); //大小
36 fontStyle.setColor(Font.COLOR_NORMAL);//颜色37 //字体样式添加到单元格样式中
38 cellStyle.setFont(fontStyle);39
40 //单元格风格
41 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
42 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中43 //单元格边框样式
44 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);45 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);46 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);47 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);48 //单元格背景色49 //cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());50 //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
51 Integer it = 0;52 putUserData(planguid, kdguid, row, cellStyle, sheet, it);53
54 //获得系统部署路径
55 String deployPath =ClassPathUtil.getDeployWarPath();56 //目标文件路径
57 String targetPath = "template/" + preName + "考场信息表_" + EpointDateUtil.convertDate2String(new Date()) + ".xls";58 //输出Excel文件
59 FileOutputStream output = null;60
61 try{62 File target = new File(deployPath + "/" +targetPath);63 output = newFileOutputStream(target);64 wb.write(output);65 output.flush();66 }67 catch(Exception e) {68 e.printStackTrace();69 }70 finally{71 try{72 if (output != null) {73 output.close();74 }75 }76 catch(IOException e) {77 e.printStackTrace();78 }79 }80 returntargetPath;81 }82
83 public voidputUserData(String planguid, String kdguid, HSSFRow row, HSSFCellStyle cellStyle, HSSFSheet sheet,84 Integer it) {85 int i = 0, m = 0, n = 0;86 //查找本次考试计划下所有参与的考点
87 List listkd =service.findKDListByPlanguid(planguid);88 if(StringUtil.isNotBlank(kdguid)) {89 listkd =service.findKDListByPlanguid(planguid, kdguid);90 }91 int kdi = 0;92 for (i = 0; i < listkd.size(); i++) {93 if (i == 0) {94 row =sheet.createRow(it);95 kdi =it;96 }97 else{98 row = sheet.createRow(it + 1);99 kdi = it + 1;100 }101 //生成单元格
102 HSSFCell cellkd = row.createCell(0);//0列
103 HSSFCell cellkd1 = row.createCell(1);//0列
104 HSSFCell cellkd2 = row.createCell(2);//0列
105 HSSFCell cellkd3 = row.createCell(3);//0列
106 HSSFCell cellkd4 = row.createCell(4);//0列
107 HSSFCell cellkd5 = row.createCell(5);//0列
108 HSSFCell cellkd6 = row.createCell(6);//0列109 //单元格内容
110 cellkd.setCellValue(listkd.get(i).getKaodname());111 //设置单元格样式
112 cellkd.setCellStyle(cellStyle);113 cellkd1.setCellStyle(cellStyle);114 cellkd2.setCellStyle(cellStyle);115 cellkd3.setCellStyle(cellStyle);116 cellkd4.setCellStyle(cellStyle);117 cellkd5.setCellStyle(cellStyle);118 cellkd6.setCellStyle(cellStyle);119
120 //合并单元格
121 CellRangeAddress kd = new CellRangeAddress(kdi, kdi, 0, 6); //起始行, 终止行, 起始列, 终止列
122 sheet.addMergedRegion(kd);123
124 List listkc =service.findKCListByPlanguid(planguid, listkd.get(i).getKaodguid());125 Integer kci = 0;126 for (m = 0; m < listkc.size(); m++) {127 if (i == 0 && m == 0) {128 kci =it;129 }130 else{131 kci = it + 1;132 }133 row = sheet.createRow(kci + 1);134 //生成单元格
135 HSSFCell cellkc = row.createCell(0);//行0列
136 HSSFCell cellkc1 = row.createCell(1);//行0列
137 HSSFCell cellkc2 = row.createCell(2);//行0列
138 HSSFCell cellkc3 = row.createCell(3);//行0列
139 HSSFCell cellkc4 = row.createCell(4);//行0列
140 HSSFCell cellkc5 = row.createCell(5);//行0列
141 HSSFCell cellkc6 = row.createCell(6);//行0列
142 row = sheet.createRow(kci + 2);143 HSSFCell celltime = row.createCell(0);//行0列
144 HSSFCell celltime1 = row.createCell(1);//行0列
145 HSSFCell celltime2 = row.createCell(2);//行0列
146 HSSFCell celltime3 = row.createCell(3);//行0列
147 HSSFCell celltime4 = row.createCell(4);//行0列
148 HSSFCell celltime5 = row.createCell(5);//行0列
149 HSSFCell celltime6 = row.createCell(6);//行0列150 //单元格内容
151 cellkc.setCellValue("第" + listkc.get(m).getKaocnum() + "考场考试信息表");152 String ny = EpointDateUtil.convertDate2String(listkc.get(m).getKaosstart(), "MM月dd日");153 String h = EpointDateUtil.convertDate2String(listkc.get(m).getKaosstart(), "HH");154 Integer ht =Integer.parseInt(h);155 String t = "下午";156 if (ht <= 12) {157 t = "上午";158 }159 String t1 = EpointDateUtil.convertDate2String(listkc.get(m).getKaosstart(), "HH:mm");160 String t2 = EpointDateUtil.convertDate2String(listkc.get(m).getKaosend(), "HH:mm");161 celltime.setCellValue(ny + t + t1 + "~" +t2);162 //设置单元格样式
163 cellkc.setCellStyle(cellStyle);164 cellkc1.setCellStyle(cellStyle);165 cellkc2.setCellStyle(cellStyle);166 cellkc3.setCellStyle(cellStyle);167 cellkc4.setCellStyle(cellStyle);168 cellkc5.setCellStyle(cellStyle);169 cellkc6.setCellStyle(cellStyle);170 celltime.setCellStyle(cellStyle);171 celltime1.setCellStyle(cellStyle);172 celltime2.setCellStyle(cellStyle);173 celltime3.setCellStyle(cellStyle);174 celltime4.setCellStyle(cellStyle);175 celltime5.setCellStyle(cellStyle);176 celltime6.setCellStyle(cellStyle);177 //合并单元格
178 CellRangeAddress kc = new CellRangeAddress((kci + 1), (kci + 1), 0, 6); //起始行, 终止行, 起始列, 终止列
179 CellRangeAddress time = new CellRangeAddress((kci + 2), (kci + 2), 0, 6); //起始行, 终止行, 起始列, 终止列
180 sheet.addMergedRegion(kc);181 sheet.addMergedRegion(time);182
183 row = sheet.createRow(kci + 3);184 //生成单元格
185 HSSFCell tcell0 = row.createCell(0);//0列
186 HSSFCell tcell1 = row.createCell(1);//1列
187 HSSFCell tcell2 = row.createCell(2);//2列
188 HSSFCell tcell3 = row.createCell(3);//3列
189 HSSFCell tcell4 = row.createCell(4);//4列
190 HSSFCell tcell5 = row.createCell(5);//5列
191 HSSFCell tcell6 = row.createCell(6);//6列192 //单元格内容
193 tcell0.setCellValue("序号");194 tcell1.setCellValue("专业");195 tcell2.setCellValue("科目");196 tcell3.setCellValue("姓名");197 tcell4.setCellValue("准考证号");198 tcell5.setCellValue("考场编号");199 tcell6.setCellValue("座位号");200 //设置单元格样式
201 tcell0.setCellStyle(cellStyle);202 tcell1.setCellStyle(cellStyle);203 tcell2.setCellStyle(cellStyle);204 tcell3.setCellStyle(cellStyle);205 tcell4.setCellStyle(cellStyle);206 tcell5.setCellStyle(cellStyle);207 tcell6.setCellStyle(cellStyle);208
209 List listbmd =bmdservice.findKD_KCUserlist(planguid, listkc.get(m).getCourseguid(),210 listkd.get(i).getKaodguid(), listkc.get(m).getKaocnum().toString());211 it = (kci + 4);212 for (n = 0; n < listbmd.size(); n++) {213 row =sheet.createRow(it);214 it = it + 1;215 //生成单元格
216 HSSFCell cell0 = row.createCell(0);//0列
217 HSSFCell cell1 = row.createCell(1);//1列
218 HSSFCell cell2 = row.createCell(2);//2列
219 HSSFCell cell3 = row.createCell(3);//3列
220 HSSFCell cell4 = row.createCell(4);//4列
221 HSSFCell cell5 = row.createCell(5);//5列
222 HSSFCell cell6 = row.createCell(6);//6列223 //单元格内容
224 cell0.setCellValue(n + 1);225 cell1.setCellValue(listbmd.get(n).getStr("gangwname"));226 cell2.setCellValue(listbmd.get(n).getStr("coursename"));227 cell3.setCellValue(listbmd.get(n).getStr("name"));228 cell4.setCellValue(listbmd.get(n).getStr("zhunkznum"));229 cell5.setCellValue(listbmd.get(n).getStr("kaocnum"));230 cell6.setCellValue(listbmd.get(n).getStr("zuownum"));231 //设置单元格样式
232 cell0.setCellStyle(cellStyle);233 cell1.setCellStyle(cellStyle);234 cell2.setCellStyle(cellStyle);235 cell3.setCellStyle(cellStyle);236 cell4.setCellStyle(cellStyle);237 cell5.setCellStyle(cellStyle);238 cell6.setCellStyle(cellStyle);239 }240 }241 }242 }