How to autofit rows and columns for excel worksheets?

Aspose.Cells Product Family
1

Hi,

We are using Aspose.Cells SDK to convert all excel documents to PDF. I now have a requirement to fit all the columns of an excel worksheet into 1 page prior to saving it as .pdf. How can I accomplish this? Please see the code snippet below-

                  if (fileExtension.ToLower().Contains(".xls"))
									{
										//Convert Excel doc to PDF
										var workbook = new Aspose.Cells.Workbook(mStream);											
										workbook.Worksheets.RemoveAt("help_Overview");											

										if (workbook.Worksheets.Any())
										{
											var pdfStream = new MemoryStream();
											
											workbook.Save(pdfStream, Aspose.Cells.SaveFormat.Pdf);

											BlobContainerClient container = new BlobContainerClient(_settings.CloudStorageConnection, "abc");												
											BlobClient blob = container.GetBlobClient($"/xyz/{newFilePath}");
											pdfStream.Position = 0;
											blob.Upload(pdfStream, true);
											pdfStream.Close();
										}
									}

Any help is greatly appreciated. Thank you in advance.

2

@s2205

You have two options to achive this.

Option 1: using PdfSaveOptions.AllColumnsInOnePagePerSheet, set it to true, all the columns of one sheet will be into one page. However, the width of paper size set in PageSetup will be ignored.

if (fileExtension.ToLower().Contains(".xls"))
{
    //Convert Excel doc to PDF
    var workbook = new Aspose.Cells.Workbook(mStream);
    workbook.Worksheets.RemoveAt("help_Overview");

    if (workbook.Worksheets.Any())
    {
        PdfSaveOptions saveOptions = new PdfSaveOptions();
        saveOptions.AllColumnsInOnePagePerSheet = true;

        var pdfStream = new MemoryStream();

        workbook.Save(pdfStream, saveOptions);

        BlobContainerClient container = new BlobContainerClient(_settings.CloudStorageConnection, "abc");
        BlobClient blob = container.GetBlobClient($"/xyz/{newFilePath}");
        pdfStream.Position = 0;
        blob.Upload(pdfStream, true);
        pdfStream.Close();
    }
}

Option 2: using PageSetup.SetFitToPages(1, 0), it will shrink content of one sheet to fit the width of paper size set in PageSetup. However, the minimun zoom is 10%, if a sheet contain too much columns with data, there is a possibility that columns will be split into more than one pages.

if (fileExtension.ToLower().Contains(".xls"))
{
    //Convert Excel doc to PDF
    var workbook = new Aspose.Cells.Workbook(mStream);
    workbook.Worksheets.RemoveAt("help_Overview");

    if (workbook.Worksheets.Any())
    {
        foreach(Worksheet sheet in workbook.Worksheets)
        {
            sheet.PageSetup.SetFitToPages(1, 0);
        }

        var pdfStream = new MemoryStream();

        workbook.Save(pdfStream, Aspose.Cells.SaveFormat.Pdf);

        BlobContainerClient container = new BlobContainerClient(_settings.CloudStorageConnection, "abc");
        BlobClient blob = container.GetBlobClient($"/xyz/{newFilePath}");
        pdfStream.Position = 0;
        blob.Upload(pdfStream, true);
        pdfStream.Close();
    }
}
1 Like
3

Hi @Peyton.Xu,

Thank you so much for your response. I tried the first option and it worked :slight_smile:

4

@s2205,

Good to know that the suggested option works for your needs well. In the event of further queries or issue, feel free to write us back.