Apache POI使用詳解

閱讀目錄

• 開始
• 1.POI結構與常用類
• 2.Excel的基本操作
• 3.Excel的單元格操作
• 4.使用Excel公式
• 5.使用圖形
• 6.Excel表操作
• 7.Excel行列操作
• 8.Excel的其他功能
• 9.POI對Word的基本操作

回到頂部

1.POI結構與常用類

(1)POI介紹

    Apache POI是Apache軟體基金會的開源專案，POI提供API給Java程式對Microsoft Office格式檔案讀和寫的功能。 .NET的開發人員則可以利用NPOI (POI for .NET) 來存取 Microsoft Office文件的功能。

(2)POI結構說明

包名稱 說明

HSSF 提供讀寫Microsoft Excel XLS格式檔案的功能。

XSSF 提供讀寫Microsoft Excel OOXML XLSX格式檔案的功能。

HWPF 提供讀寫Microsoft Word DOC格式檔案的功能。

HSLF 提供讀寫Microsoft PowerPoint格式檔案的功能。

HDGF 提供讀Microsoft Visio格式檔案的功能。

HPBF 提供讀Microsoft Publisher格式檔案的功能。

HSMF 提供讀Microsoft Outlook格式檔案的功能。

(3)POI常用類說明

類名                說明

HSSFWorkbook        Excel的文件物件

HSSFSheet     Excel的表單
HSSFRow             Excel的行
HSSFCell     Excel的格子單元
HSSFFont            Excel字型

HSSFDataFormat      格子單元的日期格式

HSSFHeader          Excel文件Sheet的頁首

HSSFFooter          Excel文件Sheet的頁尾

HSSFCellStyle       格子單元樣式

HSSFDateUtil        日期

HSSFPrintSetup      列印

HSSFErrorConstants  錯誤資訊表

回到頂部

2.Excel的基本操作

(1)建立Workbook和Sheet

publicclassTest00
{
publicstaticvoid main(String[] args)throwsIOException
{
String filePath="d:\\users\\lizw\\桌面\\POI\\sample.xls";//檔案路徑
HSSFWorkbook workbook =newHSSFWorkbook();//建立Excel檔案(Workbook)
HSSFSheet sheet = workbook.createSheet();//建立工作表(Sheet)
		sheet = workbook.createSheet("Test");//建立工作表(Sheet)
FileOutputStream out =newFileOutputStream(filePath);
		workbook.write(out);//儲存Excel檔案
		out.close();//關閉檔案流
System.out.println("OK!");
}
}

(2)建立單元格

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);// 建立行,從0開始
HSSFCell cell = row.createCell(0);// 建立行的單元格,也是從0開始
cell.setCellValue("李志偉");// 設定單元格內容
row.createCell(1).setCellValue(false);// 設定單元格內容,過載
row.createCell(2).setCellValue(newDate());// 設定單元格內容,過載
row.createCell(3).setCellValue(12.345);// 設定單元格內容,過載

(3)建立文件摘要資訊

workbook.createInformationProperties();//建立文件資訊
DocumentSummaryInformation dsi= workbook.getDocumentSummaryInformation();//摘要資訊
dsi.setCategory("類別:Excel檔案");//類別
dsi.setManager("管理者:李志偉");//管理者
dsi.setCompany("公司:--");//公司
SummaryInformation si = workbook.getSummaryInformation();//摘要資訊
si.setSubject("主題:--");//主題
si.setTitle("標題:測試文件");//標題
si.setAuthor("作者:李志偉");//作者
si.setComments("備註:POI測試文件");//備註

(4)建立批註

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = patr.createAnchor(0,0,0,0,5,1,8,3);//建立批註位置
HSSFComment comment = patr.createCellComment(anchor);//建立批註
comment.setString(newHSSFRichTextString("這是一個批註段落！"));//設定批註內容
comment.setAuthor("李志偉");//設定批註作者
comment.setVisible(true);//設定批註預設顯示
HSSFCell cell = sheet.createRow(2).createCell(1);
cell.setCellValue("測試");
cell.setCellComment(comment);//把批註賦值給單元格

    建立批註位置HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法引數說明：

dx1         第1個單元格中x軸的偏移量
dy1         第1個單元格中y軸的偏移量
dx2         第2個單元格中x軸的偏移量
dy2         第2個單元格中y軸的偏移量
col1        第1個單元格的列號
row1        第1個單元格的行號
col2        第2個單元格的列號
row2        第2個單元格的行號

