How to read / write excel files in java ?

---

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)

Steps to follow to create an excel file:

• 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();
    }
}