Apache POI – update existing excel sheet
In this tutorial you will learn how to update an existing excel sheet using Apache POI. This is extremely useful, as nowadays in almost all the cases you need to parse an excel file.
Let’s assume that we need a simple application that will insert some contact information into an excel file.
First of all we will need a class that will represent a single row in or excel file. Let’s create it:
[code language=”java”]
// Apache POI – update existing excel sheet
public class ExcelRow {
private String name;
private String phoneNumber;
private String email;
private String status;
private String employee;
private String notes;
public ExcelRow(String name, String phoneNumber, String email, String status, String employee, String notes) {
this.name = name;
this.phoneNumber = phoneNumber;
this.email = email;
this.status = status;
this.employee = employee;
this.notes = notes;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the phoneNumber
*/
public String getPhoneNumber() {
return phoneNumber;
}
/**
* @param phoneNumber the phoneNumber to set
*/
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
/**
* @return the email
*/
public String getEmail() {
return email;
}
/**
* @param email the email to set
*/
public void setEmail(String email) {
this.email = email;
}
/**
* @return the status
*/
public String getStatus() {
return status;
}
/**
* @param status the status to set
*/
public void setStatus(String status) {
this.status = status;
}
/**
* @return the employee
*/
public String getEmployee() {
return employee;
}
/**
* @param employee the employee to set
*/
public void setEmployee(String employee) {
this.employee = employee;
}
/**
* @return the notes
*/
public String getNotes() {
return notes;
}
/**
* @param notes the notes to set
*/
public void setNotes(String notes) {
this.notes = notes;
}
}
// Apache POI – update existing excel sheet
[/code]
The above class represents a single row in our excel sheet. This is how we will sort out rows. You will be able to push as many rows objects as you wish in a map, or list.
Once the object is ready, we can create the main class which will do the most of the important work. For the tutorials sake we will pass a row object to this class and get the values that we need by the get methods we just created in the code above.
[code language=”java”]
// Apache POI – update existing excel sheet
public class ExcelHandler {
public void updateFile(ExcelRow ex) throws Exception {
//Read the spreadsheet that needs to be updated
FileInputStream input_document = new FileInputStream(new File("./Contacts.xls"));
//Access the workbook
HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
//Access the worksheet, so that we can update / modify it.
HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
// declare a Cell object
Cell cell = null;
int lastRow = my_worksheet.getLastRowNum() + 1;
// Access the cell first to update the value
HSSFRow row = my_worksheet.createRow(lastRow);
row.createCell(0).setCellValue(ex.getName());
row.createCell(1).setCellValue(ex.getPhoneNumber());
row.createCell(2).setCellValue(ex.getEmail());
row.createCell(3).setCellValue(ex.getStatus());
row.createCell(4).setCellValue(ex.getEmployee());
row.createCell(5).setCellValue(ex.getNotes());
my_worksheet.autoSizeColumn(1);
my_worksheet.autoSizeColumn(2);
my_worksheet.autoSizeColumn(3);
my_worksheet.autoSizeColumn(4);
my_worksheet.autoSizeColumn(5);
//Close the InputStream
input_document.close();
//Open FileOutputStream to write updates
FileOutputStream output_file = new FileOutputStream(new File("./Contacts.xls"));
//write changes
my_xls_workbook.write(output_file);
//close the stream
output_file.close();
}
}
// Apache POI – update existing excel sheet
[/code]
The above class will spot the latest excel row and append a new row using the object’s data. You can loop this method over and over until you insert all of the available rows.
In order to make everything working you will need to include the Apache POI library to your project.
It is that simple! Now you know how to update an excel sheet from your application.
