技術文章 » 【開源庫推薦】#4 Poi-辦公檔案處理庫

【開源庫推薦】#4 Poi-辦公檔案處理庫

2022-12-08 15:00:31

原文：【開源庫推薦】 #4 Poi-辦公檔案處理庫 - Stars-One的雜貨小窩

github倉庫apache/poi

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

提示：下文程式碼範例有Java和Kotlin形式

PS: 目前不確定此庫能否在Android平臺上使用，但從Github上的搜尋結果來看，應該需要移植

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格式檔案的功能。

目前只研究了讀寫表格的相關操作，至於Word等其他檔案還沒有細緻研究，之後有研究了再補充了..

讀寫Excel

首先，先引入依賴

<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>

<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

poi是隻支援xls格式，而poi-ooxml則可以支援xlsx格式

1.讀取excel檔案資料

上面我們也是知道，由於存在兩種格式，所以我們的寫法上得注意一下

  • XSSFWorkbook 對應xls檔案
  • HSSFWorkbook 對應xlsx檔案

由於XSSFWorkbook和HSSFWorkbook都是實現的Workbook介面，所以可利用到多型的原理來編寫程式碼，達到相容寫法

fun main(args: Array<String>) {

    val file = File("D:\\download\\test.xlsx")
    
    var workbook: Workbook? = null

    if (file.extension.toLowerCase() == "xlsx") {
        //xlsx
        workbook = XSSFWorkbook(file)
    }
    if (file.extension.toLowerCase() == "xls") {
        //xls
        workbook = HSSFWorkbook(FileInputStream(file))
    }

    workbook?.let {
        val sheet = workbook.getSheetAt(0)

        //讀取第一行第一列單元格資料
        val firstColumn = sheet.getRow(0).first()

        //需要判斷一下但單元格的型別
        if (firstColumn.cellType == CellType.STRING) {
            println(firstColumn.stringCellValue)
        }
        workbook.close()
    }
}

PS:就是覺得有些神奇的是，XSSFWorkbook建構函式可以接收File型別的物件引數，而HSSFWorkbook只能接收FileInputSteam

2.建立execl檔案資料

val file = File("D:\\download\\myoutput.xlsx")
val workbook: Workbook = XSSFWorkbook()
//val workbook: Workbook =  HSSFWorkbook()

val sheet = workbook.createSheet("sheet0")
val row = sheet.createRow(0)
val cell = row.createCell(0)
cell.cellType = CellType.STRING
cell.setCellValue("hello world")
//輸出到檔案
workbook.write(FileOutputStream(file))
workbook.close()

效果：

讀寫Word

可以檢視此教學Apache POI Word(docx) 入門範例教學

暫無需求，還沒有細研究

除此之外，可以選擇Word模板然後注入對應的資料進去，推薦使用Poi-tl Documentation

補充-EXCEL常用操作方法

1、得到Excel常用物件

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(0); 
//得到Excel工作表指定行的單元格 
HSSFCell cell = row.getCell(0); 
//得到單元格樣式
cellStyle = cell.getCellStyle();

2、建立Excel常用物件

HSSFWorkbook wb = new HSSFWorkbook(); 
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)0); 
HSSFCell cell = sheet.createCell((short)0); 
cellStyle = wb.createCellStyle(); 
//指定單元格樣式和值
cell.setCellStyle(cellStyle); 
cell.setCellValue(1);

3、sheet相關

//設定sheet名稱
wb.setSheetName(0, "12" + "月合同到期");
//取得sheet的數目 
wb.getNumberOfSheets();
//新建名稱為Output的sheet
HSSFSheet sheet = wb.createSheet("Output"); 
//根據index取得sheet物件
HSSFSheet sheet = wb.getSheetAt(0); 
//選中指定的工作表
sheet.setSelected(true);

4、有效的行,單元格個數

//取得有效的行數
int rowcount = sheet.getLastRowNum(); 
//取得一行的有效單元格個數
row.getLastCellNum();

5、單元格值型別讀寫

//根據單元格不同屬性返回字串數值
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; 
}


//設定單元格為STRING型別
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//設定單元格的值---有過載
cell.setCellValue(value);

POI CellType型別有以下幾種

CellType型別 值
CELL_TYPE_NUMERIC 數值型 0
CELL_TYPE_STRING 字串型 1
CELL_TYPE_FORMULA 公式型 2
CELL_TYPE_BLANK 空值 3
CELL_TYPE_BOOLEAN 布林型 4
CELL_TYPE_ERROR 錯誤 5

使用POI讀取單元格的資料有兩種方式：

  1. 通過setCellType將單元格型別設定為字串，然後通過getRichStringCellValue讀取該單元格資料，然後將讀取到的字串轉換為對應的型別，
  2. 通過getCellType獲取單元格型別，然後通過對應的getcellvalue方法讀取該單元格資料，如:getNumericCellValue

