How to read a specific excel columns from particular row using Apache POI

0
votes

I want to store data from excel sheet using Apache POI.

  • In that excel first row consist of employee information (that is an employee name) and the column values consist of employee details (that is an employee address).

  • So each header row (emp name) consist of multiple columns (emp address)

How do I get row and column values stored in a single entity?

I'm using Java Spring data.

1
javaexcelapache-poi
stackoverflow.com/help/how-to-ask – mallikarjun

1 Answers

0
votes

Assuming you are using spring annotatios, you can use the following code to read values from excel sheet.

  @RequestMapping(value = "/excelSheetProgram")
            public String excelSheetProgram(@ModelAttribute("excel"),
                    HttpServletRequest request, RedirectAttributes attributes, HttpServletResponse response, Object command,
                    BindException errors, @RequestParam("Excelfile") MultipartFile Excelfile) throws Exception {

            InputStream inputStream = null;
            inputStream = Excelfile.getInputStream();

            XSSFWorkbook workbook = new XSSFWorkbook(new 
            PushbackInputStream(inputStream));
            XSSFSheet mySheet = workbook.getSheetAt(0);

    // boolean variable for checking when to stop execution of reading from excel fields
            boolean inData = true;

    Iterator<Row> rowIterator = mySheet.iterator();
        rowIterator.next();
        int count = 0;
        for (int i = 0; rowIterator.hasNext() && inData; i++) {
            count++;

            Row row = rowIterator.next();



            // For each row, iterate through each columns
           Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            // This will change all Cell Types to String
            cell.setCellType(Cell.CELL_TYPE_STRING);
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:

                break;
            case Cell.CELL_TYPE_NUMERIC:

                break;
            case Cell.CELL_TYPE_STRING:



                break;
            }

        }

if (row == null || row.getCell(1) == null || row.getCell(1).getStringCellValue() == null
                    || inData == false) {
                inData = false;
            } 
            else {

                // getting values from excel cells
                employee_name = row.getCell(0).getStringCellValue();
                employee_address = row.getCell(1).getStringCellValue();
    }
}