c# - 将任何 Excel 电子表格导入 DataGridView - C#

Question

我有一个程序可以将 Excel 电子表格导入到数据 GridView 中。我写的代码如下:

try
                {
                    OleDbConnectionStringBuilder connStringBuilder = new OleDbConnectionStringBuilder();
                    connStringBuilder.DataSource = file;
                    connStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    connStringBuilder.Add("Extended Properties", "Excel 8.0;HDR=NO;IMEX1");

                    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

                    DbDataAdapter adapter = factory.CreateDataAdapter();

                    DbCommand selectCommand = factory.CreateCommand();
                    selectCommand.CommandText = "SELECT * FROM [All Carpets to Excel$]";

                    DbConnection connection = factory.CreateConnection();
                    connection.ConnectionString = connStringBuilder.ConnectionString;

                    selectCommand.Connection = connection;

                    adapter.SelectCommand = selectCommand;

                    data = new DataSet();

                    adapter.Fill(data);

                    dataGridView1.DataSource = data.Tables[0].DefaultView;

                }
                catch (IOException)
                {

                }

行“selectCommand.CommandText = “SELECT * FROM [All Carpets to Excel$]”;”从具有该名称的工作表中获取数据。我想知道如何让这个程序打开具有任何工作表名称的 excel 文档。一个我可能不知道的。

Answer 1

您可以像这样获取所有工作表的名称..

public string[] GetExcelSheetNames(string excelFileName)
{
        OleDbConnection con = null;
        DataTable dt = null;
        String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;";
        con= new OleDbConnection(conStr);
        con.Open();
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            return null;
        }

        String[] excelSheetNames = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
        {
            excelSheetNames[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        return excelSheetNames;
}