处理EXCEL - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以下代码换成普通函数调用执行是没有问题的，换成异步函数调用就报“超出索引数组范围”的错。（处理单个文件不报错，处理带子目录的EXCEL就报这个错）请老师帮忙看看哪里写的不对？

调用异步代码：

For Each file In FileSys.GetFiles(path)

Functions.AsyncExecute("N多线程提取_异步",file)

FileTotal = FileTotal + 1

For Each p As String In FileSys.GetDirectories(path)

Functions.AsyncExecute("N提取处理函数",p)

异步函数关键代码：

DataTables("翻译内容").StopRedraw

Dim app As new MSExcel.Application

app.DisplayAlerts= False '不显示EXCEL警示

Dim excelcol As Integer

Dim excelrows As Integer

If file.EndsWith(".xls") OrElse file.EndsWith(".XLS") OrElse file.EndsWith(".xlsx") OrElse file.EndsWith(".XLSX") Then

If file.EndsWith(".xls") OrElse file.EndsWith(".XLS") Then

excelcol = 256

excelrows =65536

Else

excelcol = 16384

excelrows =1048576

End If

Dim wb As MsExcel.WorkBook

Dim WS As MsExcel.WorkSheet

try

wb=app.WorkBooks.open(file)

For k As Integer=1 To wb.worksheets.Count

Ws=Wb.WorkSheets(k)

Dim Rg As MSExcel.Range = Ws.UsedRange

'lbl9.text = "Sheet名称:" & " " & Ws.name

If rg.Count = 1 And rg(1).Value Is Nothing Then

Continue For

End If

If Numcb2.Text = Nothing And Numcb3.Text = Nothing Then

RowsMax = 0

ColsMax = 0

'获取最大行

For i As Integer = 1 To rg.Columns.count

Dim r = ws.cells(excelrows,i).End(MsExcel.XlDirection.xlUp).Row

If r > RowsMax Then

RowsMax = r

End If

'Output.Show("rowsmax =" & rowsmax )

'获取最大列

For i As Integer = 1 To RowsMax

Dim r = ws.cells(i,excelcol).End(MsExcel.XlDirection.xlToLeft).Column

If r > ColsMax Then

ColsMax = r

End If

If Rowsmax=1 And ColsMax=1 Then '解决只有一行一列数据时报错问题

InputValue(Rowsmax, "提示", ws.name & "输入最大行数:")

InputValue(ColsMax, "提示", ws.name & "输入最大列数:")

End If

Else

RowsMax = Numcb2.Value

ColsMax = Numcb3.Value

'Output.Show("ColsMax =" & ColsMax )

End If

'lbl8.text ="有效行列:" & " " & RowsMax & "," & Colsmax

rg = Ws.Range(Ws.Cells(1,1), Ws.Cells(RowsMax,ColsMax))

'output.show("rg.Count:" & rg.count)

Dim ary = rg.value

For i As Integer=1 To RowsMax

If CBox2.Checked OrElse rg.Rows(i).height <> 0 Then

For j As Integer = 1 To Colsmax

If CBox2.Checked OrElse rg.Columns(j).width <> 0 Then '提隐藏行

If ary(i,j) <> Nothing Then

If CBox1.Checked = False OrElse CBox1.Checked = True AndAlso System.Text.RegularExpressions.Regex.Match(ary(i,j),"[\u4e00-\u9fa5]+").Tostring()>"" '提中文

Dim dr As DataRow = DataTables("Table1").AddNew

dr("listfile") = ary(i, j)

'lbl5.text ="提取内容:" & ary(i,j)

'Application.DoEvents

End If

Else

txt5.text =file & "(" & Ws.name & ")" & vbcrlf & txt5.text & vbcrlf

End If

Catch ex As Exception

Dim txt2 As WinForm.TextBox = Forms("提取工具").Controls("TextBox4")

txt2.text = file & vbcrlf & txt2.text & vbcrlf

BadFileCount =BadFileCount +1

msgbox(ex.message)

finally

If ws IsNot Nothing Then ws = Nothing

If Wb IsNot Nothing Then Wb.Close(Type.Missing, Type.Missing, Type.Missing)

