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...

  1. ...to use 2 different languages (Finnish and English)
  2. ...have static texts in Excel that needs to be translatable
  3. ...use pivottables
  4. ...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
  5. ...filter the data from UI
    • Translation for that: We need to pass parameters to the database query
  6. ...have easily maintainable system (=minimize the amount of Excels)
  7. ...flexible solution. So if we later want more complex scenarios this needs to be supported in your solution.
  8. ...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:

  1. Create database for the example
  2. Create template Excel file
    • This is then used to create the "static copy instance"
  3. Create SharePoint structure for the demo
  4. 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
    • en-US:
      • default.aspx
      • MyEWA.aspx
    • fi-FI
      • default.aspx
      • MunEWA.aspx

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:

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;
          //}
          
// Do we calculations that we need to refresh?
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull, out status); byte[] wb = es.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out status); es.CloseWorkbook(sessionId, out status); // Let's write our cache file to the disk: // (you need to have write rights for that location!) File.WriteAllBytes(xlFile, wb); } } // TODO: set all necessary parameters to EWA: ewa.ShowWorkbookParameters = false; ewa.WorkbookUri = Uri.UriSchemeFile + Uri.SchemeDelimiter + xlFile; ewa.ToolbarStyle = ToolbarVisibilityStyle.None; ewa.ChromeType = PartChromeType.None; // TODO: you can also get settings from various places: if (string.IsNullOrEmpty(this.Page.Request["NamedItem"]) == false) { ewa.VisibleItem = this.Page.Request["NamedItem"].ToString(); } if (string.IsNullOrEmpty(this.Page.Request["Rows"]) == false) { ewa.RowsToDisplay = Convert.ToInt32(this.Page.Request["Rows"]); } if (string.IsNullOrEmpty(this.Page.Request["Columns"]) == false) { ewa.ColumnsToDisplay = Convert.ToInt32(this.Page.Request["Columns"]); } } private string GetSnapshotFile() { byte[] wb; string snapshotCacheFile = xlFile + ".snapshot.xlsx"; if (File.Exists(snapshotCacheFile) == false) { // No cache file yet.. so let's create it: using (ExcelService es = new ExcelService()) { Status[] status; string sessionId = es.OpenWorkbook(xlFile, culture, culture, out status); // Note! If we take the 'normal' excel file then we return formulas as well // => we really want to use the snapshot file! wb = es.GetWorkbook(sessionId, WorkbookType.PublishedItemsSnapshot, out status); es.CloseWorkbook(sessionId, out status); } File.WriteAllBytes(snapshotCacheFile, wb); } return snapshotCacheFile; } private string BuildExportScript(string exportFormat, string file) { string url = this.Page.Request.Url.ToString(); if (url.IndexOf('?') == -1) { url += "?"; } else { url += "&"; } url += "ExportFormat=" + exportFormat + "&File=" + file; return "window.frames['MyEWA'].location='" + url + "'; return false;"; } private void ExportSnapshotAsExcel() { byte[] fileData = File.ReadAllBytes(this.GetSnapshotFile()); // TODO: set meaningful name for the file (end user sees it File Download dialog) string filename = "ExcelData.xlsx"; this.Page.Response.ClearHeaders(); this.Page.Response.ClearContent(); this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename); this.Page.Response.AddHeader("LAST-MODIFIED", DateTime.Now.ToString("r")); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.OutputStream.Write(fileData, 0, fileData.Length); this.Page.Response.OutputStream.Flush(); this.Page.Response.End(); } private string GetSnapshotFileCSV() { StringBuilder sb = new StringBuilder(2048); string snapshotCacheFile = xlFile + ".snapshot.csv"; if (File.Exists(snapshotCacheFile) == false) { // No cache file yet.. so let's create it: using (ExcelService es = new ExcelService()) { Status[] status; string sessionId = es.OpenWorkbook (this.GetSnapshotFile(), culture, culture, out status); RangeCoordinates range = new RangeCoordinates(); range.Column = 0; range.Row = 0; // TODO: get real range according to the data (not fixed :-) // You could store the value when you fill the Excel with data... range.Width = 5; range.Height = 300; object[] data = es.GetRange(sessionId, "Display", range, false, out status); foreach (object row in data) { if (row is object[]) { object[] columns = row as object[]; foreach (object columnData in columns) { if (columnData != null) { sb.Append(columnData); } sb.Append(";"); } sb.Append(Environment.NewLine); } } es.CloseWorkbook(sessionId, out status); } File.WriteAllBytes(snapshotCacheFile, Encoding.Convert(Encoding.UTF7, Encoding.GetEncoding("windows-1250"), Encoding.UTF7.GetBytes(sb.ToString()))); } return snapshotCacheFile; } private void ExportSnapshotAsCSV() { byte[] fileData = File.ReadAllBytes(this.GetSnapshotFileCSV()); // TODO: set meaningful name for the file (end user sees it) string filename = "CSVData.csv"; this.Page.Response.Clear(); this.Page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename); this.Page.Response.AddHeader("LAST-MODIFIED", DateTime.Now.ToString("r")); this.Page.Response.ContentType = "text/plain"; this.Page.Response.OutputStream.Write(fileData, 0, fileData.Length); this.Page.Response.Flush(); this.Page.Response.End(); } protected override void Render(HtmlTextWriter writer) { this.RenderChildren(writer); // Let's add our export iframe: writer.Write("<iframe name=\"MyEWA\" src=\"about:blank\" width=\"1\" height=\"1\" />"); } } }

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
  • 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

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

Comments (3)

  1. Anpassung Liam Cleary MOSS2007 โ€“ Link Lists and the Content Query Web part Adding "OpenInNewWindow" option

  2. uday says:

    Can you please tell me how I should proceed if I just want to display a chart in the EWA from an excel file? I have tried to provision the EWA on a page through code that works fine on my dev system, but fails to locate the dll Microsoft.Office.Excel.WebUI on other systems that i use to test the code even though they have SharePoint installed on them and similar settings for excel services.

Skip to main content