Excel pending in memory only when formatting or alignment used

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
Hi,
I struggled with problem of pending excel in memory and I found solution. My code simply runs procedure at SQL Server and copy data into excel. Excel is then saved, closed and released from memory. After that I have added code for Cells alignemnt and Cells
formatting and problem raised again and excel is again pending in running processes. 4 rows of sheet alignemnt causes that, and when I comment them, then its ok again. Same when I used formatting by .NumberFormat. Could anyone help please ?

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small <font size="2" color="#0000ff" style="color:#0000ff; font-size:x-small
Public
</font>
<span style="font-size:x-small <span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Sub<span style="font-size:x-small Main()
<span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small xlApp
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small Excel.Application, xlWB
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small Excel.Workbook, xlSheet
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<font size="2 Excel.Worksheet</font><span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small row_nr
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Integer<font size="2 = 2</font><span style="font-size:x-small


<font size="2" style="font-size:x-small
xlApp =
</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<font size="2 Excel.Application</font><span style="font-size:x-small <font size="2" style="font-size:x-small
xlApp.Application.DisplayAlerts =
</font><font size="2" color="#0000ff <font size="2" color="#0000ff False</font></font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small
xlWB = xlApp.Workbooks.Open(
</font><span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "\czceslorfil1logistPersonalPolaSSIS_testBPG_DPP2.xlsx"<font size="2 )</font><span style="font-size:x-small
xlSheet = xlWB.Worksheets(1)
xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(30000, 100)).Clear()
<font size="2" style="font-size:x-small

</font><font size="2" color="#008000 <font size="2" color="#008000 Sheet(alignment) WHEN ALIGNEMNT commented, excel is closed in running processes.</font></font><span style="color:#008000; font-size:x-small <span style="color:#008000; font-size:x-small <span style="font-size:x-small
xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(30000, 100)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
xlSheet.Range(xlSheet.Cells(2, 2), xlSheet.Cells(30000, 2)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
xlSheet.Range(xlSheet.Cells(2, 11), xlSheet.Cells(30000, 11)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
xlSheet.Range(xlSheet.Cells(2, 11), xlSheet.Cells(30000, 11)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small consql
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<span style="font-size:x-small SqlConnection
<span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small comsql
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small New<font size="2 SqlCommand</font><span style="font-size:x-small <font size="2" style="font-size:x-small
consql.ConnectionString =
</font><font size="2" color="#a31515 <font size="2" color="#a31515 "Data Source=CZCESLORSQL2;Initial Catalog=DEOFILES;Integrated Security=SSPI;"</font></font><span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small <span style="font-size:x-small
consql.Open()
comsql.Connection = consql
<font size="2" style="font-size:x-small
comsql.CommandText =
</font><font size="2" color="#a31515 <font size="2" color="#a31515 "EXEC BPG_maindata_for_export"</font></font><span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small dr
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<font size="2 SqlDataReader = comsql.ExecuteReader()</font><span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Dim<span style="font-size:x-small i
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff Integer</font></font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small While<font size="2 dr.Read</font><span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small For<span style="font-size:x-small i = 0
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small To<font size="2 dr.FieldCount - 1</font><span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small If<span style="font-size:x-small i = 9
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Or<span style="font-size:x-small i = 26
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Or<span style="font-size:x-small i = 28
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Or<span style="font-size:x-small i = 37
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Then<span style="font-size:x-small

<span style="font-size:x-small xlSheet.Cells(row_nr, i + 1) = Mid(dr.Item(dr.GetName(i)), 7, 2) &
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "."<span style="font-size:x-small & Mid(dr.Item(dr.GetName(i)), 5, 2) &
<span style="color:#a31515; font-size:x-small <span style="color:#a31515; font-size:x-small "."<span style="font-size:x-small & Mid(dr.Item(dr.GetName(i)), 1, 4)<font size="2" style="font-size:x-small


</font><font size="2" color="#0000ff <font size="2" color="#0000ff Else</font></font>

<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small
xlSheet.Cells(row_nr, i + 1) = dr.Item(dr.GetName(i))
</font><font size="2" color="#008000 <font size="2" color="#008000 ostatni textove udaje</font></font><span style="color:#008000; font-size:x-small <span style="color:#008000; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff If</font></font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Next<font size="2 i</font><span style="font-size:x-small
row_nr = row_nr + 1
<font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff While</font></font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small



<span style="font-size:x-small
xlWB.Save()
xlWB.Close()
xlApp.Quit()

ReleaseObject(xlApp)
ReleaseObject(xlWB)
ReleaseObject(xlSheet)
Dts.TaskResult = ScriptResults.Success

<font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff Sub</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Private<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Sub<span style="font-size:x-small ReleaseObject(<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small ByVal<span style="font-size:x-small
obj <span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<span style="font-size:x-small
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Object<font size="2 )</font><span style="font-size:x-small <font size="2" style="font-size:x-small

</font><font size="2" color="#0000ff <font size="2" color="#0000ff Try</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
<font size="2" style="font-size:x-small
obj =
</font><font size="2" color="#0000ff <font size="2" color="#0000ff Nothing</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small Catch<span style="font-size:x-small ex
<span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small As<font size="2 Exception</font><span style="font-size:x-small
MessageBox.Show(ex.ToString())
<font size="2" style="font-size:x-small

</font><font size="2" color="#0000ff <font size="2" color="#0000ff Finally</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small
obj =
</font><font size="2" color="#0000ff <font size="2" color="#0000ff Nothing</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small
GC.Collect()
<font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff Try</font></font><span style="color:#0000ff; font-size:x-small <span style="font-size:x-small <font size="2" style="font-size:x-small

</font><span style="color:#0000ff; font-size:x-small <span style="color:#0000ff; font-size:x-small End<span style="font-size:x-small
<font size="2" color="#0000ff <font size="2" color="#0000ff Sub</font></font><span style="color:#0000ff; font-size:x-small

View the full article
 
Back
Top