Reuse existing table in Template : A feature that would make this project even more awesome than it already is

Apr 12, 2011 at 9:32 PM

Hi EPPlus!,

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

2. EPPlus


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");
excelPackage.Save(output File);

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

 

Tom

 

Coordinator
Apr 14, 2011 at 1:51 PM

Hi,

Currently the Address method off the ExcelTable class is readonly (to simplify), but I guess it could be allow to be changed as long as the start row is the same and the number of colums is intact. I'll add that to my TODO list.

If you need a fix now you can set the table/@Ref attribute and the table/autoFilter/@ref  to the wanted range-address (use the Table.TableXml property).

The "plus" is just ExcelPackage+ (but it's not much left from the startup project :) ).

Jan

Apr 14, 2011 at 9:36 PM

Hello Jan,

Thanks for your reply

I hope I got my suggestion across. I will try the xpath solution tomorrow when I behind my developer box

 

The template solution would work as follows. We are in Excel designing our table with conditional formatting:

http://content.screencast.com/users/buckleyXP/folders/Jing/media/91ba5c83-064b-4def-84ff-524e75408664/2011-04-14_2325.png

Next we clear the data and save it

http://content.screencast.com/users/buckleyXP/folders/Jing/media/738e69fb-0b01-4d53-8fdf-65b447aecacf/2011-04-14_2326.png

After that we just let EPPlus inject the data and we have our awesome table in no time.

Can I suggest you give some thought about the branding of this project? EPPlus doesnt communicate what the project is about unless you know its history :)

Maybe "Excel Generation" or "Excel Evolved" or "Lightspeed Excel" ? I have to admit I choose ExtremeML because of its name and would have sticked without if it was so slow bause of the underlying SDK

The edge that EPPlus currently had over all the other options is its speed

 

Can't wait to try your suggestion :)

 

Kind Regards, Tom

 

Apr 25, 2014 at 10:07 PM
Aug 5, 2014 at 9:27 PM
The preceding link simply loads this same discussion page. I have a feeling it might have been meant to go here.
Feb 11, 2015 at 8:17 AM
My +10 for altering existing tables. I got it working now with the XML hack in the link above but it would be great if it was just part of the library so I can be confident it will always work! Thanks!!