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!
We want...
- ...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
Phases:
- 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 ProductModeltranslation 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
- en-US:
- default.aspx
- MyEWA.aspx
- fi-FI
- default.aspx
- MunEWA.aspx
- en-US:
Obviously en-US is site that has regional settings (Site settings->Site administration: Regional settings) set toEnglish 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 | using System; using System.Runtime.InteropServices; using System.Web.UI; using System.Web.UI.WebControls.WebParts; using System.Xml.Serialization; using Microsoft.SharePoint; using Microsoft.Office.Excel.WebUI; using System.IO; using System.Threading; using Microsoft.Office.Excel.Server.WebServices; using System.Web.UI.WebControls; using System.Web; using System.Resources; using System.Data; using System.Data.SqlClient; using System.Text; namespace MyEWA { [Guid("97db47dc-7f23-4975-b246-26c237e74243")] public class MyEWA : System.Web.UI.WebControls.WebParts.WebPart { ExcelWebRenderer ewa; DropDownList productsList; Button exportSnapshot; Button exportSnapshotCSV; string xlFileWithoutPath; string xlFile; string xlPath; string xlStorageDirectory; string xlTemplateFile; string culture; public MyEWA() { this.ExportMode = WebPartExportMode.All; } private string ConnectionString { get { // ERROR: don't ever store connection string in your class! return "..."; } } // TODO: refactor this into separate class! private DataTable GetExcelData(string product, string culture) { using (SqlConnection conn = new SqlConnection(this.ConnectionString)) { // If you have translations inside your database you can pass on the culture to the db: SqlCommand cmd = new SqlCommand( "SELECT Production.vProductAndDescription.ProductModel, " + "Production.vProductAndDescription.Description AS Product, " + "Production.Product.ListPrice AS Price " + "FROM Production.vProductAndDescription INNER JOIN " + "Production.Product ON " + "Production.vProductAndDescription.ProductID = Production.Product.ProductID AND " + "Production.vProductAndDescription.ProductModel LIKE @Product+'%' AND " + "Production.vProductAndDescription.CultureID LIKE @Culture+'%'", conn); cmd.Parameters.AddWithValue("@Product", product); cmd.Parameters.AddWithValue("@Culture", culture.Substring(0,2)); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds.Tables[0]; } } protected override void CreateChildControls() { // Get default locale from current site: // (Note: this can be changed from site settings) culture = SPContext.Current.Web.Locale.ToString(); if (string.IsNullOrEmpty(this.Page.Request["Language"]) == false) { // I'll give way to change the language on the fly: culture = this.Page.Request["Language"]; Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(culture); Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(culture); } Controls.Clear(); base.CreateChildControls(); this.ChildControlsCreated = true; Label productLabel = new Label(); productLabel.Text = MyEWAResources.ProductList_Select; this.Controls.Add(productLabel); productsList = new DropDownList(); productsList.ID = "productsList"; // TODO: get your product filter list using SQL statements // and remember to store that in cache => performance. // Something like this: // this.Page.Cache.Add("ProductList", ...); // And of course you can use SqlCacheDependency if you want. // I'll just fill in static filters (I'm lazy!): productsList.Items.Add(new ListItem(MyEWAResources.ProductList_Item1, "Mountain")); productsList.Items.Add(new ListItem(MyEWAResources.ProductList_Item2, "Road")); productsList.Items.Add(new ListItem(MyEWAResources.ProductList_Item3, "HL")); productsList.AutoPostBack = true; this.Controls.Add(productsList); // Create some buttons: exportSnapshot = new Button(); exportSnapshot.Text = MyEWAResources.ExportSnapshot; this.Controls.Add(exportSnapshot); exportSnapshotCSV = new Button(); exportSnapshotCSV.Text = MyEWAResources.ExportSnapshotCSV; this.Controls.Add(exportSnapshotCSV); ewa = new ExcelWebRenderer(); this.Controls.Add(ewa); } protected override void OnLoad(EventArgs e) { this.EnsureChildControls(); base.OnLoad(e); // Get values from UI: string productFilter = productsList.SelectedValue; // TODO: get these values from web part properties!!! // This is the UNC path of the Excel files: xlPath = "//demo1/ExcelServices/xlsx"; // This is folder where static instances will be created (under previous folder) xlStorageDirectory = "Storage/"; // This name of the static Excel file: xlFileWithoutPath = string.Format("MyEWA-{0}-{1}.xlsx", culture, productFilter); // This name of the template Excel file // NOTE: You should store this inside SharePoint!!! xlTemplateFile = xlPath + "MyEWA.xlsx"; xlFile = xlPath + xlStorageDirectory + xlFileWithoutPath; // Let's add Export calls to the client side of the buttons: exportSnapshot.OnClientClick = this.BuildExportScript("xlsx", xlFileWithoutPath); exportSnapshotCSV.OnClientClick = this.BuildExportScript("csv", xlFileWithoutPath); // Do we need to export file? (new .aspx page for this would be good idea!) if (string.IsNullOrEmpty(this.Page.Request["ExportFormat"]) == false && string.IsNullOrEmpty(this.Page.Request["File"]) == false) { // Yes we need to export file // TODO: add sanity checks for the filename xlFile = xlPath + xlStorageDirectory + this.Page.Request["File"].ToString(); string format = this.Page.Request["ExportFormat"].ToString(); switch (format) { case "csv": ExportSnapshotAsCSV(); break; case "xlsx": ExportSnapshotAsExcel(); break; default: throw new Exception( string.Format("Export format '{0}' not supported!", format)); } } // Does the cache file already exists? bool useCachedXlFile = File.Exists(xlFile); if (useCachedXlFile == true) { // Cache file exists but let's compare timestamps: FileInfo src = new FileInfo(xlTemplateFile); FileInfo dst = new FileInfo(xlFile); if (src.LastWriteTimeUtc > dst.LastWriteTimeUtc) { // Template file is newer than cache file! useCachedXlFile = false; } } if (useCachedXlFile == true) { // TODO: add your own custom logic to check that cache is still valid // I.e. check site property bag about 'UpdateExcels' timestamp } if (useCachedXlFile == false) { // We need to create the cache file! // Let's get data to the Excel: DataTable excelData = this.GetExcelData(productFilter, culture); // In my example 'Description' field is actually already translated in the db // so we need to translated only the 'ProductModel' in our data // (this is here just to demonstrate that text can be translated in many // different layers: Excel, Code, DB etc.) if (culture.StartsWith("en", StringComparison.OrdinalIgnoreCase) == false) { // This culture isn't the "original" database culture // so we need to translate "ProductModel" field // TODO: implement real translation foreach (DataRow row in excelData.Rows) { row["ProductModel"] = "Suomeksi - " + row["ProductModel"]; row.AcceptChanges(); } excelData.AcceptChanges(); } using (ExcelService es = new ExcelService()) { Status[] status; string sessionId = es.OpenWorkbook(xlTemplateFile, culture, culture, out status); // Set culture to the parameters: es.SetCellA1(sessionId, "Parameters", "Culture", culture, out status); // TODO: get your fancy data with current information: object[] excelDataArray = new object[excelData.Rows.Count + 1]; object[] excelRowArray = new object[excelData.Columns.Count]; // You might think that you could change the columns of your data with following code: // for (int i = 0; i < excelData.Columns.Count; i++) // { // // Get correct labels for the texts: // excelRowArray[i] = MyEWAResources.ResourceManager.GetString( // "Column_" + excelData.Columns[i].ColumnName); // } // BUT BUT BUT... You cannot change columns since Pivottable won't work after that :-( excelDataArray[0] = excelRowArray; // Fill the data with retrieved values: for (int i = 0; i < excelData.Rows.Count; i++) { excelRowArray = new object[excelData.Columns.Count]; for (int j = 0; j < excelData.Columns.Count; j++) { excelRowArray[j] = excelData.Rows[i][excelData.Columns[j]]; } excelDataArray[i + 1] = excelRowArray; } RangeCoordinates range = new RangeCoordinates(); range.Column = 0; range.Row = 1; range.Width = excelData.Columns.Count; range.Height = excelData.Rows.Count + 1; // Store data into Excel: es.SetRange(sessionId, "Data", range, excelDataArray, out status); // You might think that you could change the Pivottable captions on the fly: // es.SetCellA1(sessionId, "Display", "A5", "My new pivot caption", out status); // BUT BUT BUT.. It doesn't work (=nothing happens) :-( // Let's refresh our datasources (=update pivottable): es.Refresh(sessionId, null, out status); // Following 'block' of code can be used if you want to have // separate sheet in your Excel file for translations: // //bool doneTranslations = false; //range = new RangeCoordinates(); //range.Column = 0; //range.Row = 1; //range.Width = 2; //range.Height = 10; //while (doneTranslations == false) //{ // // So we get all the data from Translations-sheet and then we // // copy the text over to the locations defined in Excel: // object[] rowData = es.GetRange(sessionId, // "Translations", range, false, out status); // for (int i = 0; i < range.Height; i++) // { // if (rowData is object[]) // { // object[] columnData = rowData[i] as object[]; // if (columnData[0] == null) // { // // No more translations // doneTranslations = true; // break; // } // string[] translationRange = Convert.ToString(columnData[0]).Split('!'); // es.SetCellA1(sessionId, translationRange[0], translationRange[1], // Convert.ToString(columnData[1]), out status); // } // } // range.Row += range.Height; //} |
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 :-)
Performance?
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:
http://demo1:1000/fi-FI/Sivut/MunEWA.aspx?ExportFormat=csv&File=MyEWA-fi-FI-Mountain.xlsx&Language=en-US
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
- Downside:
- 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)"
- Downside:
- 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
Final words
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!
J