excel表中的超级脚本不会出现在asp.net的数据集中

im trying to import excel sheet values to text box. in dataset super script values not coming . like x to the power of 2 . its coming as x2 in dataset i need to get super scripts as it is in data set

即时尝试将Excel工作表值导入文本框。在数据集超级脚本值不来。像x到2的力量。它作为x2在数据集中出现我需要获得超级脚本,因为它在数据集中

below is the code to get values from excel

下面是从excel获取值的代码

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                //Create Connection to Excel work book
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel
                OleDbCommand cmd = new OleDbCommand("Select [Answer],[Question],[Option1],[Option2],[Option3],[Option4],[Solution] from [Sheet1$]", excelConnection);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);

kindly check the screen shot

请检查屏幕截图

http://postimg.org/image/wbb6bv1d5/

1 个解决方案

#1

The fundamental problem is that the superscripting is not part of the cell's value, it is part of the cell's formatting.

基本问题是上标不是单元格值的一部分,它是单元格格式的一部分。

Your code extracts the cell's values which are, in effect, just simple strings and then you store these strings in your DataSet.

您的代码提取单元格的值,实际上只是简单的字符串,然后将这些字符串存储在DataSet中。

You therefore have 2 issues:

因此,您有两个问题:

You need to work out how you are going to represent, store and use this "formatted text" in your code

您需要弄清楚如何在代码中表示,存储和使用此“格式化文本”

You need to work out how to extract this extra formatting information from the excel spreadsheet

您需要弄清楚如何从Excel电子表格中提取这些额外的格式信息

I see 3 possible approaches to resolve this. Only you will know which fits best within your 'big picture' based on what you are trying to achieve and how this fits in with your other code but, based on what I know, these are roughly in order of preference:

我看到3种可能的方法来解决这个问题。根据你想要实现的目标以及它与其他代码的匹配程度,只有你知道哪个最适合你的“大图”,但根据我所知,这些大致按照优先顺序排列:

Those formatted "strings" with superscripts look awfully like numbers expressed in scientific notation to me! If that is the case you would be well advised to store them as such in both the Excel spreadsheet and within your code if at all possible. Doing this means that you then have those floating point numbers stored in your DataSet after the import. You could then feasibly display them in whichiver way you need when you come to use them from your DataSet

带有上标的那些格式化的“字符串”看起来非常像用科学记数法表达给我的数字!如果是这种情况,建议您尽可能在Excel电子表格和代码中存储它们。这样做意味着您可以在导入后将这些浮点数存储在DataSet中。然后,当您从DataSet中使用它们时,可以以您需要的方式显示它们

Plan B would be to extract the formatting information from the Excel worksheet so you can replicate this within your code. You have 2 problems to solve with this approach - The first is that you need to use a different means to export the data from your Excel file, the second is you need some suitable object to store a representation of the formatted text with the superscript element. These are acheivable (if a little kludgy) using a library like EPPlus. For example, using html to represent the string I could do something like this (within a suitable loop to loop through all the excel rows you want to export):

B计划是从Excel工作表中提取格式信息,以便您可以在代码中复制它。使用此方法需要解决2个问题 - 第一个是您需要使用不同的方法从Excel文件中导出数据,第二个是您需要一些合适的对象来存储带有上标元素的格式化文本的表示。使用像EPPlus这样的库是可以实现的(如果有点kludgy)。例如,使用html来表示字符串我可以做这样的事情(在一个合适的循环中循环遍历你要导出的所有excel行):
```
using (ExcelPackage package = new ExcelPackage(new FileInfo(@"C:\temp\superscriptExample.xlsx")))
{
 var ws = package.Workbook.Worksheets.First();


 ExcelRichTextCollection richText = ws.Cells[1, 1].RichText;
 string htmlString = "";
 foreach (var part in richText)
 {
 if (part.VerticalAlign == ExcelVerticalAlignmentFont.Superscript)
 {
 htmlString += "" + part.Text + "";
 }
 else
 {
 htmlString += part.Text;
 }
 }

} 
```
Plan C: If you are certain that all your strings are of the format "xxxxxx10ssN" where ss is the part to be in superscript you could feasibly just parse each one into an html element (or whatever other object you are using to represent the formatted text) with something like the following:

计划C:如果您确定所有字符串的格式为“xxxxxx10ssN”,其中ss是上标部分,您可以将每个字符串解析为html元素(或者您用来表示格式的任何其他对象) text)具有如下内容:
```
static string GetHtmlRepresentation(string inputTxt)
 {
 int indexOf10 = inputTxt.IndexOf("10");
 int indexOfN = inputTxt.IndexOf("N", indexOf10);
 string beforeSupPart = inputTxt.Substring(0, indexOf10 + 2);
 string supPart = inputTxt.Substring(indexOf10 + 2, indexOfN - (indexOf10 + 2));
 string afterSup = inputTxt.Substring(indexOfN, inputTxt.Length - indexOfN);

 return beforeSupPart + "" + supPart + "" + afterSup;
 }
```
This option is horribly ugly but if you just need a bit of a quick and dirty 'get the job done' solution then maybe you want to look at it.

这个选项非常丑陋但是如果你只是需要一点快速而且肮脏的“完成工作”解决方案,那么也许你想看看它。

#1