NPOI: Create a cell containing two differently sized strings

Question

so as the title intends, I want to creaete a cell in a NPOI 2.1.3-Workbook containing 2 strings: A "normal"-sized string and a "small"-sized string. => I want to change the font-size for a part of the cell.

My code so far:

var planCell = planRow.CreateCell(lineCnt + 1);

var planCellStyle = workbook.CreateCellStyle();
planCellStyle.WrapText = true;
planCellStyle.VerticalAlignment = VerticalAlignment.Top;
planCellStyle.Alignment = HorizontalAlignment.Left;
var font = workbook.CreateFont();
font.FontName = HSSFFont.FONT_ARIAL;
font.FontHeightInPoints = 16;
font.Boldweight = (short)FontBoldWeight.Bold;
planCellStyle.SetFont(font);
planCell.CellStyle = planCellStyle;

string planTitleContent = string.Empty;
... (some logic to get desired string for planTitleContent)

string planInfoContent = string.Empty;
... (some logic to get desired string for planInfoContent)

planCell.SetCellValue(planTitleContent + "\n\n"+planInfoContent);

To be precise, I want the "planInfoContent"-part to be shown in a smaller font-size than the "planCellContent"-part. I searched a lot, but I just found the CellStyle-value which applies to the whole cell. So I hope I am missing something for two cells are not really an option.

Answer 1

Just figured it out myself :)

First, create 2 fonts of the desired format (for me and for simplicities sake, only the font size is of relevance):

var font1 = excel.CreateFont();
font1.FontName = HSSFFont.FONT_ARIAL;
font1.FontHeightInPoints = 12;
font1.Boldweight = (short)FontBoldWeight.Normal;

var font2 = excel.CreateFont();
font2.FontName = HSSFFont.FONT_ARIAL;
font2.FontHeightInPoints = 8;
font2.Boldweight = (short)FontBoldWeight.Normal;

Then, after you got your string(s), make use of (N)POIs applyFont-Method.

One of its implementations in NPOI has the following signature:

applyFont(int startIndex, int endIndex, IFont font)

so now, having string planTitleContent and string planInfoContent, the remaining steps are pretty obvious: Just create an Instance of IRichTextString and add your strings to it via constructor-parameter. Then, apply the wanted fonts via the index like so:

IRichTextString formattedCellContent = new HSSFRichTextString(planTitleContent + "\n"+planInfoContent);
richString.ApplyFont(0, planTitleContent.Length, font1);
richString.ApplyFont(planTitleContent.Length + 1, (planTitleContent + "\n" + planInfoContent).Length, font2);

planCell.SetCellValue(formattedCellContent);

so, this works like a charm for me. Hope it helps some other folks out!