oledb导入xls与xlsx文件

// Excel 2007 file
if (FileName.Substring(FileName.Length - 4) == "xlsx")
{
    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName +
                                    ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
}
// xls extension file
if (FileName.Substring(FileName.Length - 3) == "xls")
{
    excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}

‘HDR=Yes，代表 Excel 档中的工作表第一行是标题栏,No，代表 Excel 档中的工作表无标题行

 ‘IMEX 汇入模式  0 只读  1 只写   2 可读写

获取工作表名称。

‘获取第一个工作表名称
Private Function GetFirstSheetName(ByVal file As String) As String
Dim sConnectionString As [String] = String.Empty
Dim kz As String = IO.Path.GetExtension(file).ToLower
Select Case kz
Case “.xls”
sConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & file & “;Extended Properties=Excel 8.0;”
Case “.xlsx”
sConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & file & “;Extended Properties=Excel 12.0;”
End Select

Dim cn As New OleDbConnection(sConnectionString)
cn.Open()
Dim tb As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
cn.Close()
If tb.Rows.Count > 0 Then
Return “[” & tb.Rows(0)(“TABLE_NAME”).ToString.Replace(“‘”, “”) & “]”
Else
Throw New Exception(“错误的 ” & file & ” 文件，不能获取Sheet名称。”)
End If
End Function