1

I have a requirement to export my list data directly to document library as new Excel document.

I have used the following code to export to excel

function getLists() {
   context = new SP.ClientContext();
   web = context.get_web();
   lists = web.get_lists();
   context.load(lists,"Include(Id, Title, Hidden, ItemCount, Views)");
   context.executeQueryAsync(onSuccess, onFail);
   return false;
}

function onSuccess() {
   var listsEnum = lists.getEnumerator();
   while (listsEnum.moveNext()) {
      var list = listsEnum.get_current();

if (list.get_title() == "Test") {
    var title = list.get_title();
    var viewCollection = list.get_views();
    var viewInfo = 'Tasks list current views: \n\n';
    var viewEnumerator = viewCollection.getEnumerator();
    while (viewEnumerator.moveNext()) {
        var view = viewEnumerator.get_current();
        if (view.get_title() == "All Items") {
            var viewGUID = view.get_id();
            //Add the export link

            document.getElementById("lnkExport").setAttribute('href',   "../../_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=" + list.get_id().toString() + "&View=" + viewGUID + "&CacheControl");

             }
         }
      }
   }
}

On success of this it returns me the owssvr.dll file which directly opens in Excel. But i don't want to download that file rather i want to directly upload that file into my Document Library as new Excel document.

If any one of you have gone through the same requirement then please let me know your thoughts.

Thanks in advance

  • Where you added this code? – Aakash Maurya May 5 '16 at 11:48
  • @AakashMorya currently i am using SharePoint Hosted App. .But any approach will do. – Codec May 5 '16 at 11:53
1

Hey I have got the same requirement to work with and I was able to accomplish this task so here I am sharing my approach.

  • I created a Excel file using SPFile object available in sp.js

                var fileCreation = new SP.FileCreationInformation();
                var d = new Date()
                var utc = d.toJSON().slice(0, 10) + "-" + d.getHours() + "-" + d.getMinutes() + "-" + d.getSeconds();
                var fileName = "EmployeeList" + utc; //gets the file name
                fileName = fileName + ".xls";
                fileCreation.set_url(fileName);
                fileCreation.set_content(new SP.Base64EncodedByteArray());
    
  • Then added the list data into that file using JavaScript code

    fileContent = str;  // where str is your data in byte array
    for (var content = 0; content < fileContent.length; content++) {
            fileCreation.get_content().append(fileContent.charCodeAt(content)); //add the text content to the file.
        }
    
  • And uploaded that file directly into the document library again using JavaScript code

                    //create a new file instance
                    var newFile = targetList.get_rootFolder().get_files().add(fileCreation);
                    context.load(newFile);
                    context.executeQueryAsync(onFileCreationSuccess, onFileCreationFail);
    
        function onFileCreationSuccess() {
        alert("File created and uploaded in document library");
        }
        function onFileCreationFail() {
        alert("Error in creating file");
        }
    

Here is the full code:

        function createAndUploadFile()
        {

        var web = context.get_web(); //gets the web object
        var list = web.get_lists(); //gets the list collection object
        var targetList;
        var fileCreation;
        var fileContent;
        targetList = list.getByTitle("Documents");
        fileCreation = new SP.FileCreationInformation();
        var d = new Date()
        var utc = d.toJSON().slice(0, 10) + "-" + d.getHours() + "-" + d.getMinutes() + "-" + d.getSeconds();
        var fileName = "EmployeeList" + utc; //gets the file name
        fileName = fileName + ".xls";
        fileCreation.set_url(fileName);
        fileCreation.set_content(new SP.Base64EncodedByteArray());
        fileContent = str;
        //gets the file contents.
        for (var content = 0; content < fileContent.length; content++) {
        fileCreation.get_content().append(fileContent.charCodeAt(content)); //add the text content to the file.
        }

        //create a new file instance
        var newFile = targetList.get_rootFolder().get_files().add(fileCreation);
        context.load(newFile);
        context.executeQueryAsync(onFileCreationSuccess, onFileCreationFail);

        }

        function onFileCreationSuccess()
        {
        alert("File created and uploaded in document library");
        }

        function onFileCreationFail()
        {
        alert("Error in creating file");
        }

You can call this function on page load or wherever you want.

This worked for me and excel document got uploaded in document library without being downloaded.

Let me know if you need more help.

|improve this answer|||||
  • where should i keep this code? – Codec Jun 2 '16 at 6:48
  • @Codec I updated my answer – Aakash Maurya Jun 2 '16 at 6:53
0

I had a similar requirement but decided to generate the Excel and later Word file using javascript. I don't have any examples for generating the excel file. What I do remember is that the basic plugins I used were transforming HTML into Excel which made Excel think that the file was a webpage (received html extension when saved without changing the file extension). I am pretty sure that there is a solution for this... My requirements changed and I moved to generating a word file. I am using the docxtemplater which works great.

You can create a template for your export and fill the template (with repeating section) with your list data. The output that you receive can then be used to upload to another library.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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