A POI related code block running dead slow

问题:

I have below piece of code block containing loops:

Row row = null;
Cell cell = null;
String dataVal = null;
String[] temp = null;

for (int j = 0; j < this.myDataValues.size(); j++) {
  row = sheet.createRow(rownum++);
  temp = this.finalRowValues.get(j);

   for (int i = 0; i < 4; i++) {
       cell = row.createCell(i);

       dataVal = temp[i];

            if (NumberUtils.isNumber(dataVal)) {
                double d = Double.valueOf(dataVal);
                cell.setCellValue(d);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("currency"));
            } else if (isValidDate(dataVal)) {
                cell.setCellValue(dataVal);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("date"));
            } else {
                cell.setCellValue(temp[i]);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("data"));
            }
            sheet.autoSizeColumn(i);
        }
    }

Where myDataValues is a List of String[] with each String[] object containing 4 values.

I am running this in Rational Application Developer version 8 and Apache POI 3.8.

There are around 5500 elements in myDataValues which is a pretty small value I believe.

However, this code block is taking more then a hour to run.

I think there is something wrong with this. 5500 elements with each containing 4 elements should run pretty fast and should be a question of several minutes. What could be the possible cause? Is there a way to make this block run faster?

There is nothing wrong with available memory of the machine or any other such issues. Everything is working as expected and I have verified it. The issue is in this block only.

回答1:

Your processing is very slow because you're calling autoSizeColumn for every row. From the Javadocs for the autoSizeColumn method:

This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.

Place the calls to autoSizeColumn outside of the loop that creates the rows, in its own for loop only on the columns. This will minimize calls to this method and improve your performance.

回答2:

try this...

for (int j = 0; j < this.myDataValues.size(); j++) {
  row = sheet.createRow(rownum++);
  temp = this.finalRowValues.get(j);

   for (int i = 0; i < 4; i++) {
       cell = row.createCell(i);

       dataVal = temp[i];

            if (NumberUtils.isNumber(dataVal)) {
                double d = Double.valueOf(dataVal);
                cell.setCellValue(d);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("currency"));
            } else if (isValidDate(dataVal)) {
                cell.setCellValue(dataVal);
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("date"));
            } else {
                cell.setCellValue(temp[i]);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("data"));
            }
        }
    }
    for (int i = 0; i < 4; i++) {
      sheet.autoSizeColumn(i);
    }