using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
//使用OLEDB操作Excel
//关于OLEDB介绍参考: http://www.cnblogs.com/moss_tan_jun/archive/2012/07/28/2612889.html
//连接字符串:
// xls : "Provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source="+fileName+";"+";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""
// xlsx: "Provider=Microsoft.ACE.OLEDB.12.0;"+"Data Source="+fileName+";"+";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""
namespace _004Excel查询示例
{
class Program
{
static void Main(string[] args)
{
//连接excel文件:
string fileName = "Test.xlsx";
string s = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
OleDbConnection connect = new OleDbConnection(s);
connect.Open();
//查询取得结果:
string sql = "select id,work,address from [Sheet1$] where work='计算机' and address='湖北';";
OleDbDataAdapter adapter = new OleDbDataAdapter(sql,connect);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet); //结果填充到dataSet里面
connect.Close();
//取得结果的tables:
DataTableCollection tableCollection = dataSet.Tables;
DataTable table = tableCollection[0]; //因为这里我们只查询了一个sheet,所以只有一个表格结果
//取得table的表头:
DataColumnCollection columnCollection = table.Columns;
//取得table的所有行:
DataRowCollection rowCollection = table.Rows;
//取得表头的字段值:
foreach (var column in columnCollection)
{
Console.Write(column + "|");
}
Console.WriteLine();
//取得每一行的字段值:
foreach (DataRow row in rowCollection)
{
//我这里有5个字段columns=5:索引0-4
int columns = columnCollection.Count;
for (int i=0;i< columns; i++)
{
Console.Write(row[i]+"|");
}
Console.WriteLine();
}
Console.ReadKey();
}
}
}
004Excel查询示例.csproj
最新推荐文章于 2024-08-27 08:30:24 发布