Apache POI XSSF examples

Create File Example

    public void createXL() {  
           XSSFWorkbook workbook = new XSSFWorkbook();  
           XSSFSheet sheet = workbook.createSheet();  
           for (int i = 0; i < 10; i++) {  
                XSSFRow row = sheet.createRow(i);  
                XSSFCell cell = row.createCell(0);  
                cell.setCellValue("Hello World" + i);  
           }  
           File file = new File(file_path);  
           if (file.exists()) {  
                file.delete();  
           }  
           try {  
                file.createNewFile();  
                FileOutputStream fos = new FileOutputStream(file);  
                workbook.write(fos);  
                workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Modify Excel file

   public void modifyXL() {  
           File file = new File(file_path);  
           try {  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                XSSFRow row = sheet.getRow(0);  
                XSSFCell cell = row.getCell(2);  
                if (cell == null)  
                     cell = row.createCell(2);  
                cell.setCellValue("new addition");  
                FileOutputStream fos = new FileOutputStream(file);  
                workbook.write(fos);  
                workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Read Excel File

      public void readXL() {  
           try {  
                File file = new File(file_path);  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                System.out.println("first "+sheet.getFirstRowNum());  
                System.out.println("Last "+sheet.getLastRowNum());  
                int totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());  
                System.out.println(totalRows);  
                for (int i=0;i<=totalRows;i++)  
                {  
                     XSSFRow rows = sheet.getRow(i);  
                     System.out.println(rows.getCell(0).getStringCellValue());  
                }  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }  

Read Excel In Key Value Pair

 package TestNG;  
 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.FileNotFoundException;  
 import java.io.IOException;  
 import java.util.HashMap;  
 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
 import org.apache.poi.xssf.usermodel.XSSFCell;  
 import org.apache.poi.xssf.usermodel.XSSFComment;  
 import org.apache.poi.xssf.usermodel.XSSFRow;  
 import org.apache.poi.xssf.usermodel.XSSFSheet;  
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
 public class DatabaseHelper {  
      XSSFCell column1;  
      XSSFCell column2;  
      String value;  
      String key;  
      String VALUE;  
      // Main method is used for testing   
      public static void main(String[] args) {  
 System.out.println(new DatabaseHelper().getQueries("Test Case 01"));  
                     ;  
      }  
      public String getQueries(String KEY) {  
           File file = new File("C://Test//sqlqueries.xlsx");  
 HashMap<String, String> sqlmap = new HashMap<String, String>();  
           try {  
                FileInputStream is = new FileInputStream(file);  
                XSSFWorkbook workbook = new XSSFWorkbook(is);  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                int totalRows = (sheet.getLastRowNum() - sheet.getFirstRowNum());  
 //               System.out.println("Total Rows " + totalRows);  
                for (int i = 1; i <= totalRows; i++) {  
                     XSSFRow row = sheet.getRow(i);  
                     column1 = row.getCell(0);  
                     column2 = row.getCell(1);  
                     if (column1 != null) {  
 //                         System.out.println(column1.getStringCellValue());  
                          key = column1.getStringCellValue();  
                     }  
                     if (column2 != null) {  
 //                         System.out.println(column2.getStringCellValue());  
                          value = column2.getStringCellValue();  
                     }  
                     sqlmap.put(key, value);  
                }  
                VALUE = sqlmap.get(KEY);  
 workbook.close();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
           return VALUE;  
      }  
 }  

Alternative way to read Rows and Columns

      public void datagen() {  
           File file = new File("C://Test//sqlqueries.xlsx");  
           try {  
                XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));  
                XSSFSheet sheet = workbook.getSheetAt(0);  
                int rowlength = sheet.getLastRowNum() - sheet.getFirstRowNum();  
                doubleDim = new String[rowlength][collength];  
                for (int i = 0; i < rowlength; i++) {  
                     XSSFRow rows = sheet.getRow(i);  
                     collength = rows.getLastCellNum() - rows.getFirstCellNum();  
                     for (int j = 0; j < collength; j++) {  
                          System.out.println(rows.getCell(j).getStringCellValue());  
 //                         doubleDim[j][i] = rows.getCell(j).getStringCellValue();  
                     }  
                }  
           } catch (FileNotFoundException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           } catch (IOException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
           }  
      }