Oct 31 2008
Writing Date to Excel from ASP.NET and C#
Ok, so i’ve just been working on outputting data from an ASP.NET based web app into an Excel Spreadsheet. I’m not going to go into the details of how to do that, but only how to avoid one little problem i encountered.
Excel has this irritating abilty to do whatever it wants with formats of cells. In particular i was getting very cross with it ignoring the Date format i was giving the template file (the the C# then writes into). The result was that all my date stamps from the C# where going into Excel as strings, which then broke all the VBA in the spreadsheet.
Very annoying!
Anyway, after about 4 hours of trying to fix it and writing a work around using the DateValue function in VBA, i eventually found the cause of all the problems in the first place.
In Excel you have the abilty to ‘lock’ cells. Now, the tool tip in Excel 2007, helpfully states that Locking the Cell will have no effect until you Protect the Sheet as well. Yes, that seems stupid to me too. But, it also seems to lie slightly – for some reason, the C# can still write to a Locked Cell but only as a string (or at least thats what seems to happen). Point is, that by unlocking the cell i was able to tell excel what format i wanted it to be AND Excel would actaully do it!
So yeah, if you ever find yourself in a position where your ASP.NET application is trying to write a date into Excel, but it keeps coming out as a string do the following:
1 – Check the Cell isn’t Locked
2 – Check the Cell is formatted a Date
No responses yet
