C#读取Excel sheet方法
时间：2010-11-03 来源：aspc
原先导入excel代码如下：
代码
private void EcxelToGridView()
代码
{
labelControl1.Text = " ";
//根据路径打开一个Excel文件并将数据填充到ds中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + BtnExcPath.Text + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
//获取Excel中的sheet的名称（当执行到这句的时候特别慢，消耗资源太多）
string SheetName = GetExcelSheetNames(BtnExcPath.Text)[0];
//方法一：直接写死为第一个sheet1，这么扩展性不好
//strExcel = "select * from [sheet1$]";
//方法二：目前也是只取第一页但是可以给出提示
strExcel = "select * from [" + SheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
System.Data.DataSet ds = new System.Data.DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
List<string> strList = new List<string>();
string str = string.Empty;
strList.Clear();
if (ds.Tables["table1"].Rows.Count == 0)
{
MessageBoxShow.ShowProMessage("要导入的Excel没有数据");
}
}
/// <summary>
/// 获取获得当前你选择的Excel Sheet的所有名字
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static string[] GetExcelSheetNames(string filePath)
{
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
int count = wb.Worksheets.Count;
string[] names = new string[count];
for (int i = 1; i <= count; i++)
{
names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
}
wb.Close(null, null, null);
excelApp.Quit();
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
excelApp = null;
wbs = null;
wb = null;
return names;
}
第二种方法:
代码
private void EcxelToGridView(){
labelControl1.Text = " ";
//根据路径打开一个Excel文件并将数据填充到ds中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + BtnExcPath.Text + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{//我是倒序插入到strTableNames中，因为dtSheetName中行是从后往前读sheet页的
strTableNames[k] = dtSheetName.Rows[dtSheetName.Rows.Count-k-1]["TABLE_NAME"].ToString();
}
//从指定的表明查询数据,可先把所有表明列出来供用户选择
strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
System.Data.DataSet ds = new System.Data.DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
List<string> strList = new List<string>();
string str = string.Empty;
strList.Clear();
if (ds.Tables["table1"].Rows.Count == 0)
{
MessageBoxShow.ShowProMessage("要导入的Excel没有数据");
}
}
方法二比上一个方法快了很多
相关阅读 更多 +
