Reading and Writing data to excel file using Apache POI

Earlier in this post, we introduced Apache POI- a Java API useful for interacting with Microsoft office documents.
Now we’ll see how can we read and write to an excel file using the API.

Writing an excel file

Writing a file using POI is very simple and involve following steps:

Create a workbook
Create a sheet in workbook
Create a row in sheet
Add cells in sheet
Repeat step 3 and 4 to write more data

// import statements 
public class POIforgfgWrite { 
public static void main(String[] args) 
    { 
        // Blank workbook 
        XSSFWorkbook workbook = new XSSFWorkbook(); 
  
        // Create a blank sheet 
        XSSFSheet sheet = workbook.createSheet("student Details"); 
  
        // This data needs to be written (Object[]) 
        Map<String, Object[]> data = new TreeMap<String, Object[]>(); 
        data.put("1", new Object[]{ "ID", "NAME", "LASTNAME" }); 
        data.put("2", new Object[]{ 1, "Pankaj", "Kumar" }); 
        data.put("3", new Object[]{ 2, "Prakashni", "Yadav" }); 
        data.put("4", new Object[]{ 3, "Ayan", "Mondal" }); 
        data.put("5", new Object[]{ 4, "Virat", "kohli" }); 
  
        // Iterate over data and write to sheet 
        Set<String> keyset = data.keySet(); 
        int rownum = 0; 
        for (String key : keyset) { 
            // this creates a new row in the sheet 
            Row row = sheet.createRow(rownum++); 
            Object[] objArr = data.get(key); 
            int cellnum = 0; 
            for (Object obj : objArr) { 
                // this line creates a cell in the next column of that row 
                Cell cell = row.createCell(cellnum++); 
                if (obj instanceof String) 
                    cell.setCellValue((String)obj); 
                else if (obj instanceof Integer) 
                    cell.setCellValue((Integer)obj); 
            } 
        } 
        try { 
            // this Writes the workbook gfgcontribute 
            FileOutputStream out = new FileOutputStream(new File("gfgcontribute.xlsx")); 
            workbook.write(out); 
            out.close(); 
            System.out.println("gfgcontribute.xlsx written successfully on disk."); 
        } 
        catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 

Reading an excel file

Reading an excel file is also very simple if we divide this in steps.

Create workbook instance from excel sheet
Get to the desired sheet
Increment row number
iterate over all cells in a row
repeat step 3 and 4 until all data is read

// import statements 
public class POIforgfgRead { 
public static void main(String[] args) 
    { 
        try { 
            FileInputStream file = new FileInputStream(new File("gfgcontribute.xlsx")); 
  
            // Create Workbook instance holding reference to .xlsx file 
            XSSFWorkbook workbook = new XSSFWorkbook(file); 
  
            // Get first/desired sheet from the workbook 
            XSSFSheet sheet = workbook.getSheetAt(0); 
  
            // Iterate through each rows one by one 
            Iterator<Row> rowIterator = sheet.iterator(); 
            while (rowIterator.hasNext()) { 
                Row row = rowIterator.next(); 
                // For each row, iterate through all the columns 
                Iterator<Cell> cellIterator = row.cellIterator(); 
  
                while (cellIterator.hasNext()) { 
                    Cell cell = cellIterator.next(); 
                    // Check the cell type and format accordingly 
                    switch (cell.getCellType()) { 
                    case Cell.CELL_TYPE_NUMERIC: 
                        System.out.print(cell.getNumericCellValue() + "t"); 
                        break; 
                    case Cell.CELL_TYPE_STRING: 
                        System.out.print(cell.getStringCellValue() + "t"); 
                        break; 
                    } 
                } 
                System.out.println(""); 
            } 
            file.close(); 
        } 
        catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 

Output:
Output

Sometimes you need to read a file at different location: Here’s how you can do it:

private static final String FILE_NAME 
    = "C:\Users\pankaj\Desktop\projectOutput\mobilitymodel.xlsx"; 
public static void write() throws IOException, InvalidFormatException 
{ 
    InputStream inp = new FileInputStream(FILE_NAME); 
    Workbook wb = WorkbookFactory.create(inp); 
    Sheet sheet = wb.getSheetAt(0); 
    ........ 
} 

You can append to the existing file using following code:

private static final String FILE_NAME 
    = "C:\Users\pankaj\Desktop\projectOutput\blo.xlsx"; 
public static void write() throws IOException, InvalidFormatException 
{ 
    InputStream inp = new FileInputStream(FILE_NAME); 
    Workbook wb = WorkbookFactory.create(inp); 
    Sheet sheet = wb.getSheetAt(0); 
    int num = sheet.getLastRowNum(); 
    Row row = sheet.createRow(++num); 
    row.createCell(0).setCellValue("xyz"); 
    ..... 
        .. 
        // Now this Write the output to a file 
        FileOutputStream fileOut = new FileOutputStream(FILE_NAME); 
    wb.write(fileOut); 
    fileOut.close(); 
} 

References :
https://poi.apache.org/
https://poi.apache.org/spreadsheet/examples.html
https://poi.apache.org/apidocs/

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Reading and Writing data to excel file using Apache POI

tags:

leave a comment

0 Comments

Subscribe to Our Newsletter

Top Followed books