naspinski - exceldevelopment :: programming :: designhttp://naspinski.net/
http://www.rssboard.org/rss-specificationBlogEngine.Net Syndication Generator 1.0.0.0 (http://dotnetblogengine.net/)en-GBhttp://naspinski.net/opml.axdhttp://www.naspinski.net/syndication.axdnaspinskinaspinskiGetting the Sheet Name(s) from an Excel Document with OleDb<h2>users name their sheets all sorts of crazy things, but sometimes, I want to be able to get the sheet names regardless of what they are named</h2>
Considering that I already have my ConnectionString <b>strC</b> set up to access my Excel file, I can just use the <b>GetOleDbSchemaTable</b> method like this:
<pre class="prettyprint">DataTable dtS;
using (OleDbConnection c = new OleDbConnection(strC))
{
c.Open();
dtS = c.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
c.Close();
}</pre><br />
Now <b>dtSchema</b> holds the sheet names (in alphabetical order).
To get them out:
<pre class="prettyprint">foreach(DataRow row in dtS.Rows)
Console.WriteLine(row.Field&lt;string&gt;("TABLE_NAME"));</pre
http://naspinski.net/post/Getting-the-Sheet-Name(s)-from-an-Excel-Document-with-OleDb.aspx
naspinskihttp://naspinski.net/post/Getting-the-Sheet-Name(s)-from-an-Excel-Document-with-OleDb.aspx#commenthttp://naspinski.net/post.aspx?id=9018a855-b8c5-48f6-96f0-c47bbd307a2bTue, 22 Sep 2009 10:21:00 -0500c#excelnaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=9018a855-b8c5-48f6-96f0-c47bbd307a2b0http://naspinski.net/trackback.axd?id=9018a855-b8c5-48f6-96f0-c47bbd307a2bhttp://naspinski.net/post/Getting-the-Sheet-Name(s)-from-an-Excel-Document-with-OleDb.aspx#commenthttp://naspinski.net/syndication.axd?post=9018a855-b8c5-48f6-96f0-c47bbd307a2bAutomatically clean up/kill Orphaned Processes<h2>A nice easy way to patrol and eliminate orphaned processes&nbsp; <br />
</h2>
<p>
I have several web applications on our local intranet that require Excel to be called to make documents for my users.&nbsp; That brings up a problem:If you add the impatient tendancies of users (hey, I just clicked that button, I want my spreadsheet NOW!) to the statelessness of html on top of the stubornnessof the Excel process you get orphans, those pesky orphans that sit around, use your resources and do nothing.&nbsp; Now programatically, <a href="http://naspinski.net/post/Eliminating-orphaned-Excel-processes-in-Net-programs.aspx">I have done all I can to kill these bastards</a> but I can&#39;t seem to stop the persistant jerks 100% of the time.
</p>
<p>
&nbsp;
</p>
<p>
So, what I decided to do was to make an &#39;enforcer&#39; on the web server that makes his rounds every five minutes and kills any of these little pricks that are still hanging out in my otherwise peaceful system.&nbsp; This enforcer is not only effective, he is also efficient (tiny script, executes instantly) and keeps track of his work (log file).
</p>
<p>
&nbsp;
</p>
<p>
The code for my vbs file can be seen here:
</p>
<p>
&nbsp;
</p>
<div class="code"><p>
Option Explicit<br />
Dim objWMIService, objProcess, colProcess, intCount<br />
Dim strComputer, strProcessKill, logPath<br />
strComputer = &quot;.&quot;<br />
strProcessKill = &quot;&#39;EXCEL.EXE&#39;&quot; &#39;process to kill<br />
logPath = &quot;C:\scripts\killExcel.log&quot; &#39;path to log file<br />
intCount = 0<br />
<br />
Set objWMIService = GetObject(&quot;winmgmts:&quot; _<br />
&amp; &quot;{impersonationLevel=impersonate}!\\&quot; _<br />
&amp; strComputer &amp; &quot;\root\cimv2&quot;)<br />
<br />
Set colProcess = objWMIService.ExecQuery _<br />
(&quot;Select * from Win32_Process Where Name = &quot; &amp; strProcessKill )<br />
For Each objProcess <span class="kwrd">in</span> colProcess<br />
objProcess.Terminate()<br />
intCount = intCount + 1<br />
Next<br />
<br />
If intCount &gt; 0 Then<br />
&nbsp;&nbsp; &nbsp;Dim fso, txtFile<br />
&nbsp;&nbsp; &nbsp;Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot;)<br />
&nbsp;&nbsp; &nbsp;Set txtFile = fso.OpenTextFile(logPath, 8)<br />
&nbsp;&nbsp;&nbsp; txtFile.WriteLine(Date &amp; &quot; - &quot; &amp; Time &amp; &quot; Killed &quot; _<br />
&nbsp;&nbsp;&nbsp; &amp; intCount &amp; &quot; processe(s)&quot;)<br />
&nbsp;&nbsp; &nbsp;txtFile.Close<br />
End If<br />
<br />
WScript.Quit
</p>
</div><p>
&nbsp;
</p>
<p>
As you can see, it&#39;s very simple, and you can change it to eliminate any kind of process you want.&nbsp; Here it is if you want to check it out, included is an empty log file, the vbs file, and the bat file used to <a href="http://naspinski.net/post/Automated-Sharepoint-Backups-including-daily-folders-and-trash-collection.aspx">schedule it</a> (assuming you place this in <strong>c:\scripts\</strong>), enjoy.
</p>
<p>
&nbsp;
</p>
<p>
&nbsp;
</p>
<div style="text-align: center">
<a style="border: 1px dotted #336600; padding: 1em; font-weight: bold; font-size: 1.4em" href="http://naspinski.net/file.axd?file=kill_process.zip">kill_process.zip (1.32 kb)</a>
</div>
http://naspinski.net/post/Automatically-clean-upkill-Orphaned-Excel-Processes.aspx
naspinskihttp://naspinski.net/post/Automatically-clean-upkill-Orphaned-Excel-Processes.aspx#commenthttp://naspinski.net/post.aspx?id=11e0d275-03ea-4380-bc6a-d32051cc1c0dWed, 03 Sep 2008 10:33:00 -0500excelsteal some codevb scriptnaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=11e0d275-03ea-4380-bc6a-d32051cc1c0d0http://naspinski.net/trackback.axd?id=11e0d275-03ea-4380-bc6a-d32051cc1c0dhttp://naspinski.net/post/Automatically-clean-upkill-Orphaned-Excel-Processes.aspx#commenthttp://naspinski.net/syndication.axd?post=11e0d275-03ea-4380-bc6a-d32051cc1c0dTranslate an Excel Serial Date into a C# DateTime<h2>An annoying little &#39;feature&#39; of Excel is that it stores dates in a strange format</h2>
<p>
I stole the logic and from <a href="http://www.codeproject.com/KB/datetime/exceldmy.aspx">Code Project</a> and wrote some code that will give you a DateTime variable if you feed it a Excel serial integer (number of days after 2/29/1900).&nbsp; Strangely enough, <strong>DateTime.Parse(&quot;2/29/1900&quot;);</strong> throws an error, so the simple solution I thought of: <strong>DateTime.Parse(&quot;2/29/1900&quot;).AddDays(excelInteger); </strong>does not work.&nbsp; But this does:
</p>
<p>
&nbsp;
</p>
<div class="code"><p>
<span class="kwrd">public</span> DateTime ExcelSerialDateToDT(<span class="kwrd">int</span> nSerialDate)<br />
{<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> l = nSerialDate + 68569 + 2415019;<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> n = ((4 * l) / 146097);<br />
&nbsp;&nbsp;&nbsp; l = l - ((146097 * n + 3) / 4);<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> i = ((4000 * (l + 1)) / 1461001);<br />
&nbsp;&nbsp;&nbsp; l = l - ((1461 * i) / 4) + 31;<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> j = ((80 * l) / 2447);<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> nDay = l - ((2447 * j) / 80);<br />
&nbsp;&nbsp;&nbsp; l = (j / 11);<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> nMonth = j + 2 - (12 * l);<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">int</span> nYear = 100 * (n - 49) + i + l;<br />
<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">return</span> DateTime.Parse(nMonth + &quot;/&quot; + nDay + &quot;/&quot; + nYear);<br />
}
</p>
</div><p>
&nbsp;
</p>
<p>
As long as your date isn&#39;t within 60 days of 2/29/1900, this will work perfectly (don&#39;t ask why those are screwed up <img style="float: none" src="/admin/tiny_mce/plugins/emotions/images/smiley-tongue-out.gif" border="0" alt="Tongue out" title="Tongue out" />).
</p>
http://naspinski.net/post/Translate-an-Excel-Serial-Date-into-a-C-DateTime.aspx
naspinskihttp://naspinski.net/post/Translate-an-Excel-Serial-Date-into-a-C-DateTime.aspx#commenthttp://naspinski.net/post.aspx?id=eff52779-c39e-4fde-8d44-931ae941512cThu, 17 Jul 2008 12:24:00 -0500c#excelnaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=eff52779-c39e-4fde-8d44-931ae941512c1http://naspinski.net/trackback.axd?id=eff52779-c39e-4fde-8d44-931ae941512chttp://naspinski.net/post/Translate-an-Excel-Serial-Date-into-a-C-DateTime.aspx#commenthttp://naspinski.net/syndication.axd?post=eff52779-c39e-4fde-8d44-931ae941512cComplete Web-Based Excel Spreadsheet Builder<h2>Have your users make spreadsheets online, no excel needed</h2>
<p>
Now first off, this places a DataTable into the Session State, and I know some people have a problem with that... I don&#39;t care.&nbsp; Now that that is out of the way, I can explain how this works.
</p>
<p>
&nbsp;
</p>
<p>
It is very simple, this program runs through all of the TextBoxes and DropDownLists that you have within the <strong>input_container</strong> Panel and adds them as string columns to a DataTable <strong>dt</strong>.&nbsp; Once that is done, each entry is simply added to the DataTable and rendered onto a GridView.&nbsp; Then I use <a href="http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html" target="_blank">Matt Berseth&#39;s GridViewExportUtil</a> to spit the spreadsheet out to the user.&nbsp; Everything is taken care of real-time with no writing to the disk. Pretty simple.
</p>
<p>
&nbsp;
</p>
<p>
I also included a way to edit as a normal method wouldn&#39;t work in this case, it just populates a DropDownList every time you add a new item and pops it back into the forms if you choose to edit it _but_ if you do not save, the record will be lost (there is a warning).
</p>
<p>
&nbsp;
</p>
<p>
Another thing to note is that this is a drop-in-and-use application.&nbsp; Everything is populated automatically, you need to do absolutely nothing to the code-behind in order to use this utility, just customize your fields in the default.aspx in the <strong>input_container</strong> and the rest of the work is done for you.&nbsp; I used my method of parsing IDs of the input fields to make column names so <strong>ddlLets_Party</strong> turns in to a column &quot;Lets Party&quot;, <strong>Last_Name</strong> becomes &quot;Last Name&quot;, <strong>strFruit</strong> becomes &quot;Fruit&quot; and so on. &nbsp; You could easily add another attribute as ColumnName or something like that if you please.
</p>
<p>
&nbsp;
</p>
<p>
Here is the provided example:&nbsp;<a href="http://examples.naspinski.net/excel_builder.aspx" target="_blank">Excel Spreadsheet Builder In Action</a>, and the code:
</p>
<br />
<br />
<div style="text-align: center">
<a style="border: 1px dotted #336600; padding: 1em; font-weight: bold; font-size: 1.4em" href="http://naspinski.net/file.axd?file=web_based_excel.zip">web_based_excel.zip (3.87 kb)</a>
</div>
http://naspinski.net/post/Complete-Web-Based-Excel-Spreadsheet-Builder.aspx
naspinskihttp://naspinski.net/post/Complete-Web-Based-Excel-Spreadsheet-Builder.aspx#commenthttp://naspinski.net/post.aspx?id=dbc4f7a8-2626-47f3-ba44-26af6a9b40dfFri, 09 May 2008 02:27:00 -0500asp.netc#excelsteal some codenaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=dbc4f7a8-2626-47f3-ba44-26af6a9b40df0http://naspinski.net/trackback.axd?id=dbc4f7a8-2626-47f3-ba44-26af6a9b40dfhttp://naspinski.net/post/Complete-Web-Based-Excel-Spreadsheet-Builder.aspx#commenthttp://naspinski.net/syndication.axd?post=dbc4f7a8-2626-47f3-ba44-26af6a9b40dfAppending a single Excel sheet to an existing Excel Spreadsheet programatically&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; <br />
<h2>This can be done via a web-interface via Excel Interop commands</h2>
<p>
<img src="http://naspinski.net/image.axd?picture=combine.jpg" alt="" />
I was asked to take some user input, produce an Excel spreadsheet, then append it to an exisiting workbook that had multiple static spreadsheets then spit the Excel sheet out as a download.&nbsp; Excel Interop processes are not that easy to work with, and can be quite a pain.&nbsp; After fumbling around, I was able to come up with a (possibly rudimentary) way to do this.
</p>
<p>
&nbsp;
</p>
<p>
Now this is just a small part of a much larger program that takes in user input, and saves it to a temporary directory as an Excel spreadsheet.&nbsp; Inside that directory there is also another file that doesn&#39;t change that thsi will be appended to, I call this my &#39;base&#39; file.&nbsp; The snippet I am providing combines the files and saves them as a seperate new file before my program sends it to the user.
</p>
<p>
&nbsp;
</p>
<p>
There is likely a better way to do this without all of the saving with a stream, but I am not sure how and am wide open for better solutions!&nbsp; Anyways, here is the function I ended up using.&nbsp; The inputs are:
</p>
<ul>
<li><strong>baseFile</strong> is the path to the static file I am appending to</li>
<li><strong>newSheet </strong>is the path to the new file that I am appending to the base</li>
<li><strong>newFile</strong> is the path to the final appended sheet&nbsp;</li>
</ul>
<p>
&nbsp;
</p>
<div class="code"><p>
<span class="kwrd">protected</span> <span class="kwrd">void</span> AppendSheet(<span class="kwrd">string</span> baseFile, <span class="kwrd">string</span> newSheet, <span class="kwrd">string</span> newFile)<br />
{<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">try</span><br />
&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc = <span class="kwrd">new</span> Microsoft.Office.Interop.Excel.Application();<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc.Workbooks.Open(baseFile, Type.Missing, <span class="kwrd">false</span>, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc.Workbooks[1].Sheets.Add(Type.Missing, exc.Workbooks[1].Sheets[1], 1, newSheet);<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span class="kwrd">if</span> (System.IO.File.Exists(newFile)) System.IO.File.Delete(newFile);<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc.Workbooks[1].SaveAs(newFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, <span class="kwrd">false</span>, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);<br />
&nbsp;&nbsp;&nbsp; }<br />
&nbsp;&nbsp;&nbsp; <span class="kwrd">catch</span> (Exception ex) <br />
&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span class="rem">//handle your exception<br /></span>
&nbsp;&nbsp;&nbsp; }<br />
}
</p>
</div>http://naspinski.net/post/Appending-a-single-Excel-sheet-to-an-existing-Excel-Spreadsheet-programatically.aspx
naspinskihttp://naspinski.net/post/Appending-a-single-Excel-sheet-to-an-existing-Excel-Spreadsheet-programatically.aspx#commenthttp://naspinski.net/post.aspx?id=4d2301cb-d829-4c09-966d-18c516a35bfaWed, 07 May 2008 09:47:00 -0500c#excelnaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=4d2301cb-d829-4c09-966d-18c516a35bfa0http://naspinski.net/trackback.axd?id=4d2301cb-d829-4c09-966d-18c516a35bfahttp://naspinski.net/post/Appending-a-single-Excel-sheet-to-an-existing-Excel-Spreadsheet-programatically.aspx#commenthttp://naspinski.net/syndication.axd?post=4d2301cb-d829-4c09-966d-18c516a35bfaDestroy those pesky orphaned Excel processes in .Net programs<h2>If you have ever worked with Excel in the .Net environment, you likely have run across the occasional orphan and they can be a pain to clean up</h2>
<p>
Recently I was doing just this, and my Task Manager was filling up with orphans fast.&nbsp; I was calling for the Excel Process to close, but that wasn&#39;t working most of the time.&nbsp; So I came up with a more elaborate way to make them disappear.&nbsp; Basically I found out that in order to close the application, you have to close the workbooks, and in order to close the workbooks, you have to close each workbook individually, and to close those, you have to close each worksheet in those workbooks individually.&nbsp; I also included COM management, so we are hitting this with multiple attacks to make sure they stay dead!&nbsp; So the super-overkill-do-everything process is:
</p>
<p>
&nbsp;
</p>
<ol>
<li>Collect each worksheet individually</li>
<li>Collect each workbook individually</li>
<li>Delete the worksheets</li>
<li>Release the worksheets</li>
<li>Null the worksheets<br />
</li>
<li>Delete the workbooks</li>
<li>Release the workbooks</li>
<li>Null the workbooks
</li>
<li>Close the workbooks collection</li>
<li>Quit the application</li>
<li>Release the application</li>
<li>Null the application</li>
<li>Collect garbage</li>
</ol>
<h4>&nbsp;</h4>
<h4>
Here&#39;s how</h4>
<p>
After your work is done, you are left with a <strong>Microsoft.Office.Interop.Excel.Application</strong> named &#39;exc&#39;, call <strong>killExcel(exc):</strong>
</p>
<blockquote>
protected void killExcel(Microsoft.Office.Interop.Excel.Application exc)<br />
{<br />
&nbsp;&nbsp;&nbsp; try<br />
&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List&lt;Microsoft.Office.Interop.Excel.Workbook&gt; wbs = new List&lt;Microsoft.Office.Interop.Excel.Workbook&gt;();<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List&lt;Microsoft.Office.Interop.Excel.Worksheet&gt; wss = new List&lt;Microsoft.Office.Interop.Excel.Worksheet&gt;();<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (Microsoft.Office.Interop.Excel.Workbook wb in exc.Workbooks)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; foreach (Microsoft.Office.Interop.Excel.Worksheet ws in wb.Worksheets)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wss.Add(ws); // collect worksheets<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wbs.Add(wb); // collect workbooks<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int i = 0; i &lt; wss.Count; i++)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wss[i].Delete();<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.Runtime.InteropServices.Marshal.ReleaseComObject(wss[i]); // release it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wss[i] = null; // null it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int i = 0; i &lt; wbs.Count; i++)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wbs[i].Close(null, null, null);<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs[i]); // release it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wbs[i] = null; // null it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc.Workbooks.Close(); // so you can close this<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc.Quit(); // so you can quit this<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); // release it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exc = null;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GC.Collect(); // this sets up the finalizers<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GC.WaitForPendingFinalizers();<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GC.Collect(); //apparently this kills it<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GC.WaitForPendingFinalizers();<br />
&nbsp;&nbsp;&nbsp; }<br />
&nbsp;&nbsp;&nbsp; catch (Exception ex)<br />
&nbsp;&nbsp;&nbsp; {<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // deal with it fool!<br />
&nbsp;&nbsp;&nbsp; }<br />
}
</blockquote>
<p>
Yes this is overkill, but I think I covered all possible ways to kill you processes, so they should be more dead than Elvis; no more orphans - yay for iteration!
</p>
<p>
&nbsp;
</p>
<h4>Some useful links:</h4>
<p>
<a href="http://www.thescarms.com/dotnet/ExcelObject.aspx" target="_blank">http://www.thescarms.com/dotnet/ExcelObject.aspx</a>&nbsp;
</p>
<p>
<a href="http://www.devcity.net/Articles/239/3/article.aspx" target="_blank">http://www.devcity.net/Articles/239/3/article.aspx&nbsp;</a>
</p>
<p>
<a href="http://krgreenlee.blogspot.com/2006/03/digipede-distributing-excel_10.html" target="_blank">http://krgreenlee.blogspot.com/...ting-excel_10.html&nbsp;</a>
</p>
http://naspinski.net/post/Eliminating-orphaned-Excel-processes-in-Net-programs.aspx
naspinskihttp://naspinski.net/post/Eliminating-orphaned-Excel-processes-in-Net-programs.aspx#commenthttp://naspinski.net/post.aspx?id=5d80bc74-29a7-47e9-a91a-b15464e4f6c2Fri, 02 May 2008 09:23:00 -0500c#excelnaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=5d80bc74-29a7-47e9-a91a-b15464e4f6c23http://naspinski.net/trackback.axd?id=5d80bc74-29a7-47e9-a91a-b15464e4f6c2http://naspinski.net/post/Eliminating-orphaned-Excel-processes-in-Net-programs.aspx#commenthttp://naspinski.net/syndication.axd?post=5d80bc74-29a7-47e9-a91a-b15464e4f6c2Exporting a Gridview to Excel<h2>
Matt Berseth supplies an awesome way to export to excel with pretty much no hassle whatsoever
</h2>
<p>
Just add in his class and call it withing as event like this&nbsp;
</p>
<blockquote>
GridViewExportUtil.Export(&quot;save_as_this.xls&quot;, this.GridViewName);
</blockquote>
And that is it, nothing else is necessary. And the code is very simple and easy to follow. <a href="http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html">Here is the link to Matt&#39;s site with more details on it.</a> <br />
<br />
<br />
<div style="text-align: center">
<a style="border: 1px dotted #336600; padding: 1em; font-weight: bold; font-size: 1.4em" href="http://naspinski.net/file.axd?file=gridviewexport_webroot.zip">gridviewexport_webroot.zip (3.06 kb)</a>
</div>
http://naspinski.net/post/Exporting-a-Gridview-to-Excel.aspx
naspinskihttp://naspinski.net/post/Exporting-a-Gridview-to-Excel.aspx#commenthttp://naspinski.net/post.aspx?id=0d309c61-36e0-42c6-8285-eab8810fe71aFri, 14 Mar 2008 00:00:00 -0500asp.netc#excelsteal some codenaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=0d309c61-36e0-42c6-8285-eab8810fe71a0http://naspinski.net/trackback.axd?id=0d309c61-36e0-42c6-8285-eab8810fe71ahttp://naspinski.net/post/Exporting-a-Gridview-to-Excel.aspx#commenthttp://naspinski.net/syndication.axd?post=0d309c61-36e0-42c6-8285-eab8810fe71aConverting an Excel Spreadsheet to a DataSet, DataTable and Multi-Dimensional Array<h2>A way to easily manipulate data from an excel spreadsheet programmatically</h2>
<p>
<img style="padding: 0px 5px 5px 0px; float: left" src="http://naspinski.net/image.axd?picture=excel-array.jpg" alt="" width="378" height="346" />I work in an office environment which is dominated by Microsoft Excel spreadsheets, and so often, people want to use them in programs.&nbsp; I am a SQL junky myself, but Excel is appropriate in a lot of cases, but integrating them into programs can be a pain.&nbsp; More specifically, getting&nbsp;the data from the&nbsp;Excel worksheet&nbsp;to a form where it is workable can be a pain.&nbsp;
</p>
<p>
&nbsp;
</p>
<p>
With this example, you can see how to take in an Excel Spreadsheet&nbsp;to get the&nbsp;data into commonly manipulatable data types&nbsp;in c#; then you can use/change it however you want to.&nbsp; This demo shows first how to put the data into a dataset, then datatable (almost the same thing), then takes the same data in puts it into a multi-dimensional array (I prefer to work it arrays).&nbsp; Since this is a linear progression, the demo only outputs the information from the array into an html table to show that it worked.&nbsp;
</p>
<p>
&nbsp;
</p>
<p>
The demo uses the&nbsp;very first row as the column names (as it is used in datatable) and assumes that the&nbsp;Spreadsheet is in default format with Sheet1 as it&#39;s first sheet.&nbsp; The demo only processes&nbsp;Sheet1.&nbsp; This can be easily customized if you want to process more.&nbsp;
</p>
<p>
&nbsp;
</p>
<p>
With this, you&nbsp;can take/change/display the excel data however you want to using asp.net controls like ListView or GridView&nbsp;or just simple c#.&nbsp; The code is pretty well commented, so you should be able to pull the parts that you may need.&nbsp; *NOTE: you need full trust enabled for this to work properly.
</p>
<br />
<br />
<div style="text-align: center">
<a style="border: 1px dotted #336600; padding: 1em; font-weight: bold; font-size: 1.4em" rel="enclosure" href="http://naspinski.net/file.axd?file=excel_to_array.zip">excel_to_array.zip (2.11 kb)</a>
</div>
http://naspinski.net/post/Converting-an-Excel-Spreedsheet-to-a-DatSet2c-Datatable-and-Multi-Dimensioanl-Array.aspx
naspinskihttp://naspinski.net/post/Converting-an-Excel-Spreedsheet-to-a-DatSet2c-Datatable-and-Multi-Dimensioanl-Array.aspx#commenthttp://naspinski.net/post.aspx?id=a2cc0bc7-2562-4e03-89d0-9ddec9fe4e5cSun, 10 Feb 2008 04:52:00 -0500asp.netc#excelsteal some codenaspinskihttp://naspinski.net/pingback.axdhttp://naspinski.net/post.aspx?id=a2cc0bc7-2562-4e03-89d0-9ddec9fe4e5c11http://naspinski.net/trackback.axd?id=a2cc0bc7-2562-4e03-89d0-9ddec9fe4e5chttp://naspinski.net/post/Converting-an-Excel-Spreedsheet-to-a-DatSet2c-Datatable-and-Multi-Dimensioanl-Array.aspx#commenthttp://naspinski.net/syndication.axd?post=a2cc0bc7-2562-4e03-89d0-9ddec9fe4e5c