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

问题描述

我想用以下code读取Excel文件在C＃

 字符串excelFileName =Book2.xls中;
字符串excelConnectString = @供应商= Microsoft.Jet.OLEDB.4.0;数据源= Book2.xls中;扩展属性=的Excel 8.0; HDR = YES;;
//字符串excelConnectString = @供应商= Microsoft.Jet.OLEDB.4.0;数据源=+ excelFileName +; +扩展属性= Excel的8.0; HDR =是; IMEX = 1;OleDbConnection的objConn =新的OleDbConnection（excelConnectString）;
OleDbCommand的objCmd =新的OleDbCommand（SELECT * FROM [工作表Sheet1 $]，objConn）;OleDbDataAdapter的objDatAdap =新OleDbDataAdapter的（）;
objDatAdap.SelectCommand = objCmd;
DataSet的DS =新的DataSet（）;
objDatAdap.Fill（DS）;
 

一切工作fine.Now我的要求是读取Excel文件类似下面

  SELECT A，B，D从[工作表Sheet1]
 

解决方案

在选择命令应该是这样的，如果你想读A1至D1：

  SELECT * FROM [SHEETNAME_HERE $ A1：D1]
 

全code：

  OleDbConnection的CON =新的OleDbConnection（
                    供应商= Microsoft.Jet.OLEDB.4.0;数据源=
                    + XLS_FILE_NAME_AND_PATH_HERE
                    +;扩展属性= Excel的8.0;）;                StringBuilder的stbQuery =新的StringBuilder（）;
                stbQuery.Append（SELECT * FROM [+ SHEETNAME_HERE +$ A1：D1]）;
                OleDbDataAdapter的ADP =新OleDbDataAdapter的（stbQuery.ToString（），CON）;                数据集dsXLS =新的DataSet（）;
                adp.Fill（dsXLS）;                数据视图dvEmp =新的数据视图（dsXLS.Tables [0]）;                dataGridView1.DataSource = dvEmp;
 

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);

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

SELECT A,B,D From [Sheet1];

解决方案

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

SELECT * FROM [SHEETNAME_HERE$A1:D1]

Whole Code:

                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屋！