(5)建立頁首和頁尾

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFHeader header =sheet.getHeader();//得到頁首
header.setLeft("頁首左邊");
header.setRight("頁首右邊");
header.setCenter("頁首中間");
HSSFFooter footer =sheet.getFooter();//得到頁尾
footer.setLeft("頁尾左邊");
footer.setRight("頁尾右邊");
footer.setCenter("頁尾中間");

    也可以使用Office自帶的標籤定義，你可以通過HSSFHeader或HSSFFooter訪問到它們，都是靜態屬性，列表如下：

HSSFHeader.tab                  &A	表名
HSSFHeader.file                 &F	檔名
HSSFHeader.startBold            &B	粗體開始
HSSFHeader.endBold              &B	粗體結束
HSSFHeader.startUnderline       &U	下劃線開始
HSSFHeader.endUnderline         &U	下劃線結束
HSSFHeader.startDoubleUnderline &E	雙下劃線開始
HSSFHeader.endDoubleUnderline   &E	雙下劃線結束
HSSFHeader.time                 &T	時間
HSSFHeader.date                 &D	日期
HSSFHeader.numPages             &N	總頁面數
HSSFHeader.page                 &P	當前頁號

回到頂部

3.Excel的單元格操作

(1)設定格式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row=sheet.createRow(0);
//設定日期格式--使用Excel內嵌的格式
HSSFCell cell=row.createCell(0);
cell.setCellValue(newDate());
HSSFCellStyle style=workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell.setCellStyle(style);
//設定保留2位小數--使用Excel內嵌的格式
cell=row.createCell(1);
cell.setCellValue(12.3456789);
style=workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cell.setCellStyle(style);
//設定貨幣格式--使用自定義的格式
cell=row.createCell(2);
cell.setCellValue(12345.6789);
style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("￥#,##0"));
cell.setCellStyle(style);
//設定百分比格式--使用自定義的格式
cell=row.createCell(3);
cell.setCellValue(0.123456789);
style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
cell.setCellStyle(style);
//設定中文大寫格式--使用自定義的格式
cell=row.createCell(4);
cell.setCellValue(12345);
style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("[DbNum2][$-804]0"));
cell.setCellStyle(style);
//設定科學計數法格式--使用自定義的格式
cell=row.createCell(5);
cell.setCellValue(12345);
style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00"));
cell.setCellStyle(style);

    HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的區別：當使用Excel內嵌的（或者說預定義）的格式時，直接用HSSFDataFormat.getBuiltinFormat靜態方法即可。當使用自己定義的格式時，必須先呼叫HSSFWorkbook.createDataFormat()，因為這時在底層會先找有沒有匹配的內嵌FormatRecord，如果沒有就會新建一個FormatRecord，所以必須先呼叫這個方法，然後你就可以用獲得的HSSFDataFormat例項的getFormat方法了，當然相對而言這種方式比較麻煩，所以內嵌格式還是用HSSFDataFormat.getBuiltinFormat靜態方法更加直接一些。

(2)合併單元格

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row=sheet.createRow(0);
//合併列
HSSFCell cell=row.createCell(0);
cell.setCellValue("合併列");
CellRangeAddress region=newCellRangeAddress(0,0,0,5);
sheet.addMergedRegion(region);
//合併行
cell=row.createCell(6);
cell.setCellValue("合併行");
region=newCellRangeAddress(0,5,6,6);
sheet.addMergedRegion(region);

    CellRangeAddress物件其實就是表示一個區域，其構造方法如下：CellRangeAddress(firstRow, lastRow, firstCol, lastCol)，引數的說明：

firstRow        區域中第一個單元格的行號
lastRow         區域中最後一個單元格的行號
firstCol        區域中第一個單元格的列號
lastCol         區域中最後一個單元格的列號

    提示：即使你沒有用CreateRow和CreateCell建立過行或單元格，也完全可以直接建立區域然後把這一區域合併，Excel的區域合併資訊是單獨儲存的，和RowRecord、ColumnInfoRecord不存在直接關係。

(3)單元格對齊

HSSFCell cell=row.createCell(0);
cell.setCellValue("單元格對齊");
HSSFCellStyle style=workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setWrapText(true);//自動換行
style.setIndention((short)5);//縮排
style.setRotation((short)60);//文字旋轉，這裡的取值是從-90到90，而不是0-180度。
cell.setCellStyle(style);

    水平對齊相關引數

如果是左側對齊就是HSSFCellStyle.ALIGN_FILL;
如果是居中對齊就是HSSFCellStyle.ALIGN_CENTER;
如果是右側對齊就是HSSFCellStyle.ALIGN_RIGHT;
如果是跨列舉中就是HSSFCellStyle.ALIGN_CENTER_SELECTION;
如果是兩端對齊就是HSSFCellStyle.ALIGN_JUSTIFY;
如果是填充就是HSSFCellStyle.ALIGN_FILL;

    垂直對齊相關引數

