In this blog post We will learn how to create/read excel files in java.
For all type of Microsoft Documents we can use an open source lib – Apache POI.
This library can help us to read/writer all Microsoft Document Formats,But we will use it for excels only.
Apache POI provides two type of API for excel manipulation
- POI-HSSF – excel manipulation in excel –97(2007) file format(xls)
- POI-XSSF - excel manipulation in excel –2007 file format(xlsx)
- Create a new workbook: Work book creates the excel files which we can use for read and write.Work Book can be created in two way-
Workbook wb = new HSSFWorkbook();// For old format
or
Workbook wb = new XSSFWorkbook();// For new format
- Create the sheets inside the workbook: These sheets contains cells in which the actual data resides.
Sheet sheet1 = wb.createSheet("Result");
Sheet sheet2 = wb.createSheet("Result2");
- Create the rows inside Sheet to hold the cells: Row row = sheet.createRow((0);//Note that rows are zero index based i.e. first row starts with 0 index.
- Create the cells inside the row to hold the data: Cells are also zero index based as rows.
Cell cell = row.createCell(0);
cell.setCellValue(1); - Writing the excel sheet to a file:
FileOutputStream fileOut = new FileOutputStream("my_workbook.xlsx");
wb.write(fileOut);
fileOut.close();
Steps to follow to read an excel file: InputStream input = new FileInputStream("my_workbook.xlsx");
Workbook wBook = WorkbookFactory.create(input);
Sheet sheet = wBook.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
Example:
public class ExcelWriter {
public static void main(String[] args) {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("demo sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
row.createCell(1).setCellValue(999);
row.createCell(2).setCellValue("Empty Heart");
row.createCell(3).setCellValue(true);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("demo.xls");
wb.write(fileOut);
fileOut.close();
}
}