NPOI 从Execel导出数据上下标问题（化学式）

最近在给一家化学工厂做数据导入导出，excel中导出的化学式上下标格式没有保留，此问题有点烧脑，网上找了许久没找到解决方案。在偶然调试中发现font属性里面FontSuperScript.Sub （下标）和FontSuperScript.Super（上标）。自己写了个化学式转换类进行处理。

 DataTable table = new DataTable();

            try
            {
                ISheet sheet = ExcelHelper.GetSheet(fileName);
                IRow headerRow = sheet.GetRow(0);

                // 添加列
                for (int i = 0; i < headerRow.LastCellNum; i++)
                {

                    ICell cell = headerRow.GetCell(i);
                    GridColumn col = gridView.VisibleColumns.OfType<GridColumn>().FirstOrDefault(x => x.Caption == cell + "");
                    if (col != null)
                    {
                        table.Columns.Add(col.FieldName);
                    }
                    else
                    {
                        table.Columns.Add(cell + "");
                    }
                }
                // 添加行
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < headerRow.LastCellNum; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Numeric && (cell + "").StartsWith("-"))
                            {
                                dataRow[j] = cell.DateCellValue;
                            }
                            else
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                                else
                                {
                                    IRichTextString richTextString = (IRichTextString)cell.RichStringCellValue;
                                    string cellValue = string.Empty;
                                    for (int m = 0; m < richTextString.Length; m++)
                                    {
                                       
                                        IFont font = sheet.Workbook.GetFontAt(richTextString.GetFontAtIndex(m));
                                        string str = string.Empty;
                                        string code= richTextString.ToString().Substring(m,1);
                                        switch (font.TypeOffset)
                                        {
                                            //下标
                                            case FontSuperScript.Sub:
                                                str = ChemistryHelper.GetSubChar(code);
                                                break;
                                            //上标
                                            case FontSuperScript.Super:
                                                str = ChemistryHelper.GetSuperChar(code);
                                                break;
                                            default:
                                                str = code;
                                                break;
                                        }
                                        cellValue = cellValue + str;
                                    }

                                    cell.SetCellValue(cellValue);
                                    dataRow[j] = cell + "";
                                }
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }

通过Unicode码进行化学式上下标转换 

 public sealed class ChemistryHelper
    {
        /// <summary>
        /// <para>说明：化学式上标</para>
        /// <para>创建人：</para>
        /// <para>创建日期：2018-07-25 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="str">The string.</param>
        /// <returns>System.String.</returns>
        public static string GetSuperChar(string str)
        {
            switch (str)
            {
                case "0": return "\x2070";
                case "1": return "\x00B9";
                case "2": return "\x00B2";
                case "3": return "\x00B3";
                case "4": return "\x2074";
                case "5": return "\x2075";
                case "6": return "\x2076";
                case "7": return "\x2077";
                case "8": return "\x2078";
                case "9": return "\x2079";
                case "+": return "\x207A";
                case "-": return "\x207B";
            }
            return str;
        }
        /// <summary>
        /// <para>说明：化学式下标</para>
        /// <para>创建人：</para>
        /// <para>创建日期：2018-07-25 </para>
        /// <para>修改人：</para>
        /// <para>修改日期：</para>
        /// <para>修改备注：</para>
        /// <para>版本：1.0</para>
        /// </summary>
        /// <param name="str">The string.</param>
        /// <returns>System.String.</returns>
        public static string GetSubChar(string str)
        {
            switch (str)
            {
                case "0": return "\x2080";
                case "1": return "\x2081";
                case "2": return "\x2082";
                case "3": return "\x2083";
                case "4": return "\x2084";
                case "5": return "\x2085";
                case "6": return "\x2086";
                case "7": return "\x2087";
                case "8": return "\x2088";
                case "9": return "\x2089";
                case "+": return "\x208A";
                case "-": return "\x208B";
            }
            return str;
        }
}

 测试如下所示：将excel 化学式成功导入表格中