如果是靠上就是HSSFCellStyle.VERTICAL_TOP;
如果是居中就是HSSFCellStyle.VERTICAL_CENTER;
如果是靠下就是HSSFCellStyle.VERTICAL_BOTTOM;
如果是兩端對齊就是HSSFCellStyle.VERTICAL_JUSTIFY;

(4)使用邊框

    邊框和其他單元格設定一樣也是呼叫CellStyle介面，CellStyle有2種和邊框相關的屬性，分別是:

邊框相關屬性	說明	範例
Border+方向	邊框型別	BorderLeft, BorderRight等
方向+BorderColor	邊框顏色	TopBorderColor,BottomBorderColor等

HSSFCell cell=row.createCell(1);
cell.setCellValue("設定邊框");
HSSFCellStyle style=workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上邊框
style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下邊框
style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左邊框
style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右邊框
style.setTopBorderColor(HSSFColor.RED.index);//上邊框顏色
style.setBottomBorderColor(HSSFColor.BLUE.index);//下邊框顏色
style.setLeftBorderColor(HSSFColor.GREEN.index);//左邊框顏色
style.setRightBorderColor(HSSFColor.PINK.index);//右邊框顏色
cell.setCellStyle(style);

 

    其中邊框型別分為以下幾種：

邊框範例圖	對應的靜態值
	HSSFCellStyle.BORDER_DOTTED
	HSSFCellStyle.BORDER_HAIR
	HSSFCellStyle.BORDER_DASH_DOT_DOT
	HSSFCellStyle.BORDER_DASH_DOT
	HSSFCellStyle.BORDER_DASHED
	HSSFCellStyle.BORDER_THIN
	HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT
	HSSFCellStyle.BORDER_SLANTED_DASH_DOT
	HSSFCellStyle.BORDER_MEDIUM_DASH_DOT
	HSSFCellStyle.BORDER_MEDIUM_DASHED
	HSSFCellStyle.BORDER_MEDIUM
	HSSFCellStyle.BORDER_THICK
	HSSFCellStyle.BORDER_DOUBLE

(5)設定字型

HSSFCell cell = row.createCell(1);
cell.setCellValue("設定字型");
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("華文行楷");//設定字型名稱
font.setFontHeightInPoints((short)28);//設定字號
font.setColor(HSSFColor.RED.index);//設定字型顏色
font.setUnderline(FontFormatting.U_SINGLE);//設定下劃線
font.setTypeOffset(FontFormatting.SS_SUPER);//設定上標下標
font.setStrikeout(true);//設定刪除線
style.setFont(font);
cell.setCellStyle(style);

    下劃線選項值：

        單下劃線 FontFormatting.U_SINGLE

        雙下劃線 FontFormatting.U_DOUBLE

        會計用單下劃線 FontFormatting.U_SINGLE_ACCOUNTING

        會計用雙下劃線 FontFormatting.U_DOUBLE_ACCOUNTING

無下劃線 FontFormatting.U_NONE

    上標下標選項值：

        上標 FontFormatting.SS_SUPER

        下標 FontFormatting.SS_SUB

        普通，預設值 FontFormatting.SS_NONE

(6)背景和紋理

HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREEN.index);//設定圖案顏色
style.setFillBackgroundColor(HSSFColor.RED.index);//設定圖案背景色
style.setFillPattern(HSSFCellStyle.SQUARES);//設定圖案樣式
cell.setCellStyle(style);

 

    圖案樣式及其對應的值：

圖案樣式	常量
	HSSFCellStyle.NO_FILL
	HSSFCellStyle.ALT_BARS
	HSSFCellStyle.FINE_DOTS
	HSSFCellStyle.SPARSE_DOTS
	HSSFCellStyle.LESS_DOTS
	HSSFCellStyle.LEAST_DOTS
	HSSFCellStyle.BRICKS
	HSSFCellStyle.BIG_SPOTS
	HSSFCellStyle.THICK_FORWARD_DIAG
	HSSFCellStyle.THICK_BACKWARD_DIAG
	HSSFCellStyle.THICK_VERT_BANDS
	HSSFCellStyle.THICK_HORZ_BANDS
	HSSFCellStyle.THIN_HORZ_BANDS
	HSSFCellStyle.THIN_VERT_BANDS
	HSSFCellStyle.THIN_BACKWARD_DIAG
	HSSFCellStyle.THIN_FORWARD_DIAG
	HSSFCellStyle.SQUARES
	HSSFCellStyle.DIAMONDS

