OpenFileDialog 无法加载 CSV 文件，但可以加载 xls/xlsx Excel 文件 [英] OpenFileDialog can't load CSV files but can load xls/xlsx Excel files

问题描述

在我的 Windows 窗体应用程序中加载 Excel 文件时，我可以加载 .xls 和 .xlsx 格式，但是当我选择 .CSV 格式时> 我收到以下错误:

When loading Excel files in my Windows Form application, I can load .xls and .xlsx formats fine but when I select a .CSV I get the following error:

System.NullReferenceException: '未将对象引用设置为对象的实例.'sConnectionString 为空.

System.NullReferenceException: 'Object reference not set to an instance of an object.' sConnectionString was null.

错误发生在线路上:

if (sConnectionString.Length > 0)

来自完整的代码部分:

public string sConnectionString;
public void FillData()
{
    if (sConnectionString.Length > 0)
    {
        OleDbConnection cn = new OleDbConnection(sConnectionString);
        {
            cn.Open();
            DataTable dt = new DataTable();
            OleDbDataAdapter Adpt = new OleDbDataAdapter("select * from [sheet1$]", cn);
            Adpt.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}

在按钮代码之前:

private void Browse_Click(object sender, EventArgs e)
{
    OpenFileDialog op = new OpenFileDialog();
    op.InitialDirectory = @"C:";
    op.Title = "Browse Excel Files";
    op.CheckFileExists = true;
    op.CheckPathExists = true;
    op.DefaultExt = "csv";
    op.Filter = "CSV Files (*.csv)|*.csv";
    op.FilterIndex = 2;
    op.RestoreDirectory = true;
    op.ReadOnlyChecked = true;
    op.ShowReadOnly = true;

    if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        if (File.Exists(op.FileName))
        {
            string[] Arr = null;
            Arr = op.FileName.Split('.');
            if (Arr.Length > 0)
            {
                if (Arr[Arr.Length - 1] == "xls")
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    op.FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            }
            else if (Arr[Arr.Length - 1] == "xlsx")
            {
                sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + op.FileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
            }
        }
        FillData();
        fileTextBox.Text = op.FileName;
    }
}

编辑

添加:

else if (Arr[Arr.Length - 1] == "csv")
    {
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + op.FileName + 
                        ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
    }

仍然出现同样的错误.

推荐答案

关于报错:

System.NullReferenceException:未将对象引用设置为一个对象的实例. sConnectionString 为空.

System.NullReferenceException: Object reference not set to an instance of an object. sConnectionString was null.

产生异常是因为连接字符串被声明为:

The exception is generated because the Connection string is declared as:

public string sConnectionString;

因为它从未被初始化，因为连接字符串的初始化仅对某些文件类型执行，而不是对 OpenFileDialog.Filter 中包含的所有文件类型执行.当代码测试字符串的长度时，字符串仍然是null.这可以避免设置初始值:

Since it's never initialized, because the initialization of the Connection string is performed only for some file types but not all those included in the OpenFileDialog.Filter. When the code tests the length of the string, the string is still null. This can be avoided setting an initial value:

public string sConnectionString = string.Empty;

关于使用 OleDbConnection 打开 .CSV 文件所需的连接字符串:

About the Connection string required to oped a .CSV file with an OleDbConnection:

• 所有 OleDb 提供商都会:
  • Microsoft.Jet.OLEDB.4.0
  • Microsoft.ACE.OLEDB.12.0
  • Microsoft.ACE.OLEDB.16.0

  Microsoft 数据库引擎 2010 可再发行版
  Microsoft 数据库引擎 2016 可再发行版

  要读取 CSV 文件，所有提供程序的连接字符串的组成如下:

  To read a CSV file, the connection string - for all providers - is composed as:

  {Provider};Data Source={Catalog}; Extended Properties="text; HDR=Yes; IMEX=1; FMT=Delimited;
  

  地点:

  • {Provider} => OleDb 提供商之一.他们中的任何一个都可以.

  • {Provider} => One of the OleDb providers. Any of them will do.

  {Catalog} => 包含要打开的文件的目录.

  {Catalog} => The Directory that contains the file to open.

  HDR=Yes/No => CSV 文件包含一个Header:如果Yes，Header 是文件的第一行

  HDR=Yes/No => The CSV file contains a Header: if Yes, the Header is the first line of the file

  IMEX=1 => 导入/导出模式设置为 1(导出模式 = 0；导入模式 = 1，链接模式 = 2)，以忽略数值并仅使用字符串.在这里实际上并不相关.最好保留它，作为一般帮助(以防文件中没有标题并且 HDR=Yes).

  IMEX=1 => Import/Export Mode set to 1 (Export Mode = 0; Import Mode = 1, Linked Mode = 2), to ignore numeric values and use strings only. Not actually relevant here. Better keep it, as a general aid (in case there's no Header in the file and HDR=Yes).

  FMT=Delimited => 文件格式:分隔.标题/字段由分隔符分隔.识别的分隔符是逗号 (,).此设置可能取决于系统(第三部分应用程序可能出于自身原因修改了注册表).要指定不同于默认的分隔符(CSV 中的 C 表示逗号)，必须有一个 Schema.ini 文件在 Catalog 为特定文件定义特定分隔符的文件夹:

  FMT=Delimited => File format: Delimited. The Header/Fields are separated by a delimiter. The recognized delimiter is a Comma (,). This setting may be System-dependant (a 3rd part app may have modified the Registry for it's own sake). To specify a delimiter different from the default (the C in CSV means comma), there must be a Schema.ini file in the Catalog folder that defines a specific delimiter for a specific file:

    [MyFile.csv]
    Format=Delimited(;)
  

• 由于 Data Source 是目录名(将其视为数据库)，因此在查询中指定要打开的文件的文件名:

• Since the Data Source is a directory name (consider it the Database), the file name of the file to open is specified in the query:

    SELECT * FROM MyFile.csv
  

使用 Microsoft.ACE.OLEDB.12.0 作为提供者的示例连接字符串:

Sample Connection string using Microsoft.ACE.OLEDB.12.0 as provider:

string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                            "Extended Properties="text; HDR=Yes; IMEX=1; FMT=Delimited";";

有关其他可用的连接字符串格式，请参阅连接字符串参考网站.

See The Connection Strings Reference web site for other available Connection string formats.

测试结果的示例代码(在本例中使用Microsoft.Jet.OLEDB.4.0):

Sample code to test the results (using Microsoft.Jet.OLEDB.4.0 in this case):

private void Browse_Click(object sender, EventArgs e)
{
    string userFileName = string.Empty;
    using (var ofd = new OpenFileDialog()) {
        ofd.Filter = "CSV Files|*.csv|Excel '97-2003|*.xls|Excel 2007-2019|*.xlsx";
        if (ofd.ShowDialog(this) == DialogResult.OK) {
            userFileName = ofd.FileName;
        }
    }
    
    if (userFileName.Length == 0) return;
    dataGridView1.DataSource = GetData(userFileName);
}

private DataTable GetData(string userFileName)
{
    string dirName = Path.GetDirectoryName(userFileName);
    string fileName = Path.GetFileName(userFileName);
    string fileExtension = Path.GetExtension(userFileName);
    string conString = string.Empty;
    string query = string.Empty;

    switch (fileExtension)
    {
        // Can also use Microsoft.ACE.OLEDB.12 or Microsoft.ACE.OLEDB.16
        case ".xls":
            conString = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                           "Extended Properties="Excel 8.0; HDR=Yes; IMEX=1"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        // Can also use Microsoft.ACE.OLEDB.16
        case ".xlsx":
            conString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                           "Extended Properties="Excel 12.0; HDR=Yes; IMEX=1"";
            query = "SELECT * FROM [Sheet1$]";
            break;
        // Can also use Microsoft.ACE.OLEDB.16
        case ".csv":
            conString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                           "Extended Properties="text; HDR=Yes; IMEX=1; FMT=Delimited"";
            query = $"SELECT * FROM {fileName}";
            break;
    }
    return FillData(conString, query);
}

private DataTable FillData(string conString, string query)
{
    var dt = new DataTable();
    using (var con = new OleDbConnection(conString)) { 
        con.Open();
        using (var cmd = new OleDbCommand(query, con))
        using (var reader = cmd.ExecuteReader()) {
            dt.Load(reader);
        };
    }
    return dt;
}

这篇关于OpenFileDialog 无法加载 CSV 文件，但可以加载 xls/xlsx Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！