1 importorg.apache.commons.lang3.exception.ExceptionUtils;2 importorg.apache.poi.hssf.usermodel.HSSFDataFormat;3 importorg.apache.poi.hssf.usermodel.HSSFSheet;4 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;5 importorg.apache.poi.hssf.util.CellReference;6 import org.apache.poi.ss.usermodel.*;7 importorg.apache.poi.ss.util.CellRangeAddress;8 importorg.apache.poi.ss.util.RegionUtil;9 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;10 importjava.io.File;11 importjava.io.FileInputStream;12 importjava.io.FileOutputStream;13 importjava.io.IOException;14 importjava.util.List;15
16 public classExcelWriterUtil_Poi {17
18
19 //用于存放結果表内容的xlsx格式的工作簿
20 private XSSFWorkbook xssfWorkbook = null;21 //用于存放結果表内容的xls格式的工作簿
22 private HSSFWorkbook hssfWorkbook = null;23 //工作的sheet頁
24 privateSheet sheet;25 //用于讀取用例表内容複制到結果标的檔案輸出流
26 private FileOutputStream stream = null;27 //用于存儲結果表的路徑的成員變量，便于在儲存結果時進行判斷
28 private String path = null;29 //單元格格式
30 private CellStyle style = null;31 //表的總行數
32 public int rows = 0;33 private String sheetName="Sheet1";//初始化預設給一個sheet名字
34 private FileInputStream in =null;35 private String resultType=null;36
37
41 publicString getSheetName() {42
43 returnsheetName;44 }45
46
51 public voiduseSheet(String sheetName) {52
53 this.sheetName =sheetName;54 try{55 if (resultType.equals(".xlsx")) {56 sheet =xssfWorkbook.getSheet(getSheetName());57 if (sheet == null) {58 return;59 }60
61 } else if (resultType.equals(".xls")) {62 sheet =hssfWorkbook.getSheet(getSheetName());63 if (sheet == null) {64 return;65 }66 }67 rows =sheet.getPhysicalNumberOfRows();68
69 sheet.setForceFormulaRecalculation(true); //重新整理公式
70
71 } catch(Exception e) {72 System.out.println(ExceptionUtils.getStackTrace(e));73 }74
75
76 }77
78
82 public voidupdateSheetName(String sheetName){83 try{84 if (resultType.equals(".xlsx")) {85 sheet =xssfWorkbook.getSheet(getSheetName());86 if(sheet == null) {87 return;88 }89 int indexSheet =xssfWorkbook.getSheetIndex(sheet);90 xssfWorkbook.setSheetName(indexSheet,sheetName);91
92 }else if(resultType.equals(".xls")){93 sheet =hssfWorkbook.getSheet(getSheetName());94 if(sheet == null) {95 return;96 }97 int indexSheet =hssfWorkbook.getSheetIndex(sheet);98 hssfWorkbook.setSheetName(indexSheet,sheetName);99 }100 }catch(Exception e){101 System.out.println(ExceptionUtils.getStackTrace(e));102 }103 }104
105
108 public voidcloseStream() {109 try{110 in.close();111 } catch(IOException e) {112 //TODO Auto-generated catch block
113 e.printStackTrace();114 }115 }116
121 publicExcelWriterUtil_Poi(String path1, String path2) {122 //截取模闆表字尾名
123 String Origintype = path1.substring(path1.lastIndexOf("."));124 //判斷是xls還是xlsx格式，完成在記憶體中建立模闆表的工作簿
125 XSSFWorkbook xssfWorkbookRead = null;126 HSSFWorkbook hssfWorkbookRead = null;127 if (Origintype.equals(".xlsx")) {128 try{129 xssfWorkbookRead = new XSSFWorkbook(newFile(path1));130 } catch(Exception e) {131 System.out.println(ExceptionUtils.getStackTrace(e));132 }133 }134 if (Origintype.equals(".xls")) {135 try{136 hssfWorkbookRead = new HSSFWorkbook(new FileInputStream(newFile(path1)));137 } catch(Exception e) {138 System.out.println(ExceptionUtils.getStackTrace(e));139 }140 }141 //如果兩種格式均不符合，則檔案打開失敗
142 if (xssfWorkbookRead == null && hssfWorkbookRead == null) {143 System.out.println("Excel檔案打開失敗！");144 return;145 }146
147 //截取結果表字尾名
148 resultType = path2.substring(path2.lastIndexOf("."));149 //确定結果表格式為excel格式
150 if (resultType.equals(".xlsx") || resultType.equals(".xls")) {151 try{152 //根據新生表的檔案名，為該檔案在記憶體中開辟空間
153 File file = newFile(path2);154 try{155 //在磁盤上面建立該檔案
156 file.createNewFile();157 } catch(Exception e1) {158 //建立失敗，提示路徑非法，并停止建立
159 System.out.println(ExceptionUtils.getStackTrace(e1));160 return;161 }162 //基于新生表，建立檔案輸出流stream
163 stream = newFileOutputStream(file);164 //将用例表中的内容寫入檔案輸出流stream
165 if (hssfWorkbookRead != null) {166 hssfWorkbookRead.write(stream);167 //關閉用例表在記憶體中的副本
168 hssfWorkbookRead.close();169 } else{170 xssfWorkbookRead.write(stream);171 xssfWorkbookRead.close();172 }173 //關閉已經寫入了用例表内容的檔案流
174 stream.close();175 //基于新生表，建立檔案輸入流
176 in = newFileInputStream(file);177 //判斷結果檔案的字尾是03版還是07版excel
178 if (resultType.equals(".xlsx")) {179 try{180 //通過檔案輸入流，在記憶體中建立結果表的工作簿
181 xssfWorkbook = newXSSFWorkbook(in);182 System.out.println(getSheetName());183 sheet =xssfWorkbook.getSheet(getSheetName());184
185 } catch(Exception e) {186 System.out.println(ExceptionUtils.getStackTrace(e));187 }188 }189 if (resultType.equals(".xls")) {190 try{191 hssfWorkbook = newHSSFWorkbook(in);192 sheet =hssfWorkbook.getSheet(getSheetName());193 } catch(Exception e) {194 System.out.println(ExceptionUtils.getStackTrace(e));195 }196 }197 rows =sheet.getPhysicalNumberOfRows();198 //将成員變量結果檔案路徑指派為path2，表示結果表已經成功建立。
199 path =path2;200
201 } catch(Exception e) {202 System.out.println( ExceptionUtils.getStackTrace(e));203 }204 } else{205 System.out.println("寫入的檔案格式錯誤！");206 }207 }208
209
213 public voidcreateSheet(String sheetName){214 if(xssfWorkbook != null){215 sheet=xssfWorkbook.createSheet(sheetName);216 }else if(hssfWorkbook!=null){217 sheet=hssfWorkbook.createSheet(sheetName);218 }219 rows =sheet.getPhysicalNumberOfRows();220 }221
222
223
224 //設定樣式為Excel中指定單元格的樣式
225 public void setStyle(int rowNo, intcolumn) {226 Row row = null;227 Cell cell = null;228 try{229 style=xssfWorkbook.createCellStyle();230 style.setVerticalAlignment( VerticalAlignment.CENTER);231 style.setAlignment(HorizontalAlignment.CENTER);232
233 } catch(Exception e) {234 e.printStackTrace();235 }236 }237
238
247 public void writeFailCell(int rowNo, int column, String value,int size,booleancon,String fontStyle) {248 if(fontStyle==null||"".equals(fontStyle)){249 fontStyle="宋體";250 }251 Row row = null;252 try{253 //擷取指定行
254 row =sheet.getRow(rowNo);255 } catch(Exception e) {256 e.printStackTrace();257 }258 //行不存在，則建立
259 if (row == null) {260 row =sheet.createRow(rowNo);261 }262 //在該行，建立指定列的單元格
263 Cell cell =row.createCell(column);264 //設定單元格值
265 cell.setCellValue(value);266 //設定單元格樣式
267 CellStyle failStyle = null;268 //建立字型樣式
269 Font font = null;270 //根據不同的excel版本進行執行個體化
271 if (hssfWorkbook != null) {272 font =hssfWorkbook.createFont();273 failStyle =hssfWorkbook.createCellStyle();274 } else{275 font =xssfWorkbook.createFont();276 failStyle =xssfWorkbook.createCellStyle();277 }278 failStyle.setVerticalAlignment( VerticalAlignment.CENTER);279 failStyle.setAlignment(HorizontalAlignment.CENTER);280 failStyle.setBorderBottom(BorderStyle.THIN); //下邊框
281 failStyle.setBorderLeft(BorderStyle.THIN);//左邊框
282 failStyle.setBorderTop(BorderStyle.THIN);//上邊框
283 failStyle.setBorderRight(BorderStyle.THIN);//右邊框
284 font.setColor(IndexedColors.BLACK.index);//字型顔色
285 font.setBold(con);286 font.setFontName(fontStyle);287 font.setFontHeightInPoints((short) size);288 //将字型顔色作為單元格樣式
289 failStyle.setFont(font);290 //設定對應單元格樣式
291 cell.setCellStyle(failStyle);292 //單元格文字自适應長度
293 for (int i = 0; i < value.length(); i++) {294 sheet.autoSizeColumn(i);295 sheet.setColumnWidth(i,sheet.getColumnWidth(i)*18/10);296 }297 }298
299
310 public void writeFailCell(int rowNo, int column, String value,int size,booleancon,String fontStyle,VerticalAlignment centerVa ,HorizontalAlignment centerHo) {311 if(fontStyle==null||"".equals(fontStyle)){312 fontStyle="宋體";313 }314 Row row = null;315 try{316 //擷取指定行
317 row =sheet.getRow(rowNo);318 } catch(Exception e) {319 e.printStackTrace();320 }321 //行不存在，則建立
322 if (row == null) {323 row =sheet.createRow(rowNo);324 }325 //在該行，建立指定列的單元格
326 Cell cell =row.createCell(column);327 //設定單元格值
328 cell.setCellValue(value);329 //設定單元格樣式
330 CellStyle failStyle = null;331 //建立字型樣式
332 Font font = null;333 //根據不同的excel版本進行執行個體化
334 if (hssfWorkbook != null) {335 font =hssfWorkbook.createFont();336 failStyle =hssfWorkbook.createCellStyle();337 } else{338 font =xssfWorkbook.createFont();339 failStyle =xssfWorkbook.createCellStyle();340 }341 failStyle.setVerticalAlignment(centerVa);342 failStyle.setAlignment(centerHo);343 failStyle.setBorderBottom(BorderStyle.THIN); //下邊框
344 failStyle.setBorderLeft(BorderStyle.THIN);//左邊框
345 failStyle.setBorderTop(BorderStyle.THIN);//上邊框
346 failStyle.setBorderRight(BorderStyle.THIN);//右邊框
347 font.setColor(IndexedColors.BLACK.index);//字型顔色
348 font.setBold(con);349 font.setFontName(fontStyle);350 font.setFontHeightInPoints((short) size);351 //将字型顔色作為單元格樣式
352 failStyle.setFont(font);353 //設定對應單元格樣式
354 cell.setCellStyle(failStyle);355 //單元格文字自适應長度
356 for (int i = 0; i < value.length(); i++) {357 sheet.autoSizeColumn(i);358 sheet.setColumnWidth(i,sheet.getColumnWidth(i)*18/10);359 }360 }361
362
368 public void sumMation(int rowNo, int column,intstartLine){369 Row row = null;370 try{371 //擷取指定行
372 row =sheet.getRow(rowNo);373 } catch(Exception e) {374 e.printStackTrace();375 }376 //行不存在，則建立
377 if (row == null) {378 row =sheet.createRow(rowNo);379 }380 //在該行，建立指定列的單元格
381 Cell cell =row.createCell(column);382 String ch1 = CellReference.convertNumToColString(column); //将目前行長度轉成ABC列
383 String ch1Start=ch1+startLine;384 String chiEnd=ch1+rowNo+"";385 //不需要給該指定的單元格指派，寫入上面行，導出時自動會合計
386 String format="SUM("+ch1Start+":"+chiEnd+")";387 cell.setCellFormula(format);388 CellStyle failStyle = null;389 //建立字型樣式
390 Font font = null;391 //根據不同的excel版本進行執行個體化
392 if (hssfWorkbook != null) {393 font =hssfWorkbook.createFont();394 failStyle =hssfWorkbook.createCellStyle();395 } else{396 font =xssfWorkbook.createFont();397 failStyle =xssfWorkbook.createCellStyle();398 }399 failStyle.setVerticalAlignment( VerticalAlignment.CENTER);400 failStyle.setAlignment(HorizontalAlignment.CENTER);401 //設定字型顔色為紅色
402 font.setColor(IndexedColors.BLACK.index);403 font.setBold(true);404 failStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));405 failStyle.setVerticalAlignment( VerticalAlignment.CENTER);406 failStyle.setAlignment(HorizontalAlignment.CENTER);407 failStyle.setBorderBottom(BorderStyle.THIN); //下邊框
408 failStyle.setBorderLeft(BorderStyle.THIN);//左邊框
409 failStyle.setBorderTop(BorderStyle.THIN);//上邊框
410 failStyle.setBorderRight(BorderStyle.THIN);//右邊框411 //将字型顔色作為單元格樣式
412 failStyle.setFont(font);413 //設定對應單元格樣式
414 cell.setCellStyle(failStyle);415 }416
417
418 //寫入一整行的内容
419 public void writeLine(int rowNo, Listlist) {420 Row row = null;421 try{422 //擷取指定行
423 row =sheet.getRow(rowNo);424 } catch(Exception e) {425 e.printStackTrace();426 }427 //行不存在，則建立
428 if (row == null) {429 row =sheet.createRow(rowNo);430 }431 Cell cell = null;432 for (int i = 0; i < list.size(); i++) {433 //在該行，建立指定列的單元格
434 cell =row.createCell(i);435 //設定單元格值
436 cell.setCellValue(list.get(i));437 //設定單元格樣式
438 cell.setCellStyle(style);439 }440 }441
442
449 public void mergeCells(int m,int n,int p,intq){450 CellRangeAddress region = newCellRangeAddress(m, n, p, q);451 sheet.addMergedRegion(region);452
453 RegionUtil.setBorderBottom(1, region, sheet); //下邊框
454 RegionUtil.setBorderLeft(1, region, sheet); //左邊框
455 RegionUtil.setBorderRight(1, region, sheet); //有邊框
456 RegionUtil.setBorderTop(1, region, sheet); //上邊框
457 Row row = null;458 try{459 //擷取指定行
460 row =sheet.getRow(m);461 } catch(Exception e) {462 e.printStackTrace();463 }464 //行不存在，則建立
465 if (row == null) {466 row =sheet.createRow(m);467 }468 //在該行，建立指定列的單元格
469 Cell cell =row.createCell(p);470 CellStyle failStyle = null;471 //建立字型樣式
472 Font font = null;473 //根據不同的excel版本進行執行個體化
474 if (hssfWorkbook != null) {475 font =hssfWorkbook.createFont();476 failStyle =hssfWorkbook.createCellStyle();477 } else{478 font =xssfWorkbook.createFont();479 failStyle =xssfWorkbook.createCellStyle();480 }481 font.setBold(true);482 failStyle.setFont(font);483 cell.setCellStyle(failStyle);484 }485
486
487
490 public voidsave()491 {492 System.out.println("儲存檔案");493 //如果結果表檔案未建立，則不儲存
494 if (path != null) {495 try{496 //基于結果表路徑建立檔案輸出流
497 stream = new FileOutputStream(newFile(path));498 //将結果表的workbook工作簿的内容寫入輸出流中，即寫入檔案
499 if (xssfWorkbook != null) {500 xssfWorkbook.write(stream);501 xssfWorkbook.close();502 } else{503 if (hssfWorkbook != null) {504 hssfWorkbook.write(stream);505 hssfWorkbook.close();506 } else{507 System.out.println("未打開Excel檔案！");508 }509 }510 //公式重新整理
511 sheet.setForceFormulaRecalculation(true);512 //關閉輸出流，完成将記憶體中workbook寫入檔案的過程，儲存檔案。
513 stream.close();514 } catch(Exception e) {515 e.printStackTrace();516 }517 }518 }519
520
525 public void copySheet(intsheetIndex, String sheetName){526 try{527 //基于結果表路徑建立檔案輸出流528
529 //将結果表的workbook工作簿的内容寫入輸出流中，即寫入檔案
530 if (xssfWorkbook != null) {531 sheet=xssfWorkbook.cloneSheet(sheetIndex, sheetName);532
533 } else{534 if (hssfWorkbook != null) {535 HSSFSheet rows =hssfWorkbook.cloneSheet(sheetIndex);536 useSheet(rows.getSheetName());537 updateSheetName(sheetName);538 } else{539 System.out.println("未打開Excel檔案！");540 }541 }542 //公式重新整理
543 sheet.setForceFormulaRecalculation(true);544 } catch(Exception e) {545 e.printStackTrace();546 }547 }548
549 }