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 AM Dan S <dsti...@gmail.com> 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 AM PJ Fanning <fannin...@apache.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/d26bdb3be3aac829a77ec6912bb63811c4812e06 >> >> On Wed, 12 Feb 2025 at 05:27, Dan S <dsti...@gmail.com> 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 <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? >> > >> > >> > --------------------------------------------------------------------- >> > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >> > For additional commands, e-mail: user-h...@poi.apache.org >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >> For additional commands, e-mail: user-h...@poi.apache.org >> >>
