Partial data display issue when cell has multi-lines of data

[UshaThavasiappan]

Hello Experts,

Im converting XLSX file into PDF file using spire.xls-12.9.1 API.

Sample code, sample source file and the image of exact error highlighted are attached for your reference.

A cell with multiple lines of data shows only the last line. After tabular data, point number 1 displays only "as of January 1, 2021.", but it should display the actual value

Attached rieter-consolidated-balance-sheet-2021-en.xlsx file has multiple lines data at 45th row. Attached image file (PatialDataIssue.png) for your reference.

I really appreciate your help on this.

PatialDataIssue.zip

(266.56 KiB) Downloaded 72 times

[Simple.Li]

Hello,

Thanks for your inquiry.
The following conversion code could avoid your issue. If you have any further questions, please feel free to contact us.

Code: Select all

Workbook workbook = new Workbook();
workbook.loadFromFile("rieter-consolidated-balance-sheet-2021-en.xlsx");
workbook.getConverterSetting().setSheetFitToPage(true);
workbook.saveToFile("output.pdf", com.spire.xls.FileFormat.PDF);


Sincerely,
Simple
E-iceblue support team

[UshaThavasiappan]

Hi,
Thanks for your immediate response.

Actually I'm iterating the sheets to set autoFitRows and autoFitColumns for my business need. The cell data are clipped when not using these autoFit methods.

[Lisa.Li]

Hello,

Thanks for your feedback.
Kindly note that our Spire.Xls mimics the same behavior as MS Excel does. As shown in the attached screenshot, when setting the autoFitRows and autoFitColumnsI by MS Excel, there is the same display effect. And based on the code my colleague Simple provided, I didn't find the data clipped issue on my testing result "output.pdf", Did this issue occur on your side? If so, please share your system information (E.g. Win10, 64 bit) and region setting (E.g. China, Chinese) for our further investigation.

Sincerely,
Lisa
E-iceblue support team

[UshaThavasiappan]

Hello Lisa,

Yes, I agree. The clipping of data issue is present in the attached file.
I have shared a file with screenshot of what I'm facing when not using autoFitRows and autoFitColumns.

If you still have any alternates, please suggest me.

AutoFitIssue.zip

(251.67 KiB) Downloaded 77 times

[Lisa.Li]

Hello,

Thank you for sharing.
I did an initial test and reproduced the behavior, we will do further investigation and update you about the progress accordingly.

Sincerely,
Lisa
E-iceblue support team

[UshaThavasiappan]

Hi,
Thanks for accepting. I will be waiting for the solution.

[Lisa.Li]

Hello,

After more tests, we found that the cell A45 of your previous file ("rieter-consolidated-balance-sheet-2021-en.xlsx") has the "Wrap Text" and "Merge" settings. When applying "autoFitRows()", the cell height will be adjusted, thus the text will show incompletely as MS Excel does. In order to make both two files conversions meet your requirements, you need to only use "autoFitColumns()" but not invoke "autoFitRows()". If there is still any doubts, just feel free to write back.

Sincerely,
Lisa
E-iceblue support team

[UshaThavasiappan]

Hello,
I agree considering only those two files.
But in business, it may apply autoFitRows for other files too.

I'm not processing a single file in a single shot. Instead, I'm converting a directory containing n-number of files from excel to pdf format.
I can not identify in person which file has this functionalities to be applied.

Could you share something to achieve this feature ?

[Lisa.Li]

Hello,

Thanks for your feedback.
As previous informed that our product mimics the same behavior as MS Excel does. I tried to use the following code to meet all files, but the merged row of "rieter-consolidated-balance-sheet-2021-en.xlsx" was not recognized. This issue has been logged into our bug tracking system with the ticket SPIREXLS-4233. Our Dev team will further investigate and fix it. Once it is fixed, we will let you know asap.
Meanwhile, you can use this code to test your other files, if there is any issues, please share your file for further testing.

Code: Select all

 Workbook workbook = new Workbook();
        workbook.loadFromFile("rieter-consolidated-balance-sheet-2021-en.xlsx");
        workbook.getConverterSetting().setSheetFitToWidth(true);
        Boolean  merged=false;
        for (int i = 0; i < workbook.getWorksheets().size(); i++) {
            Worksheet worksheet = workbook.getWorksheets().get(i);
            for (int r = 0; r < worksheet.getLastRow(); r++) {
                       if (worksheet.getRows()[r].hasMerged())
                   {
                       merged = true;
                       break;
                   }
                    break;
            }
            if (merged)
            {
                worksheet.getAllocatedRange().autoFitColumns();
            }
            if (!merged)
            {
                worksheet.getAllocatedRange().autoFitColumns();
                worksheet.getAllocatedRange().autoFitRows();
            }
        }
        workbook.saveToFile("output.pdf", FileFormat.PDF);


Sincerely,
Lisa
E-iceblue support team

[UshaThavasiappan]

Hello Lisa,

Thanks for the code snippet. It is working for most of the files.

Please do post me for the ticket SPIREXLS-4233 solution.

[Lisa.Li]

Hello,

Thanks for your feedback.
The following modified code is more effective when processing the large excel file, please use it instead.

Code: Select all

        Workbook workbook = new Workbook();
        workbook.loadFromFile("in.xlsx");
        workbook.getConverterSetting().setSheetFitToWidth(true);
        Boolean  merged=false;
        for (int i = 0; i < workbook.getWorksheets().size(); i++) {
            Worksheet worksheet = workbook.getWorksheets().get(i);
            CellRange[] rows = worksheet.getRows();
            for (int r = 0; r < rows.length; r++) {
                if (rows[r].hasMerged())
                {
                    merged = true;
                    break;
                }
            }
            if (merged)
            {
                worksheet.getAllocatedRange().autoFitColumns();
            }
            if (!merged)
            {
                worksheet.getAllocatedRange().autoFitColumns();
                worksheet.getAllocatedRange().autoFitRows();
            }
        }
        workbook.saveToFile("out.pdf", FileFormat.PDF);


And for SPIREXLS-4233, my previous code added an additional "break", this code also can avoid it. If there is any other issues, just feel free to contact.

Sincerely,
Lisa
E-iceblue support team

[UshaThavasiappan]

Hello,
Sure, I will replace the previous one with the new changes.

[Lisa.Li]

Hello,

Thanks for your quick reply.
If you have other questions, please do not hesitate to contact us. Wish you all the best!

Sincerely,
Lisa
E-iceblue support team