none
SP 2007 (WSS 3.0) Export of WebPart DataView on list fails

    Question

  • We have created a DataView WebPart on a list in Sharepoint 2007 using SharePoint Designer with the Sharepoint List Toolbar (so we have the Actions button, including Export to Spreadsheet).  When we try to Export to a Spreadsheet (Excel 2003) it fails.  We've tried a couple of different scenarios, we get a different error for each.

    If we just create the DataView onto a new aspx page then open the page in IE and try to export the data we get an error box that states and unexpected error has occurred and the data could not be saved.  No spreadsheet is created.

    If we create a public view page in Sharepoint then open the resulting .aspx in Sharepoint Designer, remove the automagically created view and replace with a DataView Webpart, then save and reopen the page in IE, attempting the export results in a spreadsheet being created but only the ID numbers exported.  We then get a message box saying that some hidden or read only fields need data but do not have a default value.

    As the original list is quite complex and has a variety of different data types we created a much simpler list on a separate subsite and repeated these scenarios, with exactly the same outcomes.

    We are using Sharepoint 2007 (WSS 3.0) via Internet Explorer 6.0 on Windows XP SP2 to export the data into Excel 2003 SP3.  This has been replicated on three different PCs.

    A regular View (created in Sharepoint via the Views menu) will export fine.  Unfortunately the end user has a requirement that cannot be fulfilled with a regular view.

    Any suggestions as to how we can resolve this issue?

    Thanks

    Stephen

    Thursday, August 19, 2010 2:15 PM

