查询excel表在c＃ [英] Query excel sheet in c#

问题描述

我想使用以下代码读取c＃中的Excel文件

I want to read Excel file in c# using following code

string excelFileName = "Book2.xls";
string excelConnectString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Book2.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
//string excelConnectString = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + excelFileName + ";" + "Extended Properties = Excel 8.0; HDR=Yes;IMEX=1";

OleDbConnection objConn = new OleDbConnection(excelConnectString);
OleDbCommand objCmd = new OleDbCommand("Select * From [Sheet1$]", objConn);

OleDbDataAdapter objDatAdap = new OleDbDataAdapter();
objDatAdap.SelectCommand = objCmd;
DataSet ds = new DataSet();
objDatAdap.Fill(ds);

一切正常工作。现在我的要求是阅读excel文件，如下所示

Everything is working fine.Now my requirement is to read the excel file something like below

SELECT A,B,D From [Sheet1];

推荐答案

如果你想要的话，Select-command应该是这样的阅读A1到D1：

The Select-command should look like this if you want to read A1 to D1:

SELECT * FROM [SHEETNAME_HERE$A1:D1]

整个代码：

                OleDbConnection con = new OleDbConnection(
                    "provider=Microsoft.Jet.OLEDB.4.0;data source="
                    + XLS_FILE_NAME_AND_PATH_HERE
                    + ";Extended Properties=Excel 8.0;");

                StringBuilder stbQuery = new StringBuilder();
                stbQuery.Append("SELECT * FROM [" + SHEETNAME_HERE + "$A1:D1]");
                OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

                DataSet dsXLS = new DataSet();
                adp.Fill(dsXLS);

                DataView dvEmp = new DataView(dsXLS.Tables[0]);

                dataGridView1.DataSource = dvEmp;

这篇关于查询excel表在c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！