I have a client application (excel addIn) that retrievs data from WCF server.
These are the classes :
public class myWorksheet
{
public int Id { get; set; }
public string WorksheetName { get; set; }
public List<myData> MyDataChildren { get; set; }
public List<string> Dependencies { get; set; }
}
public class myData
{
public int Id { get; set; }
public int WorksheetId { get; set; }
public int RowId { get; set; }
public string ColumnId { get; set; }
public string Value { get; set; }
public string Formula { get; set; }
public string Comment { get; set; }
}
Dependencies - list of worksheet names that the current worksheet has a reference to (in formulas)
I retrieve the List from WCF (actually IEnumerable) and have that data on the client. The problem is that when I'm writing the data to excel it takes way to long.
What I'm doing is :
On "Application_SheetActivate" event
Like this :
public List<myWorksheet> WorksheetList { get; set; }
private void Rebind(myWorksheet currWorksheet, out Excel.Workbook workbook)
{
foreach (string connectedWorksheet in currWorksheet.Dependencies)
{
myWorksheet ws = DataHelper.FindWorksheetFromList(connectedWorksheet, this.WorksheetList);
Excel.Worksheet sheet = DataHelper.FindWorksheetFromWorkbook(connectedWorksheet,workbook);
this.LoadData(sheet,ws);
}
Excel.Worksheet sheetMain = DataHelper.FindWorksheetFromWorkbook(currWorksheet.WorksheetName,workbook);
this.LoadData(sheetMain,currWorksheet);
}
private void LoadData(Excel.Worksheet worksheet, myWorksheet worksheetEnt)
{
foreach (myData cell in worksheetEnt.MyDataChildren)
{
Excel.Range excelCell = worksheet.Range(cell.ColumnId.ToString() + cell.RowId.ToString());
excelCell.Value2 = cell.Value;
excelCell.Formula = cell.Formula;
excelCell.AddComment(cell.Comment);
}
}
The problem is :
Some worksheets have about 200 dependencies. (and loading of that dependencies takes 15 minutes).
Is this iteration what I'm doing wrong? Is there a better/ faster way that I'm not aware of?
Managed to solve the issue.
Tried
And that helped a little. But what really did the trick is
so my code now looks like this:
private void Rebind(myWorksheet currWorksheet, out Excel.Workbook workbook)
{
currWorksheet.Application.Calculation = Excel.XlCalculation.xlCalculationManual
currWorksheet.Application.ScreenUpdating=False
foreach (string connectedWorksheet in currWorksheet.Dependencies)
{
myWorksheet ws = DataHelper.FindWorksheetFromList(connectedWorksheet, this.WorksheetList);
Excel.Worksheet sheet = DataHelper.FindWorksheetFromWorkbook(connectedWorksheet,workbook);
this.LoadData(sheet,ws);
}
Excel.Worksheet sheetMain = DataHelper.FindWorksheetFromWorkbook(currWorksheet.WorksheetName,workbook);
this.LoadData(sheetMain,currWorksheet);
currWorksheet.Application.Calculation = Excel.XlCalculation.xlCalculationAutomatic
currWorksheet.Application.ScreenUpdating=True
}