userimage
Moe

how to handle both xls and xlsx file upload using apache poi api

my requirement is i want to handle both xls and xlsx file using apache poi api for upload and i want to handle it using a single method. how can i handle both type of file using apache poi api?
userimage
Moe

We can use below method to solve the problem:



private void uploadExcelAbove2007(InputStream inputStream)
                     throws IOException {
              XSSFWorkbook myWorkBook = new XSSFWorkbook(inputStream);
              XSSFSheet mySheet = myWorkBook.getSheetAt(0);
              int countOfRows = mySheet.getPhysicalNumberOfRows();
              for (int i = 0; i < countOfRows; i++) {
                     Row row = mySheet.getRow(i);
                     if (row.getCell(0).getCellType() == Cell.CELL_TYPE_BLANK) {
                           i = countOfRows;
                           break;
                     }
                     for (int j = 0; j < 5; j++) {
                           Cell cell = row.getCell(j);
                           if (null == cell) {
                                // DO YOUR STUFF
                           } else {
                                  switch (cell.getCellType()) {
                                  case Cell.CELL_TYPE_NUMERIC: {
                                        // DO YOUR STUFF
                                         break;
                                  }
                                  case Cell.CELL_TYPE_STRING: {
                                    // DO YOUR STUFF
                                         break;
                                  }
                                  case Cell.CELL_TYPE_BLANK: {
                                        // DO YOUR STUFF
                                         break;
                                  }
                                  default:
                                         break;
                                  }
                           }
                     }
              }
              myWorkBook.close();
       }

       private void uploadExcelBelow2007(InputStream inputStream)
                     throws IOException {
              POIFSFileSystem fs = new POIFSFileSystem(inputStream);
              @SuppressWarnings("resource")
              HSSFWorkbook wb = new HSSFWorkbook(fs);
              HSSFSheet sheet = wb.getSheetAt(0);
              int countOfRows = sheet.getPhysicalNumberOfRows();
              for (int i = 0; i < countOfRows; i++) {
                     Row row = sheet.getRow(i);
                     for (int j = 0; j < 6; j++) {
                           Cell cell = row.getCell(j);
                           if (null == cell) {
                                  tempList.add("");
                                  counter = counter + 1;
                           } else {
                                   switch (cell.getCellType()) {
                                  case Cell.CELL_TYPE_NUMERIC: {
                                        // DO YOUR STUFF
                                         break;
                                  }
                                  case Cell.CELL_TYPE_STRING: {
                                    // DO YOUR STUFF
                                         break;
                                  }
                                  case Cell.CELL_TYPE_BLANK: {
                                        // DO YOUR STUFF
                                         break;
                                  }
                                  default:
                                         break;
                                  }
                           }
                     }
              }
       }

       public <RETURN TYPE> commonExcelUpload(
                     InputStream inputStream, String createdBy) throws IOException,
                     FileNotFoundException {
              try {
                     uploadExcelAbove2007(inputStream); // for xlsx
                     // DO YOUR STUFF
              } catch (POIXMLException e) {
                     try {
                           uploadExcelBelow2007(inputStream); // for xls
                           // DO YOUR STUFF
                     } catch (IOException e1) {
                           e1.printStackTrace();
                          // HANDLE EXCEPTION
                     }
              }
              return <>;
       }
Answer is