We’ve a customer who wants Excel Workbooks in one of their libraries to always open on a specific worksheet. Normally, Excel opens from SharePoint showing whatever the last selected tab was, but they’d like theirs to always open on the first worksheet.
I was curious, so I set up a document library with a template XSLX file. In it, the 4th worksheet was the active one when I saved the workbook as the template.
I created a new document in the library with that template. When Excel opened, it showed me the 4th tab, as expected. I saved the document, and downloaded a copy. I reopened the document from SharePoint, selected the first tab, saved the document and downloaded a second copy.
So, now I have 2 XLSX file for the same workbook, but with different tabs selected. I changed their extensions to .zip, and unzipped them. Next, I ran WinMerge on the folders they unzipped to to see what the differences were.
There weren’t many really – most were related to ‘last saved time’ and things like that. There were three XML files in the archive that seemed relevant – 2 for the worksheets (the one that was selected and the one that is now), and the Workbook xml.
Here are the differences for one of the WorkSheets:
Yup, the difference is the tabSelected=1 attribute. If it there, that’s the selected tab.
Or is it? The WorkBook xml also contains a difference:
This is a little more complicated – activeTab seems to be the index number into the Sheets node, if you treat it as a zero-based array. And if the first tab is selected, there doesn’t seem to be an activeTab element at all. Still, not too bad, I’m sure I could work with that.
So, how would this help? Well, if the customer is really keen that the first tab is always selected, then we could write an event receiver that captures a document uploaded to their library and then:
- Checks it really is an Excel 2007 file
- If it is, opens it up
- Edits the XML we’ve just seen
- Resaves the file.
Pretty straight forward, really.