java檔案操作之-poi操作excel

第一步：引入依賴包

<!-- tools -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.7</version>
</dependency>

<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>

<!--poi-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>3.12</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.12</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.12</version>
</dependency>
           

第二步：上代碼

基礎變量的準備

public static String first_file_Path="";
public static String second_file_Path="";
public static String previous_excel_path="";
public static String final_excel_path= "";
public static final String first_sheet_name = "";
public static final String second_sheet_name = "";
           

準備讀寫的檔案

File file_template = new File(previous_excel_path);
File file_final = new File(final_excel_path);
FileUtils.copyFile(file_template, file_final);
           

FileInputStream把檔案轉換成檔案輸入流

FileInputStream fileInputStream = new FileInputStream(file_template);
           

通過檔案輸入流建立Workbook

OPCPackage opcPackage= OPCPackage.open(fileInputStream);
Workbook workbook = WorkbookFactory.create(opcPackage);
           

特殊處理

String[] firstFileStr=FileUtils.readFileToString(new File(first_file_Path),"utf-8").split("\n");
String[] secondFileStr =FileUtils.readFileToString(new File(second_file_Path),"utf-8").split("\n");
           

通過Workbook擷取對應的sheet

Sheet sheet1 = workbook.getSheet(first_sheet_name);
Sheet sheet2 = workbook.getSheet(second_sheet_name);
           

sheet裡面寫入值

/sheet1
for(int i=0;i<configYamls.length;i++){
    Row row=sheet1.getRow(i+1);
    if(row==null){
       row=sheet1.createRow(i+1);
    }
    Cell c = row.getCell(0);
    if(c == null){
       c = row.createCell(0);
    }
    c.setCellType(Cell.CELL_TYPE_STRING);
    c.setCellValue(configYamls[i]);
}
//sheet2
for(int i=0;i<inputTemplates.length;i++){
    Row row=sheet2.getRow(i+1);
    if(row==null){
       row=sheet2.createRow(i+1);
    }
    Cell c = row.getCell(0);
    if(c == null){
        c = row.createCell(0);
    }
    c.setCellType(Cell.CELL_TYPE_STRING);
    c.setCellValue(inputTemplates[i]);
}
           

輸出流

ByteArrayOutputStream workbook_os = new ByteArrayOutputStream();
           

寫入到輸出流

workbook.write(workbook_os);
           

FileOutputStream寫入檔案

FileOutputStream fileOutputStream = new FileOutputStream(file_final);
fileOutputStream.write(workbook_os.toByteArray());
           

關閉檔案流

fileOutputStream.close();
fileInputStream.close();