How to export a list from SharePoint to Excel with header / footer / page orientation?
We would like to export a custom SharePoint list view to Excel based on repeatability and with little formatting.
I made an .iqy file using Actions / Export to Spreadsheet in the View menu and then uploaded that .iqy file back to SharePoint. When users click on it, it outputs the data to Excel, but without headers, each page repeats, with the columns spilling over to the second page (widthwise), etc.
The .iqy file appears to be a simple text file that can be opened in Notepad, so maybe there is a way to pass parameters to Excel through that to do the trick? Somebody knows?
Alternatively, I also saved one of the result tables from opening the .iqy file, applied some formatting to it, and then loaded it back into SharePoint. I set it up to refresh the data connection on open and I think it works fine except for two frustrations.
First, by default Excel says it has blocked data connections and I need to manually enable them. Is there a way to prevent this from happening without having to edit the Trust Center settings on each computer that opens this table?
Second, when I click on a spreadsheet in SharePoint, we are asked if we want to open the document for editing or reading. Ideally, I would like to just give the user the option to open or save the document (and of course not save it back to the server as I expect Edit will do).
Does anyone have any wise advice for me that will make the first try (.iqy file) or the second try (formatted table) work successfully? Or maybe you have other, better ideas?
Thank,
Regards, David W
source to share
Excel Web Query (iqy) just doesn't work. Its sole purpose is to define a "web request" to open Excel.
First disappointment, I'm not sure, but digitally signing a workbook can avoid requesting a blocked data connection. Also, if I remember correctly, there is a Global Policy setting for this, so if you are part of a domain, you can change this setting when the user logs on to the domain.
Second disappointment. There isn't much you can do here.
Offers, all kinds. We will need to understand your requirements and limitations. But I suggest you create a book dynamically using XMLSS. You have complete control over formatting, layout, and almost everything else, without having to install Excel on a server. The serviced workbook must then be saved locally and cannot be saved to the server.
See XML Spreadsheet link 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 . ASP search gives more results. Note that this is easy to translate / port to ASP.NET, so don't let ASP throw you.
I recommend starting by 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 towards the end because there is an example of creating a book and setting the page orientation. In addition, there is an example on how to create a template.
source to share