老师 现在根据你的提示 我将代码完善如下:
运行的时候,总是很慢!需要操作近五分钟,感觉系统假死一样了 有没有办法简化,并提高数据导入效率呢?
就三个文件,删除行与列,更新第一行所有列名 然后创建临时表 再讲exel表中数据导入到狐表中
代码:
If FileSys.DirectoryExists("C:\MyFolder") Then '如果目录C:\MyFolder存在
'FileSys.DeleteDirectory("C:\MyFolder",2,3) '则删除之
Else
FileSys.CreateDirectory("C:\MyFolder")
End If
If FileSys.DirectoryExists("C:\MyFolder1") Then '如果目录C:\MyFolder存在
'FileSys.DeleteDirectory("C:\MyFolder1",2,3) '则删除之
Else
FileSys.CreateDirectory("C:\MyFolder1")
End If
Dim dlg As New OpenFileDialog
dlg.Filter = "excel文件|*.xls;*.xlsx"
dlg.MultiSelect = True '允许选择多个文件
If dlg.ShowDialog =DialogResult.OK Then
For Each fl As String In dlg.FileNames
Dim ps As System.Diagnostics.Process() = System.Diagnostics.Process.GetProcesses()
For Each p As System.Diagnostics.Process In ps
'output.show(p.processName)
If ("EXCEL" = p.ProcessName AndAlso p.MainWindowTitle = "")
output.show(p.ProcessName)
p.kill
End If
Next
Dim f2 As String ="C:\MyFolder\" & FileSys.GetName(fl)
If FileSys.FileExists(f2) Then
FileSys.DeleteFile(f2,2,2) '则彻底删除之
End If
Dim f3 As String ="C:\MyFolder1\" & FileSys.GetName(fl)
If FileSys.FileExists(f3) Then
FileSys.DeleteFile(f3,2,2) '则彻底删除之
End If
Dim s1 As String =FileSys.GetName(fl)
Dim s2 As String
Dim s3 As String
s2 = s1.SubString(0,4)
s3 = s1.SubString(4,2)
Dim Values() As String
Dim Book As New XLS.Book(fl) '定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) '引用工作簿的第一个工作表
Dim bb As Integer=Sheet.Rows.Count
Dim cc As Integer=Sheet.cols.Count
Dim 判断文件名 As String =FileSys.GetName(fl)
If 判断文件名.Contains("AJT1") Then
Sheet.Rows.RemoveAt(26) '在最前面插入一行
Sheet.Rows.RemoveAt(25) '在最前面插入一行
For ii As Integer = 0 To 5
Sheet.Rows.RemoveAt(0) '在最前面插入一行
Next
For ii As Integer = 0 To 3
Sheet.Cols.RemoveAt(0) '在最前面插入一行
Next
End If
If 判断文件名.Contains("AJT15") Then
For ii As Integer = 0 To 4
Sheet.Rows.RemoveAt(0) '在最前面插入一行
Next
End If
If 判断文件名.Contains("AJT20") Then
For ii As Integer = 0 To 3
Sheet.Rows.RemoveAt(0) '在最前面插入一行
Next
End If
Sheet.Cols.Insert(0)
Sheet.Cols.Insert(0)
Book.Save(f2)
Dim bm As String
Dim App As New MSExcel.Application
try
Dim Book2 As New XLS.Book(f2) '定义一个Excel工作簿
Dim Sheet2 As XLS.Sheet = Book2.Sheets(0) '引用工作簿的第一个工作表
Dim bb2 As Integer=Sheet2.Rows.Count
Dim cc2 As Integer=Sheet2.cols.Count
output.show(bb2 & "||||" & cc2)
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(f2)
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
'msgbox(s1)
Values = s1.Split(".")
'msgbox(Values.length)
bm=Values(0)
'msgbox(bm)
Ws.Name = "新建表"
'msgbox(Ws.Name)
Dim Rg As MSExcel.Range
For i1 As Integer = 1 To Sheet2.cols.Count '从第1行第1列开始 将所有单元格都重新定义为 "A" & i1
Rg = Ws.Cells(1,i1)
Rg.Value = "A" & i1
Next
For i As Integer = 1 To Sheet2.Rows.Count
Rg = Ws.Cells(i+1,1)
Rg.Value = s2
Rg = Ws.Cells(i+1,2)
Rg.Value = s3
Next
Wb.SaveAs(f3)
App.Visible = False
catch ex As exception
msgbox(ex.message)
finally
app.quit
End try
'
If DataTables.Contains(Values(0)) Then '如果表C已经加载
DataTables.Unload(Values(0)) '卸载表C
End If
Dim dtb As New DataTableBuilder(Values(0))
For ii As Integer = 1 To 50
dtb.AddDef("A" & ii, Gettype(Integer))
Next
dtb.Build()
MainTable= Tables(Values(0))
Dim Book1 As New XLS.Book(f3) '定义一个Excel工作簿
Dim Sheet1 As XLS.Sheet = Book1.Sheets(0) '引用工作簿的第一个工作表
Dim mg As New Merger
mg.SourcePath = f3
mg.Format = "excel" '指定格式
mg.SourceTableName = sheet1.name & "$"
mg.DataTableName = Values(0)
mg.Merge()
Next
End If '