Re: Controlling Threads

Tech-Archive recommends: Fix windows errors by optimizing your registry

A word of advice; Do NOT mix Excel Automation with .NET Framework 1.1
runtime. Especially for what you are trying to do. Once an instances of excel
is created, it becomes very 'difficult' to release it effectively. Anything
you 'touch' or instantiate under that instance; Workbook, Work***, cell,
etc. will hold a reference to COM. and you have to kill every single
Workbook, work***, cell to get rid of Excel process. You will find lots of
threads in this and other newsgroups regarding this issue. Also doing COM
Interop, and that's what you're doing with Excel, will not yeild the best
performance results, it seems you really care about that. No number of
threads will help you with that, if anything they will make it worse by
deadlocking.
I had to develop a very similar application, I ran into so many problems
with Excel and .NET, that I finally gave up on it, and developed a solution
in good old Vb6 and a few VBA Excel Macros. You could probably do the same
thing.


"Richard Welch" wrote:

> Willy,
> I agree, it seems definately wrong to have to do multiple instances of
> Excel to accomplish what I need to do. If there is a way to create between 3
> and 4 thousand different workbooks quicker, I would definately like to know.
> I am trying to multithread to take advantage of the hyperthreading availalbe.
> The process I am trying to write takes over 30 hours to accomplish in a
> single Excel instance. I would like to be able to hold it down to a smaller
> number of threads, but am unsure how to accomplish this.
> --
> Richard A. Welch
> IT Manager
> House of Raeford Farms, Inc. - Raeford Division
>
>
>
> "Willy Denoyette [MVP]" wrote:
>
> > Why do you have 125 Excel processes running in the first place? Do you mean
> > you create an instance of Excel in each worker thread you start? Well this
> > is wrong, you should only create a single Excel instance in your main thread
> > and you should not use any other thread at all.
> >
> > Willy.
> >
> >
> > "Richard Welch" <RichardWelch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:DA8BAC21-6B2A-4858-92EB-51AC07D63573@xxxxxxxxxxxxxxxx
> > >I am having a problem with QueueUserWorkItem. I thought there was a maximum
> > > number of worker threads a threadpool had available, 25 per processor, or
> > > something like that. However, when executing the following code, after
> > > about
> > > 125 or so Excel processes are running I get:
> > >
> > > "An unhandled exception of type
> > > 'System.Runtime.InteropServices.COMException' occurred in Fiscal Year
> > > End.exe"
> > >
> > > Additional information: Server execution failed
> > >
> > > When another thread starts to execute. I would expect at most 50
> > > concurrent
> > > Excel threads on a P4 hyperthreading system. Is there something wrong in
> > > my
> > > code that keeps the system from recognizing the maximum thread count?
> > >
> > > Private ExcelPool As System.Threading.ThreadPool
> > >
> > > <Some Code>
> > >
> > > For Each AccountRow In ReportingDS.Tables("Ledger Accounts").Rows
> > > SummaryRows = AccountRow.GetChildRows("Account Summary")
> > > For Each SummaryRow In SummaryRows
> > > TargetRange = WS.Cells(CurrentRow, 1)
> > > TargetRange.Resize(1, _
> > > SummaryRow.ItemArray.GetLength(0)).Value =
> > > SummaryRow.ItemArray
> > > TargetRange = WS.Cells(CurrentRow, 1)
> > > WS.Hyperlinks.Add(TargetRange, SaveSubDirectoryString & _
> > > WS.Cells(CurrentRow, 1).Value & ".xls", , _
> > > "Click on account number to open account activity.", _
> > > WS.Cells(CurrentRow, 1).Value)
> > > CurrentRow += 1
> > > Next
> > > Dim StObj As New StateObj
> > > StObj.DataRowArg = AccountRow.GetChildRows("Account Detail")
> > > StObj.DataTableArg = ReportingDS.Tables("Ledger Detail").Clone
> > > StObj.SavePath = SaveSubDirectoryString & AccountRow(0) + ".xls"
> > > StObj.DSName = AccountRow(0)
> > > ExcelPool.QueueUserWorkItem(New System.Threading.WaitCallback _
> > > (AddressOf SaveExcelData), StObj)
> > > Next
> > >
> > > <More Code>
> > >
> > > Private Sub SaveExcelData(ByVal StateObj As Object)
> > > Dim StObj As StateObj
> > > StObj = CType(StateObj, StateObj)
> > > Dim ThreadExcel As New Excel.Application
> > > Dim ThreadWorkbook As Excel.Workbook = ThreadExcel.Workbooks.Add
> > > While ThreadWorkbook.Worksheets.Count > 1
> > > ThreadWorkbook.Worksheets(ThreadWorkbook.Worksheets.Count).Delete()
> > > End While
> > > Dim ThreadWorksheet As Excel.Worksheet = ThreadWorkbook.Worksheets(1)
> > > Dim DR As DataRow
> > > Dim DC As DataColumn
> > > For Each DC In StObj.DataTableArg.Columns
> > > ThreadWork***.Cells(1, DC.Ordinal + 1) = DC.ColumnName
> > > Next
> > > ThreadWork***.Rows(1).Font.Bold = True
> > > ThreadWork***.Rows(1).Font.Underline =
> > > Excel.XlUnderlineStyle.xlUnderlineStyleSingle
> > > Dim CurrentRow As Integer = 2
> > > Try
> > > For Each DR In StObj.DataRowArg
> > > For Each DC In StObj.DataTableArg.Columns
> > > ThreadWork***.Cells(CurrentRow, DC.Ordinal + 1).Value =
> > > DR(DC.Ordinal)
> > > Next
> > > CurrentRow += 1
> > > Next
> > > Catch ex As Exception
> > > MsgBox(ex.Message, MsgBoxStyle.Critical, "Thread Excel")
> > > ThreadWork*** = Nothing
> > > ThreadWorkbook = Nothing
> > > ThreadExcel.Quit()
> > > ThreadExcel = Nothing
> > > End Try
> > > ThreadWorkbook.SaveAs(StObj.SavePath)
> > > ThreadWorkbook.Close()
> > > ThreadExcel.Quit()
> > > ThreadExcel = Nothing
> > > StObj = Nothing
> > > End Sub
> > >
> > >
> > > --
> > > Richard A. Welch
> > > IT Manager
> > > House of Raeford Farms, Inc. - Raeford Division
> > >
> >
> >
> >
.

Quantcast