(7)設定寬度和高度

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("123456789012345678901234567890");
sheet.setColumnWidth(1,31*256);//設定第一列的寬度是31個字元寬度
row.setHeightInPoints(50);//設定行的高度是50個點

    這裡你會發現一個有趣的現象，setColumnWidth的第二個引數要乘以256，這是怎麼回事呢？其實，這個引數的單位是1/256個字元寬度，也就是說，這裡是把B列的寬度設定為了31個字元。

    設定行高使用HSSFRow物件的setHeight和setHeightInPoints方法，這兩個方法的區別在於setHeightInPoints的單位是點，而setHeight的單位是1/20個點，所以setHeight的值永遠是setHeightInPoints的20倍。

    你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法設定預設的列寬或行高。

(8)判斷單元格是否為日期

    判斷單元格是否為日期型別，使用DateUtil.isCellDateFormatted(cell)方法，例如：

HSSFCell cell = row.createCell(1);
cell.setCellValue(newDate());//設定日期資料
System.out.println(DateUtil.isCellDateFormatted(cell));//輸出：false
HSSFCellStyle style =workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell.setCellStyle(style);//設定日期樣式
System.out.println(DateUtil.isCellDateFormatted(cell));//輸出：true

回到頂部

4.使用Excel公式

(1)基本計算

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellFormula("2+3*4");//設定公式
cell = row.createCell(1);
cell.setCellValue(10);
cell = row.createCell(2);
cell.setCellFormula("A1*B1");//設定公式

(2)SUM函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(1);
row.createCell(1).setCellValue(2);
row.createCell(2).setCellValue(3);
row.createCell(3).setCellValue(4);
row.createCell(4).setCellValue(5);
row = sheet.createRow(1);
row.createCell(0).setCellFormula("sum(A1,C1)");//等價於"A1+C1"
row.createCell(1).setCellFormula("sum(B1:D1)");//等價於"B1+C1+D1"

(3)日期函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFCellStyle style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
HSSFRow row = sheet.createRow(0);
Calendar date=Calendar.getInstance();//日曆物件
HSSFCell cell=row.createCell(0);
date.set(2011,2,7);
cell.setCellValue(date.getTime());
cell.setCellStyle(style);//第一個單元格開始時間設定完成
cell=row.createCell(1);
date.set(2014,4,25);
cell.setCellValue(date.getTime());
cell.setCellStyle(style);//第一個單元格結束時間設定完成
cell=row.createCell(3);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")");
cell=row.createCell(4);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")");
cell=row.createCell(5);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"d\"),\"日\")");

    以上程式碼中的公式說明：
        DATEDIF(A1,B1,\"y\")：取得A1單元格的日期與B1單元格的日期的時間間隔。(“y”:表示以年為單位,”m”表示以月為單位;”d”表示以天為單位)。

        CONCATENATE(str1,str2,…)：連線字串。

        更多Excel的日期函式可參考：http://tonyqus.sinaapp.com/archives/286

(4)字串相關函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("abcdefg");
row.createCell(1).setCellValue("aa bb cc dd ee fF GG");
row.createCell(3).setCellFormula("UPPER(A1)");
row.createCell(4).setCellFormula("PROPER(B1)");

    以上程式碼中的公式說明：
        UPPER(String)：將文字轉換成大寫形式。

        PROPER(String)：將文字串的首字母及任何非字母字元之後的首字母轉換成大寫。將其餘的字母轉換成小寫。

        更多Excel的字串函式可參考：http://tonyqus.sinaapp.com/archives/289

(5)IF函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(12);
row.createCell(1).setCellValue(23);
row.createCell(3).setCellFormula("IF(A1>B1,\"A1大於B1\",\"A1小於等於B1\")");

    以上程式碼中的公式說明：

        IF(logical_test,value_if_true,value_if_false)用來用作邏輯判斷。其中Logical_test表示計算結果為 TRUE 或 FALSE 的任意值或表示式 ; value_if_true表示當表示式Logical_test的值為TRUE時的返回值；value_if_false表示當表示式Logical_test的值為FALSE時的返回值。

(6)CountIf和SumIf函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(57);
row.createCell(1).setCellValue(89);
row.createCell(2).setCellValue(56);
row.createCell(3).setCellValue(67);
row.createCell(4).setCellValue(60);
row.createCell(5).setCellValue(73);
row.createCell(7).setCellFormula("COUNTIF(A1:F1,\">=60\")");
row.createCell(8).setCellFormula("SUMIF(A1:F1,\">=60\",A1:F1)");

 

    以上程式碼中的公式說明：

        COUNTIF(range,criteria)：滿足某條件的計數的函式。引數range：需要進行讀數的計數；引數criteria：條件表示式，只有當滿足此條件時才進行計數。

        SumIF(criteria_range, criteria,sum_range)：用於統計某區域內滿足某條件的值的求和。引數criteria_range：條件測試區域，第二個引數Criteria中的條件將與此區域中的值進行比較；引數criteria：條件測試值，滿足條件的對應的sum_range項將進行求和計算；引數sum_range：彙總資料所在區域，求和時會排除掉不滿足Criteria條件的對應的項。

(7)Lookup函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(0);
row.createCell(1).setCellValue(59);
row.createCell(2).setCellValue("不及格");
row = sheet.createRow(1);
row.createCell(0).setCellValue(60);
row.createCell(1).setCellValue(69);
row.createCell(2).setCellValue("及格");
row = sheet.createRow(2);
row.createCell(0).setCellValue(70);
row.createCell(1).setCellValue(79);
row.createCell(2).setCellValue("良好");
row = sheet.createRow(3);
row.createCell(0).setCellValue(80);
row.createCell(1).setCellValue(100);
row.createCell(2).setCellValue("優秀");
row = sheet.createRow(4);
row.createCell(0).setCellValue(75);
row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)");
row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");

    以上程式碼中的公式說明：
        LOOKUP(lookup_value,lookup_vector,result_vector)，第一個引數：需要查詢的內容，本例中指向A5單元格，也就是75；第二個引數：比較物件區域，本例中的成績需要與$A$1:$A$4中的各單元格中的值進行比較；第三個引數：查詢結果區域，如果匹配到會將此區域中對應的資料返回。如本例中返回$C$1:$C$4中對應的值。

