-1

Did open xml sdk support sharepoint online. If not could you please suggest me other ways to create excel file programatically. Many thanks

closed as unclear what you're asking by Benny Skogberg Jun 29 '15 at 5:50

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Please provide more details like how and when are you trying to create an excel? what are the scenarios, the source of data for the excel. Currently the question is vague in my opinion. – Taran Goel Jun 29 '15 at 4:54
2

OpenXml is not supported in office 365, please look at Tim's response here. There are couple of workarounds to achieve the desired functionality.

1. Sandbox Solution

  • Export the list contents into a Data Table
  • Use JavaScript to convert DataTable into Excel

2. Client side solution


  • Use Jquery like SpServices to call the List and fetch the items
  • Iterate the items and add them to excel using javascript.

The below code will help you create an excel file from a HTML table, it can used with a dt as well with slight modifications. I used this solution and it worked real great, thanks to the person who wrote it (I'll update the blog name once I can find it)

function createExcel()
        {
                 var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
                 var textRange; var j=0;
              tab = document.getElementById('documentListtable'); // id of table


              for(j = 0 ; j < tab.rows.length ; j++) 
              {     
                    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
                    //tab_text=tab_text+"</tr>";
              }

              tab_text=tab_text+"</table>";
              tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
              tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
                          tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

                   var ua = window.navigator.userAgent;
                  var msie = ua.indexOf("MSIE "); 

                     if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
                        {
                               txtArea1.document.open("txt/html","replace");
                               txtArea1.document.write(tab_text);
                               txtArea1.document.close();
                               txtArea1.focus(); 
                                sa=txtArea1.document.execCommand("SaveAs",true,"Claim Lines.xls");
                              }  
                      else                 //other browser not tested on IE 11
                          sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  


                          return (sa);
                                }

This Code will help you fetch items of the list at client side

function FetchListItems() {
var fieldsToRead = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Employee_x0020_Id' /><FieldRef Name='Frequency' /><FieldRef Name='Paid_x0020_Amount' /><FieldRef Name='Export_x0020_Date' /><FieldRef Name='Claim_x0020_ID_x003a_NoEditClaim' /></ViewFields>";

$().SPServices({ 
                operation:"GetListItems", 
                async: false, 
                CAMLQuery: "<Query><Where><And><And><Eq><FieldRef Name='Claims_x0020_Header_x003a_NoEdit0'/><Value Type='Lookup'>"+ thisClaimStatus+ "</Value></Eq><Eq><FieldRef Name='Claims_x0020_Header_x003a_NoEdit2' /><Value Type='Lookup'>"+thisPayRoll+"</Value></Eq></And><Eq><FieldRef Name='Re_x002d_Export' /><Value Type='Choice'>Y</Value></Eq></And></Where></Query>",
                listName: claimDetailsList,
                CAMLViewFields: fieldsToRead, 
                completefunc: addDataToTable // this function adds data to Html table from the returned XML.
                });
createExcel();


}

Add SpServices XML response into HTML table

function addDataToTable(xData, Status) {
            var index = 0;
            $documentListtable = $("#documentListtable");
            //Navigate through the XML
            $(xData.responseXML).find("z\\:row, row").each(function () {

                //Get the values to a local variable

                var EmployeeName = $(this).attr("ows_Title");
                var EmployeeID = $(this).attr("ows_Employee_x0020_Id");
                var Frequency= $(this).attr("ows_Frequency");
                var PaidAmount = $(this).attr("ows_Paid_x0020_Amount");
                var ExportDate = $(this).attr("ows_Export_x0020_Date").substring(0,10);
                var claimType = $(this).attr("ows_Claim_x0020_ID_x003a_NoEditClaim").split('#')[1];
                                //Create clone of the table row
                var $row = $("#templates").find(".row-template").clone();

                //Add values to the column based on the css class
                $row.find(".EmployeeName").html(EmployeeName);
                $row.find(".EmployeeID").html(EmployeeID);
                $row.find(".Frequency").html(Frequency);
                $row.find(".PaidAmount").html(PaidAmount);
                $row.find(".ExportDate").html(ExportDate);
                $row.find(".ClaimType").html(claimType);


                //add the row to table
                $documentListtable.append($row);
            });
  • Many thanks. the answer is very useful. Do you have any suggestion to upload the created excel file back to SharePoint. – fazil Jun 29 '15 at 8:00
  • you can use the "AddAttachment" function of SpServices. You are using Sandbox or Client Side solution? – Taran Goel Jun 29 '15 at 8:07
  • Thanks for the prompt response. Let me give a try on "Addattachment" but what i am trying to do is. create excel file using createExcel() function and Fetchlistitems() and add data to table and then export the created file directly to SharePoint. Instead of downloading file locally and upload it back. Create and upload directly in SharePoint. – fazil Jun 29 '15 at 8:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.