#java #apache-poi
#java #apache-poi
Вопрос:
У меня проблема с производительностью при записи файла в POI. Я пишу около 400 строк (100 итераций), а время работы составляет около 2 минут. Это очень долго. Я использую стиль, объединяю строки, автоматически изменяю размер. Если около 800 итераций, то время работы 3 часа.
первая итерация — 60-70 мс, следующая увеличена на 10-20 мс. На 35 итерации время работы 1 сек.
Но я прокомментировал setStyle тогда раз в 5-10 секунд. Не могли бы вы, пожалуйста, помочь мне? Может быть, использовать SXFFWorkbook?
мой код:
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();
}
private static XSSFFont getFont(XSSFSheet sheet) {
XSSFFont font = sheet.getWorkbook().createFont();
font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
font.setFontName("Times New Roman");
font.setFontHeight(12.0);
return font;
}
private static short format(XSSFSheet sheet, boolean with00) {
XSSFDataFormat dataFormat = sheet.getWorkbook().createDataFormat();
return with00 ? dataFormat.getFormat("#,##0") : dataFormat.getFormat("#,##0.00");
}
private static XSSFCellStyle cellStyleHead(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 153)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleHeadBottom(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 153)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleHeadRight(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 153)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleField(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldFormat(XSSFSheet sheet, boolean with00) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
cellStyle.setDataFormat(format(sheet, with00));
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldNull(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
XSSFFont font = getFont(sheet);
font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
cellStyle.setFont(font);
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldRightBold(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldCargo(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldNeedCalc(XSSFSheet sheet, boolean with00) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
XSSFFont font = getFont(sheet);
font.setBold(true);
cellStyle.setFont(font);
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(214, 214, 214)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setDataFormat(format(sheet, with00));
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldTotal(XSSFSheet sheet) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(204, 255, 204)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldTotalFormat(XSSFSheet sheet, boolean with00) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(204, 255, 204)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cellStyle.setDataFormat(format(sheet, with00));
return cellStyle;
}
private static XSSFCellStyle cellStyleFieldTotalRight(XSSFSheet sheet, boolean with00) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setFont(getFont(sheet));
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderTop(BorderStyle.DOTTED);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.DOTTED);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(new XSSFColor(new Color(204, 255, 204)));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cellStyle.setDataFormat(format(sheet, with00));
return cellStyle;
}
Комментарии:
1. @AxelRichter Я добавил все методы. Эти методы — это цветовой стиль и стиль границ. Мне нужны они
2. @AxelRichter Я создал классы CellStyleHead cellStyleHead = new CellStyleHead(XSSFWorkbook); … CellStyleField cellStyleField = новое поле CellStyleField(XSSFWorkbook);. И вызвал setCellStyle(cellStyleHead.getXssfCellStyle()); Но время работы не меняется ((
3. общедоступное поле CellStyleField(XSSFWorkbook XSSFWorkbook) { this.XSSFCellStyle = XSSFWorkbook.createCellStyle(); cellStyleField(); } частное пустое поле cellStyleField() { // XSSFCellStyle CellStyle = sheet.getWorkbook().createCellStyle(); //CellStyle.setFont(getFont(лист)); XSSFCellStyle.setBorderBottom(стиль границы. ПУНКТИРНО);
4. Я создал стиль с помощью конструктора в классе
5. может быть, проблема в setCellStyle() ?