可能有人會問，字典中沒有75對應的成績啊，那麼Excel中怎麼匹配的呢？答案是模糊匹配，並且LOOKUP函式只支援模糊匹配。Excel會在$A$1:$A$4中找小於75的最大值，也就是A3對應的70，然後將對應的$C$1:$C$4區域中的C3中的值返回，這就是最終結果“良好”的由來。
        VLOOKUP(lookup_value,lookup_area,result_col,is_fuzzy )，第一個引數：需要查詢的內容，這裡是A5單元格；第二個引數：需要比較的表，這裡是$A$1:$C$4，注意VLOOKUP匹配時只與表中的第一列進行匹配。第三個引數：匹配結果對應的列序號。這裡要對應的是成績列，所以為3。第四個引數：指明是否模糊匹配。例子中的TRUE表示模糊匹配，與上例中一樣。匹配到的是第三行。如果將此引數改為FALSE，因為在表中的第1列中找不到75，所以會報“#N/A”的計算錯誤。

另外，還有與VLOKUP類似的HLOOKUP。不同的是VLOOKUP用於在表格或數值陣列的首列查詢指定的數值，並由此返回表格或陣列當前行中指定列處的數值。而HLOOKUP用於在表格或數值陣列的首行查詢指定的數值，並由此返回表格或陣列當前列中指定行處的數值。讀者可以自已去嘗試。

(8)隨機數函式

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellFormula("RAND()");//取0-1之間的隨機數
row.createCell(1).setCellFormula("int(RAND()*100)");//取0-100之間的隨機整數
row.createCell(2).setCellFormula("rand()*10+10");//取10-20之間的隨機實數
row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");//隨機小寫字母
row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");//隨機大寫字母
//隨機大小寫字母
row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");

    以上程式碼中的公式說明：

        上面幾例中除了用到RAND函式以外，還用到了CHAR函式用來將ASCII碼換為字母，INT函式用來取整。值得注意的是INT函式不會四捨五入，無論小數點後是多少都會被捨去。

(9)獲得公式的返回值

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(7);//A1
row.createCell(1).setCellValue(8);//B1
HSSFCell cell=row.createCell(2);
cell.setCellFormula("A1*B1+14");
HSSFFormulaEvaluator e =newHSSFFormulaEvaluator(workbook);
cell = e.evaluateInCell(cell);//若Excel檔案不是POI建立的，則不必呼叫此方法
System.out.println("公式計算結果："+cell.getNumericCellValue());

回到頂部

5.使用圖形

