Replacing a font by another in an Excel file.
Sometimes you might want to replace all occurrences of a font by another inside an Excel file. Maybe the old font isn't widely available in some new platforms that you want to support, maybe there are licensing issues with the font, or maybe your company changed their corporate font and you need to change it in all existing reports. The reason doesn't really matter. So, how do we do it?
Note
Sometimes you might want to only change the font in the resulting PDF file, not in the xls/x files. If you only care about PDF outputs, make sure also to read the PDF exporting guide
Step 1: Manually inspect the old and the new font.
Before even starting, let's make this clear: Fonts can have different metrics. Some fonts are very similar, like Arial and Helvetica, but some other fonts can be completely different.
If the fonts you are replacing have similar metrics, you can skip to the next step. But if they don't, the first thing to do is to analyze how different they are.
In this example we are going to replace Calibri by Verdana, so let's write a sentence in Calibri 11pt and Verdana 11pt to see how different they are:
Oops... they are quite different. So we can't just do a "search and replace" from Calibri to Arial, without breaking the report layout. For this particular phrase, Calibri 11pt should be replaced by something like Verdana 8.5 pt:
And this means that the existing Calibri text must be reduced in size by about 0.8 to get a similar text in Verdana.
Note
0.8 is the factor for the phrase that we tested: "The secret of getting ahead is getting started." While it shouldn't vary too much, some phrases will be shorter or longer, depending on the metrics of every character in both fonts. We are doing no exact science here, and unless you are replacing very similar fonts like Arial by Helvetica, you must expect some layouts to break.
Step 2: Replace the fonts.
Ok, so we now know we have to replace Calibri 11pt by Verdana 8.5pt, or more in general Calibri Npt by Verdana N*0.8pt.
In Excel, we would have to change the fonts in every cell, and the styles, and the themes, and text inside autoshapes, and text inside charts, and so on. But luckily for us, in FlexCel it is more straightforward. There are two places we have to change: The font list and the themes. And you can do so with the code below:
const string FontToReplace = "Calibri";
const string ReplaceWith = "Verdana";
const double FontFactor = 0.8;
XlsFile xls = new XlsFile("original_file.xlsx", true);
//Change the fonts
for (int i = 0; i <= xls.FontCount; i++)
{
TFlxFont fnt = xls.GetFont(i);
if (String.Equals(fnt.Name, FontToReplace, StringComparison.OrdinalIgnoreCase))
{
fnt.Name = ReplaceWith;
fnt.Size20 = (int)(fnt.Size20 * FontFactor);
}
xls.SetFont(i, fnt);
}
//Change the theme font
var Theme = xls.GetTheme();
Theme.Name = "My theme";
Theme.Elements.FontScheme.Name = "My font scheme";
var textFont = new TThemeTextFont(ReplaceWith, "", TPitchFamily.FIXED_PITCH__SWISS_FONT_FAMILY, TFontCharSet.Ansi);
if (String.Equals(Theme.Elements.FontScheme.MajorFont.Latin.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MajorFont.Latin = textFont;
}
if (String.Equals(Theme.Elements.FontScheme.MajorFont.ComplexScript.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MajorFont.ComplexScript = textFont;
}
if (String.Equals(Theme.Elements.FontScheme.MajorFont.EastAsian.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MajorFont.EastAsian = textFont;
}
if (String.Equals(Theme.Elements.FontScheme.MinorFont.Latin.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MinorFont.Latin = textFont;
}
if (String.Equals(Theme.Elements.FontScheme.MinorFont.ComplexScript.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MinorFont.ComplexScript = textFont;
}
if (String.Equals(Theme.Elements.FontScheme.MinorFont.EastAsian.Typeface, FontToReplace,
StringComparison.OrdinalIgnoreCase))
{
Theme.Elements.FontScheme.MinorFont.EastAsian = textFont;
}
xls.SetTheme(Theme);
xls.Save("result_file.xlsx");