将电子表格数据显示为 HTML 表格

更新时间：2022-06-23 09:59:10

这是我用来获取所有电子表格的一段旧代码.让它有机会运行，因为一开始加载所有电子表格需要一段时间.它还允许您编辑所有工作表.我猜您将能够弄清楚如何使用此示例来确定如何解决您的问题.

Here's an old piece of code that I used to get all of my Spreadsheets. Give it a chance to run as it takes a while to load all of the spreadsheets at first. It also allows you to edit all of the sheets as well. I'm guessing that you'll be able to figure out how you can use this example to figure how to solve your problem.

htmlss.hmtl:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {
      $('#msg').html('Please wait. Collecting a list of all Spreadsheets...');
      google.script.run
         .withSuccessHandler(updateSelect)
         .getAllSpreadSheets();
    });

    function updateSS(i,j)
    {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      var ssId=$('#sel1').val();
      var name=$('#sel2').val();
      var updObj={rowIndex:i,colIndex:j,value:value,id:ssId,name:name};
      $(str).css('background-color','#ffff00');
      google.script.run
         .withSuccessHandler(updateSheet)
         .updateSpreadsheet(updObj);
    }

    function updateSheet(data)
    {
      //$('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }

    function updateSelect(dtO)
    {
      $('#sel1').css('background','#ffffff');
      $('#sel2').css('background','#ffffff');
      $('#msg').html('Spreadsheet List has been updated.  Now select a SpreadSheet to display');
      var select = document.getElementById(dtO.type);
      var vA=dtO.array;
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i][0],vA[i][vA[i].length-1]);
      }
    }

    function loadSelectSheet()
    {
       var shId=$('#sel1').val();
       var name=$('#sel1').text();
       $('#sel1').css('background','#ffff00');
       document.getElementById('ssname').innerHTML="";
       var ssO={name:name ,id:shId}
       google.script.run
           .withSuccessHandler(updateSelect)
           .getAllSheets(ssO);

    }

    function displaySelectedSheet()
    {
       var ssId=$('#sel1').val();
       var name=$('#sel2').val();
       $('#sel2').css('background','#ffff00');
       document.getElementById('shname').innerHTML="";
       var ssO={id:ssId,name:name};
       google.script.run
           .withSuccessHandler(displaySheet)
           .htmlSpreadsheet(ssO);
    }

    function displaySheet(opO)
    {
        $('#sel2').css('background','#ffffff');
        document.getElementById('ssname').innerHTML=opO.name;
        document.getElementById('shname').innerHTML=opO.shname;
        document.getElementById('sss').innerHTML=opO.hl;
    }

    console.log('My Code');
    </script>
    <style>
      th{text-align:left}
    </style>
  </head>
  <body>
  <div id="msg"></div><br />
  <br /><select id="sel1" onChange="loadSelectSheet();"></select>
  <br /><select id="sel2" onChange="displaySelectedSheet();"></select>
  <div id="ssname"></div>
  <div id="shname"></div>
  <div id="sss"></div>
  </body>
</html>

代码.gs:

function htmlSpreadsheet(ssO) {
  var br='<br />';
  var s='';
  var hdrRows=1;
  var ss=SpreadsheetApp.openById(ssO.id);
  var sht=ss.getSheetByName(ssO.name);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  s+='<table>';
  for(var i=0;i<rngA.length;i++)
  {
    s+='<tr>';
    for(var j=0;j<rngA[i].length;j++)
    {
      if(i<hdrRows)
      {
        s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      } 
      else
      {
        s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      }
    }
    s+='</tr>';
  }
  s+='</table>';
  s+='</body></html>';
  var namehl=Utilities.formatString('<h1>%s</h1>', ss.getName());
  var shnamehl=Utilities.formatString('<h2>%s</h2>', sht.getName());
  var opO={hl:s,name:namehl,shname:shnamehl};
  return opO;
}

function updateSpreadsheet(updObj) {
  var i=updObj.rowIndex;
  var j=updObj.colIndex;
  var value=updObj.value;
  var ss=SpreadsheetApp.openById(updObj.id);
  var sht=ss.getSheetByName(updObj.name);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  rngA[i][j]=value;
  rng.setValues(rngA);
  var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
  return data;
}

function doGet() {
  var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
  return userInterface.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function getAllSpreadSheets() {
  var files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS); 
  var s = '';
  var vA=[['Select Spreadsheet',0]];
  while(files.hasNext())
  {
    var file = files.next();
    var fileName=file.getName();
    var fileId=file.getId();
    vA.push([fileName,fileId]);
  }
  //return vA;
  return {array:vA,type:'sel1'};
}

//working on this function right now 2017/11/08
function getAllSheets(ssO) {
  var ss=SpreadsheetApp.openById(ssO.id);
  var allSheets=ss.getSheets();
  var vA=[['Select Sheet']];
  for(var i=0;i<allSheets.length;i++)
  {
    var name=allSheets[i].getName();
    vA.push([name]);
  }
  return {array:vA,type:'sel2'};
}

上一篇 : ：使用ms字中的VBA宏访问sharepoint在线列表数据时拒绝访问下一篇 : JMeter记录Siebel CRM系统

将电子表格数据显示为 HTML 表格

相关阅读

技术问答最新文章