这个是项目中用到的时候，做的一个类，
1package com.topwalk.iwp.pluging; 2 3import java.awt.Color; 4import java.io.File; 5import java.io.FileOutputStream; 6import java.io.IOException; 7import java.util.List; 8 9import org.apache.commons.lang3.RandomStringUtils; 10import org.apache.poi.hssf.util.CellRangeAddress; 11import org.apache.poi.ss.usermodel.BorderStyle; 12import org.apache.poi.ss.usermodel.CellStyle; 13import org.apache.poi.ss.usermodel.FillPatternType; 14import org.apache.poi.ss.usermodel.Font; 15import org.apache.poi.ss.usermodel.HorizontalAlignment; 16import org.apache.poi.ss.usermodel.VerticalAlignment; 17import org.apache.poi.xssf.usermodel.XSSFCell; 18import org.apache.poi.xssf.usermodel.XSSFCellStyle; 19import org.apache.poi.xssf.usermodel.XSSFColor; 20import org.apache.poi.xssf.usermodel.XSSFFont; 21import org.apache.poi.xssf.usermodel.XSSFRow; 22import org.apache.poi.xssf.usermodel.XSSFSheet; 23import org.apache.poi.xssf.usermodel.XSSFWorkbook; 24import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; 25 26public class CreateExcel { 27 private static XSSFFont font; 28 private static XSSFWorkbook workbook; 29 private static XSSFSheet sheet; 30 31/** 32 * 创建Excel文件，返回File文件对象 33 * @param excelDataBean 34 */ 35public static File createExcelReturnFile(ExcelDataBean excelDataBean,String IWPTitle){ 36 //创建WorkBook对象 37 workbook=new XSSFWorkbook(); 38 font=createFont(workbook,(short)0); 39 FileOutputStream out=null; 40 /*设置文件名*/ 41 String src="https://my.oschina.net//huiger/blog/224616/ExcelFile/"; 42 File dir=new File(src); 43 if(!dir.exists()){ 44 dir.mkdir(); 45 } 46 String filename=RandomStringUtils.randomAlphanumeric(10); 47 filename=new StringBuffer(src).append(filename).append(".xlsx").toString(); 48 File file=new File(filename); 49 try { 50 file.createNewFile(); 51 //创建SHEET 52 sheet = workbook.createSheet(); 53 workbook.setSheetName(0, "查询结果"); 54 //设置默认高度，默认宽度 55 sheet.setDefaultColumnWidth((short)25); 56 //sheet.setDefaultRowHeightInPoints(20); 57 sheet.setTabColor(123); 58 //创建头 59 createHeadRow(workbook, sheet,IWPTitle); 60 //写入IWP返回的查询信息 61 putValueToExcel(excelDataBean); 62 out=new FileOutputStream(file); 63 } catch (Exception e) { 64 }finally{ 65 try { 66 workbook.write(out); 67 out.close(); 68 } catch (IOException e) { 69 e.printStackTrace(); 70 } 71 } 72 return file; 73} 74 75 76/** 77 * 78 * @param excelDataBean 79 * @param IWPTitle 80 * @return 81 */ 82public static XSSFWorkbook createExcelReturnXSSFWorkbook(ExcelDataBean excelDataBean,String IWPTitle){ 83 //创建WorkBook对象 84 workbook=new XSSFWorkbook(); 85 font=createFont(workbook,(short)0); 86 FileOutputStream out=null; 87 /*设置文件名*/ 88 String src=""; 89 String filename=RandomStringUtils.randomAlphanumeric(10); 90 filename=new StringBuffer(src).append(filename).append(".xlsx").toString(); 91 File file=new File(filename); 92 try { 93 //file.createNewFile(); 94 //创建SHEET 95 sheet = workbook.createSheet(); 96 workbook.setSheetName(0, "查询结果"); 97 //设置默认高度，默认宽度 98 sheet.setDefaultColumnWidth((short)25); 99 //sheet.setDefaultRowHeightInPoints(20); 100 sheet.setTabColor(123); 101 //创建头 102 createHeadRow(workbook, sheet,IWPTitle); 103 //写入IWP返回的查询信息 104 putValueToExcel(excelDataBean); 105 out=new FileOutputStream(file); 106 } catch (Exception e) { 107 }finally{ 108 try { 109 workbook.write(out); 110 out.close(); 111 file.deleteOnExit(); 112 } catch (Exception e) { 113 e.printStackTrace(); 114 } 115 } 116 return workbook; 117} 118 119 120/** 121 * 设置CellStyle格式 122 * @param workbook 123 * @return 124 */ 125public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook){ 126 XSSFCellStyle cellStyle=workbook.createCellStyle(); 127 // 设置单元格边框样式 128 // CellStyle.BORDER_DOUBLE 双边线 129 // CellStyle.BORDER_THIN 细边线 130 // CellStyle.BORDER_MEDIUM 中等边线 131 // CellStyle.BORDER_DASHED 虚线边线 132 // CellStyle.BORDER_HAIR 小圆点虚线边线 133 // CellStyle.BORDER_THICK 粗边线 134 //getAllPersonBean 135 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); 136 cellStyle.setBorderTop(CellStyle.BORDER_THIN); 137 cellStyle.setBorderLeft(CellStyle.BORDER_THIN); 138 cellStyle.setBorderRight(CellStyle.BORDER_THIN); 139 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 140 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 141 //创建字体 142 Font fontHeader=workbook.createFont(); 143 //字体号码 144 fontHeader.setFontHeightInPoints((short)10); 145 //字体名称 146 fontHeader.setFontName("宋体"); 147 cellStyle.setFont(fontHeader); 148 return cellStyle; 149} 150 151 152/** 153 * 竖向合并表格 154 * @param sheet 155 * @param rownumber 156 * @param columnsize 157 */ 158public static void AddMergedRegion(XSSFSheet sheet,int columnsize,int lastRowNum,int rownum){ 159 for (int i = 0; i <=columnsize; i++) { 160 sheet.addMergedRegion(new CellRangeAddress(lastRowNum,lastRowNum+rownum-1, i,i)); 161 } 162} 163 164 165/** 166 * 功能：创建CellStyle样式 167 * @param wb XSSFWorkbook 168 * @param backgroundColor 背景色 169 * @param foregroundColor 前置色 170 * @param font 字体 171 * @return CellStyle 172 */ 173public static CellStyle createCellStyle(XSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){ 174 CellStyle cs=wb.createCellStyle(); 175 cs.setAlignment(halign); 176 cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 177 cs.setFillBackgroundColor(backgroundColor); 178 cs.setFillForegroundColor(foregroundColor); 179 cs.setFillPattern(CellStyle.SOLID_FOREGROUND); 180 cs.setFont(font); 181 return cs; 182} 183/** 184 * 功能：创建带边框的CellStyle样式 185 * @param wb XSSFWorkbook 186 * @param backgroundColor 背景色 187 * @param foregroundColor 前置色 188 * @param font 字体 189 * @return CellStyle 190 */ 191public static CellStyle createBorderCellStyle(XSSFWorkbook wb,short backgroundColor,short foregroundColor,short halign,Font font){ 192 CellStyle cs=wb.createCellStyle(); 193 cs.setAlignment(halign); 194 cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 195 cs.setFillBackgroundColor(backgroundColor); 196 cs.setFillForegroundColor(foregroundColor); 197 cs.setFillPattern(CellStyle.SOLID_FOREGROUND); 198 cs.setFont(font); 199 cs.setBorderLeft(CellStyle.BORDER_DASHED); 200 cs.setBorderRight(CellStyle.BORDER_DASHED); 201 cs.setBorderTop(CellStyle.BORDER_DASHED); 202 cs.setBorderBottom(CellStyle.BORDER_DASHED); 203 return cs; 204} 205 206 207/** 208 * 功能：创建CELL 209 * @param row HSSFRow 210 * @param cellNum int 211 * @param style HSSFStyle 212 * @return HSSFCell 213 */ 214public static XSSFCell createCell(XSSFRow row,int cellNum,CellStyle style){ 215 XSSFCell cell=row.createCell(cellNum); 216 cell.setCellStyle(style); 217 return cell; 218} 219/** 220 * 功能：合并单元格 221 * @param sheet XSSFSheet 222 * @param firstRow int 223 * @param lastRow int 224 * @param firstColumn int 225 * @param lastColumn int 226 * @return int 合并区域号码 227 */ 228public static int mergeCell(XSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){ 229 return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn)); 230} 231/** 232 * 功能：创建字体 233 * @param wb XSSFWorkbook 234 * @param boldweight short 235 * @param color short 236 * @return Font 237 */ 238public static Font createFont(XSSFWorkbook wb,short boldweight,short color,short size){ 239 Font font=wb.createFont(); 240 font.setBoldweight(boldweight); 241 font.setColor(color); 242 font.setFontHeightInPoints(size); 243 return font; 244} 245 246/** 247 * 创建Head 248 * @param wb 249 * @param sheet 250 */ 251private static void createHeadRow(XSSFWorkbook wb, XSSFSheet sheet,String IWPTitle){ 252 XSSFRow row=sheet.createRow(0); 253 XSSFFont font=createFont(wb, (short)18); 254 // 创建单元格样式 255 XSSFColor color=new XSSFColor(new Color(36, 142, 195)); 256 XSSFCellStyle style =createStyle(wb, color); 257 style.setFont(font);// 设置字体 258 XSSFCell cell=row.createCell(0); 259 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 260 //cell.setCellStyle(style); 261 262 //合并单元个(第0行到第一行合并，) 263 sheet.addMergedRegion(new CellRangeAddress(0,1,0,10)); 264 //设置合并单元格的边框(合并一行用row(0) 265 //setCellBorder(0,10,row,style); 266 //合并两行，必须要用roe(1)才能设置,设置边框 267 setCellBorder(0,10,sheet.createRow(1),style); 268 setCellBorder(0,10,row,style); 269 //设置文件头 270 cell.setCellValue(IWPTitle); 271 cell.setCellStyle(style); 272} 273 274 275/** 276 * 合并单元格加边框 水平 277 * @param sheet 278 * @param region 279 * @param cs 280 */ 281public static void setCellBorder(int start, int end, XSSFRow row, XSSFCellStyle style) { 282 for(int i=start;i<=end;i++){ 283 XSSFCell cell = row.createCell(i); 284 cell.setCellValue(""); 285 cell.setCellStyle(style); 286 } 287} 288 289/** 290 * 创建字体 291 * @param workbook 292 * @param size 字体大小 293 * @return 294 */ 295private static XSSFFont createFont(XSSFWorkbook workbook,short size){ 296 XSSFFont font=workbook.createFont(); 297 //字体样式 298 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); 299 //字体颜色 300 font.setColor(XSSFFont.COLOR_NORMAL); 301 //字体大小 302 if(0==size){ 303 font.setFontHeightInPoints(XSSFFont.DEFAULT_FONT_SIZE); 304 }else{ 305 font.setFontHeightInPoints(size); 306 } 307 font.setFontName("微软雅黑"); 308 return font; 309} 310 311/** 312 * 创建CellStyle 313 * @param workbook 314 * @param XSSFColor color 颜色 315 */ 316private static XSSFCellStyle createStyle(XSSFWorkbook workbook,XSSFColor color){ 317 318 XSSFCellStyle cellStyle=workbook.createCellStyle(); 319 //对齐样式 320 cellStyle.setAlignment(HorizontalAlignment.CENTER); 321 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); 322 //设置颜色 323 cellStyle.setFillForegroundColor(color); 324 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 325 326 cellStyle.setBorderTop(BorderStyle.MEDIUM); 327 cellStyle.setBorderBottom(BorderStyle.MEDIUM); 328 cellStyle.setBorderLeft(BorderStyle.MEDIUM); 329 cellStyle.setBorderRight(BorderStyle.MEDIUM); 330 //设置边框颜色 331 cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new XSSFColor(Color.BLACK)); 332 cellStyle.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM,new XSSFColor(Color.BLACK)); 333 cellStyle.setBorderColor(XSSFCellBorder.BorderSide.LEFT,new XSSFColor(Color.BLACK)); 334 cellStyle.setBorderColor(XSSFCellBorder.BorderSide.RIGHT,new XSSFColor(Color.BLACK)); 335 //设置字体 336 cellStyle.setFont(font); 337 //设置自动换行 338 cellStyle.setWrapText(true); 339 return cellStyle; 340} 341 342 343/** 344 * 将数据填充到Excel中去 345 * @param bean 346 */ 347private static void putValueToExcel(ExcelDataBean bean){ 348 //查询时间 349 String queryDate = bean.getQueryDate(); 350 //查询参数 351 String queryParam = bean.getQueryParam(); 352 //查询结果 353 List<QueryResult> queryResultList = bean.getQueryResult(); 354 //写第一行信息，查询参数，查询时间 row 为2 355 writeParamRow(queryParam, queryDate); 356 //遍历 写查询获得是真正结果 空一行 row从4开始 357 for (int i = 0; i < queryResultList.size(); i++) { 358 QueryResult queryResult = queryResultList.get(i); 359 //服务名称(空一行，另起一行 服务名称：达梦数据库查询) 360 //服务名称行样式 361 XSSFCellStyle serviceNameRowStyle=createStyle(workbook, new XSSFColor(new Color(255,255,255))); 362 //每个服务方法结果时间空一行，所以加2 363 XSSFRow serviceNameRow = sheet.createRow(sheet.getLastRowNum()+2); 364 XSSFCell keyCell = serviceNameRow.createCell(0); 365 keyCell.setCellValue("服务名称"); 366 keyCell.setCellStyle(serviceNameRowStyle); 367 XSSFCell valueCell=serviceNameRow.createCell(1); 368 valueCell.setCellValue(queryResult.getServiceName()); 369 valueCell.setCellStyle(serviceNameRowStyle); 370 //开始写查询结果 371 List<List<String>> resultList = queryResult.getList(); 372 for (int j = 0; j < resultList.size(); j++) { 373 //遍历，这里的每一个rowValueList就对应Excel文件上的一行 374 List<String> rowValueList = resultList.get(j); 375 //如果是第一个对象，则是查询字段的字段名，表头，设置样式 376 if(j==0){ 377 XSSFRow tableHeadRow=sheet.createRow(sheet.getLastRowNum()+1); 378 //创建Style 有边框，字体，单元格背景等 379 XSSFCellStyle headRowStyle=createStyle(workbook, new XSSFColor(new Color(155,187,89))); 380 for (int k = 0; k < rowValueList.size(); k++) { 381 XSSFCell cell=tableHeadRow.createCell(k); 382 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 383 cell.setCellValue(rowValueList.get(k)); 384 cell.setCellStyle(headRowStyle); 385 } 386 //如果不是第一个对象，则是服务查询返回的结果信息，需要判断结果中是不是有空信息，设置背景色为白色 387 }else{ 388 XSSFRow tableHeadRow=sheet.createRow(sheet.getLastRowNum()+1); 389 XSSFCellStyle tableValueRowStyle=createStyle(workbook, new XSSFColor(new Color(255,255,255))); 390 for (int k = 0; k < rowValueList.size(); k++) { 391 XSSFCell cell=tableHeadRow.createCell(k); 392 //用正则判断，如果值为数据，则将Cell的格式设置为数据格式，如果不能匹配，则设置成字符串格式 393 if((rowValueList.get(k)).matches("^[1-9]\\d*$")){ 394 cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); 395 }else{ 396 cell.setCellType(XSSFCell.CELL_TYPE_STRING); 397 } 398 //如果服务查询结果中，某一个字段信息是空值，则用""填充，避免有空指针错误出现 399 if(null==rowValueList.get(k)){ 400 cell.setCellValue(""); 401 }else{ 402 cell.setCellValue(rowValueList.get(k)); 403 } 404 cell.setCellStyle(tableValueRowStyle); 405 } 406 } 407 } 408 } 409 410 411} 412/** 413 * 写第一行信息 414 * @param queryParam 查询参数 415 * @param queryDate 查询时间 416 */ 417private static void writeParamRow(String queryParam,String queryDate){ 418 //黄色 419 XSSFCellStyle paramRowStyle=createStyle(workbook, new XSSFColor(new Color(255,235,156))); 420 //红色 421 XSSFCellStyle valueRowStyle=createStyle(workbook, new XSSFColor(new Color(255,199,206))); 422 //设置第三行显示查询参数和查询时间 423 XSSFRow paramRow = sheet.createRow(2); 424 XSSFCell paramKeyCell = paramRow.createCell(0); 425 paramKeyCell.setCellValue("查询参数"); 426 paramKeyCell.setCellStyle(paramRowStyle); 427 XSSFCell paramValueCell = paramRow.createCell(1); 428 paramValueCell.setCellValue(queryParam); 429 paramValueCell.setCellStyle(valueRowStyle); 430 XSSFCell dateKeyCell = paramRow.createCell(2); 431 dateKeyCell.setCellValue("查询时间"); 432 dateKeyCell.setCellStyle(paramRowStyle); 433 XSSFCell dateValueCell = paramRow.createCell(3); 434 dateValueCell.setCellValue(queryDate); 435 dateValueCell.setCellStyle(valueRowStyle); 436} 437 438}