Let us talk about writing excel files using apache POI. In earlier post, we saw that how can we read the data (get the data ) from excel file. Refer below posts for this.
1. How to read an excel 2003 (.xls) file using Apache POI
2. How to read an Excel 2007 (.xlsx) file using Apache POI
In this post, we will talk about, how can we write / update data in excel sheet.
For Writing the data, we need FileOutputStream.
We need to find out the cell which we are going to write in to. Then use setCellValue method to write the value to the desired cell.
Steps to update excel file will be:
- Open excel file in input mode (inputstream)
- Use POI API and read the excel content
- Update cell’s value using different setCellValue methods.
- Close the excel input file (inputstream)
- Open same excel file in output mode (outputstream)
- Write content of updated workbook in output file
- Close output excel file
Below example illustrate the same.
package apachePOI;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelFileClass {
@SuppressWarnings("resource")
public static void main(String[] args) throws IOException {
try {
FileInputStream file = new FileInputStream(new File("C:UsersPrakashDesktopTestData - Only String.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;
//Update the value of cell
cell = sheet.getRow(1).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
cell = sheet.getRow(2).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
cell = sheet.getRow(3).getCell(2);
cell.setCellValue(cell.getNumericCellValue() * 2);
file.close();
FileOutputStream outFile =new FileOutputStream(new File("C:UsersPrakashDesktopTestData - Only String.xlsx"));
workbook.write(outFile);
outFile.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Hope this helps !!!!