Export to excel:- headers should come below the Data

Export to excel:- headers should come below the Data

Posted by: Shalini89garg on 14 March 2018, 12:44 am EST

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 14 March 2018, 12:44 am EST

  While Exporting Data in to Excel , i want to show header below the data.
  
  Currently its coming on top of the excel.
  
  How can i achieve this.
  
  Can anyone please help on this.ExportWithHeaders.zip

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 15 March 2018, 1:52 am EST

  Hello,

  There is not direct way to achieve what you are looking for. You would need to Copy the Header Row and paste it at the bottom empty row when you export to Excel. Later you can remove the bottom once the export is complete.

  Thanks,
  
  Deepak Sharma

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy

  Posted 15 March 2018, 2:55 pm EST

  could you please provide any sample example around it. it will be very helpful.

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 19 March 2018, 2:52 am EST

  Hello,

  You can use the code similar to below:

  
    $("#btnExport").bind("click", function (e) {
                  var sheet = spread.sheets[0];
                  if (sheet) {
                      var count = sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport);
                      var colCount = sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport);
  
                      sheet.addRows(count, 1);
  
                      var fromRange = [new GC.Spread.Sheets.Range(0, 0, 1, colCount - 1, GC.Spread.Sheets.SheetArea.colHeader)];
                      var toRanges = [new GC.Spread.Sheets.Range(count+1, 0, 1, colCount-1, GC.Spread.Sheets.SheetArea.viewport)];
                      spread.commandManager().execute({ cmd: "clipboardPaste", sheetName: "Sheet1", fromSheet: sheet, fromRanges: fromRange, pastedRanges: toRanges, isCutting: false, clipboardText: "", pasteOption: GC.Spread.Sheets.ClipboardPasteOptions.all });               
  
                      var json = spread.toJSON();
                      var fileName = "ExportDownHeaders.xlsx";
                      excelIo.save(json, function (blob) {
                          saveAs(blob, fileName);
                      }, function (e) {
                          // process error
                          console.log(e);
                      });
  
                      sheet.deleteRows(count, 1);
                  }
                 
              });
  
  

  Thanks,
  
  Deepak Sharma

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy

  Posted 19 March 2018, 11:04 pm EST

  hello deepak,

  thanks for this sample code, i tried this code, pasting of rows is working but pasting of column header is not working. i want to remove header from the top and paste in the bottom most line

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 21 March 2018, 2:34 am EST

  Hi Shalini,

  You can get the value from Headers and set it in new added row. Then after export you can remove the row. Here is the code to do that:

  
   $("#btnExport").bind("click", function (e) {
                  var sheet = spread.sheets[0];
                  if (sheet) {
                      var count = sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport);
                      var colCount = sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport);
                      sheet.addRows(count, 1);
                      var headerValues = [];
  
                      for (var i = 0; i <= colCount; i++) {
                          var val = sheet.getValue(0, i, GC.Spread.Sheets.SheetArea.colHeader);
                          headerValues.push(val);
                      }
                      for (var i = 0; i <= headerValues.length; i++) {
                          sheet.setValue(count, i, headerValues[i], GC.Spread.Sheets.SheetArea.viewport);
                      }
                     
                      var fileName = document.getElementById("exportFileName").value;
                      var json = spread.toJSON({ includeBindingSource: true });
                      if (fileName.substr(-5, 5) !== '.xlsx') {
                          fileName += '.xlsx';
                      }
                      excelIO.save(json, function (blob) {
                          saveAs(blob, fileName);
                      }, function (e) {
                          console.log(e);
                      });
  
                      sheet.deleteRows(count, 1);
                  }
  
              });
  
  

  Please refer to the attached sample.

  Thanks,
  
  Deepak SharmaSpreadJSCopyHeaderRowExport.zip