POI匯入Excel中文API文件

William-Wang發表於2018-03-02
ExcelAPI

 一、 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常用物件           

  1. POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));   
  2. //得到Excel工作簿物件    
  3. HSSFWorkbook wb = new HSSFWorkbook(fs);  
  4. //得到Excel工作表物件    
  5. HSSFSheet sheet = wb.getSheetAt(0);   
  6. //得到Excel工作表的行    
  7. HSSFRow row = sheet.getRow(i);  
  8. //得到Excel工作表指定行的單元格    
  9. HSSFCell cell = row.getCell((short) j);  
  10. cellStyle = cell.getCellStyle();//得到單元格樣式  

 2、建立Excel常用物件

  1. HSSFWorkbook wb = new HSSFWorkbook();//建立Excel工作簿物件   
  2. HSSFSheet sheet = wb.createSheet("new sheet");//建立Excel工作表物件     
  3. HSSFRow row = sheet.createRow((short)0); //建立Excel工作表的行   
  4. cellStyle = wb.createCellStyle();//建立單元格樣式   
  5. row.createCell((short)0).setCellStyle(cellStyle); //建立Excel工作表指定行的單元格   
  6. row.createCell((short)0).setCellValue(1); //設定Excel工作表的值  

3、設定sheet名稱和單元格內容
  1. wb.setSheetName(1, "第一張工作表",HSSFCell.ENCODING_UTF_16);          
  2. cell.setEncoding((short) 1);      
  3. cell.setCellValue("單元格內容");  

4、取得sheet的數目 
  1. wb.getNumberOfSheets()   

5、  根據index取得sheet物件
  1. HSSFSheet sheet = wb.getSheetAt(0);  

6、取得有效的行數
  1. int rowcount = sheet.getLastRowNum();  

7、取得一行的有效單元格個數
  1. row.getLastCellNum();    

8、單元格值型別讀寫

 

  1. cell.setCellType(HSSFCell.CELL_TYPE_STRING); //設定單元格為STRING型別   
  2. cell.getNumericCellValue();//讀取為數值型別的單元格內容  

 

9、設定列寬、行高

 

  1. sheet.setColumnWidth((short)column,(short)width);      
  2. row.setHeight((short)height);    

 

10、新增區域，合併單元格

 

  1. Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo  
  2. ,(short)columnTo);//合併從第rowFrom行columnFrom列   
  3. sheet.addMergedRegion(region);// 到rowTo行columnTo的區域      
  4. //得到所有區域       
  5. sheet.getNumMergedRegions()   

 

11、儲存Excel檔案

 

  1. FileOutputStream fileOut = new FileOutputStream(path);   
  2. wb.write(fileOut);   

 

 

12、根據單元格不同屬性返回字串數值

 

  1. public String getCellStringValue(HSSFCell cell) {      
  2.         String cellValue = "";      
  3.         switch (cell.getCellType()) {      
  4.         case HSSFCell.CELL_TYPE_STRING://字串型別   
  5.             cellValue = cell.getStringCellValue();      
  6.             if(cellValue.trim().equals("")||cellValue.trim().length()<=0)      
  7.                 cellValue=" ";      
  8.             break;      
  9.         case HSSFCell.CELL_TYPE_NUMERIC: //數值型別   
  10.             cellValue = String.valueOf(cell.getNumericCellValue());      
  11.             break;      
  12.         case HSSFCell.CELL_TYPE_FORMULA: //公式   
  13.             cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);      
  14.             cellValue = String.valueOf(cell.getNumericCellValue());      
  15.             break;      
  16.         case HSSFCell.CELL_TYPE_BLANK:      
  17.             cellValue=" ";      
  18.             break;      
  19.         case HSSFCell.CELL_TYPE_BOOLEAN:      
  20.             break;      
  21.         case HSSFCell.CELL_TYPE_ERROR:      
  22.             break;      
  23.         default:      
  24.             break;      
  25.         }      
  26.         return cellValue;      
  27.     }     

 

13、常用單元格邊框格式

 

  1. HSSFCellStyle style = wb.createCellStyle();      
  2. style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下邊框        
  3. style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左邊框        
  4. style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框        
  5. style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框    

 

 

14、設定字型和內容位置

 

  1. HSSFFont f  = wb.createFont();      
  2. f.setFontHeightInPoints((short) 11);//字號       
  3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗       
  4. style.setFont(f);      
  5. style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中       
  6. style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中       
  7. style.setRotation(short rotation);//單元格內容的旋轉的角度       
  8. HSSFDataFormat df = wb.createDataFormat();      
  9. style1.setDataFormat(df.getFormat("0.00%"));//設定單元格資料格式       
  10. cell.setCellFormula(string);//給單元格設公式       
  11. style.setRotation(short rotation);//單元格內容的旋轉的角度   

 

15、插入圖片

 

  1. //先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產生ByteArray       
  2.       ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();      
  3.       BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));      
  4.       ImageIO.write(bufferImg,"jpg",byteArrayOut);      
  5. //讀進一個excel模版       
  6. FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");       
  7. fs = new POIFSFileSystem(fos);      
  8. //建立一個工作薄       
  9. HSSFWorkbook wb = new HSSFWorkbook(fs);      
  10. HSSFSheet sheet = wb.getSheetAt(0);      
  11. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();      
  12. HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);           
  13. patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));    

 

