Re: How to preserve dates when copying rows with a CellCopyPolicy value for cellStyle set to false

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/d26bdb3be3aac829=
a77ec6912bb63811c4812e06

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 Cel=
lCopyPolicy and I refactored the method copyRows from org.apache.poi.xssf.u=
sermodel.XSSFSheet and method copyRowFrom from org.apache.poi.xssf.usermode=
l.XSSFRow instantiating a CellCopyContext in copyRows. The problem though i=
s when I ran the following worksheet through, the resulting workbook with a=
 single sheet has date formatted columns (as expected) in the column with h=
eader transaction_date is but the values in the column header transaction t=
ime 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=E2=80=AFPM 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/j=
ava/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 a=
nd 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 a=
n
>> > 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 XSSFWork=
book
>> > 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 shee=
t to
>> > place in the new XSSFWorkbook. The CellCopyPolicy used has cellStyle s=
et to
>> > false in order to avoid exceeding the maximum number of cell styles (f=
ixed
>> > in NIFI-13726 <https://issues.apache.org/jira/browse/NIFI-13726>). The
>> > drawback though of using cellStyle set to false is that any columns wh=
ich
>> > have dates, its date formatting is lost and what is left is a meaningl=
ess
>> > 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 h=
ave
>> > the formatting needed for dates?
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]