We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

AutoFitColumns() is spanning data across wide page

Hello,

We are using AutoFitColumns() method while converting Excel to PDF to get the whole data, but it is getting spanned across wider pages and columns are having too much spaces. Please check it at 3rd page of converted PDF. Also, how to get rid of blank pages coming in the converted PDF? We are using Aspose.Total license version 21.3.0 Please find original and converted files in attachment.

1538041204.zip (646.4 KB)

@Vaidehi123,

The problem “data getting spanned wider pages… too much spaces” looks due to “AllColumnsInOnePagePerSheet” which you might have set in code apart from calling AutoFitColumns. When you set this boolean attribute on, all the columns are pasted on the page which consequently widens the pages. So if you do not want this display then do no use this boolean attribute.

Please note, generally, Aspose.Cells renders a sheet (to PDF) as it is displayed in the print preview of the sheet (in MS Excel). For your needs, you should try to specify your desired Page Setup options (using Page Setup dialog) and now take the print preview to see how it will be rendered into PDF. Now use relevant PageSetup APIs (provided by Aspose.Cells) in your code before rendering to PDF to get similar results. If you still could not evaluate, kindly do provide your updated Excel file (which shows your desired view when taking the print preview of the sheet) and expected PDF file (by MS Excel). We will check it soon.

@Amjad_Sahi Thanks for your response. We can not exclude “AllColumnsInOnePagePerSheet” boolean because of the client’s requirement. I have tried using the Pagesetup options, but still did not get desired output. I have attached original Excel file which gives the desired print preview and the converted pdf using MS Excel. PDF converted by MS Excel is just the same as the desired output except its having blank pages in between. Below is the code which I tried.

string dataDir = System.IO.Path.GetFullPath(@"..\..\");
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense(dataDir + "\\Aspose.Total.lic");
            var loadOptions = new LoadOptions();
            loadOptions.CheckExcelRestriction = false;
            Workbook workbook = new Workbook(pInFile, loadOptions);
            Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
            // option to set all the columns of excel in one page.
            pdfSaveOptions.AllColumnsInOnePagePerSheet = true;
            pdfSaveOptions.MergeAreas = true;
            /* Retain the structure of original excel */
            pdfSaveOptions.ExportDocumentStructure = true;
            /* Formula calculation for any digit formulla applied in excels */
            /*it is best to call Workbook.CalculateFormula() just before rendering the spreadsheet to PDF. 
           * This ensures  that the formula dependent values are recalculated, and the correct
           * values are rendered in the PDF.*/
            workbook.CalculateFormula();
            /* For some sheet, print area is applied, to display all the data in pdf, 
             * below setting is needed */
            
            foreach (Worksheet wks in workbook.Worksheets)
            {
                
                foreach (Cell cell in wks.Cells)
                {
                   
                    Style style = cell.GetStyle();
                    
                    /* if cell is numeric, consider its category type as numeric and update the format */
                    if (cell.Type == CellValueType.IsNumeric &&
                        cell.NumberCategoryType != NumberCategoryType.Number)
                    {
                        
                        style.SetCustom("#,##0.00", true);
                    }
                    style.VerticalAlignment = TextAlignmentType.Center;
                    /* set fonts */
                    style.Font.Name = "Helvetica";
                    cell.SetStyle(style);
                }
               
                wks.PageSetup.PrintArea = "";
                wks.PageSetup.Orientation = PageOrientationType.Portrait;
                wks.PageSetup.IsHFScaleWithDoc = true;
                wks.PageSetup.IsHFAlignMargins = false;
                wks.PageSetup.IsHFDiffFirst = false;
                wks.PageSetup.IsHFDiffOddEven = false;
                wks.PageSetup.Order = PrintOrderType.DownThenOver;
                wks.PageSetup.SetFitToPages(1, 0);
                wks.PageSetup.CenterHorizontally = true;
                wks.PageSetup.BottomMargin = 1;
                wks.PageSetup.LeftMargin = 1;
                wks.PageSetup.RightMargin = 1;
                wks.PageSetup.TopMargin = 1;
                PictureCollection pk = wks.Pictures;
                if (pk?.Count() <= 0)
                {
                    wks.Cells.DeleteBlankRows();
                }
                wks.AutoFitColumns();
            }
            workbook.Save(pOutFile, pdfSaveOptions);

1538041204.zip (556.5 KB)

@Vaidehi123,

I used your newly attached template Excel file and converted to PDF via Aspose.Cells for .NET (latest version) and the resultant PDF looks to me almost same. Could you share what is the major difference b/w Aspose.Cells rendering and MS Excel’s rendering using your template (new) Excel file. Give us some screenshots to evaluate it on our end.

Moreover, those blank pages are sheet ends, so you cannot escape from it (even using MS Excel rendering). One workaround though is copy all the data from different sheets into single sheet using Range.Copy() method but it might have other drawbacks. May be you could use Aspose.PDF API to re-process the output PDF to remove those blank areas by it.

@Amjad_Sahi I have tried with latest version to check if it resolves the issue but it won’t work. I have attached screen shot of actual converted and desired pdf page. Also, attached actual converted PDF.
Files.zip (730.4 KB)

@Vaidehi123,

You did not do what I asked previously. I asked you to just render using the following lines to convert your updated Excel file to PDF and check if you find any significant issue when comparing with MS Excel output (PDF).
e.g.
Sample code:

var stringPath = "e:\\test2\\1538041204.xlsx";
var workbook = new Aspose.Cells.Workbook(stringPath);
workbook.Save("out1.pdf", Aspose.Cells.SaveFormat.Pdf); 

If you use your previous or existing code, surely, it will try to paste all the columns (in the sheet) in output PDF (pages), so consequently data would be pasted and pages would be widens. Please see my reply for reference.

@Amjad_Sahi It’s working as expected by excluding other settings now.

For blank pages, I tried to reprocess the generated PDF from excel to exclude blank areas but its giving exception like “At most 4 elements (for any collection) can be viewed in evaluation mode.” But I am using the aspose total license version 21.3.0. I am using below code. Please let me know am I missing something here? Attaching the processed PDF file also.1538041202.pdf (557.8 KB)

            string dataDir = System.IO.Path.GetFullPath(@"..\..\");
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense(dataDir + "\\Aspose.Total.lic");
            Document pdfDocument = new Document("\\Files\\1538041202.pdf");
                for (int i = 1; i <= pdfDocument.Pages.Count; i++)
                {
                    bool isBlank = pdfDocument.Pages[i].IsBlank(0.01d);
                    if (isBlank)
                    {
                        pdfDocument.Pages.Delete(i);
                    }
                }
                pdfDocument.Save(pOutFile);

@Vaidehi123,

It looks like you have not set the license for Aspose.PDF for .NET, please set the license in code (as you did for Aspose.Cells for .NET APIs) and give it a try again.

@Amjad_Sahi Yes, that was it. Thanks!

@Vaidehi123,

You are welcome.