'读取Excel中Sheet表名称(做项目用的是用VB.net的代码，如果有朋友是用C#改改就好，语法不同而且,这是我项目中的一部分代码，朋友们可根据自已的需要去改善自已的代码，我只是提供一个例子而且,希望对需要的人有些帮助)
Private Function GetExcelSheetName(ByVal FilePath As String) As Data.DataTable Try Dim FilePaths As String = FilePath ' ' F:\G2000-S041 G2000MEN GMF MASS NON-CHINA SP-2012 ORDER FORM.xls Dim FileWZ = FilePaths.LastIndexOf(".") '文件的位置 Dim File_H_Z = FilePaths.Substring(FileWZ) '得到后缀 If File_H_Z = ".xls" Then conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + FilePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'" End If If File_H_Z = ".xlsx" Then conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + FilePath + ";Extended Properties ='Excel 12.0;HDR=YES;IMEX=1'" End If If conn.State = ConnectionState.Closed Then conn.Open() End If Return conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"}) Catch ex As Exception Throw ex Finally If Not conn Is Nothing Then conn.Close() If Not conn Is Nothing Then conn.Dispose() End Try End Function
'创建-----dataTable表---------------
dt.Columns.Add("SheetName") dt.Columns.Add("Season") dt.Columns.Add("Item") dt.Columns.Add("PC") dt.Columns.Add("Factory") dt.Columns.Add("GVPO") dt.Columns.Add("Supplier") '供应商 dt.Columns.Add("Supplier_PI") '供应商 dt.Columns.Add("Trim_Ref") '编号 dt.Columns.Add("Color_Size") '颜色 dt.Columns.Add("Qty") '数量 dt.Columns.Add("Country") '产地 Dim dr As DataRow '创建行dr = dt.NewRow '创建行 '------------------------------------------------'----start取表名以及得到不同连接语句
Dim sheets As DataTable = GetExcelSheetName(File1.Value) '表列表集合(sheetName,因为一个execl中有多个表) Dim objConn As New OleDbConnection '连接字符串 Dim v_sheet As String '取表名 Dim strSql As String '得到不同sql语句 Dim ds As New DataSet '创建ds文档 Dim Connn_String As String Dim FilePaths As String = File1.Value ' F:\G2000-S041 G2000MEN GMF MASS NON-CHINA SP-2012 ORDER FORM.xls Dim FileWZ = FilePaths.LastIndexOf(".") '文件的位置 Dim File_H_Z = FilePaths.Substring(FileWZ) '得到后缀 If File_H_Z = ".xls" Then Connn_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + FilePaths + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'" End If If File_H_Z = ".xlsx" Then Connn_String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + FilePaths + ";Extended Properties ='Excel 12.0;HDR=NO;IMEX=1'" End If '----------------------------------------------------------------------- '-----对execl进行表操作-------------------------------------------------
objConn.ConnectionString = Connn_String
objConn.Open() '打开 For i As Integer = 0 To sheets.Rows.Count - 1 '循环表， Dim flag As Boolean = False v_sheet = sheets.Rows(i).Item("TABLE_NAME").ToString.Replace("''", "'") '得到不同的表 Dim TempV_Sheet As String = v_sheet.Replace("'", "").Trim() If TempV_Sheet.Substring(TempV_Sheet.Length - 1) = "$" ThenstrSql = "Select * From [" + v_sheet + "]" '来自数据表
Dim objCmd As New OleDbCommand(strSql, objConn) '创建命令对象 Dim objAdataper As New OleDbDataAdapter(objCmd) '创建适配器 Dim objds As New DataSet objAdataper.Fill(objds) '填充ds '----------------------- 操作数 ---------------------------------------------- For j As Integer = 0 To objds.Tables(0).Rows.Count - 1 '行数'If objds.Tables(0).Rows(0)(8) = "SEASON" Then
'End If
If Not IsDBNull(objds.Tables(0).Rows(j)(0)) Then '防止为空DBNULL If (objds.Tables(0).Rows(j)(0) = "Item") Then '表示读取到第j行第0列的时候判断是否为ItemFor h As Integer = j + 1 To objds.Tables(0).Rows.Count - 1 '之后就从第j行进行操作
If IsDBNull(objds.Tables(0).Rows(h)(0)) Then flag = True ' Exit For '退出 Else Dim TrimRefIsNull As Boolean = False dr = dt.NewRow dr("SheetName") = v_sheet.Replace("'", "") '表名 dr("Season") = objds.Tables(0).Rows(0)(10) '表示第0行第10列6 dr("Item") = objds.Tables(0).Rows(h)(0) 'item dr("PC") = objds.Tables(0).Rows(h)(1) ' dr("Factory") = objds.Tables(0).Rows(h)(2) dr("GVPO") = objds.Tables(0).Rows(h)(3) dr("Supplier") = objds.Tables(0).Rows(h)(4) dr("Supplier_PI") = objds.Tables(0).Rows(h)(5)dr("Trim_Ref") = objds.Tables(0).Rows(h)(6)
If (Not IsDBNull(dr("Trim_Ref"))) Then TrimRefIsNull = True End If dr("Color_Size") = objds.Tables(0).Rows(h)(7) dr("Qty") = objds.Tables(0).Rows(h)(8) dr("Country") = objds.Tables(0).Rows(h)(9) If (TrimRefIsNull) Then dt.Rows.Add(dr) dt.Dispose()End If
End If Next '---------------------------------------------------------------------------------------End If
End IfNext
End If
Next
'-------------------------------- for循环dt -----------------------------------------For i As Integer = 0 To dt.Rows.Count - 1 '循环表，
If (IsDBNull(dt.Rows(i)("SheetName"))) Then
dt.Rows(i)("SheetName") = "" End If If (IsDBNull(dt.Rows(i)("Season"))) Then dt.Rows(i)("Season") = "" End If If (IsDBNull(dt.Rows(i)("Item"))) Then dt.Rows(i)("Item") = "" End If If (IsDBNull(dt.Rows(i)("PC"))) Then dt.Rows(i)("PC") = "" End If If (IsDBNull(dt.Rows(i)("Factory"))) Then dt.Rows(i)("Factory") = "" End If If (IsDBNull(dt.Rows(i)("GVPO"))) Then dt.Rows(i)("GVPO") = "" End If If (IsDBNull(dt.Rows(i)("Supplier"))) Then dt.Rows(i)("Supplier") = "" End If If (IsDBNull(dt.Rows(i)("Supplier_PI"))) Then dt.Rows(i)("Supplier_PI") = "" End If If (IsDBNull(dt.Rows(i)("Trim_Ref"))) Then dt.Rows(i)("Trim_Ref") = "" End If If (IsDBNull(dt.Rows(i)("Color_Size"))) Then dt.Rows(i)("Color_Size") = "" End If If (IsDBNull(dt.Rows(i)("Qty"))) Then dt.Rows(i)("Qty") = "" End If If (IsDBNull(dt.Rows(i)("Country"))) Then dt.Rows(i)("Country") = "" End If '------插入数据表----------------------------------------------------- Insert(dt.Rows(i)("SheetName"), dt.Rows(i)("Season"), dt.Rows(i)("Item"), dt.Rows(i)("PC"), dt.Rows(i)("Factory"), dt.Rows(i)("GVPO"), dt.Rows(i)("Supplier"), dt.Rows(i)("Supplier_PI"), dt.Rows(i)("Trim_Ref"), dt.Rows(i)("Color_Size"), dt.Rows(i)("Qty"), dt.Rows(i)("Country")) Next Datagrid1.DataSource = Bind() Datagrid1.DataBind()'Datagrid1.Items(0).Cells(0)..BackColor = Color.LightGray
For f As Integer = 0 To Datagrid1.Items.Count - 1 '循环表， If (Datagrid1.Items(f).Cells(12).Text = " ") Then Datagrid1.Items(f).Cells(8).BackColor = Color.Red End If If (Datagrid1.Items(f).Cells(6).Text <> "3") Then Datagrid1.Items(f).Cells(6).BackColor = Color.Red End If' Dim SheetName As String = Datagrid1.Items(f).Cells(0).Text 'SheetName
' Dim Season As String = Datagrid1.Items(f).Cells(1).Text 'Season ' Dim Item_Type As String = Datagrid1.Items(f).Cells(2).Text 'Season ' Dim PC_NO As String = Datagrid1.Items(f).Cells(3).Text 'PC_NO ' Dim Factory_ID As String = Datagrid1.Items(f).Cells(4).Text 'Factory_ID ' Dim GV_PO As String = Datagrid1.Items(f).Cells(5).Text 'GV_PO ' Dim Supplier_No As String = Datagrid1.Items(f).Cells(6).Text 'Supplier_No ' Dim Trim_Ref_No As String = Datagrid1.Items(f).Cells(8).Text 'Trim_Ref_No ' Dim Color_Size As String = Datagrid1.Items(f).Cells(9).Text 'Color_Size ' Dim Qty As String = Datagrid1.Items(f).Cells(10).Text 'Qty ' Dim Country As String = Datagrid1.Items(f).Cells(11).Text 'Country ' Dim Price As String = Datagrid1.Items(f).Cells(14).Text 'Price ' Dim Unit As String = Datagrid1.Items(f).Cells(15).Text 'Unit ' Dim SizeFlag As String = Datagrid1.Items(f).Cells(17).Text 'SizeFlag ' Dim DetailFlag As String = Datagrid1.Items(f).Cells(18).Text 'DetailFlag ' Dim CartonFlag As String = Datagrid1.Items(f).Cells(19).Text 'CartonFlag' Insert_OrderTable(SheetName, Season, Item_Type, PC_NO, Factory_ID, GV_PO, Supplier_No, Trim_Ref_No, Color_Size, Qty, Country, Price, Unit, SizeFlag, DetailFlag, CartonFlag)
Next