Excel No Responding 的解决方案(Slow Addin-减少访问次数)

  Excel Add-in的program运行时经常会遇到No responding。 原因可能是low-Addin 引起的。那么问题来了，如何让我们的程序跑的快点呢？

常见优化的三个思考方向：

1. 代码逻辑优化（一般大家的思考逻辑都是差不多的，可能只有大神才能有质的改变）

2. 改变访问Excel的方式（效果显著， OLEDB， EPPLUS， COM等等）

3. 减少访问次数

当项目处于后期时，1和2可能已经固定下来了。 下面讨论如何减少访问次数以及读写分离（类似于数据库的主从分离思想，- -！）

思路来源：点击打开链接

Public Class labReduceAccess    'Desc: Fetch info from ws1,ws2 and ws3. Put the processed data into ws3    'Author: ZhenYang Cheng    'Date: 01/23/2015    'Input:     'Output:    Shared Sub process()        Dim ws_Lab1 As New Microsoft.Office.Interop.Excel.Worksheet        Dim ws_Lab2 As New Microsoft.Office.Interop.Excel.Worksheet        Dim ws_Lab3 As New Microsoft.Office.Interop.Excel.Worksheet        Dim ws_number As Integer        If GlobalDataAndFunction.Instance().IsWorksheetExists(Singleton.Instance().Application, "ws_Lab1") Then            ws_number = GlobalDataAndFunction.Instance().GetWorksheetNo(Singleton.Instance().Application, "ws_Lab1")            ws_Lab1 = Singleton.Instance().Application.Worksheets(ws_number)        End If        If GlobalDataAndFunction.Instance().IsWorksheetExists(Singleton.Instance().Application, "ws_Lab2") Then            ws_number = GlobalDataAndFunction.Instance().GetWorksheetNo(Singleton.Instance().Application, "ws_Lab2")            ws_Lab2 = Singleton.Instance().Application.Worksheets(ws_number)        End If        If GlobalDataAndFunction.Instance().IsWorksheetExists(Singleton.Instance().Application, "ws_Lab3") Then            ws_number = GlobalDataAndFunction.Instance().GetWorksheetNo(Singleton.Instance().Application, "ws_Lab3")            ws_Lab3 = Singleton.Instance().Application.Worksheets(ws_number)        End If        Dim stepLen As Integer = ws_Lab3.UsedRange.Columns.Count + 1        Cursor.Current = Cursors.WaitCursor        GlobalDataAndFunction.Instance.MakeLog("-------------------------Generation Time--------------------------reset time(Unit: ms)")        Dim watch As New Stopwatch        watch.Start()        ''''''''''''''''''''''''''''''''''''''''''''Case 1''''''''''''''''''''''''''''''''''''''''''''''        'For i = 1 To ws_Lab3.UsedRange.Rows.Count        '    For j = 1 To stepLen - 1        '        Dim tmpCell1 As String = CType(ws_Lab1.Cells(i, j).value, String)        '        Dim tmpCell2 As String = CType(ws_Lab2.Cells(i, j).Value, String)        '        Dim tmpCell3 As String = CType(ws_Lab3.Cells(i, j).value, String)        '        Dim tmpResult As String = tmpCell1 + tmpCell2 + tmpCell3        '        For m = 0 To 3        '            GlobalDataAndFunction.Instance.setCellBackGround(ws_Lab3, i, j + m * stepLen, GlobalDataAndFunction.ColorOrange)        '            ws_Lab3.Cells(i, j + m * stepLen).value = tmpResult        '        Next        '    Next        'Next        'GlobalDataAndFunction.Instance.MakeLog("Case 1-----------" + watch.ElapsedMilliseconds.ToString)        ''''''''''''''''''''''''''''''''''''''''''''Case 2 Reduce Access times''''''''''''''''''''''''''''''''''''''''''''        Dim range_Lab1 As Object 'Type is object? Not Excel.range?        Dim range_Lab2 As Object        Dim range_Lab3 As Object        range_Lab1 = ws_Lab1.Range(ws_Lab1.Cells(1, 1), ws_Lab1.Cells(ws_Lab1.UsedRange.Rows.Count, ws_Lab1.UsedRange.Columns.Count)).Value2        range_Lab2 = ws_Lab2.Range(ws_Lab2.Cells(1, 1), ws_Lab2.Cells(ws_Lab2.UsedRange.Rows.Count, ws_Lab2.UsedRange.Columns.Count)).Value2        range_Lab3 = ws_Lab3.Range(ws_Lab3.Cells(1, 1), ws_Lab3.Cells(ws_Lab3.UsedRange.Rows.Count, 500)).Value2        'For i = 1 To 250        '    For j = 1 To stepLen - 1        '        Dim tmpCell1 As String = CType(range_Lab1(i, j), String)        '        Dim tmpCell2 As String = CType(range_Lab2(i, j), String)        '        Dim tmpCell3 As String = CType(range_Lab3(i, j), String)        '        Dim tmpResult As String = tmpCell1 + tmpCell2 + tmpCell3        '        For m = 0 To 3        '            GlobalDataAndFunction.Instance.setCellBackGround(ws_Lab3, i, j + m * stepLen, GlobalDataAndFunction.ColorOrange) 'simplest case        '            range_Lab3(i, j + m * stepLen) = tmpResult        '        Next        '    Next        'Next        'ws_Lab3.Range(ws_Lab3.Cells(1, 1), ws_Lab3.Cells(ws_Lab3.UsedRange.Rows.Count, ws_Lab3.UsedRange.Columns.Count)).Value2 = range_Lab3        'GlobalDataAndFunction.Instance.MakeLog("Case 2-----------" + watch.ElapsedMilliseconds.ToString)        '''''''''''''''''''''''''''''''''''''''''Case 3 Range Copy&Paste'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''        For i = 1 To 250            For j = 1 To stepLen - 1                Dim tmpCell1 As String = CType(range_Lab1(i, j), String)                Dim tmpCell2 As String = CType(range_Lab2(i, j), String)                Dim tmpCell3 As String = CType(range_Lab3(i, j), String)                Dim tmpResult As String = tmpCell1 + tmpCell2 + tmpCell3                range_Lab3(i, j) = tmpResult                GlobalDataAndFunction.Instance.setCellBackGround(ws_Lab3, i, j, GlobalDataAndFunction.ColorOrange) 'simplest case            Next        Next        ws_Lab3.Range(ws_Lab3.Cells(1, 1), ws_Lab3.Cells(ws_Lab3.UsedRange.Rows.Count, ws_Lab3.UsedRange.Columns.Count)).Value2 = range_Lab3        Dim copyRange As Microsoft.Office.Interop.Excel.Range        copyRange = ws_Lab3.Range(ws_Lab3.Cells(1, 1), ws_Lab3.Cells(ws_Lab3.UsedRange.Rows.Count, ws_Lab3.UsedRange.Columns.Count))        copyRange.Copy()        Dim pasteRange As Microsoft.Office.Interop.Excel.Range        For m = 1 To 3            pasteRange = ws_Lab3.Range(ws_Lab3.Cells(1, m * stepLen), ws_Lab3.Cells(1, m * stepLen))            pasteRange.PasteSpecial()        Next        GlobalDataAndFunction.Instance.MakeLog("Case 3-----------" + watch.ElapsedMilliseconds.ToString)        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''        Cursor.Current = Cursors.Default        watch.Stop()    End SubEnd Class

这里我做了一个lab， 有三张表ws_Lab1, ws_Lab2, ws_Lab3.  分别存放着 250行100列的信息。 现在的操作是将三张表相同location的cell value叠加，再将结果复制4遍（横向等距）到ws_Lab3里，  并将改变值的cell background设置成orange， 记录时间并打印log。这样做是模拟从多张sheet读写，改变sheet format（最简单format，实际情况可能复杂的多）的耗时操作。 

， 三个case

case 1:   按照基本逻辑编写代码, read: 250*100*3   write: 250*100*8(设置Format算一次)

case 2: 空间换时间  read: 3  write: 250*100*4 + 1

case 3: Excel 内部方法range.copy, range.paste   read:3 write: 250*100*1 + 1 + 3