使用 C# 访问 Excel 电子表格有时会返回某些单元格的空白值

Question

我需要访问 Excel 电子表格并将电子表格中的数据插入 SQL 数据库。 然而，主键是混合的，大多数是数字，有些是字母数字。

我遇到的问题是，当数字和字母数字键位于同一电子表格中时，字母数字单元格返回空白值，而所有其他单元格返回数据没有问题。

我正在使用 OleDb 方法访问 Excel 文件。 使用命令字符串检索数据后，我将数据放入 DataAdapter 中，然后填充 DataSet。 我迭代数据集中第一个数据表中的所有行 (dr)。

我通过使用 dr["..."].ToString() 引用列。

如果我在 Visual Studio 2008 中调试项目并查看“扩展属性”，通过将鼠标悬停在“dr”上，我可以查看DataRow 的值，但应为字母数字的主键是 {}。 其他值用引号引起来，但空白值带有大括号。

这是 C# 问题还是 Excel 问题？

有没有人以前遇到过这个问题，或者可能找到了解决方法/修复？

提前致谢。

Answer 1

解决方案：

连接字符串：

Provider=Microsoft.Jet.OLEDB.4.0;数据源=FilePath;扩展
属性=“Excel 8.0;HDR=是;IMEX=1”;

1. HDR=Yes; 表示第一行包含列名，而不是数据。 HDR=No; 表示相反。

2. IMEX=1; 告诉驱动程序始终将“混合”（数字、日期、字符串等）数据列读取为文本。 请注意，此选项可能会对 Excel 工作表写入访问产生负面影响。

SQL 语法SELECT * FROM [sheet1$]。 即 Excel 工作表名称后跟 $ 并用 [ ] 括号括起来。

重要提示：

• 检查位于注册表 REG_DWORD“TypeGuessRows”的 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]。 这是不让 Excel 仅使用前 8 行来猜测列数据类型的关键。 将此值设置为 0 以扫描所有行。 这可能会影响性能。

• 如果 Excel 工作簿受密码保护，则即使通过连接字符串提供正确的密码，也无法打开它进行数据访问。 如果您尝试，您会收到以下错误消息：“无法解密文件。”

Answer 2

Excel 数据源为整个列选择列类型。 如果其中一个单元格与该类型不完全匹配，则会留下这样的空白。 我们遇到了问题，打字员在数字列中输入了“8”（数字前的空格，因此 Excel 将其转换为该单元格的字符串）。 对我来说，尝试 .Net Parse 方法是有意义的，因为它们更强大，但我想这不是 Excel 驱动程序的工作方式。

由于我们使用数据库导入服务，我们的修复方法是记录所有以这种方式“失败”的行。 然后，我们返回 XLS 文档并重新输入这些单元格，以确保基础类型正确。 （我们发现仅仅删除空格并不能解决问题——我们必须先清除整个单元格，然后重新输入“8”。）感觉很老套，也不灵活，但这是我们发现的最好的方法。 如果 Excel 驱动程序本身无法正确读取数据，那么一旦您进入 .Net，您就无法从那里取出该数据。

这是 Office 以简单性为名向用户隐藏重要详细信息的另一种情况，因此当您必须精确地使用电源时，这会变得更加困难。

Answer 3

{} 表示这是某种空对象，而不是字符串。 当您将鼠标悬停在对象上时，您应该能够看到其类型。 同样，当您使用 Quickwatch 查看 dr["..."] 时，您应该看到对象类型。 您收到的对象是什么类型？

Answer 4

ItemArray 是一个对象数组。 因此，我假设我尝试引用的 DataRow 中的“列”是对象类型。

Answer 5

为了兼容 VISTA，您可以在连接字符串中使用 EXCEL 12.0 驱动程序。 这应该可以解决您的问题。 它做到了我的。

Answer 6

解决方案：

1. 您设置 HDR=No，以便第一行不被视为列标题。
   连接字符串：Provider=Microsoft.Jet.OLEDB.4.0；数据源=FilePath；扩展属性=“Excel 8.0；HDR=No；IMEX=1”；
2. 您忽略第一行，并通过任何您想要的方式访问数据（DataTable、DataReader 等）。 您可以通过数字索引而不是列名来访问列。

这对我有用。 这样你就不必修改寄存器了！

Answer 7

我在此处回答了类似的问题。 为了您的方便，我在这里复制并粘贴了相同的答案：

我遇到了同样的问题，但能够解决它，而无需求助于 Excel COM 接口或第 3 方软件。 它涉及一点处理开销，但似乎对我有用。

1. 首先读入数据以获取列名称，
2. 然后使用每个列创建一个新的 DataSet，将每个列的 DataType 设置为字符串。
3. 再次将数据读入这个新的
   数据集。 瞧——科学的
   符号现在消失了，所有内容都作为字符串读入。

这里有一些代码说明了这一点，作为额外的好处，它甚至是 StyleCopped！

public void ImportSpreadsheet(string path)
{
    string extendedProperties = "Excel 12.0;HDR=YES;IMEX=1";
    string connectionString = string.Format(
        CultureInfo.CurrentCulture,
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"{1}\"",
        path,
        extendedProperties);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM [Worksheet1$]";
            connection.Open();

            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            using (DataSet columnDataSet = new DataSet())
            using (DataSet dataSet = new DataSet())
            {
                columnDataSet.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(columnDataSet);

                if (columnDataSet.Tables.Count == 1)
                {
                    var worksheet = columnDataSet.Tables[0];

                    // Now that we have a valid worksheet read in, with column names, we can create a
                    // new DataSet with a table that has preset columns that are all of type string.
                    // This fixes a problem where the OLEDB provider is trying to guess the data types
                    // of the cells and strange data appears, such as scientific notation on some cells.
                    dataSet.Tables.Add("WorksheetData");
                    DataTable tempTable = dataSet.Tables[0];

                    foreach (DataColumn column in worksheet.Columns)
                    {
                        tempTable.Columns.Add(column.ColumnName, typeof(string));
                    }

                    adapter.Fill(dataSet, "WorksheetData");

                    if (dataSet.Tables.Count == 1)
                    {
                        worksheet = dataSet.Tables[0];

                        foreach (var row in worksheet.Rows)
                        {
                            // TODO: Consume some data.
                        }
                    }
                }
            }
        }
    }
}

Answer 8

按 ascii 代码按降序对 xls 文件中的记录进行排序，以便字母数字字段将显示在标题行下方的顶部。 这确保读取的第一行数据将数据类型定义为“varchar”或“nvarchar”

Answer 9

嗨，所有这些代码也获取字母数字值

using System.Data.OleDb;

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties="+(char)34+"Excel 8.0;IMEX=1;"+(char)34;

string CommandText = "select * from [Sheet1$]";

OleDbConnection myConnection = new OleDbConnection(ConnectionString);
myConnection.Open();

OleDbDataAdapter myAdapter = new OleDbDataAdapter(CommandText, myConnection);

ds = null;
ds = new DataSet();
myAdapter.Fill(ds);

Answer 10

这并不完全正确！ 显然，如果前 8 行为空，无论 IMEX=1 为何，Jet/ACE 始终假定为字符串类型。 即使我将注册表中的行读取为 0，我仍然遇到同样的问题。 这是让它正常工作的唯一可靠的方法：
<代码> <代码>

try
{
    Console.Write(wsReader.GetDouble(j).ToString());
}
catch   //Lame unfixable bug
{
    Console.Write(wsReader.GetString(j));
}

>