Import issue:-Rename tab name and maintain formula's if sheet tab name exist

Import issue:-Rename tab name and maintain formula's if sheet tab name exist

Posted by: Shalini89garg on 26 February 2018, 1:27 am EST

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 26 February 2018, 1:27 am EST

  Hi,

  Can anyone provide me sample of Import using excel IO.
  
  I have below requirements

  1. I have already 3 tabs in my SPread js project (named test 1, test 2, test 3). If i import a excel file where tab name is test 1 then it should change its name as custom sheet 1 , else its name should remain whatever is their in sheet name

  2. If we have filters applied in imported Excel, it should ignore filters and show all the data.

  3.If we import the excel sheet with Tab name as Number (Example :1 ,2 ), then these tabs should appear only after pre existing 3 tabs (as mentioned in point 1)

  I am using spread js 11.

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 28 February 2018, 2:59 pm EST

  Hi,

  Apologies for the delay in getting back to you.

  1&3 - There’s no direct way to merge the sheets in Spread.Sheets and the imported excel file. What I would suggest you is to add a second spread to your page with its display set to none. Then import the excel file into this second spread and then add the sheets from this second spread to your actual spread. The code would look something like this:

  
  <div id="ss" style="height: 80%; border: 1px solid #ddd;"></div>
  <div id="ss2" style="height :0px;display:none"></div>
  
  window.onload = function () {
      spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
      spread2 = new GC.Spread.Sheets.Workbook(document.getElementById("ss2"));
      excelIO = new GC.Spread.Excel.IO();
  };
  
  function processFileSelected() {    
      var file = this.files[0],
          action = $(this).data("action");
  
      if (!file) return false;
      // clear to make sure change event occures even when same file selected again
      $("#fileSelector").val("");
  
      if (action === "doImport") {
          return ImportFile(file);
      }
  }
  
  function ImportFile(excelFile) {  
  
      excelIO.open(excelFile, function (json) {
          var workbookObj = json;        
          spread2.fromJSON(workbookObj);
          var sheets = spread2.getSheetCount();
          spread.addSheet(3, spread2.sheets[1]);
          spread.addSheet(4, spread2.sheets[2]);
          spread.addSheet(4, spread2.sheets[3]);
          
      }, function (e) {
          console.log(e);
      });
  }
  
  

  2. SpreadJS automatically picks up the filters applied in an imported excel file for better user experience. If you wish to ignore those filters, you’ll have to clear the filters once the excel file has been imported. The code would be something like below:

  var activeSheet = spread.getActiveSheet();
          var rowFilter = activeSheet.rowFilter();
          if (rowFilter) {
              var cols = activeSheet.getColumnCount();
              for (var i = 0; i < cols; i++) {
                  if (rowFilter.isFiltered(i))
                      rowFilter.removeFilterItems(i);
              }
              rowFilter.filter();
          }
  

  Hope it helps

• 

  Shalini89garg

  • Post Options:
  • Link
    Copy

  Posted 28 February 2018, 11:23 pm EST

  Hello
  
  Thanks.

  It helps