1 /************2019-11-28 ******************/
  2     /**
  3      * 数据导出 2019-11-28
  4      */
  5     public String 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         return createExcel(kdname, planguid, kdguid);
 14     }
 15 
 16     public String createExcel(String preName, String planguid, String kdguid) {
 17         // 第一步,创建一个webbook,对应一个Excel文件
 18         HSSFWorkbook wb = new HSSFWorkbook();
 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 = new FileOutputStream(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         return targetPath;
 81     }
 82 
 83     public void putUserData(String planguid, String kdguid, HSSFRow row, HSSFCellStyle cellStyle, HSSFSheet sheet,
 84             Integer it) {
 85         int i = 0, m = 0, n = 0;
 86         //查找本次考试计划下所有参与的考点
 87         List<KsKaoc> 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<KsKaoc> 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<Record> 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     }
View Code

 

最终表格样式大致如下: