Free Support Forum - aspose.com

Cells with huge data

I am facing issue.When i tried to convert excel to pdf using aspose.cells for .net

My excel file has just cell which hold large data in that cell, while in generated pdf i see only some text converted rest is missing. Can you help on this

Regards

Rangaraj

Hi Rangaraj,


Thank you for contacting Aspose support.

Please call the Worksheet.AutoFitColumns & Worksheet.AutoFitRows methods just before saving the spreadsheet in PDF format. This should resolve the issue, however, in case the problem persists, please provide us the sample spreadsheet for our testing.

Hi Raza

I have tried the options that you mentioned, still no luck. Please find the attached file.

Hi Rangaraj,


Thank you for sharing the sample spreadsheet.

We have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.5.2.4, and the method Worksheet.AutoFitColumns works fine. Please check the attached resultant PDF. If you are using any previous release of the API then the problem could be due to some bug in that particular version, that has already been fixed. Please give a try to the latest version of your side and feed us back with your results.

C#

var book = new Workbook(“D:/Test (1).xlsx”);
var sheet = book.Worksheets[0];
sheet.AutoFitColumns();
book.Save(“D:/output.pdf”, SaveFormat.Pdf);

Hi Raza

Thanks for your reply.

On looking the output PDF sent by you, the data present in the excel is not fully converted into PDF. On using the version specified by you, I still face the same issue only certain part of the text in Excel is converted to PDF.

Thanks

Rangaraj

Hi Rangaraj,


Please accept my apologies for overlooking the issue while testing earlier. You are right, the contents are not fully rendered in previously shared PDF file. I have checked further, the reason for incomplete rendering is that the content height is more that the maximum height allowed for a row. You may confirm this by expanding the row height to max, you will notice that Excel will not allow you to expand the row height beyond 409.5.

That said, one possible solution for this scenario is to merge the cells to expand the visual area. Please check the following piece of code and attached resultant files for your reference.

C#

var book = new Workbook(“D:/Test.xlsx”);
var sheet = book.Worksheets[0];
var cell = sheet.Cells[“A1”];
sheet.AutoFitRows();
if(cell.GetHeightOfValue() > sheet.Cells.Rows[cell.Row].Height)
{
sheet.Cells.Merge(0, 0, 31, 1);
}
book.Save(“D:/output.xlsx”);
book.Save(“D:/output.pdf”, SaveFormat.Pdf);

Hi Raza

Thanks for your reply.

In the Code you have given, i have a couple of things noted.

1. The text can be in any cell /in multiple cells also.

2. if the total rows is hardcoded , if i have some text in the next cell will that not get be overrided/hidden.

Thanks

Rangaraj

Hi Rangaraj,


The code was provided for demonstration purposes therefore you can surely tweak it to match your requirements. For instance;

  1. You can check all data cells to determine which cells need merging. You can simply iterate over the cells to perform the same check as demonstrated in previous example.
  2. You can calculate the number of rows to be merged using the formula provided at the bottom.
  3. In case there are data cells below the cell containing the large text, you can first insert a few rows then perform the merge, this way, the below data rows will not be overwritten.
Hope this helps a bit.

C#

//Default row height is 15.75
double defaultRowHeight = 15.75;
//Find the difference in height of the row and contents
double difference = cell.GetHeightOfValue() - sheet.Cells.Rows[cell.Row].Height;
//Determine how many rows have to be added
int numberOfRowsToBeAdded = Convert.ToInt16(Math.Round(difference / defaultRowHeight));
//Insert rows right under the cell containing the large text
sheet.Cells.InsertRows(cell.Row+1, numberOfRowsToBeAdded);
//Merge cells
sheet.Cells.Merge(cell.Row, cell.Column, cell.Row + numberOfRowsToBeAdded, 1);