Convert list data to excel and upload it to document library without downloading

Question

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

Answer 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.

Answer 2

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.