6、設定列寬、行高

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

//POI設定自適應列寬sheet.autoSizeColumn(i); （版本不能太老）
sheet.autoSizeColumn(i, true);（合併的單元格使用）
sheet.setColumnWidth(i, 「列名」.getBytes().length*2*256);（中文適用）
公式單元格自適應的是公式，將值算出後再設定：
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
CellValue cell71Val = evaluator.evaluate(cell71);
cell71.setCellValue(cell71Val.getNumberValue());

//調整單元格寬度 
sheet.setAutobreaks(true); 
sheet.setColumnWidth((short)i,colsWidth[i]); //設定單元格長度 
sheet.autoSizeColumn((short) i);//自動根據長度調整單元格長度

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

//合併從第rowFrom行columnFrom列 
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo ,(short)columnTo);
sheet.addMergedRegion(region);// 到rowTo行columnTo的區域 
// 獲得一個 sheet 中合併單元格的數量
int sheetmergerCount = sheet.getNumMergedRegions();

8、常用單元格邊框格式

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. 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

9、設定字型和內容位置

HSSFFont font = wb.createFont();

font.setFontName("華文行楷");//設定字型名稱
font.setFontHeightInPoints((short) 11);                    //字號 
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗 
font.setColor(HSSFColor.RED.index);               //設定字型顏色
font.setUnderline(FontFormatting.U_SINGLE);  //設定下劃線
font.setTypeOffset(FontFormatting.SS_SUPER);//設定上標下標
font.setStrikeout(true);                                   //設定刪除線
style.setFont(font); 

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 
style.setRotation(short rotation);//單元格內容的旋轉的角度 
Style.setWrapText(true); //設定excel單元格中的內容換行
HSSFDataFormat df = wb.createDataFormat(); 
style1.setDataFormat(df.getFormat("0.00%"));//設定單元格資料格式 
cell.setCellStyle(style);

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

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);

11、填充和顏色設定

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);

12、工作表的放大縮小

sheet.setZoom(1,2); // 50 percent magnification 
sheet.setZoom(75); //75% scale

13. 使用公式

cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C2:C3)" ); //給單元格設公式

14. 設定超連結

cell.setCellFormula("hyperlink(\"http://www.yiibai.com/testng/\",\"testng\")");

15、插入圖片

public static void test1(){
    
    FileOutputStream fileOut = null;     
    BufferedImage bufferImg = null;     
   try {  
       ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  
     //載入圖片  
       bufferImg = ImageIO.read(new File(picture));     
       ImageIO.write(bufferImg, "jpg", byteArrayOut);  
       HSSFWorkbook wb = new HSSFWorkbook();     
       HSSFSheet sheet = wb.createSheet("sheet1");    
       
       HSSFPatriarch patriarch = sheet.createDrawingPatriarch();     
          HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)4,37);    
       //插入圖片 1   
       patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));      
       //圖片2  
       anchor = new HSSFClientAnchor(500, 0, 0, 0,(short) 5, 1, (short) 9, 38);  
       patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));  
       fileOut = new FileOutputStream( filepath+"/excel.xls");     
       // 輸出檔案   
        wb.write(fileOut);  
        System.out.println("test1");
   } catch (Exception e) {  
       e.printStackTrace();  
   }          
}  


public static void test3(){
    
    FileOutputStream fileOut = null;     
    BufferedImage bufferImg = null;     
   try {  
       ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  
     //載入圖片  
       bufferImg = ImageIO.read(new File(picture));     
       ImageIO.write(bufferImg, "jpg", byteArrayOut);  
       XSSFWorkbook wb = new XSSFWorkbook();     
       XSSFSheet sheet1 = wb.createSheet("sheet1");    
       XSSFDrawing drawing = sheet1.createDrawingPatriarch();     
          XSSFClientAnchor anchor = new XSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);          
       //插入圖片 1   
          drawing.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));      
       fileOut = new FileOutputStream( filepath+ "/excel2.xlsx");     
       // 輸出檔案   
        wb.write(fileOut);  
        System.out.println("test3");
   } catch (Exception e) {  
       e.printStackTrace();  
   }          
}

16. 從Excel檔案提取圖片

public static void testread(){
    InputStream inp;
    try {
        inp = new FileInputStream(filepath+"/excel.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(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("jpeg"))//判斷檔案格式,依照實際圖片格式設定
            {
                FileOutputStream png=new FileOutputStream(filepath +"\\Apache.jpg");
                png.write(pic.getData());
                png.close();//儲存圖片
                System.out.println("test11");
            }
        }
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

參考