C＃并从Excel文件中读取值

I have an Excel file and the columns have names (data source is NOT controlled by me.. it's given to me by a client). Although the columns change, the column headers never change.

我有一个Excel文件,列有名称(数据源不是由我控制的......它是由客户提供给我的)。虽然列发生了更改,但列标题永远不会更改。

In the file, it's called "First Name"

在文件中,它被称为“名字”

How do I access the data in every cell within the same column?

如何访问同一列中每个单元格中的数据?

5 个解决方案

#1

Open your Excel file as a database. Then you will not have to bother about the position of the columns:

将Excel文件作为数据库打开。那你就不用担心列的位置了:

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\MyExcelFile.xls;Extended Properties=\"Excel 8.0;HDR=YES\"";
using (var conn = new System.Data.OleDb.OleDbConnection(connString)) {
    conn.Open();
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("Select * From [SheetName$]", conn);
    OleDbDataReader reader = cmd.ExecuteReader();
    int firstNameOrdinal = reader.GetOrdinal("First Name");
    int lastNameOrdinal = reader.GetOrdinal("Last Name");
    while (reader.Read()) {
        Console.WriteLine("First Name: {0}, Last Name: {1}", 
            reader.GetString(firstNameOrdinal), 
            reader.GetString(lastNameOrdinal));
    }
}

#2

I would take a look at this example from Microsoft:

我想看看微软的这个例子:

How to query and display excel data by using ASP.NET, ADO.NET, and Visual C#.NET

如何使用ASP.NET,ADO.NET和Visual C#.NET查询和显示Excel数据

#3

I've used excelLibrary in the past and found it very easy to use.

我过去使用过excelLibrary,发现它很容易使用。

#4

I have successfully used the FileHelpers Library to read Excel files in the past.

我已经成功使用FileHelpers Library来读取过去的Excel文件。

#5

You can do something like this where you use ODBC to connect to the file and download the content of a sheet.

您可以执行以下操作,使用ODBC连接到文件并下载工作表的内容。

 private bool DownloadExcelData(string fileName, ref DataTable informationDT)
            {
                // bool success
                bool success = true;

                // open the file via odbc
                string connection = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
                connection = String.Format(connection, FilePath + fileName);
                OleDbConnection conn = new OleDbConnection(connection);
                conn.Open();

                try
                {
                    // retrieve the records from the first page
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Information$]", conn);
                    OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
                    adpt.Fill(informationDT);
                }
                catch { success = false; }

                // close the connection
                conn.Close();
                return success;
            }

Here are some sample ODBC connections for xls and xlsx files:

以下是xls和xlsx文件的一些示例ODBC连接:

<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'" />
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0" />

#1