0

This syntax works - but it takes roughly 3 minutes to complete, which is WAY to high. Is there a way to re-write this syntax for optimization? I know that the first problem with the syntax with this syntax is using Microsoft Excel Interop however I could not write syntax for EPPlus to achieve such.

Can this be re-written to optimize the code and speed up the process?

public static Excel.Application xlApp;
public static Excel.Workbook xlWorkBook;
public static Excel.Worksheet xlWorkSheet;
public static string sheetprefix;

private void btnOneClickClickClick_Click(object sender, EventArgs e)
{
    SecondSetOfHyperLinks();
    FirstSetOfHyperLinks();     
}
public static void SecondSetOfHyperLinks()
{
    sheetprefix = "FS";
    long lr, i;
    string cellVal;
    WS = xlApp.ActiveWorkbook.ActiveSheet;
    lr = WS.Cells[WS.Rows.Count, 2].End(Excel.XlDirection.xlUp).Row;
    for (i = 2; i <= lr; i++)
    {
        Object Anchor = WS.Cells[i, 7];
        Object TextToDisplay = Convert.ToString(WS.Cells[i, 9]);
        cellVal = WS.Cells[i, 1].Value;
        cellVal = cellVal.Substring(0, Math.Min(28, cellVal.Length));
        rangeToHoldHyperlink = WS.Range["G" + i];
        xlApp.ActiveSheet.Hyperlinks.Add(Anchor, "", "'" + "FS " + cellVal + "'!A1", "", TextToDisplay);
    }
}
public static void FirstSetOfHyperLinks()
{
    sheetprefix = "SE";
    long lr, i;
    string cellVal;
    WS = xlApp.ActiveWorkbook.ActiveSheet;
    lr = WS.Cells[WS.Rows.Count, 2].End(Excel.XlDirection.xlUp).Row;
    for (i = 2; i <= lr; i++)
    {
        Object Anchor = WS.Cells[i, 7];
        Object TextToDisplay = Convert.ToString(WS.Cells[i, 9]);
        cellVal = WS.Cells[i, 1].Value;
        cellVal = cellVal.Substring(0, Math.Min(28, cellVal.Length));
        rangeToHoldHyperlink = WS.Range["G" + i];
        xlApp.ActiveSheet.Hyperlinks.Add(Anchor, "", "'" + "SE " + cellVal + "'!A1", "", TextToDisplay);
    }

Edit

Unless I am missing the obvious, it does not look like I have the profiler. DEBUG MENU

KeepCalmAndCarryOn
  • 8,817
  • 2
  • 32
  • 47
Michael Mormon
  • 539
  • 2
  • 8
  • 19
  • I can't see anything that is taking an excessive amount of time other than the `for` loop, which you can't really avoid. Unfortunately, I don't think anything can be done - you're using excel Interop and if there's a lot of rows there's not much you can do... – coolboyjules Feb 13 '17 at 19:48
  • @coolboyjules - SecondSetOfHyperlinks is processing roughly 200 rows, and FirstSetOfHyperlinks is processing roughly 100 rows. Is that about an average time? – Michael Mormon Feb 13 '17 at 19:49
  • Have you tried to use the performance profiler in VS to see which line(s) are taking an excessive amount of time? And to be honest, that seems pretty low... it shouldn't be taking 3 minutes... – coolboyjules Feb 13 '17 at 19:53
  • I have never used that feature. How would I do that? – Michael Mormon Feb 13 '17 at 19:53
  • 1
    Check this out: https://msdn.microsoft.com/en-CA/library/ms182372.aspx – coolboyjules Feb 13 '17 at 19:55
  • It looks like I do not have the profiler option? – Michael Mormon Feb 13 '17 at 20:13
  • @coolboyjules - If I altered from a `for` loop to `LINQ` list - would I see improvement or is that no guarantee? – Michael Mormon Feb 13 '17 at 21:03

0 Answers0