Apache POI를 사용하여 Excel 파일 업데이트

Apache POI를 사용하여 Excel 파일 업데이트

Apache POI를 사용하여 기존 엑셀 파일을 업데이트하려고 합니다.코드를 실행할 때마다 아래와 같은 오류가 발생합니다.FileInputStreamNewFile 같은 것도 시도해 보았습니다.

Exception in thread "main" java.lang.NullPointerException
    at com.gma.test.WriteExcelTest.writeXLSXFile(WriteExcelTest.java:26)
    at com.gma.test.WriteExcelTest.main(WriteExcelTest.java:44)

아래 코드를 찾아주세요.도와주셔서 감사합니다.

package com.gma.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelTest {

    public static void writeXLSXFile(int row, int col) throws IOException {
        try {
            FileInputStream file = new FileInputStream("C:\\Anuj\\Data\\Data.xlsx");

            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Update the value of cell
            cell = sheet.getRow(row).getCell(col);
            cell.setCellValue("Pass");

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\Anuj\\Data\\Data.xlsx"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        writeXLSXFile(3, 3);
    }

}

대체하는 경우

//Update the value of cell
cell = sheet.getRow(row).getCell(col);
cell.setCellValue("Pass");

와 함께

//Retrieve the row and check for null
HSSFRow sheetrow = sheet.getRow(row);
if(sheetrow == null){
    sheetrow = sheet.createRow(row);
}
//Update the value of cell
cell = sheetrow.getCell(col);
if(cell == null){
    cell = sheetrow.createCell(col);
}
cell.setCellValue("Pass");

효과가 있을 겁니다!

좋은 예를 들어주신 Jelle Houzel에게 감사드립니다.
저는 다른 사람들이 그들의 코드에 더 빨리 통합할 수 있도록 작업 코드를 추가하고 싶었습니다.

또한 HSSFlow 대신 XSSFlow를 사용해야 했지만 그 외에는 잘 작동합니다.

package stackoverflow.appachePOI;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelTest {

    public static void writeXLSXFile(int row, int col) throws IOException {
        try {
            FileInputStream file = new FileInputStream("C:\\Users\\Sam\\files\\Masterproef lca\\lca-analysebeheer\\Test-Files\\exceltemplates\\template.xlsx");

            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Retrieve the row and check for null
            XSSFRow sheetrow = sheet.getRow(row);
            if(sheetrow == null){
                sheetrow = sheet.createRow(row);
            }
            //Update the value of cell
            cell = sheetrow.getCell(col);
            if(cell == null){
                cell = sheetrow.createCell(col);
            }
            cell.setCellValue("Pass");

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\Users\\Sam\\files\\Masterproef lca\\lca-analysebeheer\\Test-Files\\exceltemplates\\Output.xlsx"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        writeXLSXFile(3, 3);
    }

}

저는 이것으로 노력했고 XLSX와 XSSF에서 일합니다.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestStackOver {

    public static void writeXLSXFile(int row, int col) throws IOException {
        try {
            FileInputStream file = new FileInputStream(Constante.ruta);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;
          //Retrieve the row and check for null
            XSSFRow sheetrow = sheet.getRow(row);
            if(sheetrow == null){
                sheetrow = sheet.createRow(row);
            }
            //Update the value of cell
            cell = sheetrow.getCell(col);
            if(cell == null){
                cell = sheetrow.createCell(col);
            }
            cell.setCellValue("Pass");

            file.close();


            FileOutputStream outFile =new FileOutputStream(new File(Constante.ruta_salida));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        System.out.println("inicio");
        writeXLSXFile(1, 14);
        System.out.println("terminado");
    }

}

언급URL : https://stackoverflow.com/questions/32273072/updating-excel-file-using-apache-poi