This project has moved and is read-only. For the latest updates, please go here.

Subscript and Superscript part of a value

Jun 30, 2011 at 11:14 AM
Edited Jun 30, 2011 at 11:15 AM

I am attempting to use EPPlus to create a .xlsx file from database data. It is all working apart from values that contain subscripted and superscripted text.

The data is stored like so "Some Normal Text<sub>Subscripted</sub> Some other text <sup>superscripted</sup> some other text". So it stores the html tags.

If I allocate the Value property of a cell to this:-

worksheet.Cells[index, 3].Value = p.name;

then I just get the exact text in the cell including the <sub> and <sup> as text.

Using the Style attribute of a cell, I can set it to be superscript or subscript

worksheet.Cells[index, 3].Style.Font.VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Subscript;

but that changes the whole cell. I only want to change certain parts of the cell value.

Any ideas how I can do this?

Jun 30, 2011 at 12:18 PM
Edited Jun 30, 2011 at 1:10 PM

If you want to mix styles in one cell you need to use RichText property.

If RichText Class doesnt have what you want, you need to do by your own.. like in older versions. http://epplus.codeplex.com/discussions/207785

Based on Jan´s example here....http://epplus.codeplex.com/discussions/236637

        static void Main(string[] args)
        {

            FileInfo f = new FileInfo(DateTime.Now.Ticks + ".xlsx");
            ExcelPackage p = new ExcelPackage(f);

            var ws =  p.Workbook.Worksheets.Add("f");
            ws.Cells[1, 1].IsRichText = true;
 
            var rs = ws.Cells[1,1].RichText;
            var r1 = rs.Add("Blue ");
            r1.Color = Color.Blue;
            r1.Bold = true;
            r1.VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Subscript;
            
            var r2 = rs.Add("Red");
            r2.Color = Color.Red;
            r2.VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;

            p.Save();
        }



Jun 30, 2011 at 1:13 PM
Edited Jun 30, 2011 at 1:14 PM

<edit>Just seen the code example you posted. So, you have to create an instance of the RichText collection to then add subscript to it, I guess?</edit>

Thanks for the reply. I was just reading up about it.

What I think I need to do is iterate through the string property and then subscript and superscript the text between the HTML tags. Am just struggling with the syntax at the moment.

Can you just keep calling RichText.Add within a loop and it appends the text to the value in the cell already? This is basically what I have at the moment:-

 

                    if (oName.IndexOf("<sup>") != -1)
                    {
                        cName = cName.Substring(cIndex, cName.IndexOf("<sup>"));
                        cIndex = oName.IndexOf("<sup>", cIndex);
                        if (cName.Length != 0) worksheet.Cells[index, 3].RichText.Add(cName);
                        rName = oName.Substring(cIndex, ((oName.Length - 1) - cIndex));
                        while (rName.IndexOf("<sup>") != -1)
                        {
                            cName = oName.Substring((cIndex + 5), (oName.IndexOf("</sup>", cIndex) - (cIndex + 5)));
                            cIndex = oName.IndexOf("<sup>", cIndex);
                            if (cName.Length != 0) worksheet.Cells[index, 3].RichText.Add(cName).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
                            rName = oName.Substring((cIndex + 5), ((oName.Length - 1) - (cIndex + 5)));
                        }
                        if (rName.Length != 0) worksheet.Cells[index, 3].RichText.Add(rName);
                    }

I'm unsure if my logic is right at the moment, but do these lines look right?

 

worksheet.Cells[index, 3].RichText.Add(cName);
worksheet.Cells[index, 3].RichText.Add(cName).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;