用OleDbConnection打开Excel
前一段时间做了一个打开Excel的,代码如下:
1 OleDbConnection conn = null;
2 OleDbDataAdapter myCommand = null;
3
4 string connectString2003 = string.Format("provider = microsoft.jet.oledb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", filePath);
5 string connectString2007 = string.Format("provider = microsoft.ace.oledb.12.0;data source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=1'", filePath);
6
7 if (filePath.ToLower().Contains(".xlsx"))
8 conn = new OleDbConnection(connectString2007);
9 else
10 conn = new OleDbConnection(connectString2003);
11
12 DataTable dt = new DataTable();
13 string strExcel = string.Format("select {0} from [" + WorksheetName + "$]", ColumnsName);
14 conn.Open();
15 myCommand = new OleDbDataAdapter(strExcel, conn);
16 myCommand.Fill(dt);
2 OleDbDataAdapter myCommand = null;
3
4 string connectString2003 = string.Format("provider = microsoft.jet.oledb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", filePath);
5 string connectString2007 = string.Format("provider = microsoft.ace.oledb.12.0;data source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=1'", filePath);
6
7 if (filePath.ToLower().Contains(".xlsx"))
8 conn = new OleDbConnection(connectString2007);
9 else
10 conn = new OleDbConnection(connectString2003);
11
12 DataTable dt = new DataTable();
13 string strExcel = string.Format("select {0} from [" + WorksheetName + "$]", ColumnsName);
14 conn.Open();
15 myCommand = new OleDbDataAdapter(strExcel, conn);
16 myCommand.Fill(dt);
Note:WorksheetName是要打开的Excel文件里的sheet名, ColumnsName是"F1,F2,F3..."
如果Workbook是有密码保护的,用上述方法是打不开的,可以用Microsoft.Office.Interop.Excel.dll打开; 如果Worksheet是有密码保护的,上述方法依然可以打开。