16、調整工作表位置

 

  1. HSSFWorkbook wb = new HSSFWorkbook();     
  2. HSSFSheet sheet = wb.createSheet("format sheet");     
  3. HSSFPrintSetup ps = sheet.getPrintSetup();     
  4. sheet.setAutobreaks(true);     
  5. ps.setFitHeight((short)1);     
  6. ps.setFitWidth((short)1);   

 

17、設定列印區域

 

  1. HSSFSheet sheet = wb.createSheet("Sheet1");     
  2. wb.setPrintArea(0, "$A$1:$C$2");    

 

18、標註腳註

 

  1. HSSFSheet sheet = wb.createSheet("format sheet");     
  2. HSSFFooter footer = sheet.getFooter()     
  3. footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );   

 

19、在工作單中清空行資料，調整行位置

 

  1. HSSFWorkbook wb = new HSSFWorkbook();     
  2. HSSFSheet sheet = wb.createSheet("row sheet");     
  3. // Create various cells and rows for spreadsheet.      
  4. // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)      
  5. sheet.shiftRows(5, 10, -5);    

 

20、選中指定的工作表

 

  1. HSSFSheet sheet = wb.createSheet("row sheet");     
  2. heet.setSelected(true);     

 

21、工作表的放大縮小

 

  1. HSSFSheet sheet1 = wb.createSheet("new sheet");     
  2. sheet1.setZoom(1,2);   // 50 percent magnification    

 

22、頭注和腳註

 

  1. HSSFSheet sheet = wb.createSheet("new sheet");     
  2. HSSFHeader header = sheet.getHeader();     
  3. header.setCenter("Center Header");     
  4. header.setLeft("Left Header");     
  5. header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +     
  6. HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");  

 

23、自定義顏色

 

  1. HSSFCellStyle style = wb.createCellStyle();     
  2. style.setFillForegroundColor(HSSFColor.LIME.index);     
  3. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);     
  4. HSSFFont font = wb.createFont();     
  5. font.setColor(HSSFColor.RED.index);     
  6. style.setFont(font);     
  7. cell.setCellStyle(style);     

 

24、填充和顏色設定

 

  1. HSSFCellStyle style = wb.createCellStyle();     
  2. style.setFillBackgroundColor(HSSFColor.AQUA.index);     
  3. style.setFillPattern(HSSFCellStyle.BIG_SPOTS);     
  4. HSSFCell cell = row.createCell((short) 1);     
  5. cell.setCellValue("X");     
  6. style = wb.createCellStyle();     
  7. style.setFillForegroundColor(HSSFColor.ORANGE.index);     
  8. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);     
  9. cell.setCellStyle(style);   

25、強行重新整理單元格公式

 

  1. HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);    
  2. private static void updateFormula(Workbook wb,Sheet s,int row){     
  3.         Row r=s.getRow(row);     
  4.         Cell c=null;     
  5.         FormulaEcaluator eval=null;     
  6.         if(wb instanceof HSSFWorkbook)     
  7.             eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);     
  8.         else if(wb instanceof XSSFWorkbook)     
  9.             eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);     
  10.         for(int i=r.getFirstCellNum();i
  11.             c=r.getCell(i);     
  12.             if(c.getCellType()==Cell.CELL_TYPE_FORMULA)     
  13.                 eval.evaluateFormulaCell(c);     
  14.         }     
  15.     }    

說明：FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法，計算公式儲存結果，但不改變公式。而evaluateInCell(Cell cell) 方法是計算公式，並將原公式替換為計算結果，也就是說該單元格的型別不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了靜態方法evaluateAllFormu

laCells(HSSFWorkbook wb) ，計算一個Excel檔案的所有公式，用起來很方便。 

-------------------------------------------poi 方法總結-------------------------------------------------------------------

-.設定不顯示excel網格線 
  sheet.setDisplayGridlines(false);其中sheet是Sheet物件 
2.設定excel單元格中的內容換行 
  cellStyle.setWrapText(true);其中cellStyle是WorkBook建立的CellStyle物件,然後將cellStyle設定到要換行的Cell物件,最後在要換行的物件(一般為字串)加入"/r/n"。如 
topTile.append("/r/n" +"cellContent"); 

3.單元格的合併 
  sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));本示例為合併4行2列 

4.設定頁首和頁尾的頁數 
    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被明確為一個分數，例如下面的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);設定縮放調整為10頁高 
  print.setFitWidth((short) 10);設定縮放調整為寬高 

  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.調整單元格寬度 
　　sheet.setAutobreaks(true); 
　　sheet.setColumnWidth((short)i,colsWidth[i]); //設定單元格長度 
    sheet.autoSizeColumn((short) i);//自動根據長度調整單元格長度 

 原博：http://blog.sina.com.cn/s/blog_91c0fdb50101kfd4.html

相關文章