First a well meant thank you for saving me :) Really, its in the middle of the night and I believe I can pull my requirements off with EPPlus.
I have tried the Openxml SDK V2 which is nice if you have a month to spare and so went for another codeplex project called ExtremeML
which uses a template approach to excel generation.
This template approach is what I was after as it allows me to create the Excel like I'm used to leveraging the power of conditional formatting, pivots, charts and all the other good stuff.
ExtremeML would then just inject the data in the template and be done with it. This allows for creation of insightful spreadsheets in no time ... This is awesome and drove me to ExtremeML in the first place.
However, I was doing my tests with 100 records / 20 columns and it ran fine. Once it was time to plug in the real data performance degraded enormously. 10000 records took 20 minutes all of a sudden and 1,5 gig of memory was consumed.
I then considered 2 options
1. use the sax approach blogged at http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx
This seems feasible and it looks interesting to give it a try
Enter EPPlus which I believe will save my day :)
BTW for what does PLus stand for?
My questions is about how far we can go with the template approach using EPPlus. When converting my ExtremeML code I noticed that in the current version (please correct me if wrong) tables have to constructed and can't be reused.
With EPPlus it isn't possible to have a table in the template where the custom formats are set, conditional formatting is applied, etc and let EPPlus inject the data in it.
It is my understanding that to use a table one has to write code like this
ExcelPackage excelPackage = new ExcelPackage(new FileInfo(@"c:\temp\template.xlsx"));
excelPackage.Workbook.Worksheets["Sheet1"].Cells["A1"].LoadFrom DataTable(dataTable, true);
excelPackage.Workbook.Worksheets["Sheet1"].Tables.Add(new ExcelAddressBase(1, 1,dataTable.Rows.Count+1,dataTable.Columns.Count),"table1");
FileInfo output File = new FileInfo(@"c:\temp\out.xlsx");
My humble question:
Is it possible that EPPlus injects data in an existing table? I saw that the current API has a dimension property with an internal setter. This makes me believe that resizing an existing table isn't supported.
I believe that by resizing a table the full power of templates can be achieved...
For now I can use pivots and charts in my template which is awesome. Only tables seem out of reach.
Here is an example of an excel that I would like to generate. Currently I have no clue to get to such a table.
Thanks for your awesome contribution to our .NET community!
Looking forward to see this project flourish