I have one excel with Arabic and English language and some of the merged cell length greater than 32k.
I am trying to convert that excel to pdf using CheckExcelRestriction = false flag, but conversion is not done properly.
Please zip and attach your input Excel file as well. Moreover, please provide your expected (output) PDF file using MS Excel. We will check your issue soon.
I checked the files in the zipped archive. Probably, the PDF is the output PDF file using MS Excel. I think you may try the following sample code, it will render/wrap the bigger (merged) cells properly to display all the data.
e.g. Sample code:
Hi @Amjad_Sahi … Thanks for your reply. I have tried above code but still its not rendering as per the original excel file. I am attaching the converted PDF file generated from above code. Could you please help?1522221182.pdf (1.3 MB)
Just to update, we have a licensed version of aspose total 21.3.0 and we are using that for conversion.
Please notice, I am able to find the issue as you mentioned using your template file and following sample code. I found Arabic Excel file with cell length more than 32k is not converting to PDF properly, the text is not wrapped in those cells. I tried many variations and options but to no avail:
e.g. Sample code:
@Peyton.Xu Thanks for your reply. Above code is able to converted the required Arebic pdf but it’s disturbing the other PDF conversion. In our system, input file format and language is not identical. I have attached the original and 1522206818.zip (239.9 KB)
converted file which is converted using above code. Please share your thoughts.
string dataDir = System.IO.Path.GetFullPath(@"..\..\");
Workbook workbook = new Workbook(pInFile);
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());
}
}
}
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.Type == CellValueType.IsNumeric || cell.Type == CellValueType.IsDateTime)
{
var updatedValue = cell.DisplayStringValue.Contains("#") ? cell.Value : cell.DisplayStringValue;
cell.PutValue(updatedValue.ToString());
}
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);
Please try to use FileFormatUtil.DetectFileFormat(string filePath) to detect file format, and do some adaptations for Html file format.
FileFormatInfo fileFormatInfo = FileFormatUtil.DetectFileFormat(pInFile);
LoadOptions loadOptions;
if (fileFormatInfo.FileFormatType == FileFormatType.Html)
{
HtmlLoadOptions htmlLoadOptions = new HtmlLoadOptions();
htmlLoadOptions.AutoFitColsAndRows = true;
loadOptions = htmlLoadOptions;
}
else
{
loadOptions = new LoadOptions();
}
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 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());
}
}
}
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.Type == CellValueType.IsNumeric || cell.Type == CellValueType.IsDateTime)
{
var updatedValue = cell.DisplayStringValue.Contains("#") ? cell.Value : cell.DisplayStringValue;
cell.PutValue(updatedValue.ToString());
}
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);
The code works with Aspose.Cells v21.1.1 for both of your shared files. There is only a little issue for the source html file with Arabic text: some lines are not wrapped and are rendered as a single long line.
@Peyton.Xu - Thanks for the updated code. Could you please provide me your converted pdf file using above code? Because I have some issues in converted file like all table columns are not in one row etc. I just want to compare the converted files to get more idea.