In the previous article, we have learned how to read an Excel file using POI in this article we will learn how to Write Excel File in Java using POI API.
In order to use POI we need to have the following dependencies added in your project.
- poi-3.15.jar
- poi-ooxml-3.15.jar
- poi-ooxml-schemas-3.15.jar
- xmlbeans-2.6.jar
If you are running on maven add the below dependency to your pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>
Write Excel File in Java using POI – XLS format (2003 or earlier)
package com.javainterviewpoint; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; public class XLSWriter { public static void main(String[] args) { try { String excelPath = "C:\\Football.xls"; FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath)); // Create Workbook instance holding .xls file HSSFWorkbook workbook = new HSSFWorkbook(); // Create a new Worksheet HSSFSheet sheet = workbook.createSheet("Footbal Players"); Object[][] footballPlayers = { {"Name","Country"}, {"Ronaldo","Portugal"}, {"Rooney","England"}, {"Roben","Netherland"}, {"Messi","Argentina"} }; int rownum = 0; for(Object[] player : footballPlayers) { Row row = sheet.createRow(rownum++); int colnum = 0; for(Object value : player) { Cell cell = row.createCell(colnum++); if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); } } } //Write workbook into the excel workbook.write(fileOutputStream); //Close the workbook workbook.close(); } catch (IOException ie) { ie.printStackTrace(); } } }
- Create a new HSSFWorkbook instance
HSSFWorkbook workbook = new HSSFWorkbook();
- Create a new Worksheet “Football Players”
HSSFSheet sheet = workbook.createSheet("Footbal Players");
- footballPlayers[][] array will hold the array of string which has to be written into the sheet.
Object[][] footballPlayers = { {"Name","Country"}, {"Ronaldo","Portugal"}, {"Rooney","England"}, {"Roben","Netherland"}, {"Messi","Argentina"} };
- Iterate each object and create a new row for each object
for(Object[] player : footballPlayers) { Row row = sheet.createRow(rownum++);
- Get the individual cells from the above-created rows and create a new cell using createCell() method
for(Object value : player) { Cell cell = row.createCell(colnum++);
- Finally, using the write() method of the workbook instance write it into the fileOutputStream and close the workbook.
workbook.write(fileOutputStream); workbook.close();
Output :
You will have the Football.xls created with the below content
Read Excel File in Java using POI – XLSX format (2007 or later)
In order to write XLSX file format we just need to replace HSSF to XSSF in the above code.
package com.javainterviewpoint; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class XLSXWriter { public static void main(String[] args) { try { String excelPath = "C:\\Jackson\\Football.xlsx"; FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath)); // Create Workbook instance holding .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(); // Create a new Worksheet XSSFSheet sheet = workbook.createSheet("Footbal Players"); Object[][] footballPlayers = { {"Name","Country"}, {"Ronaldo","Portugal"}, {"Rooney","England"}, {"Roben","Netherland"}, {"Messi","Argentina"} }; int rownum = 0; for(Object[] player : footballPlayers) { Row row = sheet.createRow(rownum++); int colnum = 0; for(Object value : player) { Cell cell = row.createCell(colnum++); if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); } } } //Write workbook into the excel workbook.write(fileOutputStream); //Close the workbook workbook.close(); } catch (IOException ie) { ie.printStackTrace(); } } }
Convert Java Object To Excel
In order to convert Java objectTo Excel, we will be writing real-time java object instead of writing String array to the excel. Let’s see how we can achieve this.
package com.javainterviewpoint; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ConvertJavaToExcel { public static void main(String[] args) { List footballPlayers = new ArrayList(); footballPlayers.add(new Football("Klose","Germany")); footballPlayers.add(new Football("Gerrard","England")); footballPlayers.add(new Football("Drogba","Ivory Coast")); footballPlayers.add(new Football("Zidane","France")); ConvertJavaToExcel(footballPlayers); } public static void ConvertJavaToExcel(List players) { try { String excelPath = "C:\\Jackson\\Football1.xlsx"; FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath)); // Create Workbook instance holding .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(); // Create a new Worksheet XSSFSheet sheet = workbook.createSheet("Footbal Players"); int rownum = 0; for(Football player : players) { Row row = sheet.createRow(rownum++); int cellnum = 0; //Get the name put in the first cell row.createCell(cellnum++).setCellValue(player.getName()); //Get the country put in the second cell row.createCell(cellnum++).setCellValue(player.getCountry()); } //Write workbook into the excel workbook.write(fileOutputStream); //Close the workbook workbook.close(); } catch (IOException ie) { ie.printStackTrace(); } } }
Output:
Leave a Reply