POI操作Excel常用方法總結(jié)

2019-11-11 04:52:54

字體：大中小

供稿：網(wǎng)友

一、 POI簡介

Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給java程序?qū)icrosoft Office格式檔案讀和寫的功能。

二、 HSSF概況

HSSF 是Horrible SPReadSheet Format的縮寫，通過HSSF，你可以用純Java代碼來讀取、寫入、修改Excel文件。HSSF 為讀取操作提供了兩類API：usermodel和eventusermodel，即“用戶模型”和“事件-用戶模型”。

三、 POI EXCEL文檔結(jié)構(gòu)類

HSSFWorkbook excel文檔對象

HSSFSheet excel的sheet HSSFRow excel的行

HSSFCell excel的單元格 HSSFFont excel字體

HSSFName 名稱 HSSFDataFormat 日期格式

HSSFHeader sheet頭

HSSFFooter sheet尾

HSSFCellStyle cell樣式

HSSFDateUtil 日期

HSSFPrintSetup 打印

HSSFErrorConstants 錯(cuò)誤信息表

四、 EXCEL常用操作方法

1、得到Excel常用對象

[c-sharp] view plaincopyPOIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); //得到Excel工作簿對象 HSSFWorkbook wb = new HSSFWorkbook(fs); //得到Excel工作表對象 HSSFSheet sheet = wb.getSheetAt(0); //得到Excel工作表的行 HSSFRow row = sheet.getRow(i); //得到Excel工作表指定行的單元格 HSSFCell cell = row.getCell((short) j); cellStyle = cell.getCellStyle();//得到單元格樣式

2、建立Excel常用對象[c-sharp] view plaincopyHSSFWorkbook wb = new HSSFWorkbook();//創(chuàng)建Excel工作簿對象 HSSFSheet sheet = wb.createSheet("new sheet");//創(chuàng)建Excel工作表對象 HSSFRow row = sheet.createRow((short)0); //創(chuàng)建Excel工作表的行 cellStyle = wb.createCellStyle();//創(chuàng)建單元格樣式 row.createCell((short)0).setCellStyle(cellStyle); //創(chuàng)建Excel工作表指定行的單元格 row.createCell((short)0).setCellValue(1); //設(shè)置Excel工作表的值

3、設(shè)置sheet名稱和單元格內(nèi)容[c-sharp] view plaincopywb.setSheetName(1, "第一張工作表",HSSFCell.ENCODING_UTF_16); cell.setEncoding((short) 1); cell.setCellValue("單元格內(nèi)容");

4、取得sheet的數(shù)目 [c-sharp] view plaincopywb.getNumberOfSheets()

5、根據(jù)index取得sheet對象[c-sharp] view plaincopyHSSFSheet sheet = wb.getSheetAt(0);

6、取得有效的行數(shù)[c-sharp] view plaincopyint rowcount = sheet.getLastRowNum();

7、取得一行的有效單元格個(gè)數(shù)[c-sharp] view plaincopyrow.getLastCellNum();

8、單元格值類型讀寫

[c-sharp] view plaincopycell.setCellType(HSSFCell.CELL_TYPE_STRING); //設(shè)置單元格為STRING類型 cell.getNumericCellValue();//讀取為數(shù)值類型的單元格內(nèi)容

9、設(shè)置列寬、行高

[c-sharp] view plaincopysheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height);

10、添加區(qū)域，合并單元格

[c-sharp] view plaincopyRegion 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 plaincopyFileOutputStream fileOut = new FileOutputStream(path); wb.write(fileOut);

12、根據(jù)單元格不同屬性返回字符串?dāng)?shù)值

[c-sharp] view plaincopypublic 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 plaincopyHSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下邊框 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左邊框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框

14、設(shè)置字體和內(nèi)容位置

[c-sharp] view plaincopyHSSFFont 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è)置單元格數(shù)據(jù)格式 cell.setCellFormula(string);//給單元格設(shè)公式 style.setRotation(short rotation);//單元格內(nèi)容的旋轉(zhuǎn)的角度

15、插入圖片

[c-sharp] view plaincopy//先把讀進(jìn)來的圖片放到一個(gè)ByteArrayOutputStream中，以便產(chǎn)生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); ImageIO.write(bufferImg,"jpg",byteArrayOut); //讀進(jìn)一個(gè)excel模版 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt"); fs = new POIFSFileSystem(fos); //創(chuàng)建一個(gè)工作薄 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 plaincopyHSSFWorkbook 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、設(shè)置打印區(qū)域

[c-sharp] view plaincopyHSSFSheet sheet = wb.createSheet("Sheet1"); wb.setPrintArea(0, "$A$1:$C$2");

18、標(biāo)注腳注

[c-sharp] view plaincopyHSSFSheet 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 plaincopyHSSFWorkbook 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 plaincopyHSSFSheet sheet = wb.createSheet("row sheet"); heet.setSelected(true);

21、工作表的放大縮小

[c-sharp] view plaincopyHSSFSheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(1,2); // 50 percent magnification

22、頭注和腳注

[c-sharp] view plaincopyHSSFSheet 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 plaincopyHSSFCellStyle 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、填充和顏色設(shè)置

[c-sharp] view plaincopyHSSFCellStyle 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、強(qiáng)行刷新單元格公式

[c-sharp] view plaincopyHSSFFormulaEvaluator 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)方法，計(jì)算公式保存結(jié)果，但不改變公式。而evaluateInCell(Cell cell) 方法是計(jì)算公式，并將原公式替換為計(jì)算結(jié)果，也就是說該單元格的類型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了靜態(tài)方法evaluateAllFormu

laCells(HSSFWorkbook wb) ，計(jì)算一個(gè)Excel文件的所有公式，用起來很方便

上一篇：莫隊(duì)算法——解決序列上詢問的利器

下一篇：Opengl學(xué)習(xí)程序之uniform