The biggest change in SXSSFWorkbook since POI 3.17 is the support for ZIP64 mode. https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#setZip64Mode-org.apache.commons.compress.archivers.zip.Zip64Mode-
https://commons.apache.org/proper/commons-compress/apidocs/org/apache/commons/compress/archivers/zip/Zip64Mode.html https://rzymek.github.io/post/excel-zip64/ Try this: SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();sxssfWorkbook.setZip64Mode(Zip64Mode.Never); With Zip64Mode.Never, POI 5 will behave more like POI 3.17. You should also contact the maintainers of that OpenXml lib you mentioned to ask them why your workbook won't open when you use their lib. On Sunday 17 July 2022 at 17:50:45 IST, Levinson, Boaz <[email protected]> wrote: At first I thought it might be that the workbook is not disposed as in this thread : https://stackoverflow.com/questions/70624232/xlsx-file-content-is-corrupted-after-upgrading-apache-poi-from-3-17-to-5-0-0-ver but this wasn’t the case . I was closing it explicitly by workbook.close() , I even had a workbook.dispose() as allowed to do on SXSSFWorkbook. I then switched to try-with-resource , none helped . Microsoft Excel opens the file without errors, but another team in our company is trying to open it using OpenXml and get the error publicTest(string fileName) { //Stream = new StreamReader(fileName); using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName,false)) // this will throw exception { } } internalclassProgram { staticvoid Main(string[] args) { try { Test test =new Test("C:\\DataExportFolder\\readyReports\\10018553p3eTkzU\\1002855\\local\\5054b652-e1ee-4a3b-9083-93bb1836f969.xlsx"); } catch (Exception ex) { Console.WriteLine(ex.Message); } } } I tried to Isolate the place where corruption happens , closing the workbook as early as possible , after creating the summary in createSummarySheet() (seen in below code) But anything I do results with a corrupted files in OpenXml. Note : when using XSSFWorkbook instead of SXSSFWorkbook , I have no more corruptions , and this started from the upgrade from 3.17 to 5.2 and even in latest 5.2.2 POI version. I also tried returning the workbook object at each method call to keep its reference and then close the object (the code doesn’t show that entirely now , as I switched to try-with-resource and I expect it to work. ) The object doesn’t seem closed even after close is called (debugging the internal poi lib) , as the GC may have not worked yet . but even if I kill the debugger and stop the app and open the files long after creation They are still corrupted . I tried looking for places that I might do something wrong with the SXSSFWorkbook but I spotted nothing special , I didn’t see any overriding of cell \ row data as suggested in some forums , since SXSSF is stream read only object. Maybe people here can spot it better than me.. I used 7zip to open the corrupted file vs the uncorrupted file from version 3.17 , and noticed there is a missing Modified date in the corrupted one and some size differences . Also the new version is readable while the old and working poi 3.17 version sheet.xml is unreadable (could be encrypted ? ) for example , I have 2 sheets , I opened the smaller one (summary) : Uncorrupted corrupted This is how the corrupted sheet looks like <?xml version="1.0" encoding="UTF-8"?> <worksheet xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main><dimension ref="A1:E7"/><sheetViews><sheetView workbookViewId="0" tabSelected="true"/></sheetViews><sheetFormatPr defaultRowHeight="15.0"/><cols><col min="1" max="1" width="40.0" customWidth="true"/><col min="2" max="2" width="40.0" customWidth="true"/></cols><sheetData><row r="1"><c r="A1" t="s" s="1"><v>0</v></c></row><row r="2"><c r="A2" t="s" s="3"><v>1</v></c><c r="B2"/></row><row r="3"><c r="A3" t="s" s="3"><v>2</v></c><c r="B3" t="s"><v>3</v></c></row><row r="4"><c r="A4" t="s" s="3"><v>4</v></c><c r="B4" t="s"><v>5</v></c></row><row r="5"><c r="A5" t="s" s="3"><v>6</v></c><c r="B5" t="s"><v>3522</v></c><c r="C5" t="n"><v>3115.0</v></c><c r="D5" t="n"><v>3115.0</v></c></row><row r="7"><c r="A7" t="s" s="3"><v>8</v></c><c r="B7"/></row></sheetData><pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/></worksheet> And this is how the uncorrupted sheet looks like : <?xml version="1.0" encoding="UTF-8"?> <worksheet xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main><dimension ref="A1"/><sheetViews><sheetView workbookViewId="0" tabSelected="true"/></sheetViews><sheetFormatPr defaultRowHeight="15.0"/><cols><col min="1" max="1" width="40.0" customWidth="true"/><col min="2" max="2" width="40.0" customWidth="true"/></cols><sheetData> <row r="1"> <c r="A1" s="1" t="inlineStr"><is><t>Interaction Data Export</t></is></c></row> <row r="2"> <c r="A2" s="3" t="inlineStr"><is><t>Project:</t></is></c><c r="B2"></c></row> <row r="3"> <c r="A3" s="3" t="inlineStr"><is><t>Created:</t></is></c><c r="B3" t="inlineStr"><is><t>07-17-2022 16:08:01</t></is></c></row> <row r="4"> <c r="A4" s="3" t="inlineStr"><is><t>By:</t></is></c><c r="B4" t="inlineStr"><is><t>wsuperuser</t></is></c></row> <row r="5"> <c r="A5" s="3" t="inlineStr"><is><t>Number of results:</t></is></c><c r="B5" t="inlineStr"><is><t>3115 out of 3115 (100.0%)</t></is></c><c r="C5" t="n"><v>3115.0</v></c><c r="D5" t="n"><v>3115.0</v></c></row> <row r="7"> <c r="A7" s="3" t="inlineStr"><is><t>Export_Categories_Query_Title</t></is></c><c r="B7"></c></row> </sheetData><pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/></worksheet> Reminding that Microsoft excel opens both without any issue , and the data between them is identical , the assumption is that The excel is somehow fixing the invalid \ corrupted areas somehow but I saw no error log file added to C:\Users\<myuser>\AppData\Local\Temp when Opening it with excel . the only issue happens in OpenXml , using the most standard way to open an xlsx file (also, working fine on files created by POI 3.17) . The commons-compress version POI is using is the latest (1.21) , I deleted it and re-ran maven to see that it gets back to m2 folders So, it’s either a bug , or a new enforcement of SXSSFWorkbook of a certain unwelcomed things I do with the object, which I cannot spot in my code. Hope you can assist .. Here is the entire code that uses SXSSFWorkbook , and inner methods that are relevant : public void generateReport(Report report, HashMap<String, LocalizationBundle> localizationMapping, boolean isLocalizationEnabled, CategoriesBundle categoriesBundle, String generateStartTime, int backgroundNumOfRows, String reportZipPassword) throws Exception { String reportGuid = report.getReportGUID(); String userId = report.getUserId(); String reportName = report.getName(); String outputFileName = ""; String reportParameters = String.format("-> Report guid: %s report name: %s user id: %s . ", reportGuid, reportName, userId); AppLogger.info(LoggerCategories.DataAccessLayer, String.format("In dal.generateReport() -> Started creating report. reportParameters: %s", reportParameters)); SheetInfo sheetInfoObject = null; try { // The Current row number int eh current sheet final String categoryHitSign = "1"; final String categoryNotHitSign = ""; String csvFolder; // Create temporary folder for the report. // build since for sometimes the first report deleted all files of the other threads File poiTempFileDirectory = new File(System.getProperty("java.io.tmpdir"), reportGuid); TempFileCreationStrategy newTempFileCreationStrategy = createTempFileCreationStrategy(poiTempFileDirectory); TempFile.setTempFileCreationStrategy(newTempFileCreationStrategy); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("poiTempFileDirectory: %s. reportParameters: %s", poiTempFileDirectory, reportParameters)); //The POI objects try (SXSSFWorkbook workBook = new SXSSFWorkbook(ReportsConstants.NUM_OF_ROWS_TO_FLUSH)) { workBook.setCompressTempFiles(true); // workBook.setZip64Mode(Zip64Mode.Always); setCellStyles(workBook); // Create the CsvParser CsvParserSettings settings = new CsvParserSettings(); settings.setMaxCharsPerColumn(csvMaxCharPerColumn); settings.setLineSeparatorDetectionEnabled(true); CsvParser csvParser = new CsvParser(settings); // Get the localization bundle LocalizationBundle localizationBundle = this.getLocalizationBundle(localizationMapping, report.getLanguageCode(), report.getCountryCode()); // Get the folder of the CSV files csvFolder = getReportTempFolder(report); //The CSV input file list (sorted). These later will be deleted File[] csvFilesList = this.getSortedCSVFiles(csvFolder); if (csvFilesList == null) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - CSV folder %s is empty. reportParameters: %s", csvFolder, reportParameters)); // no way to continue if the the files array is null. return; } StringBuilder csvFilesBuilder = new StringBuilder(); Arrays.stream(csvFilesList).forEach(file -> csvFilesBuilder.append(file.getPath()).append(" \n")); AppLogger.info(LoggerCategories.DataAccessLayer, String.format("csvFiles: %s , reportParameters: %s", csvFilesBuilder.toString(), reportParameters)); // Open the output file (xlsx\zip file) outputFileName = this.getReadyReportFilePath(report); try (FileOutputStream outputStream = createFileStream(outputFileName, reportParameters)) { // Create the first sheet with the report summary this.createSummarySheet(workBook, report, localizationBundle, isLocalizationEnabled, generateStartTime); HashSet<String> categoriesFoundInReport = new HashSet<>(); // conf default is set if VTA split category was empty boolean categoriesSplitFlagOn = this.getSplitCategoriesFlag(report); if (categoriesSplitFlagOn) { categoriesFoundInReport = findCategoriesInReport(true, csvParser, csvFilesList, categoriesBundle); } csvParser = new CsvParser(settings); // Iterate on each file in the input folder //=============================================================== AppLogger.info(LoggerCategories.DataAccessLayer, String.format("Beginning csvParser work on files, Displaying sorted csv files list paths and row values in TRACE mode only. reportParameters: %s", reportParameters)); // A single line from the CSV file sheetInfoObject = parseCsvFiles(report, csvFilesList, csvParser, reportParameters, categoriesBundle, categoriesFoundInReport, categoryHitSign, categoryNotHitSign, workBook, localizationBundle, isLocalizationEnabled, categoriesSplitFlagOn); // Add the num of rows of the last sheet (the bulk that was not added in the modulo condition: if (sheetInfo.getSheetRowNum() % this.numRowsInSheet == 0) - without the columns header row. sheetInfoObject.setTotalNumOfRows(sheetInfoObject.getTotalNumOfRows() + sheetInfoObject.getSheetRowNum()); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportsFileManager - generateReport. updating summary sheet with total num of rows. reportParameters: %s", reportParameters)); this.updateSummaryTotalRows(workBook, sheetInfoObject.getTotalNumOfRows(), backgroundNumOfRows, localizationBundle); // Write the Stream and close it AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportsFileManager - generateReport. write the output stream. reportParameters: %s", reportParameters)); workBook.write(outputStream); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportsFileManager - generateReport. closed the output stream. reportParameters: %s", reportParameters)); // On enriched mode if (report.getReportTemplateObject().getTemplateType().equalsIgnoreCase(Configuration.EnrichedLicense)) { AppLogger.debug(LoggerCategories.DataAccessLayer, "In Enriched Mode, Zipping output."); // Zip the csv output and password protect it. this.zipAndProtectCsvOutput(outputFileName, reportZipPassword); } } //workBook.dispose(); } } catch (FileNotFoundException e) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - generateReport. FileNotFoundException. Error: %s, Cause: %s reportParameters: %s, %s stack trace: %s", e.getMessage(), e.getCause(), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n"))); throw e; } catch (IOException e) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - generateReport. IOException. Error: %s, Cause: %s reportParameters: %s stack trace: %s", e.getMessage(), e.getCause(), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n"))); throw e; } catch (Exception e) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - generateReport. General Exception Error: %s, Cause: %s, reportParameters: %s, stack trace: %s", e.getMessage(), e.getCause(), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n"))); throw e; } finally { AppLogger.info(LoggerCategories.DataAccessLayer, String.format("Finished creating report reportParameters: %s", reportParameters)); try { //deleting the temporary files if (report.getReportTemplateObject().getTemplateType().equalsIgnoreCase(Configuration.EnrichedLicense) && !StringUtils.isEmptyOrWhiteSpace(outputFileName)) { // After zipping and password protecting it, try to delete the original file . log an error message if it can't for some reason. this.deleteReadyReportFileByPath(outputFileName); } deletePoiTemporaryFolder(reportGuid); // Delete the temporary CSV folder this.deleteCSVFolder(report); } catch (Exception finalException) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager- generateReport's Finally . General Exception Error: %s, Cause: %s, reportParameters: %s, stack trace: %s", finalException.getMessage(), finalException.getCause(), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n"))); } } } /** * This function is implement the interface of the file creation strategy for the POI files * It's implement only the file creation (which create for each SHEET) * The implementation needed for create each report in it's own directory * (written as a bug fix when one thread delete files from the main POI folder and create exception to other threads) * * @param poiTempFileDirectory - the report temp directory * @return */ private TempFileCreationStrategy createTempFileCreationStrategy(File poiTempFileDirectory) { return new TempFileCreationStrategy() { @Override public File createTempFile(String prefix, String suffix) throws IOException { if (!poiTempFileDirectory.exists()) { poiTempFileDirectory.mkdir(); } File newFile = File.createTempFile(prefix, suffix, poiTempFileDirectory); return newFile; } @Override public File createTempDirectory(String prefix) throws IOException { return null; } }; } private SXSSFWorkbook setCellStyles(SXSSFWorkbook workBook) { // Set the summary title style Font summaryTitleFont = workBook.createFont(); summaryTitleFont.setColor(IndexedColors.WHITE.getIndex()); summaryTitleFont.setBold(true); summaryTitleFont.setFontHeightInPoints(ReportsConstants.SUMMARY_REPORT_TITLE_FONT_SIZE); summaryTitleStyle = workBook.createCellStyle(); summaryTitleStyle.setAlignment(HorizontalAlignment.CENTER); summaryTitleStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex()); summaryTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); summaryTitleStyle.setFont(summaryTitleFont); // Create the fields font style for the header Font columnsHeadersFont = workBook.createFont(); columnsHeadersFont.setColor(IndexedColors.WHITE.getIndex()); columnsHeadersFont.setBold(true); columnsHeadersFont.setFontHeightInPoints(ReportsConstants.REPORT_HEADERS_FONT_SIZE); columnsHeadersStyle = workBook.createCellStyle(); columnsHeadersStyle.setAlignment(HorizontalAlignment.CENTER); columnsHeadersStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex()); columnsHeadersStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); columnsHeadersStyle.setFont(columnsHeadersFont); // Create the fields font style for the summary fields Font summaryFieldsFont = workBook.createFont(); summaryFieldsFont.setBold(true); summaryFieldsStyle = workBook.createCellStyle(); summaryFieldsStyle.setFont(summaryFieldsFont); return workBook; } private FileOutputStream createFileStream(String outputFileName, String reportParameters) throws Exception { FileOutputStream outputStream = null; try { AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("Got output file name: %s for report - report parameters: %s , creating file output stream...", outputFileName, reportParameters)); outputStream = new FileOutputStream(outputFileName, false); } catch (Exception outputStreamEx) { try { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager while creating FileOutputStream " + "could not create a file with the path %s. attempting to create the entire path and file" + "Error: %s, Cause: %s, report parameters: %s stack trace: %s", outputFileName, outputStreamEx.getMessage(), outputStreamEx.getCause()), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n")); AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - creating new file %s report parameters: %s", outputFileName, reportParameters)); File f = new File(outputFileName); AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - done creating new file %s report parameters: %s", outputFileName, reportParameters)); if (f.getParentFile() != null && !f.getParentFile().exists()) { AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - the parent file %s for outputFileName: %s, did not exist for report, making directories. report parameters: %s", f.getParentFile().getPath(), outputFileName, reportParameters)); if (f.getParentFile().mkdirs()) { AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - the parent directories of %s were created " + "report parameters: %s ", f.getParentFile().getPath(), reportParameters)); } else { AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - could not create parent directories for %s" + "report parameters: %s ", f.getParentFile().getPath(), reportParameters)); } if (!f.exists()) { f.createNewFile(); } } else { throw new Exception("Could not get parent file."); } outputStream = new FileOutputStream(outputFileName, false); } catch (Exception ex) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager while second attempt of creating FileOutputStream in path %s" + " please contact system admin / support. Error: %s, Cause: %s report parameters: %s, stack trace: %s", outputFileName, ex.getMessage(), ex.getCause()), reportParameters, Arrays.toString(Thread.currentThread().getStackTrace()).replace(",", "%n")); throw ex; } } return outputStream; } /** * This function is writing the report's summary sheet (such as: date, numOfRows...) * Each pair of key and value in a row * * @param workBook - The current WorkBook * @param localizationBundle - The localization bundle * @param isLocalizationEnabled - indicates if localization needed * @param generateStartTime - The time when generating files started * @param report - The report object */ private SXSSFWorkbook createSummarySheet(SXSSFWorkbook workBook, Report report, LocalizationBundle localizationBundle, boolean isLocalizationEnabled, String generateStartTime) { int rowNum = 0; String tenantAndChannel = String.format("%s_%s", report.getTenantId(), report.getChannel()); // The sheet name (reportName + "summary") String sheetName = String.format("%s %s", report.getName(), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_SHEET_SUFFIX_KEY, tenantAndChannel)); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("Before creating new sheet on report guid %s , report name: %s , sheet name %s", report.getReportGUID(), report.getName(), sheetName)); // Crate a new sheet SXSSFSheet currentSheet = workBook.createSheet(sheetName); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("After creating new sheet on report guid %s , report name: %s , sheet name %s", report.getReportGUID(), report.getName(), sheetName)); // Insert the Title (with the style) this.insertSummaryTitle(currentSheet.createRow(rowNum++), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_REPORT_TITLE_KEY, tenantAndChannel)); // Insert the fields this.insertSummaryField(currentSheet.createRow(rowNum++), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_PROJECT_FIELD_KEY, tenantAndChannel), report.getChannelDisplayName()); this.insertSummaryField(currentSheet.createRow(rowNum++), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_CREATED_FIELD_KEY, tenantAndChannel), getFormatedDate(generateStartTime)); this.insertSummaryField(currentSheet.createRow(rowNum++), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_BY_FIELD_KEY, tenantAndChannel), report.getReportCreator()); this.insertSummaryField(currentSheet.createRow(rowNum++), getLocalizationTransfer(localizationBundle, isLocalizationEnabled, ReportsConstants.SUMMARY_NUM_OF_RESULT_FIELD_KEY, tenantAndChannel), ""); // Keep the num of results row index this.numOfResultsRowIndex = rowNum - 1; if (!StringUtils.isEmptyOrWhiteSpace(report.getQueryTitle())) { //Add empty line rowNum++; this.insertSummaryField(currentSheet.createRow(rowNum++), report.getQueryTitle(), null); // Insert the Query and Background hashMaps this.insertHashMap(currentSheet, report.getQueryMap()); // Get the last row (add "1" because it's start from 0) rowNum = currentSheet.getLastRowNum() + 1; } // If there is a background query if (report.getBackgroundMap().size() > 0) { //Add empty line rowNum++; this.insertSummaryField(currentSheet.createRow(rowNum++), report.getBackgroundTitle(), null); // Insert the Query and Background hashMaps this.insertHashMap(currentSheet, report.getBackgroundMap()); } // Set the width of the columns currentSheet.setColumnWidth(0, ReportsConstants.SUMMARY_COLUMNS_WIDTH); currentSheet.setColumnWidth(1, ReportsConstants.SUMMARY_COLUMNS_WIDTH); return workBook; } private SheetInfo parseCsvFiles(Report report, File[] csvFilesList, CsvParser csvParser, String reportParameters, CategoriesBundle categoriesBundle, HashSet<String> categoriesFoundInReport, String categoryHitSign, String categoryNotHitSign, SXSSFWorkbook workBook, LocalizationBundle localizationBundle, boolean isLocalizationEnabled, boolean categoriesSplitFlagOn) { int categoriesColumnsIndex = -1; int textTotalColumnsIndex = -1; boolean isFirstCsvFile = true; String[] line; //the Header string to be kept for each sheet and the categories columnsIndex String[] headerLine = null; // The Current sheet index (the row number can't be more then "numOfRowsInSheet", the XSL max row limitation) SheetInfo sheetInfoObject = new SheetInfo(); for (File file : csvFilesList) { try { AppLogger.trace(LoggerCategories.DataAccessLayer, String.format("Sorted csv files list path: %s reportParameters: %s - parsing the csv file now..", file.getPath(), reportParameters)); csvParser.beginParsing(file); // Start reading the file (only if there are lines to read) while ((line = csvParser.parseNext()) != null) { // If we are in the first csv file - we need to take the columns headers if (isFirstCsvFile == true) { headerLine = line; // Get the categories column index categoriesColumnsIndex = Arrays.asList(headerLine).indexOf(ReportsConstants.CATEGORIES_COLUMN_KEY); isFirstCsvFile = false; //check if the report has text_<language>_total String textTotalFL = findTextInRequest(String.join(",", line)); textTotalColumnsIndex = Arrays.asList(headerLine).indexOf(textTotalFL); // Go to the next line continue; } //if text total column exists and we are not running on headers row (first line) if (textTotalColumnsIndex != -1 && !isFirstCsvFile) { try { AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportsFileManager.generateReport() - line.length is %s. reportParameters: %s", line.length, reportParameters)); //Replace escaped commas with regular commas ("\," -> ",") on last row if (line[line.length - 1] != null) { line[line.length - 1] = line[line.length - 1].replace("\\,", ","); } else { AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportsFileManager.generateReport() - the line[line.length - 1] is null. interactionId is :%s. reportParameters: %s ", line[0], reportParameters)); } } catch (Exception ex) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - replace commas. Row number is : %s .Error: %s, Cause: %s report parameters: %s", sheetInfoObject.getSheetRowNum(), ex.getMessage(), ex.getCause()), reportParameters); ex.printStackTrace(); throw ex; } } sheetInfoObject = createRowInCurrentOrNewSheet(workBook, headerLine, categoriesFoundInReport, categoryHitSign, categoryNotHitSign, reportParameters, report, localizationBundle, categoriesBundle, categoriesColumnsIndex, line, sheetInfoObject, isLocalizationEnabled, categoriesSplitFlagOn, getNumRowsInSheet(report)); } } catch (Exception ex) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - parse csv file operation . Error: %s, Cause: %s reportParameters: %s", ex.getMessage(), ex.getCause()), reportParameters); ex.printStackTrace(); throw ex; } } return sheetInfoObject; } private SXSSFWorkbook updateSummaryTotalRows(SXSSFWorkbook workBook, int totalNumOfRows, int backgroundNumOfRows, LocalizationBundle localizationBundle) { Row row; Cell cell; String cellValue; int cellIndex = 1; try { // Get the summary Sheet SXSSFSheet summarySheet = workBook.getSheetAt(0); // Get the relevant row row = summarySheet.getRow(this.numOfResultsRowIndex); // Get the cell cell = row.getCell(cellIndex); // calculate the percentage float percentage = ((float) (totalNumOfRows * ReportsConstants.MAGIC_NUMBER_100) / backgroundNumOfRows); cellValue = String.format("%s %s %s (%.1f%%)", totalNumOfRows, localizationBundle.getTranslatedValue(ReportsConstants.SUMMARY_OUT_OF_KEY, ""), backgroundNumOfRows, percentage); row.createCell(++cellIndex).setCellValue(totalNumOfRows); row.createCell(++cellIndex).setCellValue(backgroundNumOfRows); // Update the cell with value cell.setCellValue(cellValue); } catch (Exception e) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("An exception occurred in ReportsFileManager - updateSummaryTotalRows. errorMessage: %s", e.getMessage())); } return workBook; } /** * This function id deleting the POI temporary folder * Since the "Dispose" doesn't release the file immidiatlly, we retries a few time to delete the folder * * @param reportGuid */ private void deletePoiTemporaryFolder(String reportGuid) { int count = 0; File poiTempFileDirectory = new File(System.getProperty("java.io.tmpdir"), reportGuid); AppLogger.info(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - deletePoiTemporaryFolder - Delete POI temporary folder: %s ", poiTempFileDirectory.toString())); try { long startDeleteDirectory = System.currentTimeMillis(); if (poiTempFileDirectory.exists()) { // Rerun again while their are files in the folder (because the "dispose" doesn't always delete the files on time) while (poiTempFileDirectory.list().length > 0 && count < deleteTemporaryRetries) { count++; Thread.sleep(processingSleep); } FileUtils.deleteDirectory(poiTempFileDirectory); long endDeleteDirectory = System.currentTimeMillis(); AppLogger.debug(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - deletePoiTemporaryFolder - delete temporary POI folder after: %d milliseconds", endDeleteDirectory - startDeleteDirectory)); } } catch (InterruptedException ex) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - deletePoiTemporaryFolder - sleep InterruptedException: %s", ex.getMessage())); } catch (IOException ex) { AppLogger.error(LoggerCategories.DataAccessLayer, String.format("ReportFileManager - deletePoiTemporaryFolder - unable to Delete POI temporary folder: %s ,ErrorMessage: %s", poiTempFileDirectory.toString(), ex.getMessage())); } } This electronic message may contain proprietary and confidential information of Verint Systems Inc., its affiliates and/or subsidiaries. The information is intended to be for the use of the individual(s) or entity(ies) named above. If you are not the intended recipient (or authorized to receive this e-mail for the intended recipient), you may not use, copy, disclose or distribute to anyone this message or any information contained in this message. If you have received this electronic message in error, please notify us by replying to this e-mail.
