Friday, October 19, 2007

ExcelPackage

I've been reviewing the ExcelPackage project on CodePlex. It is a useful project but one thing I was wondering is why does it directly interact with the XML?

I think it is a performance and a code maintenance hit to work directly with the XML for the workbook and worksheets. In my oh so humble opinion I think it would be easier to create plain old C# objects to work with the cells, rows, sheets, etc. and then serialize and de-serialize as needed. I say this because in the XML for a worksheet the cells are stored in XML nodes that have the row number and individual cell addresses in them:

[xml]



[/xml]

So if we work directly with the XML what has to happen when we insert a row? We have to loop through all of the XML nodes and update the row values. Note that I am not even talking about how to deal with merge cells or ranges (which the ExcelPackage does not work with).

I think that creating a Attribute-value system would be a better approach. Rows and Columns could be a linked list, Cells could be in an indexed cache or hashtable. And the collections and individual objects would handle where they were in the domain of the spreadsheet.

Guess I should get busy and prove my point.

Wednesday, October 10, 2007

EntLib Data Access Block and Excel

I need to be able to open an Excel workbook and grab the data from one of the worksheets. I don't want to use any Office InterOperability (as that sucks) and I know I can do it using ADO.Net. But since 1.) I hate writing all of that DataAdapter / Connection code because 2.) I am lazy I decided to see how hard it would be to use the EntLib DAB.

Turned out it was pretty easy.

Create your configuration as such:

Then call your code as such:

[csharp]
public static DataSet GetWorksheet()
{
string sql = "SELECT * FROM [Sheet1$]";
string connectionName = "Book1";

return DatabaseFactory.CreateDatabase(connectionName)
.ExecuteDataSet(CommandType.Text, sql);
}
[/csharp]
And that is that. One DataSet ready to go.

Crappy Apps

Why is it that so many crappy apps are out there in the wild?

Is it because of the visual tools available in the .Net realm that generate so much crap that the average programmer just does not have time to clean it up? Or are the developers just too lazy to clean up the mess as they go?