Create custom template for Exporting a list to Excel? RRS feed

  • Question

  • I have noticed when you are in the Datasheet View of a List, and if you choose "Query List with Excel" from the task pane, that when the list opens in Excel is it in a formatted worksheet (alternating colors/rows of light blue and dark blue). Yuck!

    Anyway, I want to be able to Export the data to an Excel worksheet in a format of my choosing. Our team will want a minimalist approach that is setup for proper printing with defined page breaks, etc. So, I would like to know how I can develop that kind of export template. Thanks!

    Thursday, April 7, 2011 6:47 PM


  • Out of the box, you can't change the format of "Export to Spreadsheet".

    However, what I've done is do an export to spreadsheet and then format the workbook how you need it to look. Save the workbook and just have the users open the workbook and refresh the data when they need it (or set it to automatically refresh when opened by editing the connection properties. 

    In order to preserve the formatting of your workbook, you will need to edit the External Data Properties (go to Data > Properties) and uncheck "Preserve column sort/filter/layout" so that the layout will not reset each time you refresh the data.

    Hope that helps.

    Planet Technologies || SharePoint Task Force
    • Marked as answer by Peng Lei Friday, April 15, 2011 3:11 AM
    Friday, April 8, 2011 5:40 AM