AutoFitColumns() is spanning data across wide page

@Amjad_Sahi, We are not facing any issue for above excel using AutoFitColumns() but we have many different files to be converted for which format are not same and not fixed. If we use AutoFitColumns(), it is causing the data spanning to wider page issue for some documents(Please find the issue in the PDF of 1538041204.zip (646.4 KB)
). Also, it is not fixed everytime that only first column will have the wrapping data around it. Please find the latest code we are using as below:

            Aspose.Cells.License license = new Aspose.Cells.License();
            string workingDirectory = Environment.CurrentDirectory;
            string dataDir = System.IO.Path.GetFullPath(@"..\..\");
            license.SetLicense(dataDir + "\\Aspose.Total.Product.Family.lic");
            FileFormatInfo fileFormatInfo = FileFormatUtil.DetectFileFormat(pInFile);
            Aspose.Cells.LoadOptions loadOptions;
            if (fileFormatInfo.FileFormatType == FileFormatType.Html)
            {
                Aspose.Cells.HtmlLoadOptions htmlLoadOptions = new Aspose.Cells.HtmlLoadOptions
                {
                    AutoFitColsAndRows = true
                };
                loadOptions = htmlLoadOptions;
            }
            else
            {
                loadOptions = new Aspose.Cells.LoadOptions();
            }
            loadOptions.CheckExcelRestriction = false;
            Workbook workbook = new Workbook(pInFile, loadOptions);
            workbook.Settings.CheckExcelRestriction = false;
            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 custom named range, need below code to display number instead of ## */
            Range[] range = workbook.Worksheets?.GetNamedRanges();
            // workbook.Worksheets.get
            if (range != null && range.Length > 0)
            {
                foreach (var r in range)
                {
                    if (!r.Address.Contains(":"))
                    {
                        r.Worksheet.Cells[r.Address].PutValue(r.Value?.ToString());
                    }

                }
            }
            /* 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 (Aspose.Cells.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.DisplayStringValue.Contains("#") ||
                            (cell.DisplayStringValue.ToLower().Contains("e") &&
                            cell.Type == CellValueType.IsNumeric)))
                    {
                        style.HorizontalAlignment = TextAlignmentType.Right;
                        cell.PutValue(cell.Value.ToString());
                    }
                    /* If cell is numeric minus value and display having space between value
                        and minus sign, use below condition to remove the space in between */
                    else if (cell.Type == CellValueType.IsNumeric && cell.Value.ToString().Contains("-")
                            && cell.DisplayStringValue.Contains(" "))
                    {
                        cell.PutValue(Regex.Replace(cell.DisplayStringValue, @"\s+", ""));
                        style.HorizontalAlignment = TextAlignmentType.Right;
                    }
                    style.VerticalAlignment = TextAlignmentType.Center;
                    cell.SetStyle(style);
                }
                wks.PageSetup.PrintArea = "";
                wks.PageSetup.BottomMargin = 1;
                wks.PageSetup.LeftMargin = 1;
                wks.PageSetup.RightMargin = 1;
                wks.PageSetup.TopMargin = 1;
            }
            workbook.Save(pOutFile, pdfSaveOptions);

Yes, AutoFitColumns will always try to expand columns’ width in accordance with the longest text/data in cell of the column. This is what MS Excel’s auto-fit columns operation is doing.

I guess you may check and scan each cell in a column and check the width via Cell.GetWidthOfValue(), then evaluate (by yourselves using your own code) the highest value (obtained) if that width will widen the page broadly, so you may set the column’s width to some reasonable width (by Cells.SetColumnWidth()) and use wrap text/data feature for the cells of the specified column here.

Generally, there is no better way to handle it accurately and there won’t be any single solution that could work for every file. I am afraid, you have to evaluate by yourselves for which Excel files/spreadsheets or specified sheets, you will use only AutoFitColumns method. And, for which worksheets, you will adopt the above procedure to wrap text/data accordingly. Please notice, in MS Excel there is not any option available which could automatically fix the problem for every file. You have to use AutoFitColumns for certain scenarios/cases. Similarly, you have to use word wrapping feature for other ones where there are long text/data in the cells. You may simply exercise in MS Excel to accomplish your desired display of worksheets data manually and then write your code/logic using Aspose.Cells for .NET by yourselves to accomplish your desired display of data in output PDF.

Understood, Thank you @Amjad_Sahi

@Vaidehi123,

You are welcome.