Blog Image

Read & Write Data with Excel Sheet in Java  

Apache poi is the api to deal with Microsoft Excel File in java. The use case to deal with excel sheet is very common. In this blog we are going to see a basic example of reading the data from the excel sheet and writing data from java program to excel sheet.

Apache POI is the api provided by Apache Software Foundation to deal with different Microsoft File Format using java programming.

Here is explained about reading the data from excel sheet i.e. .xlsx file and writing the data into .xlsx file in java programming using POI api. POI api has provided the some packages having interfaces and implementation classes using which we can read the data from xlsx file as well as write the data into xlsx file.

Workbook is an interface in org.apache.poi.ss.usermodel package provide with implementation classes like HSSFWorkbook and XSSFWorkbook which provides ways to read spreadsheets create, modify, read and write XLS spreadsheets.

The following is the basic example for reading the data from the .xlsx file.


public class XlsxFileReading {
    private static final String FILE_PATH = "D:\\EmployeeDetails.xlsx";

    @SuppressWarnings({ "resource", "rawtypes", "unchecked" })
    public static void main(String[] args) throws IOException {
        FileInputStream excelFile = null;
        Sheet datatypeSheet = null;
        Workbook workbook = null;
        List excelData = null;

        excelData = new ArrayList();
        excelFile = new FileInputStream(new File(FILE_PATH));
        workbook = new XSSFWorkbook(excelFile);
        datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();

            while (cellIterator.hasNext()) {

                Cell currentCell = cellIterator.next();
                excelData.add(currentCell);

            }
        }
        for (int i = 0; excelData.size() > i; i++) {
            if (i % 3 == 0) {
                System.out.println();
            }
            System.out.print(" " + excelData.get(i) + " ");
        }
    }
}


The output for the above program is as follow:


 Emp_No   Name   Address
 1.0          John     Hi-Tech City
 2.0        Mathew   Banjara Hills
 3.0          Susan   Hi-Tech City


When we are creating object of XSSFWorkbook from Workbook interface it creates the POI file system with all the nodes of excel sheet with data. From this file system we can get the data by using workbook.getSheetAt(0) method and can use the data for our requirement.


The following is the basic example for writing the data in .xlsx file.

public class WritingDataToXlsx {
    public static void main(String[] args) throws IOException {
        @SuppressWarnings("resource")
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        Map<String, Object[]> data = new HashMap<String, Object[]>();
        data.put("1", new Object[] { "Emp_No.", "Name", "Address" });
        data.put("2", new Object[] { "1", "John", "Hi-tech city" });
        data.put("3", new Object[] { "2", "Mathew", "Banjar Hills" });
        data.put("4", new Object[] { "3", "Susan", "Hi-tech city" });

        Set<String> keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                    cell.setCellValue((String) obj);
            }
        }

        FileOutputStream out = new FileOutputStream(new File("D:\\excel.xls"));
        workbook.write(out);

    }
}

In above program the object of HSSFWorkbook constructs the POI filesystem around your InputStream, including all nodes. The workbook.createSheet() create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns the high level representation. Then using the map we have populate the data in the excel sheet.

You can refer to the working examples for the above programs from attachments.



About author

User Image
AdityaT

I am a software developer. I am working working on jdk , jee technologies. I am working on Spring Framework as well. I am passionate about learning java technologies like Web Services and Restful Services.

2

-Comments

Be the first person to write a comment for this Blog
  • author image
    by:
      AdityaT
      13-12-2017 11:16:19 AM

    From the attachments you can download the above program. You can get the maven dependency for poi api in pom.xml from the downloaded program.

  • author image
    by:
      sandhyaM
      13-12-2017 06:42:29 AM

    Thanks for this blog.It is very usefull.can you help me help me in finding the poi api jars.

Load More

No More Comments

Leave a Comment

Your comment has been posted and will appear soon.