C# 获取指数格式的excel数据
Excel 已控制格式，如 此处
在 Excel 中的示例数据（字母数字）中所述：-
在我的示例代码中：-
DataTable dt = new DataTable("PartUpload");
DataColumn 列；
列=新的DataColumn();
column.DataType = System.Type.GetType("System.String");
列.AllowDBNull = true;
column.ColumnName = "部分原创";
dt.Columns.Add(列);
列=新的DataColumn();
column.DataType = System.Type.GetType("System.String");
列.AllowDBNull = true;
Column.ColumnName = "Part_ToString";
dt.Columns.Add(列);
列=新的DataColumn();
column.DataType = System.Type.GetType("System.String");
列.AllowDBNull = true;
Column.ColumnName = "Part_TryParse";
dt.Columns.Add(列);
字符串 connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;数据源=C:\Documents and Settings\seahc1\Desktop\test1.xls;扩展属性=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1 ”;
OleDbConnection objConn = new OleDbConnection(connStr);
objConn.Open();
OleDbDataAdapter adap = new OleDbDataAdapter(@"从 [Sheet1$] 选择零件作为 [原始零件]", connStr);
adap.Fill(dt);
foreach（dt.Rows 中的 DataRow 行）
{
row["Part_ToString"] = row["原始部分"].ToString();
双双ConverResult；
bool result = double.TryParse(row["Part Original"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out doubleConverResult);
如果（结果）
{
row["Part_TryParse"] = doubleConverResult.ToString();
}
}
Part_ToString = 指数 = "3.1270013375e+011"
Part_TryParse = 四舍五入到最近的=312700133750
我调试并发现 adap.Fill(dt) 用指数数据填充数据表。
如何通过 C# 程序获得准确的值，因为我不希望最终用户格式化他们的 Excel 电子表格。
请多多指教，谢谢。
Excel had controlled over the formatting as explained under here
In my sample data (alphanumeric) in excel: -
In my sample code: -
DataTable dt = new DataTable("PartUpload");
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "Part Original";
dt.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "Part_ToString";
dt.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.AllowDBNull = true;
column.ColumnName = "Part_TryParse";
dt.Columns.Add(column);
string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\seahc1\Desktop\test1.xls;Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1";
OleDbConnection objConn = new OleDbConnection(connStr);
objConn.Open();
OleDbDataAdapter adap = new OleDbDataAdapter(@"SELECT Part as [Part Original]FROM [Sheet1$]", connStr);
adap.Fill(dt);
foreach (DataRow row in dt.Rows)
{
row["Part_ToString"] = row["Part Original"].ToString();
double doubleConverResult;
bool result = double.TryParse(row["Part Original"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out doubleConverResult);
if (result)
{
row["Part_TryParse"] = doubleConverResult.ToString();
}
}
Part_ToString = with exponential = "3.1270013375e+011"
Part_TryParse = round to nearest=312700133750
I debug and found that adap.Fill(dt) fill the datatable with data in exponential.
How can I get the exact value through the C# programs because I do not want the end user to format their excel spreadsheet.
Please advice, Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我明白水晶的意思了。
创建一个 Excel 文档并将数字 312700133751 放入单元格中。单元格的格式必须为“常规”。
Excel 将显示 3.127E+11 而不是 312700133751。
问题是，当您使用 OleDbConnection 驱动程序读取文件时，DataTable 将填充“3.1270013505e+011”字符串值（即使 Excel 具有“General”格式化）。
问题是 3.1270013505e+011 等于 312700133750，而不是 312700133751（注意最后一位数字，是零而不是一）。这就是问题所在！ OleDbConnection 将数字的小数位“截断”至 10 位，这会在转换中留下一位额外的数字。
我也没有找到解决办法...
I see what Crystal means.
Create an Excel document and put 312700133751 number into a cell. The formatting of the cell must be "General".
Excel will display 3.127E+11 instead of 312700133751.
The problem is that, when you read the file with the OleDbConnection driver, the DataTable will be filled with the "3.1270013505e+011" string value (even when Excel has the "General" formatting).
The problem is that 3.1270013505e+011 equals 312700133750, not 312700133751 (notice the last digit, is a zero instead of a one). This is the issue! That the OleDbConnection is "truncating" the decimals of the number up to 10 digits, which is leaving one extra digit out of the conversion.
I haven't found a solution either...