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

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

PJ,
 I just wanted to follow up on what you mentioned the other day

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.


Can you please make a ticket for this also? Thanks!

On Wed, Feb 12, 2025 at 5:10=E2=80=AFPM PJ Fanning <[email protected]> w=
rote:

> I logged https://bz.apache.org/bugzilla/show_bug.cgi?id=3D69583
>
> On Wed, 12 Feb 2025 at 21:00, Dan S <[email protected]> wrote:
> >
> > PJ,
> >  I think I found the issue and I believe this would constitute a bug bu=
t
> I
> > would like your confirmation. I narrowed down the problem to lines
> 237-239
> > in CellUtil
> >
> > if (DateUtil.isCellDateFormatted(srcCell)) {
> >        destCell.setCellValue(srcCell.getDateCellValue());
> > }
> >
> > where in my case destCell is an instance of XSSFCell where I assume
> > (although I could not track exactly) this set method calls the set meth=
od
> > in XSSFCell on lines 460-463
> >
> > protected void setCellValueImpl(Date value) {
> >         boolean date1904 =3D this.getSheet().getWorkbook().isDate1904()=
;
> >         this.setCellValue(DateUtil.getExcelDate(value, date1904));
> > }
> >
> > The third column of my data was only a time (e.g. in java.util.Date
> form: Sun
> > Dec 31 09:53:44 UTC 1899) which per the Javadocs on
> DateUtil.getExcelDate is
> > not a valid date hence -1 was returned and thereby any subsequent call
> > to getDateCellValue()
> > returned null as no date was ever set.
> >
> > I was able to get copyCell working with the following in the switch
> > statement
> >
> > case NUMERIC:
> >       destCell.setCellValue(srcCell.getNumericCellValue());
> >       break;
> >
> > without the call to the setCellValue method that takes a java.util.Date
> thereby
> > bypassing the call of DateUtil.getExcelDate.
> > Please let me know your thoughts on whether copyCell should be changed =
or
> > not. Thanks!
> >
> > On Wed, Feb 12, 2025 at 11:20=E2=80=AFAM Dan S <[email protected]> wrot=
e:
> >
> > > With some System.out statements this is what the resulting sheet in t=
he
> > > 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]> wr=
ote:
> > >
> > >> Oops sorry for the typo it should read
> > >> In other words the third column has time values and when read withou=
t
> 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]> w=
rote:
> > >>
> > >>> 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 runnin=
g
> > >>> through my code they seem to only be numeric cell types and not dat=
e
> > >>> formatted.
> > >>>
> > >>> On Wed, Feb 12, 2025 at 9:24=E2=80=AFAM Dan S <[email protected]> w=
rote:
> > >>>
> > >>>> 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 <fanningpj@apac=
he.org>
> > >>>> 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/d26bdb3be3aac8=
29a77ec6912bb63811c4812e06
> > >>>>>
> > >>>>> 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 fr=
om
> > >>>>> org.apache.poi.xssf.usermodel.XSSFRow instantiating a
> CellCopyContext in
> > >>>>> copyRows. The problem though is when I ran the following workshee=
t
> 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=E2=80=AFPM PJ Fanning <fanningpj@a=
pache.org
> >
> > >>>>> wrote:
> > >>>>> >>
> > >>>>> >> That copyRows method doesn't allow you to specify the
> > >>>>> CellCopyContext.
> > >>>>> >> We probably should add a variant of copyRows that supports thi=
s
> > >>>>> 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/ja=
va/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 whi=
ch
> > >>>>> 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 he=
re
> > >>>>> >> > <
> > >>>>>
> https://github.com/apache/nifi/blob/main/nifi-extension-bundles/nifi-poi-=
bundle/nifi-poi-services/src/main/java/org/apache/nifi/processors/excel/Spl=
itExcel.java
> > >>>>> >
> > >>>>> >> > (lines
> > >>>>> >> > 165-188).
> > >>>>> >> > In order to accomplish this, I use the
> > >>>>> >> > com.github.pjfanning.xlsx.StreamingReader to stream the Exce=
l
> > >>>>> workbook
> > >>>>> >> > which allows for easy looping over each sheet. I create a ne=
w
> > >>>>> 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]
> > >>>>>
> > >>>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>

--0000000000008fa6ea062e068e16--