我用以下代码分别直接在命令窗口中通过更改路径的方法测试了模版与生成的报表,发现问题如下(附模版+报表):
模版的列数为20列(含两个标签列),打开模版时间为25秒;
生成的Excel报表列数为255列,打开报表时间为60~80秒;
Dim App As New MSExcel.Application
try
Dim Wb As MSExcel.WorkBook = app.WorkBooks.open("D:\reports\311-17(办单号)工艺书.xls")
Dim Ws = wb.WorkSheets(1)
Dim rg As MSExcel.Range = ws.UsedRange
Dim tempWs = wb.WorkSheets.Add
Rg.WrapText = True
Rg.EntireRow.AutoFit '自动调整行高
output.show(ws.usedrange.columns.count)
Dim tempCell As MSExcel.Range = tempWs.Cells(1, 1)
For Each rg In ws.UsedRange
output.show(Date.now)
If rg.MergeCells Then
Dim width As Double = 0
Dim tempCol
For Each tempcol In rg.MergeArea.Columns
width = width + tempcol.ColumnWidth
Next
tempWs.Columns(1).WrapText = True
tempWs.Columns(1).ColumnWidth = iif(width>10, width-10, width)
tempWs.Columns(1).Font.Size = rg.Font.Size
tempcell.Value = rg.Value
tempcell.RowHeight = 0
tempcell.EntireRow.Activate
tempcell.EntireRow.AutoFit
If (rg.RowHeight < tempcell.RowHeight) Then
Dim tempHeight As Double
Dim tempCount As Integer
tempHeight = tempcell.RowHeight
tempCount = rg.MergeArea.Rows.Count
For Each addHeightRow As object In rg.MergeArea.Rows
If (addHeightRow.RowHeight < tempHeight / tempCount) Then
addHeightRow.RowHeight = tempHeight / tempCount
End If
tempHeight = tempHeight - addHeightRow.RowHeight
tempCount = tempCount - 1
Next
rg.WrapText = True
End If
End If
Next
app.DisplayAlerts = False
tempWs.Delete
app.visible = True
catch ex As exception
msgbox(ex.message)
app.quit
End try