Hello,
I have one Excel file which have the data in Font “Angsana” which is not available in the machine. While converting it to PDF, Aspose cells is converting it into Arial font due to missing fonts but data is cutting due to font size difference between Angsana and Arial. Any suggestion how we can resolve it except installing the fonts? I have attached excel and generated PDF both in the attachment.
1538041204.zip (603.9 KB)
Please note the working of the (internal) process of Aspose.Cells regarding fonts:
To render exact data with similar formation/style is install the underling font(s) on the machine. There is no other reliable way or best way to cope with it. You can substitute fonts (used in the workbook) with your desired fonts (available on the system) but this will not and always be 100% accurate. Please note, Aspose.Cells APIs provide the ability to specify the substitute font for rendering purposes. This mechanism should be helpful when a required font is not available on the machine where conversion has to take place. So, you have to provide a list of font names in similar glyph yourselves as an alternative to the originally required font, see the document on how to substitute fonts for your reference.
@Amjad_Sahi We have installed required fonts ‘Angsana New’ and ‘AngsanaUPC’ to the machine. Documents are converted properly in the local system with required fonts but its not converting in server with required fonts. We have the windows 2019 server. Could you please help what could be the issue here? (We verified that fonts are properly installed in the server and required application is getting fonts where the conversion code is written). I am attaching the source files and converted pdf file below. Files.zip (774.0 KB)
Below is the code:
string dataDir = System.IO.Path.GetFullPath(@"..\..\");
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(dataDir + "\\Aspose.Total.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();
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 share us the full path where the two fonts are installed.
It seems that there are some limitations to get installed fonts.
Please try to add the following code to the beginning of your code to see if there are some difference.
FontConfigs.SetFontFolder(FolderFullPathTheTwoFontsInstalled, false);
//Your code followed
string dataDir = System.IO.Path.GetFullPath(@"..\..\");
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(dataDir + "\\Aspose.Total.lic");
...@Peyton.Xu Font path is C:\Windows\Fonts, we tried by adding the code above, but no difference observed. Still fonts are not loading.
It looks like your application cannot access the fonts folder, so make sure you have proper rights to access the fonts folder. It is also possible that your font configuration code is not processed at first and before using any other Aspose.Cells APIs. So, make sure the suggested line of code should be processed at first (at the start) and before using any other Aspose.Cells APIs. Moreover, see the document on how to configure fonts for rendering spreadsheets for your complete reference.
By the way, you may create a custom folder to store fonts and then specify that folder path using FontConfigs at the start of your program if it makes any difference.
In case, you still could not evaluate the issue, kindly do share screenshots of the installed fonts in folder, complete sample (runnable) code and output PDF by Aspose.Cells APIs for reference. We will check your issue soon.
@Amjad_Sahi We tried to check if required fonts are accessible to the code base or not and identified that it is accessible using below code.
InstalledFontCollection installedFontCollection = new InstalledFontCollection();
FontFamily[] fontFamilies;
// Get the array of FontFamily objects.
fontFamilies = installedFontCollection.Families;
var angFont = fontFamilies.Where(x => x.Name.ToLower() == "angsana new");
var angUpcFont = fontFamilies.Where(x => x.Name.ToLower() == "angsanaupc");
if (angFont.Count() > 0 || angUpcFont.Count() > 0)
{
System.Diagnostics.EventLog eventLog = new System.Diagnostics.EventLog();
eventLog.Source = "Angsana";
eventLog.WriteEntry("Angsana fonts are available",
System.Diagnostics.EventLogEntryType.Information);
}
we created a custom folder to store fonts and then specify that folder path using FontConfigs at the start of program but it doesn’t make any difference.
Angsana fonts are getting replaced with Simsun fonts we are not able to understand how it is replacing. Both fonts are available in the fonts folder. Below is the screenshot of installed fonts in folder.
font1.PNG (129.0 KB)
font2.PNG (130.8 KB)
font3.PNG (133.5 KB)
font4.PNG (82.6 KB)
Below is the complete runnable code.
string dataDir = System.IO.Path.GetFullPath(@"..\..\");
IndividualFontConfigs fontConfigs = new IndividualFontConfigs();
fontConfigs.SetFontFolder(dataDir + "Fonts", false);
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(dataDir + "\\Aspose.Total.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.FontConfigs = fontConfigs;
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());
}
}
}
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);
Below are the source and converted file using above code.
1543221858.zip (225.4 KB)
Thanks for the screenshots and samples.
I tried your code with your template file using latest version of Aspose.Cells, it works absolutely fine on my end. See the attached output PDF which is fine tuned as you may check. The Angsana fonts are correctly used by Aspose.Cells for .NET.
1543221858 (1).pdf (461.8 KB)
By the way, I checked your output PDF file (in your attached zipped archive) and found the PDF file was actually generated by Aspose.PDF for .NET 21.3 and was not generated by Aspose.Cells for .NET. So, the issue may not be with Aspose.Cells APIs. If you still think it is an issue with Aspose.Cells for .NET APIs, kindly do execute your exact code with latest version of Aspose.Cells for .NET using your template file and generate the PDF file. Share the output PDF by Aspose.Cells for .NET here, we will check it further.
@Amjad_Sahi We are converting Excel to PDF using Aspose.Cells and then merging all the generated pdfs using Aspose.PDF. Just to confirm, once PDF is generated, no font settings are applied in merge operation by Aspose. Please confirm.
Also, is there any setting by which we can restrict Aspose to use only specified folder and not use default font folder?
We can only evaluate issues related to Aspose.Cells APIs here. If you could open the PDF generated by Aspose.Cells into Adobe acrobat, you will confirm that Angsana fonts are properly used, so there should be no issue on Aspose.Cells part. The issue might be there when you merge PDFs (“then merging all the generated pdfs using Aspose.PDF”).
See the document on configuring fonts for rendering spreadsheets to know the actual process Aspose.Cells uses for rendering:
Yes, all Windows operating systems are supported, see the document on System Requirements for your reference.