1、下面是异步函数代码,调用是:Functions.AsyncExecute("异步函数",file,dic)程序执行偶尔会报65楼的错误
2、代码最后一行显示处理文件所用时间时他是实时显示的,我想要处理完所有文件后显示,如何修改?
Dim st As Date = Date.Now
Dim app As new MSExcel.Application
Dim Numcb1 As WinForm.NumericComboBox = Forms("窗口1").Controls("NumComBox1")
Dim Numcb2 As WinForm.NumericComboBox = Forms("窗口1").Controls("NumComBox2")
Dim lbl As WinForm.Label = Forms("窗口1").Controls("Label6")
Dim file = args(0)
Dim dic = args(1)
Dim excelcol As Integer
Dim excelrows As Integer
Dim RowsMax As Integer
Dim ColsMax 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
'output.show(file & excelcol & "," & excelrows)
Dim wb=app.WorkBooks.open(file)
For k As Integer=1 To wb.worksheets.Count
'If Wb.WorkSheets(k).Visible <> 0 Then '如果sheet表为不隐藏时处理(0隐藏,-1为不隐藏
Dim Ws As MSExcel.WorkSheet=Wb.WorkSheets(k)
If Numcb1.Text = Nothing And Numcb2.Text = Nothing Then
RowsMax = 0
ColsMax = ws.UsedRange.columns.count
'获取最大行
For i As Integer = 1 To ColsMax
Dim r = ws.cells(excelrows,i).End(MsExcel.XlDirection.xlUp).Row
If r > RowsMax Then
RowsMax = r
End If
Next
'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
Next
'Output.Show("ColsMax =" & ColsMax )
Else
RowsMax = Numcb1.Value
ColsMax = Numcb2.Value
End If
Dim rg As MSExcel.Range = Ws.Range(Ws.Cells(1,1), Ws.Cells(RowsMax,ColsMax))
'If rg.Count = 1 And rg(1).Value = "" Then 'If rg.Count = 1 And rg(1).Value Is Nothing Then
'下面代码解决doubleTO string转换报错问题.2021-05-07修改
If rg.Count = 1 And rg(1).Value Is Nothing Then
Continue For
End If
Dim ary = rg.value
For i As Integer=1 To RowsMax
'If rg.Rows(i).height <> 0 Then
For j As Integer = 1 To ColsMax
'If rg.Columns(j).width <> 0 Then
If ary(i, j) <> Nothing AndAlso Typeof ary(i,j) Is String AndAlso dic.ContainsKey(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), "")) Then
rg(i, j).Value= dic(ary(i,j).ToLower.Replace(" ","").Replace(chr(10), "").Replace(chr(13), ""))
End If
'End If
Next
'End If
Next
Functions.BeginSyncExecute("def","[文件名:" & file & "]" & "," & "[Sheet名称:" & ws.name & "]")
'End If '不处理隐藏sheet
Next
wb.Save
app.quit
End If
FileCount=FileCount+1
Functions.BeginSyncExecute("同步函数","已处理" & FileCount & "个文件","耗时: " & (Date.Now - st).TotalSeconds & "秒")