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

--000000000000b3b090062df452d7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

With some System.out statements this is what the resulting sheet in the
workbook looks like. I would have thought all cell 2 columns except for row
1
should have had a value which is a date. Instead I see a negative -1 value.

row 0 cell 0 cell type STRING value transaction_id
row 0 cell 1 cell type STRING value transaction_date
row 0 cell 2 cell type STRING value transaction_time
row 1 cell 0 cell type NUMERIC value 75.0
row 1 cell 1 cell type NUMERIC value Sun Jan 01 00:00:00 UTC 2023
row 1 cell 2 cell type NUMERIC value -1.0
row 2 cell 0 cell type NUMERIC value 78.0
row 2 cell 1 cell type NUMERIC value Sun Jan 01 00:00:00 UTC 2023
row 2 cell 2 cell type NUMERIC value -1.0
row 3 cell 0 cell type NUMERIC value 80.0
row 3 cell 1 cell type NUMERIC value Sun Jan 01 00:00:00 UTC 2023
row 3 cell 2 cell type NUMERIC value -1.0
row 4 cell 0 cell type NUMERIC value 81.0
row 4 cell 1 cell type NUMERIC value Sun Jan 01 00:00:00 UTC 2023
row 4 cell 2 cell type NUMERIC value -1.0
row 5 cell 0 cell type NUMERIC value 82.0
row 5 cell 1 cell type NUMERIC value Sun Jan 01 00:00:00 UTC 2023
row 5 cell 2 cell type NUMERIC value -1.0

On Wed, Feb 12, 2025 at 10:08=E2=80=AFAM Dan S <[email protected]> wrote:

> Oops sorry for the typo it should read
> In other words the third column has time values and when read without my
> code are numeric cell types AND ARE date formatted but after running
> through my code they seem to only be numeric cell types and NOT date
> formatted.
>
> On Wed, Feb 12, 2025 at 10:06=E2=80=AFAM Dan S <[email protected]> wrote:
>
>> In other words the third column has time values and when read without my
>> code are numeric cell types are not date formatted but after running
>> through my code they seem to only be numeric cell types and not date
>> formatted.
>>
>> On Wed, Feb 12, 2025 at 9:24=E2=80=AFAM Dan S <[email protected]> wrote:
>>
>>> 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=E2=80=AFAM PJ Fanning <[email protected]=
g> 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/d26bdb3be3a=
ac829a77ec6912bb63811c4812e06
>>>>
>>>> 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 i=
n 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 thr=
ough,
>>>> 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 shou=
ld be
>>>> (as it is in the original file). Attached is my new code and the sampl=
e
>>>> file. Please advise. Thank you so much!
>>>> >
>>>> > On Tue, Feb 11, 2025 at 4:18=E2=80=AFPM PJ Fanning <fanningpj@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 ext=
ra
>>>> >> param. The context keeps track of styles so that it avoids adding t=
he
>>>> >> 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-p=
oi-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]
>>>>
>>>>

--000000000000b3b090062df452d7--