PJ, I realize it handles formatted dates. My question is that it appears my changes only handle one of the columns with dates but not the other. I understood from you the changes I made should have handled both. Please advise.
On Wed, Feb 12, 2025 at 6:36 AM PJ Fanning <[email protected]> wrote: > I have some tests that appear to show that that test class I > referenced earlier does handle formatted dates. I added an extra one > today. > > > https://github.com/pjfanning/excel-streaming-reader/commit/d26bdb3be3aac829a77ec6912bb63811c4812e06 > > On Wed, 12 Feb 2025 at 05:27, Dan S <[email protected]> wrote: > > > > 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 <[email protected]> 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 <[email protected]> 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? > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [email protected] > > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
