Hello everyone, I ran into a problem, with the formation of an Excel file, the process starts to slow down with each iteration, the first iterations take 60–70 ms, the next ones increase by 10–20 ms, by the 50th iteration more than a second.

each iteration consists of adding from 6 to 10 lines, with the use of styles, formatting, combining. That is, a report is generated.

The total number of iterations can reach up to 800 iterations.

Can someone tell me, can someone solve this problem? otherwise I can not generate a report. Maybe somehow through multithreading is possible? But unfortunately I am still raw in it.

Here is a piece of basic code, a lot of code.

try (BufferedInputStream fis = new BufferedInputStream(new FileInputStream(fileCalc))) { ZipSecureFile.setMinInflateRatio(-1.0d); xssfWorkbook = new XSSFWorkbook(fis); XSSFSheet sheet = xssfWorkbook.getSheetAt(0); // Строка старта шапки int rowStartHead = 0; int numberTable = 1; for (TotalModel totalModel: listTotalModel) { if (totalModel != null) { // Строка финиша шапки int rowFinishHead = rowStartHead + 3; for (int i = rowStartHead; i <= rowFinishHead; i++) { XSSFRow rowHead = sheet.createRow(i); Cell head0 = rowHead.createCell(0); head0.setCellValue("№п/п"); head0.setCellStyle(cellStyleHead(sheet)); Cell head1 = rowHead.createCell(1); head1.setCellValue("Станция отправления"); head1.setCellStyle(cellStyleHead(sheet)); Cell head2 = rowHead.createCell(2); head2.setCellValue("Дорога отпр."); head2.setCellStyle(cellStyleHead(sheet)); sheet.setColumnWidth(2, 2194); Cell head3 = rowHead.createCell(3); head3.setCellValue("Станция назначения"); head3.setCellStyle(cellStyleHead(sheet)); Cell head4 = rowHead.createCell(4); head4.setCellValue("Дорога назн."); head4.setCellStyle(cellStyleHead(sheet)); sheet.setColumnWidth(4, 2194); Cell head5 = rowHead.createCell(5); head5.setCellValue("Наименование груза"); head5.setCellStyle(cellStyleHead(sheet)); Cell head6 = rowHead.createCell(6); head6.setCellValue("Расст., км"); head6.setCellStyle(cellStyleHead(sheet)); Cell head7 = rowHead.createCell(7); head7.setCellValue("Время в пути, сут"); head7.setCellStyle(cellStyleHead(sheet)); Cell head8 = rowHead.createCell(8); head8.setCellValue("Погр. / выгр."); head8.setCellStyle(cellStyleHead(sheet)); Cell head9 = rowHead.createCell(9); head9.setCellValue("Оборот, сут."); head9.setCellStyle(cellStyleHead(sheet)); Cell head10 = rowHead.createCell(10); head10.setCellValue("ВО"); head10.setCellStyle(cellStyleHead(sheet)); Cell head11 = rowHead.createCell(11); if (i == rowFinishHead) { head11.setCellValue("руб/ваг."); } else { head11.setCellValue("ДОХОД"); } head11.setCellStyle(cellStyleHeadBottom(sheet)); Cell head12 = rowHead.createCell(12); if (i == rowFinishHead - 1 || i == rowFinishHead - 2) { head12.setCellValue("Тариф в собств. вагонах"); } else if (i == rowFinishHead) { head12.setCellValue("руб/ваг."); } else { head12.setCellValue("РАСХОД"); } head12.setCellStyle(cellStyleHeadBottom(sheet)); Cell head13 = rowHead.createCell(13); if (i == rowFinishHead - 1 || i == rowFinishHead - 2) { head13.setCellValue("За нахождение в пути"); } else if (i == rowFinishHead) { head13.setCellValue("руб/ваг."); } else { head13.setCellValue("ПРИБЫЛЬ"); } head13.setCellStyle(cellStyleHeadBottom(sheet)); Cell head14 = rowHead.createCell(14); if (i == rowFinishHead - 1 || i == rowFinishHead - 2) { head14.setCellValue("В сутки"); } else if (i == rowFinishHead) { head14.setCellValue("руб/ваг/сут."); } else { head14.setCellValue("ПРИБЫЛЬ"); } head14.setCellStyle(cellStyleHeadRight(sheet)); } for (int i = 0; i < 11; i++) { sheet.addMergedRegion(new CellRangeAddress(rowStartHead, rowFinishHead, i, i)); } sheet.addMergedRegion(new CellRangeAddress(rowStartHead, rowFinishHead - 1, 11, 11)); sheet.addMergedRegion(new CellRangeAddress(rowStartHead, rowStartHead, 13, 14)); for (int i = 12; i < 15; i++) { sheet.addMergedRegion(new CellRangeAddress(rowStartHead + 1, rowFinishHead - 1, i, i)); } // Строка первого рейса int rowFirstRoute = rowFinishHead + 1; // Номер первой ячейки данных int firstNumberCell = rowFirstRoute + 1; boolean isMarker = false; for (Route route : totalModel.getTotalList()) { int num = rowFirstRoute + 1; XSSFRow row = sheet.createRow(rowFirstRoute); Cell number = row.createCell(0); if (!isMarker) { number.setCellValue(numberTable); isMarker = true; } else { number.setCellValue(""); } number.setCellStyle(cellStyleField(sheet)); Cell stationDeparture = row.createCell(1); stationDeparture.setCellValue(route.getStationDeparture().getNameStation()); if (route.isFlagNeedCalc()) { stationDeparture.setCellStyle(cellStyleFieldNeedCalc(sheet,false)); } else { stationDeparture.setCellStyle(cellStyleField(sheet)); } Cell roadDeparture = row.createCell(2); roadDeparture.setCellValue(route.getStationDeparture().getRoad().getNameRoad()); if (route.isFlagNeedCalc()) { roadDeparture.setCellStyle(cellStyleFieldNeedCalc(sheet,false)); } else { roadDeparture.setCellStyle(cellStyleField(sheet)); } Cell stationDestination = row.createCell(3); stationDestination.setCellValue(route.getStationDestination().getNameStation()); if (route.isFlagNeedCalc()) { stationDestination.setCellStyle(cellStyleFieldNeedCalc(sheet,false)); } else { stationDestination.setCellStyle(cellStyleField(sheet)); } Cell roadDestination = row.createCell(4); roadDestination.setCellValue(route.getStationDestination().getRoad().getNameRoad()); if (route.isFlagNeedCalc()) { roadDestination.setCellStyle(cellStyleFieldNeedCalc(sheet,false)); } else { roadDestination.setCellStyle(cellStyleField(sheet)); } Cell cargo = row.createCell(5); if (route.getRate() != 0) { cargo.setCellValue(route.getCargo().getNameCargo()); cargo.setCellStyle(cellStyleFieldCargo(sheet)); } else { cargo.setCellValue("Порожняк"); cargo.setCellStyle(cellStyleFieldCargo(sheet)); } Cell distance = row.createCell(6); distance.setCellValue(route.getDistance()); distance.setCellStyle(cellStyleFieldFormat(sheet, true)); Cell countDays = row.createCell(7); countDays.setCellValue(route.getCountDays()); countDays.setCellStyle(cellStyleField(sheet)); Cell daysLoadUnload = row.createCell(8); daysLoadUnload.setCellValue(route.getCountDaysLoadAndUnload()); daysLoadUnload.setCellStyle(cellStyleField(sheet)); Cell fullCountDays = row.createCell(9); fullCountDays.setCellFormula("SUM(H" + num + ":I" + num + ")"); fullCountDays.setCellStyle(cellStyleField(sheet)); Cell call9 = row.createCell(10); call9.setCellValue("поваг"); call9.setCellStyle(cellStyleField(sheet)); sheet.autoSizeColumn(10); Cell rate = row.createCell(11); if (route.getRate() == 0) { rate.setCellStyle(cellStyleFieldNull(sheet)); } else { rate.setCellValue(route.getRate()); if (route.isFlagNeedCalc()) { rate.setCellStyle(cellStyleFieldNeedCalc(sheet, false)); } else { rate.setCellStyle(cellStyleFieldFormat(sheet, false)); } } Cell tariff = row.createCell(12); if (route.getTariff() == 0) { tariff.setCellStyle(cellStyleFieldNull(sheet)); } else { tariff.setCellValue(route.getTariff()); tariff.setCellStyle(cellStyleFieldFormat(sheet, false)); } Cell rateTariff = row.createCell(13); rateTariff.setCellFormula("L" + num + "-M" + num); rateTariff.setCellStyle(cellStyleFieldFormat(sheet, false)); sheet.setColumnWidth(13, 3182); Cell cell13 = row.createCell(14); cell13.setCellValue(""); cell13.setCellStyle(cellStyleFieldRightBold(sheet)); rowFirstRoute++; } // Номер последней ячейки данных int lastNumberCell = rowFirstRoute; int totalYieldNum = rowFirstRoute + 1; XSSFRow row = sheet.createRow(rowFirstRoute); Cell cell0 = row.createCell(0); cell0.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell1 = row.createCell(1); cell1.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell2 = row.createCell(2); cell2.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell3 = row.createCell(3); cell3.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell4 = row.createCell(4); cell4.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell5 = row.createCell(5); cell5.setCellStyle(cellStyleFieldTotal(sheet)); sheet.addMergedRegion(new CellRangeAddress(rowFirstRoute, rowFirstRoute, 0, 5)); // Строка итоговых расчетов Cell totalDistance = row.createCell(6); totalDistance.setCellFormula("SUM(G" + firstNumberCell + ":G" + lastNumberCell + ")"); totalDistance.setCellStyle(cellStyleFieldTotalFormat(sheet, true)); Cell totalCountDays = row.createCell(7); totalCountDays.setCellFormula("SUM(H" + firstNumberCell + ":H" + lastNumberCell + ")"); totalCountDays.setCellStyle(cellStyleFieldTotal(sheet)); Cell totalCountLoadUnloadDays = row.createCell(8); totalCountLoadUnloadDays.setCellFormula("SUM(I" + firstNumberCell + ":I" + lastNumberCell + ")"); totalCountLoadUnloadDays.setCellStyle(cellStyleFieldTotal(sheet)); Cell totalFullCountDays = row.createCell(9); totalFullCountDays.setCellFormula("SUM(J" + firstNumberCell + ":J" + lastNumberCell + ")"); totalFullCountDays.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell10 = row.createCell(10); cell10.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell11 = row.createCell(11); cell11.setCellStyle(cellStyleFieldTotal(sheet)); Cell cell12 = row.createCell(12); cell12.setCellStyle(cellStyleFieldTotal(sheet)); Cell totalRateTariff = row.createCell(13); totalRateTariff.setCellFormula("SUM(N" + firstNumberCell + ":N" + lastNumberCell + ")"); totalRateTariff.setCellStyle(cellStyleFieldTotalFormat(sheet,false)); Cell yield = row.createCell(14); yield.setCellFormula("N" + totalYieldNum + "/J" + totalYieldNum); yield.setCellStyle(cellStyleFieldTotalRight(sheet, false)); sheet.autoSizeColumn(14); Cell cell15 = row.createCell(15); cell15.setCellValue(totalModel.getYield()); Cell cell16 = row.createCell(16); cell16.setCellFormula("P" + totalYieldNum + "-O" + totalYieldNum); Cell cell17 = row.createCell(17); cell17.setCellFormula("Q" + totalYieldNum + "*J" + totalYieldNum); rowStartHead = lastNumberCell + 1; numberTable++; } } xssfWorkbook.write(outputStream); outputStream.flush(); outputStream.close(); }
  • Try to comment out everything that doesn’t concern data insertion. Styles, fonts and so on. Slow down? - Denis
  • @ Denis tried, a little faster, but still. It seems to me that the reason is that I do all this in one method. I tried to transfer all this to a separate method and call it every time, while in this method I open a temporary file where I write everything down. But now, in regret, the constant overwriting of the file leads to a restart of the volume. - Vladislav
  • I remembered about the reboot, my reloadable parameter was turned on, which tracks the change in context and restarts the application - Vladislav 6:43 pm
  • @Denis did again testing without styles, and the truth is, the work time is reduced by 100 times. Most likely these are styles. But it is a pity without them I can not. I think to try SXSSFWorkbook - Vladislav
  • write correctly, get styles 1 time. Take your time, keep looking for the cause of the brakes. You are at the beginning of the journey;) - Denis

