CODEWITHSUNDEEP
javaapache-poi
Vicky
Vicky

Reputation: 17375

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.

Upvotes: 2

Views: 1665

Answers (3)

Raphael
Raphael

Reputation: 21

Just for reference...

In my case, I had 1 millions plus, and the AutoSizeColumn still slow (even in the end).

So, I considerely boost the performance, just storing the column index and content length of every values (in a Dictionary), when it is large than last stored.

In the end of all process, just "foreach" the list and set the width of the column with sheet.SetColumnWidth.

  • With autosize: never end;
  • With witdh: 3 seconds.

Pseudo-code

if(!dictionary.Any(a => a.Key == columnIndex))
                {
                    dictionary.Add(columnIndex, columnContent.Length);
                }
                else if(dictionary.Any(a => a.Key == columnIndex && a.Value < columnContent.Length))
                {
                    dictionary[columnIndex] = columnContent.Length;
                }

And in the end

foreach (KeyValuePair<int, int> column in dictionary)
        {
            sheet.SetColumnWidth(column.Key, column.Value*300);
        }

Upvotes: 2

rgettman
rgettman

Reputation: 178263

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.

Upvotes: 8

Iv&#225;n Mari&#241;o
Iv&#225;n Mari&#241;o

Reputation: 1

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);
    }

Upvotes: 0

Related Questions