Dynamically create static Excel files for Excel Service
Excel Services is pretty nice for displaying Excel files over the network. But if you're developing Excel Services solution for the first time you might first think of the limitations or something that forces you to build your overall solution in certain way. So I thought I'll write little bit about my idea about creating dynamically static Excel files. This might be something that you could be interested in... or not :-) It of course depends on the solution that you need to build. I just want to give you few ideas that you can use in your own projects. Okay here we go!
- ...to use 2 different languages (Finnish and English)
- ...have static texts in Excel that needs to be translatable
- ...use pivottables
- ...export data into .xlsx and to .csv (and possibly to some other formats as well)
- And the end user cannot see any formulas in their exported files
- ...filter the data from UI
- Translation for that: We need to pass parameters to the database query
- ...have easily maintainable system (=minimize the amount of Excels)
- ...flexible solution. So if we later want more complex scenarios this needs to be supported in your solution.
- ...performance performance performance (but no extra costs!)
- But we only update the incoming data quite rarely
Okay... list is quite long and I need to discuss a little bit about those demands.
1 to 3) Supporting more than one language can be issue if your data contains texts that needs to be translated. Also pivottable column names, captions and total captions needs to be translated. So translating static texts is easy compared to that :-)
4) Exporting to .xlsx is easy. Just use Excel Services API and retrieve "snapshot" and you're good to go. But if you want to convert it to other formats then you need to do some extra work.
5) You can use filtering but if you think of 8) you just can't do filtering of 2 million rows if you just want to view 50 rows... that would kill the performance. So since you need to think of performance you probably want to pass parameters to your database queries. If plan to get your relational data into your Excel using Office Database Connection (ODC) you cannot unfortunately use parameters since querytext will be "hardcoded" into the ODC. Of course you can achieve that with multiple ODC files but it could create mess. You can store those in Data Connection Library (DCL) to ease up the maintenance pain but still it's quite challenging to do that (=my personal opinion). And if you then think of the 6) you don't want to create maintenance hell. And of course if it gets too complicated you would most likely have issues with ODC approach. But if you use OLAP you could manage with only one ODC.
6) You don't want to have 1,5 million different files and then update them manually? Okay... I'll get the picture.
7) I know that predicting future is hard so let's create solution that is flexible enough so that you can extend it in many ways. I don't want to give limitations to your future needs.
8) So you don't want to buy 5 new servers with lot of processors? What about memory then... it's cheap? So that's okay... let's try to create as static files as we only can. This way we can use "memory over CPU" approach as much as possible. And creating stuff in cache before users are going to use the system would be nice.
I'm ready to go to next phase and show you my example that I have created.
Implementation of my solution
- Create database for the example
- Create template Excel file
- This is then used to create the "static copy instance"
- Create SharePoint structure for the demo
- Create custom web part that hosts EWA and fills the other requirements as well
- Exports to different file formats
- Filter the query from UI
- Translate texts that are needed in order to get the file in users native language
1. Create database for the example
In my example I'll use legendary AdventureWorks database :-) (I know that for some developers this itself will cause some hatred towards me ;-)
I did minor changes to the AdventureWorks data since I want my demo to support English and Finnish. I modified French culture to be Finnish culture and then modified the texts in description to be example same as the name (so that I'll can demonstrate the translations in database layer) (Note: I didnt' change the ProductModel translation since I want to "translate" it in my code).
My plans is to demonstrate product data. Here's an example of the data:
From that data you can easily see that I'm going to use culture info to get the correct data to my Excel. Of course you could create your own SQL/stored procs to handle translations as you wish.
2. Create template Excel
Now I'm going to create Excel that uses previously modified data. Since my plan is to dynamically fill the Excel with data I'm just going to create "almost empty template" that will be placeholder for the real data. Here are screenshots from my MyEWA.xlsx Excel file:
Display-sheet has static text in A2 and then it has Pivottable that retrieves data from the Data. If you're really sharp you probably noticed that row 5 is missing from the Display-sheet... I'll come to that later.
Data-sheet contains Header row and then it contains 1 data row. This data row can been seen from the pivottable in the Display-sheet.
Third sheet is Parameters and in this example I'll only use it to pass Culture to the Excel:
So if we now take closer look at the Display-sheet and see the formula in cell A2:
A2 cell contains following formula:
=IF(Parameters!B1="en-US";"Here is report about products.";"Tässä on raportti tuotteista")
It's pretty easy to understand that if the B1 cell in Parameters-sheet is set to en-US then the text will be "Here is report about products" . and if it isn't then "Tässä on raportti tuotteista" text will appear in the cell (latter text is same as the English one but in Finnish :-). By now probably everybody knows already that we're going to change the value in Parameters!B1 dynamically... and use it to translate the static texts inside Excel into correct language.
This same can be achieved if you dynamically copy text over specific cells. I have example of that in my code but it's commented because I didn't use it in my solution. But if you need to check this approach you can create new sheet i.e. Translations and have three columns Location, Text in fi-FI and Text in en-US. And then dynamically go through those translations and copy text to correct location i.e. Data!A5.
3. Create SharePoint structure for the demo
SharePoint site structure is following in my demo (in your case you can have whatever names... I just used culturenames to make this as simple as possible):
- Example portal
Obviously en-US is site that has regional settings (Site settings->Site administration: Regional settings) set to English locale:
And fi-FI site is set to Finnish locale.
4. Create custom web part that hosts EWA and fills the other requirements as well
Now we're ready to show the user interface for our solution. My solution is MyEWA web part that contains all the necessary controls and functionality this solution needs. As always my I'll cut short in the UI implementation. I'll just add few buttons and dropdown but no fancy look & feel.
UI in MyEWA.aspx:
UI in MunEWA.aspx:
(Note: Anyone who understands Finnish may laugh at my translations since I translated them smile in my face :-)
UI is pretty easy and straight forward: 1 dropdown to select product (=this is used as filter criteria in DB request) and 2 export buttons (Excel and CSV). If user would press Export Excel output would be something like this:
And if user would press Vie Exceliin output would be something like this:
Both of those export files are Snapshots (=they don't contain formulas just data).
If user would use Vie CSV -button (=Export to CSV) it would look something like this:
(Note: You can see [again!] empty row 5... but I'll explain reason for that soon)
Now I think we're ready to start reading some code. Just to remind you that I have put all the functionality into this one .cs file but in real life you don't do that! You refactor this kind of approach into several classes so that it is more maintainable. My "one file approach" is for demonstration purposes only. Also improving error handling is out of scope of this demo. Access rights is also something that needs to be solved since that process needs to write files to file system. Of course that can be solved with impersonation but anyway that needs to be taken into account. But finally here's the code:
I'm not going to explain code since most of that is quite easily understandable... but if you have questions then post comment to this entry and I'll get back to you.
You can probably see that I have added a lot of "// TODO:"-markers to identify places you most likely will be doing some modifications if you plan to use my code. I also left some code blocks that I have tried and noticed that it doesn't work as you would expect. Good example is that you cannot change the caption / labels of the pivottable. You CAN do that in Excel but you cannot do that using Excel Services API. So in other words... this doesn't work:
es.SetCellA1(sessionId, "Display", "A5", "My Pivottable caption", out status);
That is the reason why I have actually hidden that row. User sees still caption but it's just normal text that is formatted so that it looks like caption :-) But if I unhide A5 it would look like this:
But again... that caption isn't visible if you export Excel (row 5 is empty but user needs to Unhide that row in order to see it):
Another Excel tip that you need is Pivottable filters. Because if you don't set any filter you would get "(blank)" rows into table. That can be filtered away with label filters:
Set "Does Not Equal... " -filter to be empty:
And after that you don't get those "(blank)" rows in your pivottable.
If you're interestested that how did my solution look like inside VS:
So I had resource files for default language and fi-FI. Since I used SharePoint Web Part template (I like some much the F5 integration :-) I needed manually copy the fi-FI folder under my projects Debug folder under the web applications bin directory: C:\Inetpub\wwwroot\wss\VirtualDirectories\1000\bin. I just wanted to mention this if you have issues with your resource files :-)
I ended up doing a lot of static Excel files... so I think that it should be fast since now there isn't any database queries happening (not verified). BUT at least I managed to demonstrate that you could create static files so that if everything goes fine you should benefit from memory of your app servers. And if your data changes rarely you can easily create script that forces all the cache files to be created (just loops all the necessary HTTP requests). This would be trivial to implement. Here is one example call:
You can modify ExportFormat and File and Language in order to get file that you want.
And if you think this solution more deeply... actually you don't need even database at the production environment since you create static copies of Excels in another environment (i.e. publishing environment) :-) That would be another way to look at this solution... just distribute static Excel files between environments.
What's other possibilies I have?
You probably at least two more options:
- OLAP + CUBE* -functions inside Excel
- You need to create ODC for the OLAP connection
- Store this in Data Connection Library in SharePoint
- You need to configure Single Single-On Service (SSO) to pass the credentials
- You would have a better way to handle data
- If you change filter => new database request!
- You will still have same issues with pivottable that with "old relational" pivottable
- You need to create ODC for the OLAP connection
- UDF (User Defined Function)
- Create still all the data retrieval logic with .NET
- In Excel you would have something like this "=MyRetrieveData(Parameters!B2:Parameters:!B6)"
- No preview :-(
- Database requests
- NOTE: You can use this in your Excels even if you dynamically create the static instances!
- It could be probably good idea to use this for complex logic
- Create still all the data retrieval logic with .NET
I haven't validated those as detailed as I have this "static Excel instances" approach so I might be missing something important about the other approaches :-)
More information about Excel Services and Excel
As always... internet is full of stuff around Excel Services but you might want to check out at least these:
http://technet2.microsoft.com/Office/en-us/library/eea3ace8-0863-429a-b1e8-041254ed2fc41033.mspx?mfr=true -- White papers: Excel Services step-by-step guides
http://msdn2.microsoft.com/en-us/library/bb267252.aspx#Office2007ExcelServicesUnlimited_SharePointLists -- Extending the Excel Services Programmability Framework
http://www.microsoft.com/downloads/details.aspx?FamilyId=2D779CD5-EEB2-43E9-BDFA-641ED89EDB6C&displaylang=en -- Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables
http://blogs.msdn.com/cumgranosalis/ -- Cum Grano Salis
http://blogs.msdn.com/luisbeonservices/ -- LuisBE on Services
http://blogs.msdn.com/excel/ -- The team blog for Microsoft Excel and Excel Services
http://msdn2.microsoft.com/en-us/library/bb758869.aspx -- Chapter 1: An Introduction to Excel Services
http://msdn2.microsoft.com/en-us/library/bb758868.aspx -- Chapter 3: Excel Web Access
Well that ended up being looong post :-) I'll probably still work on this subject so I might get back with follow-ups. I'll probably do some performance tests so that I'll get some indications that how well this does work.
This subject is actually quite complex. I don't consider this case as solved... I just consider this as good kickstart :-) There are still many open questions like: "What's the best place to do translations? " (and many more). Well I don't have answer to that since I think it's not that black and white :-) You may see that some are easy to translate in DB but same are too hard (or database model doesn't support it) and then you need to do that somewhere else.
If you plan to use this code you first need to generalize it since my solution is quite fixed to one certain Excel file. But I think that is actually quite easy task and I'll let you work on that.
Anyways... Happy hacking!