Answers

  • OK, well it seems IE 8 may not be an option.

    Another possibility is to use Javascript in a CEWP on the same page as your DVWP.  Here is some sample JavaScript code that may work:

    <Script Language="Javascript">

    function isIE() // Function to Determine IE or Not

    {

     return /msie/i.test(navigator.userAgent) && !/opera/i.test(navigator.userAgent);

     

    function exportToExcel() // Function to Export the Table Data to Excel.

    {

    var isIEBrowser = isIE();

    if(isIEBrowser== false)

    {

    alert('Please use Internet Explorer for Excel Export Functionality.');

    return false;

    }

    else

    {

    var strTableID = "detailsTable";  // It's the Table ID of Table in Webpart 

    var detailsTable = document.getElementById(strTableID);

    var objExcel = new ActiveXObject("Excel.Application");

    var objWorkBook = objExcel.Workbooks.Add;

    var objWorkSheet = objWorkBook.Worksheets(1); 

    for (var intRowIndex=0;intRowIndex<detailsTable.rows.length;intRowIndex++)

    {

    for (var intColumnIndex=0;intColumnIndex<detailsTable.rows(intRowIndex).cells.length;intColumnIndex++)

    {

    if(intColumnIndex != 3)

    objWorkSheet.Cells(intRowIndex+1,intColumnIndex+1) = detailsTable.rows(intRowIndex).cells(intColumnIndex).innerText;

    }

    objExcel.Visible = true;

    objExcel.UserControl = true;

    }

    </Script>

    Add this portion to a HTML button at the bottom of the CEWP (after the script above).

    <button onclick="exportToExcel();">Export to Excel File</button>

    The last step is to Set a Table ID for the Data in Dataview webpart and update that in the javascript.

     

    Please LMK if that helps!


    Tom Molskow SharePoint Architect (If I solved the issue please propose my reply as the answer.)
    • Marked as answer by Wayne Fan Friday, August 27, 2010 6:56 AM
    Friday, August 20, 2010 12:21 PM

All replies

  • Hey Stephen,

    First, have you checked the error logs -SharePoint, Event Viewer, and IIS for a more detailed error message?

    Second, here is a great article that takes you step-by-step through the process of creating a C# solution for exporting your DVWP to Excel.  If you would like to try this I can help with the C#.

     http://www.fivenumber.com/export-spgridview-to-excel-spreadsheet-in-sharepoint-2007/

    I hope that helps!


    Tom Molskow SharePoint Architect (If I solved the issue please propose my reply as the answer.)
    Thursday, August 19, 2010 3:16 PM
  • Hey Stephen,

    Also, did you try exporting from the right click menu? 

    Just go to the page, right click over the DVWP, and at the bottom of the right click menu you will see the menu option - Export to Microsoft Excel - choose this - and the process should be automatic from there. 

    This often works much better then the SharePoint export function for DVWP.

     

    I hope that helps!


    Tom Molskow SharePoint Architect (If I solved the issue please propose my reply as the answer.)
    Thursday, August 19, 2010 3:35 PM
  • Tom,

    thank you.  Unfortunately as we're using a hosted service we don't have access to the server logs (I will ask though), this also restricts us as to what we can do (no third party tools &c).

    I tried you suggestion of right clicking on the DVWP but was unable to find an Export to Excel option.

     

    Thanks

    Stephen

    Thursday, August 19, 2010 4:24 PM
  • Hey Stephen,

    I don't know if the Export to Excel capability is availble in all browsers, but it is available in IE 8.0.  What browser are you using?  Can you download and install IE 8?

    If you can only use SPD there are some reports that may be of use to you, you can check them out by choosing Site -> Reports while in SPD.

    I think it would help to reach out to your provider as well, identify the problem and it's symptoms just as you have done here, they may have had similar issues with other customers.

    It is also possible to create a seperate data sheet view, normally data sheet views export to Excel much easier.

    Finally, you said your list was complicated - can you elaborate on the complications - groupings, filters, etc.

     

     


    Tom Molskow SharePoint Architect (If I solved the issue please propose my reply as the answer.)
    Thursday, August 19, 2010 5:54 PM
  • Tom, we're using IE 6.0. It's our corporate standard as one of our major corporate apps will only work with that version of IE, it fails with IE 7 or 8 (even in compatibility mode). I'll see what I can do with our supplier, I'll have to go through one of my colleagues as he has the contacts (this is my second week on Sharepoint). The complications of the list we're working off are mostly around multi-line text fields with the append new text to existing text option turned on. These fields are used for an audit trail. The reason we're having to go this route of using a dvwp rather than a regular view is that when you export a regular view it only exports the most recent entry, the user wants to export all entries which seems to be only possible with a dvwp. Most of the views on the list do have groupings and filters &c but the dvwp does not. Thanks Stephen
    Friday, August 20, 2010 9:28 AM
  • OK, well it seems IE 8 may not be an option.

    Another possibility is to use Javascript in a CEWP on the same page as your DVWP.  Here is some sample JavaScript code that may work:

    <Script Language="Javascript">

    function isIE() // Function to Determine IE or Not

    {

     return /msie/i.test(navigator.userAgent) && !/opera/i.test(navigator.userAgent);

     

    function exportToExcel() // Function to Export the Table Data to Excel.

    {

    var isIEBrowser = isIE();

    if(isIEBrowser== false)

    {

    alert('Please use Internet Explorer for Excel Export Functionality.');

    return false;

    }

    else

    {

    var strTableID = "detailsTable";  // It's the Table ID of Table in Webpart 

    var detailsTable = document.getElementById(strTableID);

    var objExcel = new ActiveXObject("Excel.Application");

    var objWorkBook = objExcel.Workbooks.Add;

    var objWorkSheet = objWorkBook.Worksheets(1); 

    for (var intRowIndex=0;intRowIndex<detailsTable.rows.length;intRowIndex++)

    {

    for (var intColumnIndex=0;intColumnIndex<detailsTable.rows(intRowIndex).cells.length;intColumnIndex++)

    {

    if(intColumnIndex != 3)

    objWorkSheet.Cells(intRowIndex+1,intColumnIndex+1) = detailsTable.rows(intRowIndex).cells(intColumnIndex).innerText;

    }

    objExcel.Visible = true;

    objExcel.UserControl = true;

    }

    </Script>

    Add this portion to a HTML button at the bottom of the CEWP (after the script above).

    <button onclick="exportToExcel();">Export to Excel File</button>

    The last step is to Set a Table ID for the Data in Dataview webpart and update that in the javascript.

     

    Please LMK if that helps!


    Tom Molskow SharePoint Architect (If I solved the issue please propose my reply as the answer.)
    • Marked as answer by Wayne Fan Friday, August 27, 2010 6:56 AM
    Friday, August 20, 2010 12:21 PM
  • Thank you so much for this, Tom! This solution has worked well for me. I am wondering if it is possible to print multiple dataview web parts, located on the same page, by clicking on a single 'Export to Excel' button. For example, in this line of code:

              var strTableID = "detailsTable";

    could I include Table IDs in addition to "detailsTable"? How should the additional code be formatted? Thank you again.

    Monday, October 04, 2010 7:11 PM
  • Hey Bullish35,

    Sorry for the late reply, I'm not sure that can be done, so I will try it in my test environment this afternoon and LYK what I find out.

    Thanks!

    Tom


    Tom Molskow SharePoint Architect - If I solved the issue please propose my reply as the answer - Thanks!
    Tuesday, October 05, 2010 4:24 PM