1 answer 1

Try to calculate the cellStyleField(sheet) style once and other styles too and take it out of the loop, then substitute only the values.

Now you have such a construction:

  for (TotalModel totalModel: listTotalModel) { //заполнение шапки for (int i = rowStartHead; i <= rowFinishHead; i++) { ... <cell>.setCellStyle(cellStyleHead(sheet)); ... } //заполнение данными for (Route route : totalModel.getTotalList()) { ... <cell>.setCellStyle(cellStyleField(sheet)); ... } }

I suggest trying something like this:

 style1 = ... style2 = ... header = buildHeader(); for (TotalModel totalModel: listTotalModel) { setHeader(header ); setData(sheet); setStyles(sheet); } private void buildHeader(XSSFSheet sheet){ ... } private void setHeader(XSSFSheet sheet){ ... } private void setData(XSSFSheet sheet){ ... } private void setStyles(XSSFSheet sheet){ <range1>.setCellStyle(style1); <range2>.setCellStyle(style2); }

So get rid of the numerous tasks of styles for each cell, and it will be easier to understand the code in the future when asked to modify the report.

  • I rendered all the styles into separate classes, and then once more at the beginning I initialized this class CellStyleHead cellStyleHead = new CellStyleHead (xssfWorkbook); and further, during setCellStyle, it caused getting the style .setCellStyle (cellStyleHead.getXssfCellStyle ()); But unfortunately, the work time did not accelerate anyway ((( - Vladislav
  • I have already started to think, maybe this is a problem in the setCellStyle method - Vladislav
  • True, your code can not understand a bit. - Vladislav
  • @ Vladislav, what exactly could not understand? The point is not to assign a style to each cell while filling the sheet with data. And in the setStyles( XSSFSheet sheet ) method setStyles( XSSFSheet sheet ) select the desired ranges and assign the appropriate styles. - Z.John
  • Yeah, now I understand, but my problem is that I don’t know this range initially, I have tables, and each can have from 2 to 10 rows plus a total row. If only after each iteration of the totalmodel, do so. - Vladislav