loads of useful information, examples and tutorials pertaining to web development utilizing asp.net, c#, vb, css, xhtml, javascript, sql, xml, ajax and everything else...

 



Advertise Here
C-Sharpener.com - Programming is Easy!  Learn Asp.Net & C# in just days, Guaranteed!

Getting the Sheet Name(s) from an Excel Document with OleDb

by naspinski 9/22/2009 10:21:00 AM

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

Considering that I already have my ConnectionString strC set up to access my Excel file, I can just use the GetOleDbSchemaTable method like this:
DataTable dtS;
using (OleDbConnection c = new OleDbConnection(strC))
{
  c.Open();
  dtS = c.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
    new object[] { null, null, null, "TABLE" });
  c.Close();
}

Now dtSchema holds the sheet names (in alphabetical order). To get them out:
foreach(DataRow row in dtS.Rows)
  Console.WriteLine(row.Field<string>("TABLE_NAME"));

Tags: ,

c# | excel

Automatically clean up/kill Orphaned Processes

by naspinski 9/3/2008 10:33:00 AM

A nice easy way to patrol and eliminate orphaned processes 

I have several web applications on our local intranet that require Excel to be called to make documents for my users.  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.  Now programatically, I have done all I can to kill these bastards but I can't seem to stop the persistant jerks 100% of the time.

 

So, what I decided to do was to make an 'enforcer' 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.  This enforcer is not only effective, he is also efficient (tiny script, executes instantly) and keeps track of his work (log file).

 

The code for my vbs file can be seen here:

 

Option Explicit
Dim objWMIService, objProcess, colProcess, intCount
Dim strComputer, strProcessKill, logPath
strComputer = "."
strProcessKill = "'EXCEL.EXE'" 'process to kill
logPath = "C:\scripts\killExcel.log" 'path to log file
intCount = 0

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
objProcess.Terminate()
intCount = intCount + 1
Next

If intCount > 0 Then
    Dim fso, txtFile
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txtFile = fso.OpenTextFile(logPath, 8)
    txtFile.WriteLine(Date & " - " & Time & " Killed " _
    & intCount & " processe(s)")
    txtFile.Close
End If

WScript.Quit

 

As you can see, it's very simple, and you can change it to eliminate any kind of process you want.  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 schedule it (assuming you place this in c:\scripts\), enjoy.

 

 

Translate an Excel Serial Date into a C# DateTime

by naspinski 7/17/2008 12:24:00 PM

An annoying little 'feature' of Excel is that it stores dates in a strange format

I stole the logic and from Code Project 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).  Strangely enough, DateTime.Parse("2/29/1900"); throws an error, so the simple solution I thought of: DateTime.Parse("2/29/1900").AddDays(excelInteger); does not work.  But this does:

 

public DateTime ExcelSerialDateToDT(int nSerialDate)
{
    int l = nSerialDate + 68569 + 2415019;
    int n = ((4 * l) / 146097);
    l = l - ((146097 * n + 3) / 4);
    int i = ((4000 * (l + 1)) / 1461001);
    l = l - ((1461 * i) / 4) + 31;
    int j = ((80 * l) / 2447);
    int nDay = l - ((2447 * j) / 80);
    l = (j / 11);
    int nMonth = j + 2 - (12 * l);
    int nYear = 100 * (n - 49) + i + l;

    return DateTime.Parse(nMonth + "/" + nDay + "/" + nYear);
}

 

As long as your date isn't within 60 days of 2/29/1900, this will work perfectly (don't ask why those are screwed up Tongue out).

Tags: ,

c# | excel

Complete Web-Based Excel Spreadsheet Builder

by naspinski 5/9/2008 2:27:00 AM

Have your users make spreadsheets online, no excel needed

Now first off, this places a DataTable into the Session State, and I know some people have a problem with that... I don't care.  Now that that is out of the way, I can explain how this works.

 

It is very simple, this program runs through all of the TextBoxes and DropDownLists that you have within the input_container Panel and adds them as string columns to a DataTable dt.  Once that is done, each entry is simply added to the DataTable and rendered onto a GridView.  Then I use Matt Berseth's GridViewExportUtil to spit the spreadsheet out to the user.  Everything is taken care of real-time with no writing to the disk. Pretty simple.

 

I also included a way to edit as a normal method wouldn'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).

 

Another thing to note is that this is a drop-in-and-use application.  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 input_container and the rest of the work is done for you.  I used my method of parsing IDs of the input fields to make column names so ddlLets_Party turns in to a column "Lets Party", Last_Name becomes "Last Name", strFruit becomes "Fruit" and so on.   You could easily add another attribute as ColumnName or something like that if you please.

 

Here is the provided example: Excel Spreadsheet Builder In Action, and the code:



Appending a single Excel sheet to an existing Excel Spreadsheet programatically

by naspinski 5/7/2008 9:47:00 AM
      

This can be done via a web-interface via Excel Interop commands

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.  Excel Interop processes are not that easy to work with, and can be quite a pain.  After fumbling around, I was able to come up with a (possibly rudimentary) way to do this.

 

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.  Inside that directory there is also another file that doesn't change that thsi will be appended to, I call this my 'base' file.  The snippet I am providing combines the files and saves them as a seperate new file before my program sends it to the user.

 

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!  Anyways, here is the function I ended up using.  The inputs are:

  • baseFile is the path to the static file I am appending to
  • newSheet is the path to the new file that I am appending to the base
  • newFile is the path to the final appended sheet 

 

protected void AppendSheet(string baseFile, string newSheet, string newFile)
{
    try
    {
        exc = new Microsoft.Office.Interop.Excel.Application();
        exc.Workbooks.Open(baseFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        exc.Workbooks[1].Sheets.Add(Type.Missing, exc.Workbooks[1].Sheets[1], 1, newSheet);
        if (System.IO.File.Exists(newFile)) System.IO.File.Delete(newFile);
        exc.Workbooks[1].SaveAs(newFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    }
    catch (Exception ex)
    {
        //handle your exception
    }
}

Tags: ,

c# | excel