We would like to export a view of a custom SharePoint list to Excel on a repeatable basis, and with some minor formatting.
I have made a .iqy file by using Actions/Export to Spreadsheet from the view menu, and then uploaded this .iqy file back into SharePoint. When users click on it, it does bring up the data in Excel – but without the headings repeated each page, with the columns spilling onto a second page (width-wise), etc.
The .iqy file seems to be a plain text file which can be opened in Notepad so perhaps there is a way of passing parameters to Excel through this which would do the trick? Does someone know?
Alternatively, I also saved one of the resultant spreadsheets from opening the .iqy file, applied some formatting to it, and then uploaded that back into SharePoint. I set it to refresh the data connection on opening and I think this is working ok except there are two frustrations.
Firstly, by default, Excel says it has blocked data connections and I need to manually enable them. Is there a way to prevent this short of editing the trust centre settings on each computer that will open this spreadsheet?
Secondly, when I click on the spreadsheet in SharePoint we are asked if we wish to open the document for editing or read only. Ideally, I'd like to just give the user the opportunity to open or save the document (and certainly not to save it back on to the server, as I expect Edit would do.)
Does anyone have any sage advice for me that would make either the first attempt (.iqy file) or the second attempt (formatted spreadsheet) work successfully? Or maybe you have other, better ideas?
Thank you,
Regards,
David W
Best Answer
An Excel Web Query (iqy) is just not going to work. Its only purpose is to define a "web query" for Excel to open.
First frustration, I am not positive, but digitally signing the workbook may avoid the blocked data connection prompt. Also, if I remember correctly, there is a Global Policy setting for this, so if you are part of a domain, you can have this setting changed when the user logs into the domain.
Second frustration. Not much you can do here.
Suggestions, all kinds. We would need to understand your requirements and constraints. But, I suggest you dynamically create the workbook using XMLSS. You have full control over formatting, layout, and nearly everything else, without the need to have Excel installed on the server. The served workbook must then be saved locally and cannot be saved back to server.
See XML Spreadsheet Reference at http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
Here is a search for "visual basic asp xml workbooks site:microsoft.com", http://www.bing.com/search?q=visual+basic+asp+xml+workbooks+site%3Amicrosoft.com. Search for ASP generates more results. Note it is easy to translate/migrate to ASP.NET so do not let the ASP throw you.
I recommend starting with Using Visual Basic and ASP with XML to Generate Excel 2003 Workbooks at http://msdn.microsoft.com/en-us/library/aa203722(office.11).aspx, especially near the end of the article because there is an example on creating a workbook and setting the page print orientation. Moreover, there is an example on how to create a template.