- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助] 分步导入excel时这个代码怎么写呢？ (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=126144)
-- 发布时间：2018/10/15 14:58:00
-- [求助] 分步导入excel时这个代码怎么写呢？
导入excel时，想采用先找到文件，然后导入
但是我把正常的文件目录，改成了TextBox1,想通过这个导入，但是不行，请问该怎么弄呢？
Dim mg As New Merger
此主题相关图片如下：导入.jpg
mg.SourcePath = "TextBox1"
mg.Format = "excel" \'指定格式
mg.SourceTableName = "sheet0$" \'指定要合并的表
mg.DataTableName = "oyi店铺引流关键词" \'指定接收数据的表
mg.Merge() \'开始合并
此主题相关图片如下：导入.jpg-- 发布时间：2018/10/15 15:05:00
--
Dim mg As New Merger
mg.SourcePath = e.form.controls("TextBox1").Text
mg.Format = "excel" \'指定格式
mg.SourceTableName = "sheet0$" \'指定要合并的表
mg.DataTableName = "oyi店铺引流关键词" \'指定接收数据的表
mg.Merge() \'开始合并
-- 发布时间：2018/10/15 15:30:00
--
谢谢但是导入时发生错误，显示：类型不匹配
因为excel文档里的数字是已文本形式储存的，请问，怎样才能不修改元文件的格式（把以文本形式存储的数字转化为数字）而直接导入呢
-- 发布时间：2018/10/15 15:33:00
--
参考
http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=101056&skin=0
http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=123476&skin=0
-- 发布时间：2018/10/15 17:15:00
--
请问老师 我改用上面代码，怎么修改呢
红色的这个部分该怎么加上去呢
Dim Book As New XLS.Book =
e.form.controls("TextBox1").Text
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim t As Table = Tables("杭康设备总表")
t.StopRedraw()
Dim newcount As Integer = 0
Dim oldcount As Integer = 0
For n As Integer = 1 To Sheet.Rows.Count -1
Dim r As DataRow = t.DataTable.Find("序号 = \'" & sheet(n, 0).text & "\'")
If r Is Nothing Then
r = t.DataTable.AddNew()
newcount += 1
Else
oldcount += 1
End If
For i As Integer = 0 To sheet.Cols.Count -1
Dim cname As String = sheet(0, i).text
If t.Cols.Contains(cname) Then
If t.Cols(cname).IsBoolean Then
If sheet(n, i).Text = 1 OrElse sheet(n, i).Text = "true" Then
r(cname) = True
Else
r(cname) = False
End If
Else
r(cname) = sheet(n, i).Text
End If
End If
Next
Next
t.ResumeRedraw()
-- 发布时间：2018/10/15 17:16:00
--
Dim Book As New XLS.Book(e.form.controls("TextBox1").Text)
-- 发布时间：2018/10/15 18:16:00
--
我想实现，只要要导入的excel日期列的该行的日期大于数据库内的最大日期，就把该行导入，请问这个代码该怎么弄
Dim Book As New XLS.Book(e.form.controls("Text1").Text)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim t As Table = Tables("店铺数据")
t.StopRedraw()
Dim newcount As Integer = 0
Dim oldcount As Integer = 0
For n As Integer = 1 To Sheet.Rows.Count -1
Dim d1 As Date = DataTables("店铺数据").Compute("max(日期)")
‘数据库内的表的日期列
Dim d2 As Date = DataTables("店铺数据").(“日期”) ’导入的excel内的数据列
If d1<d2 Then
r = t.DataTable.AddNew()
newcount += 1
Else
oldcount += 1
End If
For i As Integer = 0 To sheet.Cols.Count -1
Dim cname As String = sheet(0, i).text
If t.Cols.Contains(cname) Then
If t.Cols(cname).IsBoolean Then
If sheet(n, i).Text = 1 OrElse sheet(n, i).Text = "true" Then
r(cname) = True
Else
r(cname) = False
End If
Else
r(cname) = sheet(n, i).Text
End If
End If
Next
Next
t.ResumeRedraw()
-- 发布时间：2018/10/15 20:16:00
--
Dim d2 As Date = cdate(sheet(n, execl日期列的索引).Text)
-- 发布时间：2018/10/15 22:05:00
--
，又出问题了，因为excel中每天有几十条数据用下面的代码时
每天只导入一条数据
但是设想需要的是 把这几十条数据都导入进去，请问怎么改呢
Dim Book As New XLS.Book(e.form.controls("Text1").Text)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim t As Table = Tables("店铺数据")
t.StopRedraw()
Dim newcount As Integer = 0
Dim oldcount As Integer = 0
For n As Integer = 1 To Sheet.Rows.Count -1
Dim d1 As Date = DataTables("店铺数据").Compute("max(统计日期)") \'数据库内的表的日期列
Dim d2 As Date = cdate(sheet(n, 0).Text)
If d1 < d2 Then
Dim r As Row = Tables("店铺数据").AddNew()
For i As Integer = 0 To sheet.Cols.Count -1
Dim cname As String = sheet(0, i).text
If t.Cols.Contains(cname) Then
If t.Cols(cname).IsBoolean Then
If sheet(n, i).Text = 1 OrElse sheet(n, i).Text = "true" Then
r(cname) = True
Else
r(cname) = False
End If
Else
r(cname) = sheet(n, i).Text
End If
End If
Next
End If
Next
-- 发布时间：2018/10/15 22:07:00
--
Dim Book As New XLS.Book(e.form.controls("Text1").Text)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim t As Table = Tables("店铺数据")
t.StopRedraw()
Dim newcount As Integer = 0
Dim oldcount As Integer = 0
Dim d1 As Date = DataTables("店铺数据").Compute("max(统计日期)") \'数据库内的表的日期列 For n As Integer = 1 To Sheet.Rows.Count -1
Dim d2 As Date = cdate(sheet(n, 0).Text)
If d1 < d2 Then
Dim r As Row = Tables("店铺数据").AddNew()
For i As Integer = 0 To sheet.Cols.Count -1
Dim cname As String = sheet(0, i).text
If t.Cols.Contains(cname) Then
If t.Cols(cname).IsBoolean Then
If sheet(n, i).Text = 1 OrElse sheet(n, i).Text = "true" Then
r(cname) = True
Else
r(cname) = False
End If
Else
r(cname) = sheet(n, i).Text
End If
End If
Next
End If
Next