(1)畫線

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
HSSFClientAnchor anchor =newHSSFClientAnchor(0,0,0,0,(short)1,0,(short)4,4);
HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//設定圖形型別
line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//設定圖形樣式
line.setLineWidth(6350);//在POI中線的寬度12700表示1pt,所以這裡是0.5pt粗的線條。

    通常，利用POI畫圖主要有以下幾個步驟：

        1. 建立一個Patriarch（注意，一個sheet中通常只建立一個Patriarch物件）；

        2. 建立一個Anchor，以確定圖形的位置；

        3. 呼叫Patriarch建立圖形；

        4. 設定圖形型別(直線，矩形，圓形等)及樣式（顏色，粗細等）。

    關於HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的引數，有必要在這裡說明一下：

        dx1：起始單元格的x偏移量，如例子中的0表示直線起始位置距B1單元格左側的距離；

        dy1：起始單元格的y偏移量，如例子中的0表示直線起始位置距B1單元格上側的距離；

        dx2：終止單元格的x偏移量，如例子中的0表示直線起始位置距E5單元格左側的距離；

        dy2：終止單元格的y偏移量，如例子中的0表示直線起始位置距E5單元格上側的距離；

        col1：起始單元格列序號，從0開始計算；

        row1：起始單元格行序號，從0開始計算，如例子中col1=1,row1=0就表示起始單元格為B1；

        col2：終止單元格列序號，從0開始計算；

        row2：終止單元格行序號，從0開始計算，如例子中col2=4,row2=4就表示起始單元格為E5；

    最後，關於LineStyle屬性，有如下一些可選值，對應的效果分別如圖所示：

(2)畫矩形

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
HSSFClientAnchor anchor =newHSSFClientAnchor(255,122,255,122,(short)1,0,(short)4,3);
HSSFSimpleShape rec = patriarch.createSimpleShape(anchor);
rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
rec.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//設定邊框樣式
rec.setFillColor(255,0,0);//設定填充色
rec.setLineWidth(25400);//設定邊框寬度
rec.setLineStyleColor(0,0,255);//設定邊框顏色

(3)畫圓形

    更改上例的程式碼如下：

        rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//設定圖片型別

 

(4)畫Grid

    在POI中，本身沒有畫Grid(網格)的方法。但我們知道Grid其實就是由橫線和豎線構成的，所在我們可以通過畫線的方式來模擬畫Grid。程式碼如下：

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
HSSFRow row = sheet.createRow(2);
row.createCell(1);
row.setHeightInPoints(240);
sheet.setColumnWidth(2,9000);
int linesCount =20;
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//因為HSSFClientAnchor中dx只能在0-1023之間,dy只能在0-255之間，這裡採用比例的方式
double xRatio =1023.0/(linesCount *10);
double yRatio =255.0/(linesCount *10);
// 畫豎線
int x1 =0;
int y1 =0;
int x2 =0;
int y2 =200;
for(int i =0; i < linesCount; i++)
{
HSSFClientAnchor a2 =newHSSFClientAnchor();
    a2.setAnchor((short)2,2,(int)(x1 * xRatio),
(int)(y1 * yRatio),(short)2,2,(int)(x2 * xRatio),
(int)(y2 * yRatio));
HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
    shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    x1 +=10;
    x2 +=10;
}
// 畫橫線
x1 =0;
y1 =0;
x2 =200;
y2 =0;
for(int i =0; i < linesCount; i++)
{
HSSFClientAnchor a2 =newHSSFClientAnchor();
    a2.setAnchor((short)2,2,(int)(x1 * xRatio),
(int)(y1 * yRatio),(short)2,2,(int)(x2 * xRatio),
(int)(y2 * yRatio));
HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
    shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    y1 +=10;
    y2 +=10;
}

 

(5)插入圖片

HSSFSheet sheet = workbook.createSheet("Test");// 建立工作表(Sheet)
FileInputStream stream=newFileInputStream("d:\\POI\\Apache.gif");
byte[] bytes=newbyte[(int)stream.getChannel().size()];
stream.read(bytes);//讀取圖片到二進位制陣列
int pictureIdx = workbook.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor =newHSSFClientAnchor(0,0,0,0,(short)0,0,(short)5,5);
HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx);
//pict.resize();//自動調節圖片大小,圖片位置資訊可能丟失

(6)從Excel檔案提取圖片

InputStream inp =newFileInputStream(filePath);
HSSFWorkbook workbook =newHSSFWorkbook(inp);//讀取現有的Excel檔案
List<HSSFPictureData> pictures = workbook.getAllPictures();
for(int i=0;i<pictures.size();i++)
{
HSSFPictureData pic=pictures.get(i);
String ext = pic.suggestFileExtension();
if(ext.equals("png"))//判斷檔案格式
{
FileOutputStream png=newFileOutputStream("d:\\POI\\Apache.png");
        png.write(pic.getData());
        png.close();//儲存圖片
}
}

回到頂部

6.Excel表操作

(1)設定預設工作表

HSSFWorkbook workbook =newHSSFWorkbook();// 建立Excel檔案(Workbook)
workbook.createSheet("Test0");// 建立工作表(Sheet)
workbook.createSheet("Test1");// 建立工作表(Sheet)
workbook.createSheet("Test2");// 建立工作表(Sheet)
workbook.createSheet("Test3");// 建立工作表(Sheet)
workbook.setActiveSheet(2);//設定預設工作表

(2)重新命名工作表

HSSFWorkbook workbook =newHSSFWorkbook();// 建立Excel檔案(Workbook)
workbook.createSheet("Test0");// 建立工作表(Sheet)
workbook.createSheet("Test1");// 建立工作表(Sheet)
workbook.createSheet("Test2");// 建立工作表(Sheet)
workbook.createSheet("Test3");// 建立工作表(Sheet)
workbook.setSheetName(2,"1234");//重新命名工作表

(3)調整表單顯示比例

HSSFWorkbook workbook =newHSSFWorkbook();// 建立Excel檔案(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 建立工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 建立工作表(Sheet)
HSSFSheet sheet3=workbook.createSheet("Test2");// 建立工作表(Sheet)
sheet1.setZoom(1,2);//50%顯示比例
sheet2.setZoom(2,1);//200%顯示比例
sheet3.setZoom(1,10);//10%顯示比例

(4)顯示/隱藏網格線

HSSFWorkbook workbook =newHSSFWorkbook();// 建立Excel檔案(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 建立工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 建立工作表(Sheet)
sheet1.setDisplayGridlines(false);//隱藏Excel網格線,預設值為true
sheet2.setGridsPrinted(true);//列印時顯示網格線,預設值為false

(5)遍歷Sheet

String filePath ="d:\\users\\lizw\\桌面\\POI\\sample.xls";
FileInputStream stream =newFileInputStream(filePath);
HSSFWorkbook workbook =newHSSFWorkbook(stream);//讀取現有的Excel
HSSFSheet sheet= workbook.getSheet("Test0");//得到指定名稱的Sheet
for(Row row : sheet)
{
for(Cell cell : row)
{
System.out.print(cell +"\t");
}
System.out.println();
}

回到頂部

7.Excel行列操作

(1)組合行、列

HSSFSheet sheet= workbook.createSheet("Test0");// 建立工作表(Sheet)
sheet.groupRow(1,3);//組合行
sheet.groupRow(2,4);//組合行
sheet.groupColumn(2,7);//組合列

    這裡簡單的介紹一下什麼叫做組合：組合分為行組合和列組合，所謂行組合，就是讓n行組合成一個集合，能夠進行展開和合攏操作。

    使用POI也可以取消組合，例如：sheet.ungroupColumn(1, 3);//取消列組合

(2)鎖定列

    在Excel中，有時可能會出現列數太多或是行數太多的情況，這時可以通過鎖定列來凍結部分列，不隨滾動條滑動，方便檢視。

HSSFSheet sheet= workbook.createSheet("Test0");// 建立工作表(Sheet)
sheet.createFreezePane(2,3,15,25);//凍結行列

    下面對CreateFreezePane的引數作一下說明：

        第一個參數列示要凍結的列數；

        第二個參數列示要凍結的行數，這裡只凍結列所以為0；

        第三個參數列示右邊區域可見的首列序號，從1開始計算；

        第四個參數列示下邊區域可見的首行序號，也是從1開始計算，這裡是凍結列，所以為0；

(3)上下移動行

FileInputStream stream =newFileInputStream(filePath);
HSSFWorkbook workbook =newHSSFWorkbook(stream);
HSSFSheet sheet = workbook.getSheet("Test0");
sheet.shiftRows(2,4,2);//把第3行到第4行向下移動兩行

    HSSFSheet.shiftRows(startRow, endRow, n)引數說明

        startRow：需要移動的起始行；

        endRow：需要移動的結束行；

        n：移動的位置，正數表示向下移動，負數表示向上移動；

回到頂部

8.Excel的其他功能

(1)設定密碼

HSSFSheet sheet= workbook.createSheet("Test0");// 建立工作表(Sheet)
HSSFRow row=sheet.createRow(1);
HSSFCell cell=row.createCell(1);
cell.setCellValue("已鎖定");
HSSFCellStyle locked = workbook.createCellStyle();
locked.setLocked(true);//設定鎖定
cell.setCellStyle(locked);
cell=row.createCell(2);
cell.setCellValue("未鎖定");
HSSFCellStyle unlocked = workbook.createCellStyle();
unlocked.setLocked(false);//設定不鎖定
cell.setCellStyle(unlocked);
sheet.protectSheet("password");//設定保護密碼

(2)資料有效性

