Extracting spreadsheet cells using Apache POI

Question

I'm working with Apache POI framework.

This code go through sheets, row and get value of cells. I will try to get value from directly indicated column. I need this because I want convert only some information from xls to csv.

FileOutputStream fos = new FileOutputStream(outputFile);

Workbook workbook = null;
for (File input : inputFile) {
  String ext = FilenameUtils.getExtension(input.toString());

  if (ext.equalsIgnoreCase("xlsx")) {
    workbook = new XSSFWorkbook(new FileInputStream(input));
  } else if (ext.equalsIgnoreCase("xls")) {
    workbook = new HSSFWorkbook(new FileInputStream(input));
  }

  int numberOfSheets = workbook.getNumberOfSheets();
  Row row;
  Cell cell;
  Sheet sheet = null;
  int[] kolumny = {1, 2, 3, 4, 5, 6, 7, 50, 51, 52, 53, 54, 55};

  for (int i = 1; i < numberOfSheets; i++) {
    sheet = workbook.getSheetAt(i);
    if (sheet.getLastRowNum() > 24) {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        row = rowIterator.next();
        for (int j = 23; j < sheet.getLastRowNum(); j++) {
          if (row.getRowNum() == j) {
            if (row.getCell(1) == null || row.getCell(1).getCellType() == Cell.CELL_TYPE_BLANK) {
              break;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
              cell = cellIterator.next();
              for (int kolumna : kolumny) {
                if (cell.getColumnIndex() == kolumna) {
                  if (cell != null) {
                    switch (cell.getCellType()) {
                      case Cell.CELL_TYPE_BOOLEAN:
                        data.append(cell.getBooleanCellValue() + ",");
                        break;
                      case Cell.CELL_TYPE_NUMERIC:
                        data.append(cell.getNumericCellValue() + ",");
                        break;
                      case Cell.CELL_TYPE_STRING:
                        data.append(cell.getStringCellValue() + ",");
                        break;
                      case Cell.CELL_TYPE_BLANK:
                        data.append("" + ",");
                        break;
                      default:
                        data.append(cell + ",");
                    }
                  }
                }
              }
            }
            data.append('\n');
          }
        }
      }
    }
  }
}

Answer 1

You could extract the content of inner loops to methods with speaking names.

E.g. You could introduce a method like

public static void processSheet(Sheet sheet)
{
  if (sheet.getLastRowNum() > 24) {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        row = rowIterator.next();
  ...  

}

Within this method you can extract another method e.g.

public static void processRow(Row row){
  for (int j = 23; j < sheet.getLastRowNum(); j++) {
    if (row.getRowNum() == j) {
      if (row.getCell(1) == null || row.getCell(1).getCellType() == Cell.CELL_TYPE_BLANK) {
        break;
    ...       

}

And so on until you have only short methods with speaking names. This makes the code easier to read and understand.

Answer 2

Try using

 workbook = WorkbookFactory.create();

instead of

String ext = FilenameUtils.getExtension(input.toString());

  if (ext.equalsIgnoreCase("xlsx")) {
    workbook = new XSSFWorkbook(new FileInputStream(input));
  } else if (ext.equalsIgnoreCase("xls")) {
    workbook = new HSSFWorkbook(new FileInputStream(input));
  }

WorkbookFactory is used for creating the appropriate kind of Workbook (be it HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.

Reference

Another suggestion is to change possible loops into smaller methods like this answer.

Answer 3

You can try to use this jar library:

https://afajardomorera.github.io/ExcelReader/

You can define a Java Object with the fields you need, configure a property file (where you can define concrete cells to extract and/or data rows from a table to extract) and invoke the reader method from the jar.

In the github code, you find a jar_generated folder with the latest version of the jar to use it.

Answer 4

I added the answer with the link recommendation to a jar:

https://afajardomorera.github.io/ExcelReader/

Last tuesday, I finished the development of the 0.0.1 version of that library.

Is an application where you can read and extract the information of an excel file and set the values to the java objects you define for it.

To do this, you have to create a property file with the configuration needed (explained in the url)... basically, you can set the sheet indexs to process, what binding class for each sheet, if you want.. what binding class for data rows (will be a list field of that type into the sheet binding class), complete cell reference to process specific cells... and the name and type of variable to binding the cell values, that have to be fields with the same name and type into binding classes.

To summarize... you only have to add jar into your app, define de property file and the binding classes (depends on the data you want to process in the excel file) and only have to invoke the ExcelReader.reader method with params: Excel File, Map and ResourceBoundle...

Map: Key is the SimpleName of the class. Value is an instance of the object (new TableDTO(), for example) ResourceBoundle: is the property file loaded into a ResourceBoundle

The reader method return a Map where Key is the sheet index and value is a List of the Objects you have defined with the information of the excel inside.

Is my first time in this web. If here only post code... I take note about that for next posts.

Thank you.