最近在给一家化学工厂做数据导入导出,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 化学式成功导入表格中