1

i have a sheet with 6 columns and like 50k rows. I would like to copy one row n times. For now I am doing like this:

for (int i=0; i<=excel_max_row; i++) {
        row = sheet3.createRow((short) i);
        cell = row.createCell(0);
        cell.setCellFormula("IFERROR(LOOKUP(Template!A"+(i+2)+",'Dropdown-Values'!A:A,'Dropdown-Values'!B:B),\"\")");

        cell = row.createCell(1);
        cell.setCellFormula("IF(Template!B"+(i+2)+"=0,\"\",Template!B"+(i+2)+")");

        cell = row.createCell(2);
        cell.setCellFormula("IFERROR(LOOKUP(Template!C"+(i+2)+",'Dropdown-Values'!C:C,'Dropdown-Values'!D:D),\"\")");

        cell = row.createCell(3);
        cell.setCellFormula("IFERROR(LOOKUP(Template!D"+(i+2)+",'Dropdown-Values'!E:E,'Dropdown-Values'!F:F),\"\")");

        cell = row.createCell(4);
        cell.setCellFormula("IFERROR(LOOKUP(Template!E"+(i+2)+",'Dropdown-Values'!E:E,'Dropdown-Values'!F:F),\"\")");

        cell = row.createCell(5);
        cell.setCellFormula("IF(Template!F"+(i+2)+"=0,\"\",Template!F"+(i+2)+")");
}
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

It works but is very slow because excel_max_row can be up to 50k. Is there any way to quick copy a row n times with also all forumals?

Thanks a lot

ayasha
  • 1,221
  • 5
  • 27
  • 46
  • what is for you `very slow`? – XtremeBaumer Sep 20 '17 at 07:18
  • 1
    I suspect the `evaluateAllFormulaCells` is the main reason for slowing down. Read [Recalculation of Formulas](https://poi.apache.org/spreadsheet/eval.html#Recalculation+of+Formulas) for alternatives. – Axel Richter Sep 20 '17 at 07:56
  • 221288ms for 200 rows, and yes the problem is the evaluation – ayasha Sep 20 '17 at 08:48
  • Fine you have found a solution. But i am curious how `row = sheet3.createRow((short) i);` will be able creating 50,000 rows since `short` has a maximum value of 32,767. Hint [Sheet.createRow(**int** rownum)](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#createRow-int-). Not sure where your casting to `short` is coming from. – Axel Richter Sep 20 '17 at 09:19

1 Answers1

1

I evaluate all formula when the user open the file:

wb.setForceFormulaRecalculation(true);
ayasha
  • 1,221
  • 5
  • 27
  • 46