获取sheet名为“ZL”的表数据
/// <summary>
/// 获取数据表
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="sheetname"></param>
/// <returns></returns>
public static DataTable GetData(string path, string year, string month, string sheetname = "ZL")
{
path += year + month + ".xls";
string table = "mapTable";
sheetname = sheetname + "$";
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
System.Data.DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, path));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet名称
string sheetName = null;
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
string name = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
if (name == sheetname) // 这里取到特定sheet名称的那个表
sheetName = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
}
//查询sheet中的数据
string strSql = "select * from [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds, table);
dt = ds.Tables[0];
return dt;
}
catch (Exception exc)
{
throw exc;
}
finally
{
conn.Close();
conn.Dispose();
}
}
获取表栏目
/// <summary>
/// 根据datatable获得列名
/// </summary>
/// <param name="dt">表对象</param>
/// <returns>返回结果的数据列数组</returns>
private static string[] GetColumnsByDataTable(DataTable dt)
{
string[] strColumns = null;
if (dt.Columns.Count > 0)
{
int columnNum = 0;
columnNum = dt.Columns.Count;
strColumns = new string[columnNum];
for (int i = 0; i < dt.Columns.Count; i++)
{
strColumns[i] = dt.Columns[i].ColumnName;
}
}
return strColumns;
}