Thank you very much for that example. It was very helpful. Based on the example, I changed my code, changing back the cellStyle to true in the CellCopyPolicy and I refactored the method copyRows from org.apache.poi.xssf.usermodel.XSSFSheet and method copyRowFrom from org.apache.poi.xssf.usermodel.XSSFRow instantiating a CellCopyContext in copyRows. The problem though is when I ran the following worksheet through, the resulting workbook with a single sheet has date formatted columns (as expected) in the column with header transaction_date is but the values in the column header transaction time is not date formatted where it should be (as it is in the original file). Attached is my new code and the sample file. Please advise. Thank you so much!
On Tue, Feb 11, 2025 at 4:18 PM PJ Fanning <fannin...@apache.org> wrote: > That copyRows method doesn't allow you to specify the CellCopyContext. > We probably should add a variant of copyRows that supports this extra > param. The context keeps track of styles so that it avoids adding the > same style over and over - i.e. it spots the duplicate styles. > > This example might be useful > > https://github.com/pjfanning/excel-streaming-reader/blob/main/src/test/java/com/github/pjfanning/xlsx/CopyToSXSSFUtil.java > > CellUtil.copyCell - this method can take a CellCopyContext instance. > > On Tue, 11 Feb 2025 at 17:48, Dan S <dsti...@gmail.com> wrote: > > > > I hope I am addressing the right list. I am an Apache NIFI developer and > I > > have developed an Apache NIFI processor named SplitExcel which splits a > > multi sheet Microsoft Excel spreadsheet into multiple Microsoft Excel > > spreadsheets where each sheet from the original file is converted to an > > individual spreadsheet. Source code for this can be found here > > < > https://github.com/apache/nifi/blob/main/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/processors/excel/SplitExcel.java > > > > (lines > > 165-188). > > In order to accomplish this, I use the > > com.github.pjfanning.xlsx.StreamingReader to stream the Excel workbook > > which allows for easy looping over each sheet. I create a new > XSSFWorkbook > > for each sheet there is in the existing workbook and I use XSSFSheet > > copyRows method to copy the rows from the existing sheet to a new sheet > to > > place in the new XSSFWorkbook. The CellCopyPolicy used has cellStyle set > to > > false in order to avoid exceeding the maximum number of cell styles > (fixed > > in NIFI-13726 <https://issues.apache.org/jira/browse/NIFI-13726>). The > > drawback though of using cellStyle set to false is that any columns which > > have dates, its date formatting is lost and what is left is a meaningless > > number (reported in NIFI-14106 > > <https://issues.apache.org/jira/browse/NIFI-14106>). Is there a way I > can > > use copyRows not to exceed the maximum number of cell styles and yet have > > the formatting needed for dates? >
SplitExcel.java
Description: application/ms-java
fileWithIssue.xlsx
Description: MS-Excel 2007 spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
