Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給java程序?qū)icrosoft Office格式檔案讀和寫的功能。
HSSF 是Horrible SPReadSheet Format的縮寫，通過HSSF，你可以用純Java代碼來讀取、寫入、修改Excel文件。HSSF 為讀取操作提供了兩類API：usermodel和eventusermodel，即“用戶模型”和“事件-用戶模型”。
HSSFWorkbook excel文檔對象
HSSFSheet excel的sheet HSSFRow excel的行
HSSFCell excel的單元格 HSSFFont excel字體
HSSFName 名稱 HSSFDataFormat 日期格式
HSSFHeader sheet頭
HSSFFooter sheet尾
HSSFCellStyle cell樣式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 錯誤信息表
8、單元格值類型讀寫
[c-sharp] view plaincopyprint?cell.setCellType(HSSFCell.CELL_TYPE_STRING); //設置單元格為STRING類型 cell.getNumericCellValue();//讀取為數(shù)值類型的單元格內(nèi)容
9、設置列寬、行高
[c-sharp] view plaincopyprint?sheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height);
10、添加區(qū)域，合并單元格
[c-sharp] view plaincopyprint?Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo ,(short)columnTo);//合并從第rowFrom行columnFrom列 sheet.addMergedRegion(region);// 到rowTo行columnTo的區(qū)域 //得到所有區(qū)域 sheet.getNumMergedRegions()
11、保存Excel文件
[c-sharp] view plaincopyprint?FileOutputStream fileOut = new FileOutputStream(path); wb.write(fileOut);
12、根據(jù)單元格不同屬性返回字符串數(shù)值
[c-sharp] view plaincopyprint?public String getCellStringValue(HSSFCell cell) { String cellValue = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING://字符串類型 cellValue = cell.getStringCellValue(); if(cellValue.trim().equals("")||cellValue.trim().length()<=0) cellValue=" "; break; case HSSFCell.CELL_TYPE_NUMERIC: //數(shù)值類型 cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: //公式 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue=" "; break; case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; default: break; } return cellValue; }
13、常用單元格邊框格式
[c-sharp] view plaincopyprint?HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下邊框 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左邊框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框
14、設置字體和內(nèi)容位置
[c-sharp] view plaincopyprint?HSSFFont f = wb.createFont(); f.setFontHeightInPoints((short) 11);//字號 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style.setFont(f); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 style.setRotation(short rotation);//單元格內(nèi)容的旋轉(zhuǎn)的角度 HSSFDataFormat df = wb.createDataFormat(); style1.setDataFormat(df.getFormat("0.00%"));//設置單元格數(shù)據(jù)格式 cell.setCellFormula(string);//給單元格設公式 style.setRotation(short rotation);//單元格內(nèi)容的旋轉(zhuǎn)的角度
15、插入圖片
[c-sharp] view plaincopyprint?//先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產(chǎn)生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); ImageIO.write(bufferImg,"jpg",byteArrayOut); //讀進一個excel模版 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt"); fs = new POIFSFileSystem(fos); //創(chuàng)建一個工作薄 HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、調(diào)整工作表位置
[c-sharp] view plaincopyprint?HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFPrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1); ps.setFitWidth((short)1);
17、設置打印區(qū)域
[c-sharp] view plaincopyprint?HSSFSheet sheet = wb.createSheet("Sheet1"); wb.setPrintArea(0, "$A$1:$C$2");
18、標注腳注
[c-sharp] view plaincopyprint?HSSFSheet sheet = wb.createSheet("format sheet"); HSSFFooter footer = sheet.getFooter() footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
19、在工作單中清空行數(shù)據(jù)，調(diào)整行位置
[c-sharp] view plaincopyprint?HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("row sheet"); // Create various cells and rows for spreadsheet. // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) sheet.shiftRows(5, 10, -5);
20、選中指定的工作表
[c-sharp] view plaincopyprint?HSSFSheet sheet = wb.createSheet("row sheet"); heet.setSelected(true);
21、工作表的放大縮小
[c-sharp] view plaincopyprint?HSSFSheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(1,2); // 50 percent magnification
22、頭注和腳注
[c-sharp] view plaincopyprint?HSSFSheet sheet = wb.createSheet("new sheet"); HSSFHeader header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
23、自定義顏色
[c-sharp] view plaincopyprint?HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style);24、填充和顏色設置
[c-sharp] view plaincopyprint?HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.AQUA.index); style.setFillPattern(HSSFCellStyle.BIG_SPOTS); HSSFCell cell = row.createCell((short) 1); cell.setCellValue("X"); style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.ORANGE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cell.setCellStyle(style);25、強行刷新單元格公式
[c-sharp] view plaincopyprint?HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); private static void updateFormula(Workbook wb,Sheet s,int row){ Row r=s.getRow(row); Cell c=null; FormulaEcaluator eval=null; if(wb instanceof HSSFWorkbook) eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb); else if(wb instanceof XSSFWorkbook) eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb); for(int i=r.getFirstCellNum();i<r.getLastCellNum();i++){ c=r.getCell(i); if(c.getCellType()==Cell.CELL_TYPE_FORMULA) eval.evaluateFormulaCell(c); } }說明：FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法，計算公式保存結果，但不改變公式。而evaluateInCell(Cell cell) 方法是計算公式，并將原公式替換為計算結果，也就是說該單元格的類型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了靜態(tài)方法evaluateAllFormu
laCells(HSSFWorkbook wb) ，計算一個Excel文件的所有公式，用起來很方便。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------poi方法總結
-.設置不顯示excel網(wǎng)格線 sheet.setDisplayGridlines(false);其中sheet是Sheet對象 2.設置excel單元格中的內(nèi)容換行 cellStyle.setWrapText(true);其中cellStyle是WorkBook創(chuàng)建的CellStyle對象,然后將cellStyle設置到要換行的Cell對象,最后在要換行的對象(一般為字符串)加入"/r/n"。如 topTile.append("/r/n" +"cellContent"); 3.單元格的合并 sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));本示例為合并4行2列 4.設置頁眉和頁腳的頁數(shù) HSSFHeader header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); HSSFFooter footer = (HSSFFooter )sheet.getFooter() footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() ); 5.使得一個Sheet適合一頁 sheet.setAutobreaks(true); 6.設置放大屬性(Zoom被明確為一個分數(shù)，例如下面的75%使用3作為分子，4作為分母) sheet.setZoom(3,4); 7.設置打印 HSSFPrintSetup print = (HSSFPrintSetup) sheet.getPrintSetup(); print.setLandscape(true);//設置橫向打印 print.setScale((short) 70);//設置打印縮放70% print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//設置為A4紙張 print.setLeftToRight(true);//設置打印順序先行后列,默認為先列行 print.setFitHeight((short) 10);設置縮放調(diào)整為10頁高 print.setFitWidth((short) 10);設置縮放調(diào)整為寬高 sheet.setAutobreaks(false); if (i != 0 && i % 30 == 0) sheet.setRowBreak(i);//設置每30行分頁打印 8.反復的行和列（設置打印標題） HSSFWorkbook wb = new HSSFWorkbook(); wb.setRepeatingRowsAndColumns(0, 0, 12, 1, 6);//設置1到12列,行1到6每一頁重復打印 9.調(diào)整單元格寬度 sheet.setAutobreaks(true); sheet.setColumnWidth((short)i,colsWidth[i]); //設定單元格長度 sheet.autoSizeColumn((short) i);//自動根據(jù)長度調(diào)整單元格長度
新聞熱點
疑難解答
