Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program execution slows down as iterations increase
Hi -
I posted this question yesterday without code because it is written in C# for a 2007 Excel applicaition and therefore thought it may not get many looks. But what the heck we'll try it again with code. So the program flow is populate the worksheet with numbers and text (these come from a cube and SQL data tables in a SQL database), copy paste it to a second worksheet in another workbook, go back to the original worksheet and populate it with new numbers from the next contract, copy paste it to the second worksheet but below the prior copy/paste section ... this is repeated many times to as many as 1000 times or more. The problem I'm having is that as the iteration increases into the 300 or 400's the program slows down to a virtual crawl, sometimes just stopping. As a shot in the dark it seems like I'm using some resource without releasing it which causes memory drain. It works fine through 50 or 100 iterations but as I said it begins to slow after that. In fact, I'm playing with a workaround which does 50 or so copy/pastes at a time, then bulk copy the 50 onto a third worksheet. This seems to work okay ... so that I avoid the "crawl", but it would be great to understand why the slow down in the first place. private void btPrintReport_Click(object sender, EventArgs e) { Object oMissing = System.Reflection.Missing.Value; //Setup Workbook that holds "printed" pages Excel.Workbook printWB = Globals.ThisWorkbook.Application.Workbooks.Add(oMi ssing); Excel.Sheets sheets = printWB.Worksheets; // Rename current sheet to Dummy Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; reportSheet.Name = "Report"; // Delete other sheets from workbook foreach (Excel.Worksheet nameWS in sheets) if (nameWS.Name != "Report") nameWS.Delete(); //Set worksheet and page counters int p = 0; int n = 0; int rowCnt = 70; int scale = 51; //Get first contract Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveFirst(); n++; do { //Print Contract to Worksheet p++; PrintContractToWorksheet2(printWB, p, rowCnt); //Print Ceded and Net contracts if (Globals.Sheet2.commonAccountNamedRange.Value2.ToS tring() == "Y") { rbACNCeded.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); rbACNNet.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); //To reset the ACN check box to Assumed ... needed when the reset checkbox is set to unchecked. rbACNAssumed.Checked = true; } //Move to next contract and increment Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveNext(); n++; } while (n <= Globals.Sheet2.tblUserInterface_MasterBindingSourc e.Count); // Remove the workbooks connection that got copied over with the worksheet.paste command above //printWB.Connections[1].Delete(); // Move the cursor back to cell a1 Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a1", oMissing); printRange.Select(); // Remove the range names from the workbook // Started at the last name and worked down to the first name .... if working up the names collection, we wouldn't have an item i to delete after half of the items are deleted. for (int i = printWB.Names.Count; i = 1; i--) if (printWB.Names.Item(i, oMissing, oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i, oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area ranges and a couple of other (Hidden) cube related ranges that caused a crash when they were deleted printWB.Names.Item(i, oMissing, oMissing).Delete(); //Save the print worksheet printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing, oMissing); printWB.Close(oMissing, oMissing, oMissing); } private void PrintContractToWorksheet2(Microsoft.Office.Interop .Excel.Workbook printWB, int p, int rowCnt) { Object oMissing = System.Reflection.Missing.Value; //Method is used to refresh the data linked to the data cube. // In theory the CalculateUntilAsyncQueriesDone should work alone but does not and is prone to hang // Showing the form "frmWorkingForm" with the sleep thread method afferted the hanging. // I stumpled onto this workaround by wanting to have the computer "do something else" until the Aysnc was done; but I really don't know why it works. Globals.ThisWorkbook.Application.CalculateUntilAsy ncQueriesDone(); frmWorkingForm workingForm = new frmWorkingForm(); workingForm.Show(); System.Threading.Thread.Sleep(0); workingForm.Close(); //Copy the results from the template Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing); // Set a reference to the sheet named "Report" Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; //Activate the print worksheet and select the cell that we are pasting the results to ((Excel._Worksheet)reportSheet).Activate(); Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p - 1) * rowCnt + 1), oMissing); printRange.Select(); //For the first iteration we have to paste the column widths ... after that we have to put in a page break if (p == 1) printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteColumnWidths, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Paste results then paste values the results // The first paste results is used to get the text box to copy .... and I'm not aware if any other way to bring them over; it also brings number formats. reportSheet.Paste(oMissing, oMissing); printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Add Solid black line and PageBreak printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt)); printRange.Select(); printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium; printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt + 1)); printRange.Select(); reportSheet.HPageBreaks.Add(printRange); } |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program execution slows down as iterations increase
I assume you have turned off ScreenUpdating, Calculation and Events before
doing this operation. You are probably either having trouble with Excel's string table or the database connection or the C# interop layer: I have not played enough with C# code to tell. If I were doing this using VBA I would make 1 connection, get a recordset for 1 contract, use copyfromrecordset to copy the recordset directly to the correct place in the second worksheet (eliminate the first worksheet and copypaste etc), then get the next recordset without dropping the connection. That's assuming you can't get multiple contracts at a time into the recordset, which would be much better. I also just spotted some PageBreak code (and formatting): avoid doing this until AFTER you have put ALL the data onto the worksheet. If the above approach is still slow and you cannot get multiple contracts per recordset then I would build a large array from the individual contract recordsets and dump it to the worksheet in 1 write operation. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mark" wrote in message ... Hi - I posted this question yesterday without code because it is written in C# for a 2007 Excel applicaition and therefore thought it may not get many looks. But what the heck we'll try it again with code. So the program flow is populate the worksheet with numbers and text (these come from a cube and SQL data tables in a SQL database), copy paste it to a second worksheet in another workbook, go back to the original worksheet and populate it with new numbers from the next contract, copy paste it to the second worksheet but below the prior copy/paste section ... this is repeated many times to as many as 1000 times or more. The problem I'm having is that as the iteration increases into the 300 or 400's the program slows down to a virtual crawl, sometimes just stopping. As a shot in the dark it seems like I'm using some resource without releasing it which causes memory drain. It works fine through 50 or 100 iterations but as I said it begins to slow after that. In fact, I'm playing with a workaround which does 50 or so copy/pastes at a time, then bulk copy the 50 onto a third worksheet. This seems to work okay ... so that I avoid the "crawl", but it would be great to understand why the slow down in the first place. private void btPrintReport_Click(object sender, EventArgs e) { Object oMissing = System.Reflection.Missing.Value; //Setup Workbook that holds "printed" pages Excel.Workbook printWB = Globals.ThisWorkbook.Application.Workbooks.Add(oMi ssing); Excel.Sheets sheets = printWB.Worksheets; // Rename current sheet to Dummy Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; reportSheet.Name = "Report"; // Delete other sheets from workbook foreach (Excel.Worksheet nameWS in sheets) if (nameWS.Name != "Report") nameWS.Delete(); //Set worksheet and page counters int p = 0; int n = 0; int rowCnt = 70; int scale = 51; //Get first contract Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveFirst(); n++; do { //Print Contract to Worksheet p++; PrintContractToWorksheet2(printWB, p, rowCnt); //Print Ceded and Net contracts if (Globals.Sheet2.commonAccountNamedRange.Value2.ToS tring() == "Y") { rbACNCeded.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); rbACNNet.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); //To reset the ACN check box to Assumed ... needed when the reset checkbox is set to unchecked. rbACNAssumed.Checked = true; } //Move to next contract and increment Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveNext(); n++; } while (n <= Globals.Sheet2.tblUserInterface_MasterBindingSourc e.Count); // Remove the workbooks connection that got copied over with the worksheet.paste command above //printWB.Connections[1].Delete(); // Move the cursor back to cell a1 Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a1", oMissing); printRange.Select(); // Remove the range names from the workbook // Started at the last name and worked down to the first name ... if working up the names collection, we wouldn't have an item i to delete after half of the items are deleted. for (int i = printWB.Names.Count; i = 1; i--) if (printWB.Names.Item(i, oMissing, oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i, oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area ranges and a couple of other (Hidden) cube related ranges that caused a crash when they were deleted printWB.Names.Item(i, oMissing, oMissing).Delete(); //Save the print worksheet printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing, oMissing); printWB.Close(oMissing, oMissing, oMissing); } private void PrintContractToWorksheet2(Microsoft.Office.Interop .Excel.Workbook printWB, int p, int rowCnt) { Object oMissing = System.Reflection.Missing.Value; //Method is used to refresh the data linked to the data cube. // In theory the CalculateUntilAsyncQueriesDone should work alone but does not and is prone to hang // Showing the form "frmWorkingForm" with the sleep thread method afferted the hanging. // I stumpled onto this workaround by wanting to have the computer "do something else" until the Aysnc was done; but I really don't know why it works. Globals.ThisWorkbook.Application.CalculateUntilAsy ncQueriesDone(); frmWorkingForm workingForm = new frmWorkingForm(); workingForm.Show(); System.Threading.Thread.Sleep(0); workingForm.Close(); //Copy the results from the template Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing); // Set a reference to the sheet named "Report" Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; //Activate the print worksheet and select the cell that we are pasting the results to ((Excel._Worksheet)reportSheet).Activate(); Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p - 1) * rowCnt + 1), oMissing); printRange.Select(); //For the first iteration we have to paste the column widths ... after that we have to put in a page break if (p == 1) printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteColumnWidths, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Paste results then paste values the results // The first paste results is used to get the text box to copy ... and I'm not aware if any other way to bring them over; it also brings number formats. reportSheet.Paste(oMissing, oMissing); printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Add Solid black line and PageBreak printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt)); printRange.Select(); printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium; printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt + 1)); printRange.Select(); reportSheet.HPageBreaks.Add(printRange); } |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program execution slows down as iterations increase
Charles -
Thanks for your comments. I simplefied and removed some code ... and its working. [It sometimes takes me a while to come back to that basic premise of KIS ... Keep it Simple] I'm not sure of the offending code, and don't have the energy to test it right now. I followed you're advice on the page breaks and formatting ... thanks for these and the other tips. Mark "Charles Williams" wrote: I assume you have turned off ScreenUpdating, Calculation and Events before doing this operation. You are probably either having trouble with Excel's string table or the database connection or the C# interop layer: I have not played enough with C# code to tell. If I were doing this using VBA I would make 1 connection, get a recordset for 1 contract, use copyfromrecordset to copy the recordset directly to the correct place in the second worksheet (eliminate the first worksheet and copypaste etc), then get the next recordset without dropping the connection. That's assuming you can't get multiple contracts at a time into the recordset, which would be much better. I also just spotted some PageBreak code (and formatting): avoid doing this until AFTER you have put ALL the data onto the worksheet. If the above approach is still slow and you cannot get multiple contracts per recordset then I would build a large array from the individual contract recordsets and dump it to the worksheet in 1 write operation. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mark" wrote in message ... Hi - I posted this question yesterday without code because it is written in C# for a 2007 Excel applicaition and therefore thought it may not get many looks. But what the heck we'll try it again with code. So the program flow is populate the worksheet with numbers and text (these come from a cube and SQL data tables in a SQL database), copy paste it to a second worksheet in another workbook, go back to the original worksheet and populate it with new numbers from the next contract, copy paste it to the second worksheet but below the prior copy/paste section ... this is repeated many times to as many as 1000 times or more. The problem I'm having is that as the iteration increases into the 300 or 400's the program slows down to a virtual crawl, sometimes just stopping. As a shot in the dark it seems like I'm using some resource without releasing it which causes memory drain. It works fine through 50 or 100 iterations but as I said it begins to slow after that. In fact, I'm playing with a workaround which does 50 or so copy/pastes at a time, then bulk copy the 50 onto a third worksheet. This seems to work okay ... so that I avoid the "crawl", but it would be great to understand why the slow down in the first place. private void btPrintReport_Click(object sender, EventArgs e) { Object oMissing = System.Reflection.Missing.Value; //Setup Workbook that holds "printed" pages Excel.Workbook printWB = Globals.ThisWorkbook.Application.Workbooks.Add(oMi ssing); Excel.Sheets sheets = printWB.Worksheets; // Rename current sheet to Dummy Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; reportSheet.Name = "Report"; // Delete other sheets from workbook foreach (Excel.Worksheet nameWS in sheets) if (nameWS.Name != "Report") nameWS.Delete(); //Set worksheet and page counters int p = 0; int n = 0; int rowCnt = 70; int scale = 51; //Get first contract Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveFirst(); n++; do { //Print Contract to Worksheet p++; PrintContractToWorksheet2(printWB, p, rowCnt); //Print Ceded and Net contracts if (Globals.Sheet2.commonAccountNamedRange.Value2.ToS tring() == "Y") { rbACNCeded.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); rbACNNet.Checked = true; p++; PrintContractToWorksheet2(printWB, p, rowCnt); //To reset the ACN check box to Assumed ... needed when the reset checkbox is set to unchecked. rbACNAssumed.Checked = true; } //Move to next contract and increment Globals.Sheet2.tblUserInterface_MasterBindingSourc e.MoveNext(); n++; } while (n <= Globals.Sheet2.tblUserInterface_MasterBindingSourc e.Count); // Remove the workbooks connection that got copied over with the worksheet.paste command above //printWB.Connections[1].Delete(); // Move the cursor back to cell a1 Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a1", oMissing); printRange.Select(); // Remove the range names from the workbook // Started at the last name and worked down to the first name ... if working up the names collection, we wouldn't have an item i to delete after half of the items are deleted. for (int i = printWB.Names.Count; i = 1; i--) if (printWB.Names.Item(i, oMissing, oMissing).Name.Contains("Print_Area") == false && printWB.Names.Item(i, oMissing, oMissing).Name.Contains("_") == false) //Keeps the print area ranges and a couple of other (Hidden) cube related ranges that caused a crash when they were deleted printWB.Names.Item(i, oMissing, oMissing).Delete(); //Save the print worksheet printWB.SaveAs(xlsPath, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlNoChange, oMissing, oMissing, oMissing, oMissing, oMissing); printWB.Close(oMissing, oMissing, oMissing); } private void PrintContractToWorksheet2(Microsoft.Office.Interop .Excel.Workbook printWB, int p, int rowCnt) { Object oMissing = System.Reflection.Missing.Value; //Method is used to refresh the data linked to the data cube. // In theory the CalculateUntilAsyncQueriesDone should work alone but does not and is prone to hang // Showing the form "frmWorkingForm" with the sleep thread method afferted the hanging. // I stumpled onto this workaround by wanting to have the computer "do something else" until the Aysnc was done; but I really don't know why it works. Globals.ThisWorkbook.Application.CalculateUntilAsy ncQueriesDone(); frmWorkingForm workingForm = new frmWorkingForm(); workingForm.Show(); System.Threading.Thread.Sleep(0); workingForm.Close(); //Copy the results from the template Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing); // Set a reference to the sheet named "Report" Excel.Worksheet reportSheet = (Excel.Worksheet)printWB.ActiveSheet; //Activate the print worksheet and select the cell that we are pasting the results to ((Excel._Worksheet)reportSheet).Activate(); Excel.Range printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p - 1) * rowCnt + 1), oMissing); printRange.Select(); //For the first iteration we have to paste the column widths ... after that we have to put in a page break if (p == 1) printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteColumnWidths, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Paste results then paste values the results // The first paste results is used to get the text box to copy ... and I'm not aware if any other way to bring them over; it also brings number formats. reportSheet.Paste(oMissing, oMissing); printRange.PasteSpecial(Microsoft.Office.Interop.E xcel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOpera tion.xlPasteSpecialOperationNone, oMissing, oMissing); //Add Solid black line and PageBreak printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt), "w" + Convert.ToString((p) * rowCnt)); printRange.Select(); printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous; printRange.Borders.get_Item(Microsoft.Office.Inter op.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium; printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt + 1), "ag" + Convert.ToString((p) * rowCnt + 1)); printRange.Select(); reportSheet.HPageBreaks.Add(printRange); } |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Program execution slows down in as iterations increase | Excel Programming | |||
Execution ends without error message in middle of program | Excel Programming | |||
Using Code Editor Slows execution speed | Excel Programming | |||
Program execution prob | Excel Programming | |||
PrintPreview slows Code Execution | Excel Programming |