If Wb IsNot Nothing Then Wb = Nothing

End try

End If

'Next

DataTables("Table1").ResumeRedraw

app.quit()

"N提取处理函数"这个函数代码看看

就是上面调用异步的代码 For Each file In FileSys.GetFiles(path) Functions.AsyncExecute("N多线程提取_异步",file) FileTotal = FileTotal + 1 Next For Each p As String In FileSys.GetDirectories(path) Functions.AsyncExecute("N提取处理函数",p) Next

N提取处理函数"这个函数代码：

Dim path As String = args(0)

Dim file As Object

Dim cb1 As WinForm.CheckBox = Forms("提取工具").Controls("CBox")

Dim cb2 As WinForm.CheckBox = Forms("提取工具").Controls("CBox2")

For Each file In FileSys.GetFiles(path)

Functions.AsyncExecute("N多线程提取_异步",file)

FileTotal = FileTotal + 1

For Each p As String In FileSys.GetDirectories(path)

Functions.AsyncExecute("N提取处理函数",p)

看不出什么问题。在异步函数里，写日志调试一下，看看哪一句代码出错

怎么个写法，老师指导一下

首先异步里去掉DataTables("翻译内容").StopRedraw、Dim dr As DataRow = DataTables("Table1").AddNew这些用法：http://www.foxtable.com/mobilehelp/topics/277.htm

然后日志参考：http://www.foxtable.com/webhelp/topics/0435.htm

比如：

Dim wb As MsExcel.WorkBook

Dim WS As MsExcel.WorkSheet

try

wb=app.WorkBooks.open(file)

Output.Logs("日志").Add("打开文件" & file & Date.Now )

For k As Integer=1 To wb.worksheets.Count

Ws=Wb.WorkSheets(k)

Output.Logs("日志").Add("处理sheet" & k & Date.Now )

Dim Rg As MSExcel.Range = Ws.UsedRange

'lbl9.text = "Sheet名称:" & " " & Ws.name

If rg.Count = 1 And rg(1).Value Is Nothing Then

Continue For

End If

If Numcb2.Text = Nothing And Numcb3.Text = Nothing Then

Output.Logs("日志").Add("开始xxx操作" & Date.Now )

RowsMax = 0

ColsMax = 0

'获取最大行

For i As Integer = 1 To rg.Columns.count

Dim r = ws.cells(excelrows,i).End(MsExcel.XlDirection.xlUp).Row

If r > RowsMax Then

RowsMax = r

End If

……

finally

If ws IsNot Nothing Then ws = Nothing

If Wb IsNot Nothing Then Wb.Close(Type.Missing, Type.Missing, Type.Missing)

If Wb IsNot Nothing Then Wb = Nothing

Output.Logs("日志").Save("c:\log.txt",True)
Output.Logs("日志").Clear

End try

End If

又学会一招，试了一下，估计是Dim dr As DataRow = DataTables("Table1").AddNew这句的问题了。可帮助说“凡是在主线程中创建的对象，例如Table、Datatable，窗口及窗口控件，菜单及菜单组件，都不应该在异步函数中调用。”

我这是在异步里创建的datatable啊，为啥也不行。这种情况怎么解决呢？是要在异步中生成临时的DATATABLE吗？

临时表可以用，DataTables("xx")这种肯定不是临时表

把创建临时表的代码写在被调用的异步函数里，

If Tables.Contains("临时表") = False Then

Dim dtb As New DataTableBuilder("临时表")

dtb.AddDef("字段1", Gettype(String),500)

dtb.AddDef("字段2", Gettype(String),500)

dtb.Build()

MainTable= Tables("临时表")

end if

然后下面的“Dim dr As DataRow = DataTables("Table1").AddNew”改为Dim dr As Row = Tables("临时表").AddNew ,不知道哪里不对，程序还是报错

报两个错误：1、在某个线程上创建的控件不能成为在另一个线程上创建的控件的父级。

2、名为“临时表的 DataTable 已属于此 DataSet。

[此贴子已经被作者于2021/6/2 16:58:04编辑过]

主题：处理EXCEL