view plaincopy to clipboardprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace MyExcel
{
public class ExcelConnector
{
string connString;
public ExcelService(string path)
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
}
private OleDbConnection conn;
public OleDbConnection Connection
{
get
{
if (conn == null)
{
conn = new OleDbConnection(connString);
conn.Open();
}
else if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
else if (conn.State == ConnectionState.Closed)
{
conn = new OleDbConnection(connString);
conn.Open();
}
return conn;
}
}
/// <summary>
/// 获取Excel 中的工作表
/// </summary>
/// <returns></returns>
public List<string> GetDataFromExcelWithAppointSheetName()
{
DataTable dtSheetName = null;
try
{
dtSheetName = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
List<string> strTableNames = new List<string>();
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
string s = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
Console.WriteLine(s);
//过滤一下没用的表，Excel 默认生成的隐藏文件
if (!s.Contains("_FilterDatabase") && s.LastIndexOf('_') + 1 != s.Length)
{
strTableNames.Add(s);
}
}
return strTableNames;
}
catch (Exception)
{
return null;
}
finally
{
Connection.Dispose();
}
}
/// <summary>
/// 通过工作表名 获取数据
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public DataTable GetContentBySheetName(string name)
{
DataTable dt = new DataTable();
OleDbDataAdapter myCommand = null;
string strExcel = "select * from [" + name + "]";
try
{
myCommand = new OleDbDataAdapter(strExcel, Connection);
dt = new DataTable();
myCommand.Fill(dt);
return dt;
}
catch (Exception)
{
return null;
}
finally
{
myCommand.Dispose();
Connection.Dispose();
}
}
}
}