XLSX to PDF - Age calculation and Date format issue

[UshaThavasiappan]

Hello,
After using the latest version 7.12.4, I faced two issues. This was fixed on the previous release. Attached screenshots of issues with sample source code.

Issue 1
hockeyplayerdata2018.xlsx file
- The converted file does not have correct values
- Issues of sheets are AgeHtWt, Heights, BMIPosPct, BMITeam and HomeProv

Issue 2
rieter-consolidated-balance-sheet-2021-en.xlsx file
- In B4 and C4 column values, the actual value is December 31, 2021. But the converted file displays only 2021. Actually this is multi-line value.

Could you please look into these ?

IssueFor7.12.4.zip

(455.98 KiB) Downloaded 335 times

[amy.zhao]

Hello,

Sorry for the delay response as a holiday here.
I did reproduce your issue 1 with hockeyplayerdata2018.xlsx file and logged it in our issue tracking system with SPIREXLS-4382. We are sorry for the inconvenience caused.
For issue 2, I didn't find rieter-consolidated-balance-sheet-2021-en.xlsx file, could you please send us it again? Thanks for your assistance in advance.

Sincerely
Amy
E-iceblue support team

[UshaThavasiappan]

Hi,
Sorry, attached the required file.

IssueFor7.12.4.zip

(476.83 KiB) Downloaded 327 times

[amy.zhao]

Hi,

Thanks for providing your files.
When opening your rieter-consolidated-balance-sheet-2021-en.xlsx in MS Excel, you can see that December 31, 2021 displays 2021. If you convert directly your xlsx file to PDF file by MS Excel tool, it also showed 2021. So it is not an issue.
To achieve your requirement, please increase the row height of cell B4 to make its contents appear complete. You can use

Code: Select all

worksheet.getCellRange("B4").autoFitRows();

to set a cell range or

Code: Select all

worksheet.getAllocatedRange().autoFitRows();

to set all cell ranges.

Sincerely
Amy
E-iceblue support team

[UshaThavasiappan]

Hi,
In my sample code, I used autoFit values right from the beginning. Actually the issue comes from the code below.

Code: Select all

for (int col = worksheet.getFirstColumn(); col <= worksheet.getLastColumn(); col++){
            CellStyle style = worksheet.getColumns()[col-1].getCellStyle();
//            CellStyle style = worksheet.getDefaultColumnStyle(col);
            style.setShrinkToFit(isShrink);
            style.setWrapText(isWrap);
        }

When I use CellStyle style = worksheet.getColumns()[col-1].getCellStyle();, I can get fully correct values. But, it took much time to process an even smaller file.
When I use CellStyle style = worksheet.getDefaultColumnStyle(col);, the value displayed what can we see on the page.
It is an issue, please suggest working alternate for this case.

Thanks.

[Nina.Tang]

Hello,

Thanks for your feedback.
I checked your code and found that you only set the autoFitColumns() to auto fit column width when the isMerged is true. Please add the worksheet.getAllocatedRange().autoFitRows();code to auto fit row height to get rid of your issue.
Besides, the getCellStyle() method will create all cell objects of column, this process will occupy the memory. It is different from the getDefaultColumnStyle(col) method. While the style added by the former takes precedence over the latter. If the cells themselves have style, setting new styles for getDefaultColumnStyle(col) will not take effect. Hope you can understand. If there is still any question, please feel free to write back.

Sincerely,
Nina
E-iceblue support team

[Nina.Tang]

Hello,

Thanks for your patient waiting.
Our Dev team made some adjustments on the bug SPIREXLS-4382 you raised. At present, the data of pivot table in AgeHtWt, Heights and HomeProv worksheets are the same as your original Excel. However, some data in BMIPosPct and BMITeam worksheets still have differences from original, and currently our Spire.XLS does not support the Slicer function. Our dev team is making every effort to repair the remaining data differences. Considering your situation, we compiled a temporary version that includes the current fix. You can download it and have a try.

Sincetely,
Nina
E-iceblue support team