How to get NPOI Excel RichStringCellValue?

Question

I am using DotNetCore.NPOI (1.2.1) in order to read an MS Excel file.

Some of the cells are of type text and contain formatted strings (e.g. some words in bold).

How do I get the formatted cell value? My final goal: Retrieve the cell text as HTML.

I tried

var cell = row.GetCell(1);
var richStringCellValue = cell.RichStringCellValue;

But this won't let me access the formatted string (just the plain string without formattings).

Does anybody have an idea or solution?

Answer 1

I think you'll have to take longer route in this case. First you'll have to maintain the formatting of cell value like date, currency etc and then extract the style from cell value and embed the cell value under that style.

best option is to write extenstion method to get format and style value.

To get the fomat Please see this link How to get the value of cell containing a date and keep the original formatting using NPOI

For styling first you'll have to check and find the exact style of running text and then return the value inside the html tag , below method will give you idea to extract styling from cell value. Code is untested , you may have to include missing library.

 public void GetStyleOfCellValue()
        {

            XSSFWorkbook wb = new XSSFWorkbook("YourFile.xlsx");
            ISheet sheet = wb.GetSheetAt(0);
            ICell cell = sheet.GetRow(0).GetCell(0);  
            XSSFRichTextString richText = (XSSFRichTextString)cell.RichStringCellValue;
            int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;

            for (int i = 0; i < formattingRuns; i++)
            {
                int startIdx = richText.GetIndexOfFormattingRun(i);
                int length = richText.GetLengthOfFormattingRun(i);
                Console.WriteLine("Text: " + richText.String.Substring(startIdx, startIdx + length));
                if (i == 0)
                {
                    short fontIndex = cell.CellStyle.FontIndex;
                    IFont font = wb.GetFontAt(fontIndex);
                    Console.WriteLine("Bold: " + (font.IsBold)); // return string <b>my string</b>.
                    Console.WriteLine("Italics: " + font.IsItalic + "\n"); // return string <i>my string</i>. 
                    Console.WriteLine("UnderLine: " + font.Underline + "\n"); // return string <u>my string</u>. 
                }
                else
                {
                    IFont fontFormat = richText.GetFontOfFormattingRun(i);
                    Console.WriteLine("Bold: " + (fontFormat.IsBold)); // return string <b>my string</b>.
                    Console.WriteLine("Italics: " + fontFormat.IsItalic + "\n");// return string <i>my string</i>. 
                }
            }
        }