HSSFSheet sheet= workbook.createSheet("Test0");// 建立工作表(Sheet)
HSSFRow row=sheet.createRow(0);
HSSFCell cell=row.createCell(0);
cell.setCellValue("日期列");
CellRangeAddressList regions =newCellRangeAddressList(1,65535,0,0);//選定一個區域
DVConstraint constraint =DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN,"1993-01-01","2014-12-31","yyyy-MM-dd");
HSSFDataValidation dataValidate =newHSSFDataValidation(regions, constraint);
dataValidate.createErrorBox("錯誤","你必須輸入一個時間！");
sheet.addValidationData(dataValidate);

    CellRangeAddressList類表示一個區域，建構函式中的四個引數分別表示起始行序號，終止行序號，起始列序號，終止列序號。65535是一個Sheet的最大行數。另外，CreateDateConstraint的第一個引數除了設定成DVConstraint.OperatorType.BETWEEN外，還可以設定成如下一些值，大家可以自己一個個去試看看效果：

    驗證的資料型別也有幾種選擇，如下：

(3)生成下拉式選單

CellRangeAddressList regions =newCellRangeAddressList(0,65535,0,0);
DVConstraint constraint =DVConstraint.createExplicitListConstraint(newString[]{"C++","Java","C#"});
HSSFDataValidation dataValidate =newHSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidate);

(4)列印基本設定

HSSFSheet sheet= workbook.createSheet("Test0");// 建立工作表(Sheet)
HSSFPrintSetup print = sheet.getPrintSetup();//得到列印物件
print.setLandscape(false);//true，則表示頁面方向為橫向；否則為縱向
print.setScale((short)80);//縮放比例80%(設定為0-100之間的值)
print.setFitWidth((short)2);//設定頁寬
print.setFitHeight((short)4);//設定頁高
print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//紙張設定
print.setUsePage(true);//設定列印起始頁碼不使用"自動"
print.setPageStart((short)6);//設定列印起始頁碼
sheet.setPrintGridlines(true);//設定列印網格線
print.setNoColor(true);//值為true時，表示單色列印
print.setDraft(true);//值為true時，表示用草稿品質列印
print.setLeftToRight(true);//true表示“先行後列”；false表示“先列後行”
print.setNotes(true);//設定列印批註
sheet.setAutobreaks(false);//Sheet頁自適應頁面大小

    更詳細的列印設定請參考：http://tonyqus.sinaapp.com/archives/271

(5)超連結

HSSFSheet sheet = workbook.createSheet("Test0");
CreationHelper createHelper = workbook.getCreationHelper();
// 關聯到網站
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://poi.apache.org/");
sheet.createRow(0).createCell(0).setHyperlink(link);
// 關聯到當前目錄的檔案
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("sample.xls");
sheet.createRow(0).createCell(1).setHyperlink(link);
// e-mail 關聯
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
sheet.createRow(0).createCell(2).setHyperlink(link);
//關聯到工作簿中的位置 
link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link.setAddress("'Test0'!C3");//Sheet名為Test0的C3位置
sheet.createRow(0).createCell(3).setHyperlink(link);

回到頂部

9.POI對Word的基本操作

(1)POI操作Word簡介

    POI讀寫Excel功能強大、操作簡單。但是POI操作時，一般只用它讀取word文件，POI只能能夠建立簡單的word文件，相對而言POI操作時的功能太少。

(2)POI建立Word文件的簡單示例

XWPFDocument doc =newXWPFDocument();// 建立Word檔案
XWPFParagraph p = doc.createParagraph();// 新建一個段落
p.setAlignment(ParagraphAlignment.CENTER);// 設定段落的對齊方式
p.setBorderBottom(Borders.DOUBLE);//設定下邊框
p.setBorderTop(Borders.DOUBLE);//設定上邊框
p.setBorderRight(Borders.DOUBLE);//設定右邊框
p.setBorderLeft(Borders.DOUBLE);//設定左邊框
XWPFRun r = p.createRun();//建立段落文字
r.setText("POI建立的Word段落文字");
r.setBold(true);//設定為粗體
r.setColor("FF0000");//設定顏色
p = doc.createParagraph();// 新建一個段落
r = p.createRun();
r.setText("POI讀寫Excel功能強大、操作簡單。");
XWPFTable table= doc.createTable(3,3);//建立一個表格
table.getRow(0).getCell(0).setText("表格1");
table.getRow(1).getCell(1).setText("表格2");
table.getRow(2).getCell(2).setText("表格3");
FileOutputStream out =newFileOutputStream("d:\\POI\\sample.doc");
doc.write(out);
out.close();

(3)POI讀取Word文件裡的文字

FileInputStream stream =newFileInputStream("d:\\POI\\sample.doc");
XWPFDocument doc =newXWPFDocument(stream);// 建立Word檔案
for(XWPFParagraph p : doc.getParagraphs())//遍歷段落
{
System.out.print(p.getParagraphText());
}
for(XWPFTable table : doc.getTables())//遍歷表格
{
for(XWPFTableRow row : table.getRows())
{
for(XWPFTableCell cell : row.getTableCells())
{
System.out.print(cell.getText());
}
}
}