By Ken Yang

stay Last blog post How to use NPOI to generate cells with rich text strings. Later, some netizens asked how to set the superscript and subscript. This is achieved by setting the HSSFFont.TypeOffset property (short type). I think the name TypeOffset is not intuitive and unfamiliar programmers will not associate it with superscript and subscript. TypeOffset property value 0 means no superscript or subscript is set; property value 1 means superscript and property value 2 means subscript. In the NPOI.HSSF.Record.FontRecord class, three public static variables, SS none, SS super and SS sub, are defined to correspond to these three types respectively.

The code is as follows:

using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

namespace NPOI_RichFormatString
{
    class Program
    {
        static void Main(string[] args)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Company Name";
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "Ken Yang";
            si.Subject = "Generating Rich Text String using NPOI";
            workbook.DocumentSummaryInformation = dsi;
            workbook.SummaryInformation = si;

            Sheet ws = workbook.CreateSheet("sheet1");

            //Set rich text string
             HSSFRichTextString rts1, rts2;
            rts1 = new HSSFRichTextString("Pepsi Cola®");

            rts2 = new HSSFRichTextString("X1 + X2 = 10");

            HSSFFont superscript = (HSSFFont)workbook.CreateFont();

            superscript.TypeOffset = NPOI.HSSF.Record.FontRecord.SS_SUPER;//Superscript
            superscript.Color = HSSFColor.RED.index;

            HSSFFont subscript = (HSSFFont)workbook.CreateFont();

            subscript.TypeOffset = NPOI.HSSF.Record.FontRecord.SS_SUB; //subscript
            subscript.Color = HSSFColor.RED.index;

            HSSFFont normalFont = (HSSFFont)workbook.CreateFont();

            // The first parameter of ApplyFont specifies the start position (0-based) of the substring, and the second parameter specifies the end position (excluding the characters in that position) of the substring.
            // For example: set the font of the 5th character in the following sentence.
            // The third parameter specifies the font.
            rts1.ApplyFont(4, 5, superscript); 
            rts2.ApplyFont(1, 2, subscript);
            rts2.ApplyFont(6, 7, subscript);

            ws.CreateRow(0).CreateCell(0).SetCellValue(rts1);

            ws.CreateRow(1).CreateCell(0).SetCellValue(rts2);

            FileStream file = new FileStream(@"e:\test.xls", FileMode.Create);

            workbook.Write(file);

        }
    }
}

Effect screenshot: