static System.Data.DataTable dt = new System.Data.DataTable();
/// <summary>
/// 读取数据源
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="dgBom"></param>
public void ReadExcel(string sExcelFile)
{
try
{
dt.Clear();
dt_date.Clear();
System.Data.DataTable ExcelTable;
DataSet ds = new DataSet();
//Excel的连接
string strconn = "provider=microsoft.jet.oledb.4.0;" + "data source=" + sExcelFile + ";" + "extended properties=excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strconn);
objConn.Open();
System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
string strSql = "select * from [" + tableName + "]";
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
ExcelTable = ds.Tables[tableName];
dt = ds.Tables[tableName];
//dgBom.DataSource = ExcelTable;
//dgBom.DataBind();
objConn.Close();
}
catch
{
try {
dt.Clear();
dt_date.Clear();
System.Data.DataTable ExcelTable;
DataSet ds = new DataSet();
//Excel的连接
string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
OleDbConnection objConn = new OleDbConnection(strconn);
objConn.Open();
System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
string strSql = "select * from [" + tableName + "]";
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
ExcelTable = ds.Tables[tableName];
dt = ds.Tables[tableName];
//dgBom.DataSource = ExcelTable;
//dgBom.DataBind();
objConn.Close();
}
catch(Exception ex)
{
Common.Message.Show(ex.Message, this);
}
}
}