POI操作Excel常用方法总结 .
2019獨角獸企業重金招聘Python工程師標準>>>
一、 POI簡介?
??????????? Apache POI是Apache軟件基金會的開放源碼函式庫，POI提供API給Java程序對Microsoft Office格式檔案讀和寫的功能。?
二、 HSSF概況?
??????????? HSSF 是Horrible SpreadSheet Format的縮寫，通過HSSF，你可以用純Java代碼來讀取、寫入、修改Excel文件。HSSF 為讀取操作提供了兩類API：usermodel和eventusermodel，即“用戶模型”和“事件-用戶模型”。
三、 POI EXCEL文檔結構類?
??????????? HSSFWorkbook excel文檔對象?
??????????? HSSFSheet excel的sheet HSSFRow excel的行?
??????????? HSSFCell excel的單元格 HSSFFont excel字體?
??????????? HSSFName 名稱 HSSFDataFormat 日期格式?
??????????? HSSFHeader sheet頭?
??????????? HSSFFooter sheet尾?
??????????? HSSFCellStyle cell樣式?
??????????? HSSFDateUtil 日期?
??????????? HSSFPrintSetup 打印?
??????????? HSSFErrorConstants 錯誤信息表?
四、 EXCEL常用操作方法?
1、 得到Excel常用對象????????????
Java代碼??
POIFSFileSystem?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();//得到單元格樣式????
POIFSFileSystem?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常用對象?
Java代碼??
HSSFWorkbook?wb?=?new?HSSFWorkbook();//創建Excel工作簿對象?????
HSSFSheet?sheet?=?wb.createSheet("new?sheet");//創建Excel工作表對象???????
HSSFRow?row?=?sheet.createRow((short)0);?//創建Excel工作表的行?????
cellStyle?=?wb.createCellStyle();//創建單元格樣式?????
row.createCell((short)0).setCellStyle(cellStyle);?//創建Excel工作表指定行的單元格?????
row.createCell((short)0).setCellValue(1);?//設置Excel工作表的值????
HSSFWorkbook?wb?=?new?HSSFWorkbook();//創建Excel工作簿對象??
HSSFSheet?sheet?=?wb.createSheet("new?sheet");//創建Excel工作表對象????
HSSFRow?row?=?sheet.createRow((short)0);?//創建Excel工作表的行??
cellStyle?=?wb.createCellStyle();//創建單元格樣式??
row.createCell((short)0).setCellStyle(cellStyle);?//創建Excel工作表指定行的單元格??
row.createCell((short)0).setCellValue(1);?//設置Excel工作表的值??
3、設置sheet名稱和單元格內容?
Java代碼??
wb.setSheetName(1,?"第一張工作表",HSSFCell.ENCODING_UTF_16);????????????
cell.setEncoding((short)?1);????????
cell.setCellValue("單元格內容");????
wb.setSheetName(1,?"第一張工作表",HSSFCell.ENCODING_UTF_16);??????????
cell.setEncoding((short)?1);??????
cell.setCellValue("單元格內容");???
4、取得sheet的數目?
Java代碼??
wb.getNumberOfSheets()?????
wb.getNumberOfSheets()???
5、? 根據index取得sheet對象?
Java代碼??
HSSFSheet?sheet?=?wb.getSheetAt(0);????
HSSFSheet?sheet?=?wb.getSheetAt(0);??
6、取得有效的行數?
Java代碼??
int?rowcount?=?sheet.getLastRowNum();????
int?rowcount?=?sheet.getLastRowNum();??
7、取得一行的有效單元格個數?
Java代碼??
row.getLastCellNum();??????
row.getLastCellNum();???
??
8、單元格值類型讀寫?
Java代碼??
cell.setCellType(HSSFCell.CELL_TYPE_STRING);?//設置單元格為STRING類型?????
cell.getNumericCellValue();//讀取為數值類型的單元格內容????
cell.setCellType(HSSFCell.CELL_TYPE_STRING);?//設置單元格為STRING類型??
cell.getNumericCellValue();//讀取為數值類型的單元格內容??
9、設置列寬、行高?
Java代碼??
sheet.setColumnWidth((short)column,(short)width);????????
row.setHeight((short)height);??????
sheet.setColumnWidth((short)column,(short)width);??????
row.setHeight((short)height);???
10、添加區域，合并單元格?
Java代碼??
Region?region?=?new?Region((short)rowFrom,(short)columnFrom,(short)rowTo????
,(short)columnTo);//合并從第rowFrom行columnFrom列?????
sheet.addMergedRegion(region);//?到rowTo行columnTo的區域????????
//得到所有區域?????????
sheet.getNumMergedRegions()?????
Region?region?=?new?Region((short)rowFrom,(short)columnFrom,(short)rowTo??
,(short)columnTo);//合并從第rowFrom行columnFrom列??
sheet.addMergedRegion(region);//?到rowTo行columnTo的區域?????
//得到所有區域??????
sheet.getNumMergedRegions()???
11、保存Excel文件?
Java代碼??
FileOutputStream?fileOut?=?new?FileOutputStream(path);?????
wb.write(fileOut);?????
FileOutputStream?fileOut?=?new?FileOutputStream(path);???
wb.write(fileOut);???
12、根據單元格不同屬性返回字符串數值?
Java代碼??
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:?//數值類型?????
????????????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;????????
????}???????
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:?//數值類型??
????????????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、常用單元格邊框格式?
Java代碼??
HSSFCellStyle?style?=?wb.createCellStyle();????????
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下邊框??????????
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左邊框??????????
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框??????????
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框??????
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、設置字體和內容位置?
Java代碼??
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);//單元格內容的旋轉的角度?????????
HSSFDataFormat?df?=?wb.createDataFormat();????????
style1.setDataFormat(df.getFormat("0.00%"));//設置單元格數據格式?????????
cell.setCellFormula(string);//給單元格設公式?????????
style.setRotation(short?rotation);//單元格內容的旋轉的角度?????
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);//單元格內容的旋轉的角度??????
HSSFDataFormat?df?=?wb.createDataFormat();??????
style1.setDataFormat(df.getFormat("0.00%"));//設置單元格數據格式??????
cell.setCellFormula(string);//給單元格設公式??????
style.setRotation(short?rotation);//單元格內容的旋轉的角度???
15、插入圖片?
Java代碼??
//先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產生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);????????
//創建一個工作薄?????????
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));??????
//先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產生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);??????
//創建一個工作薄??????
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、調整工作表位置?
Java代碼??
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);?????
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、設置打印區域?
Java代碼??
HSSFSheet?sheet?=?wb.createSheet("Sheet1");???????
wb.setPrintArea(0,?"$A$1:$C$2");??????
HSSFSheet?sheet?=?wb.createSheet("Sheet1");?????
wb.setPrintArea(0,?"$A$1:$C$2");????
18、標注腳注?
Java代碼??
HSSFSheet?sheet?=?wb.createSheet("format?sheet");???????
HSSFFooter?footer?=?sheet.getFooter()???????
footer.setRight(?"Page?"?+?HSSFFooter.page()?+?"?of?"?+?HSSFFooter.numPages()?);?????
HSSFSheet?sheet?=?wb.createSheet("format?sheet");?????
HSSFFooter?footer?=?sheet.getFooter()?????
footer.setRight(?"Page?"?+?HSSFFooter.page()?+?"?of?"?+?HSSFFooter.numPages()?);???
19、在工作單中清空行數據，調整行位置?
Java代碼??
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);??????
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、選中指定的工作表?
Java代碼??
HSSFSheet?sheet?=?wb.createSheet("row?sheet");???????
heet.setSelected(true);???????
HSSFSheet?sheet?=?wb.createSheet("row?sheet");?????
heet.setSelected(true);?????
21、工作表的放大縮小?
Java代碼??
HSSFSheet?sheet1?=?wb.createSheet("new?sheet");???????
sheet1.setZoom(1,2);???//?50?percent?magnification??????
HSSFSheet?sheet1?=?wb.createSheet("new?sheet");?????
sheet1.setZoom(1,2);???//?50?percent?magnification???
22、頭注和腳注?
Java代碼??
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");????
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、自定義顏色?
Java代碼??
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);???????
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、填充和顏色設置?
Java代碼??
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);?????
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、強行刷新單元格公式?
Java代碼??
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);???????
????????}???????
????}??????
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提供了靜態方法evaluateAllFormu?
laCells(HSSFWorkbook wb) ，計算一個Excel文件的所有公式，用起來很方便。?
轉載于:https://my.oschina.net/u/1472917/blog/472371
總結
以上是生活随笔為你收集整理的POI操作Excel常用方法总结 .的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： XenApp_XenDesktop_7.
- 下一篇： 如何成为一个合格的 Java程序员