@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);