How to export html table to excel using javascript

Question

My table is in format    lt table id  mytable  gt   lt thead gt     lt tr gt       lt th gt name lt  th gt       lt th gt place lt  th gt     lt  tr gt   lt  thead gt   lt tbody gt   lt tr gt      lt td gt adfas lt  td gt      lt td gt asdfasf lt  td gt   lt  tr gt   lt  tbody gt   lt  table gt    I found the following code online  But it doesn t work if i use  thead  and  tbody   tags  function write to excel          str            var mytable   document getElementsByTagName  table   0       var rowCount   mytable rows length      var colCount   mytable getElementsByTagName  tr   0  getElementsByTagName  td   length       var ExcelApp   new ActiveXObject  Excel Application        var ExcelSheet   new ActiveXObject  Excel Sheet        ExcelSheet Application Visible   true       for  var i   0  i  lt  rowCount  i              for  var j   0  j  lt  colCount  j                  str   mytable getElementsByTagName  tr   i  getElementsByTagName  td   j  innerHTML              ExcelSheet ActiveSheet Cells i   1  j   1  Value   str

User · Answer

I think you can also think of alternative architectures  Sometimes something can be done in another way much more easier  If the producer of HTML file is you  then you can write an HTTP handler to create an Excel document on the server  which is much more easier than in JavaScript  and send a file to the client  If you receive that HTML file from somewhere  like an HTML version of a report   then you still can use a server side language like C  or PHP to create the Excel file still very easily  I mean  you may have other ways too

User · Answer

Check https   github com linways table-to-excel  Its a wrapper for exceljs exceljs to export html tables to xlsx     x000D   x000D  TableToExcel convert document getElementById  simpleTable1     x000D   lt script src  https   cdn jsdelivr net gh linways table-to-excel v1 0 4 dist tableToExcel js  gt  lt  script gt  x000D   lt table id  simpleTable1  data-cols-width  70 15 10  gt  x000D   lt tbody gt  x000D       lt tr gt  x000D           lt td class  header  colspan  5  data-f-sz  25  data-f-color  FFFFAA00  data-a-h  center  data-a-v  middle  data-f-underline  true  gt  x000D              Sample Excel x000D           lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td colspan  5  data-f-italic  true  data-a-h  center  data-f-name  Arial  data-a-v  top  gt  x000D              Italic and horizontal center in Arial x000D           lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt th data-a-text-rotation  90  gt Col 1  number  lt  th gt  x000D           lt th data-a-text-rotation  vertical  gt Col 2 lt  th gt  x000D           lt th data-a-wrap  true  gt Wrapped Text lt  th gt  x000D           lt th data-a-text-rotation  -45  gt Col 4  date  lt  th gt  x000D           lt th data-a-text-rotation  -90  gt Col 5 lt  th gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td rowspan  1  data-t  n  gt 1 lt  td gt  x000D           lt td rowspan  1  data-b-b-s  thick  data-b-l-s  thick  data-b-r-s  thick  gt  x000D              ABC1 x000D           lt  td gt  x000D           lt td rowspan  1  data-f-strike  true  gt Striked Text lt  td gt  x000D           lt td data-t  d  gt 05-20-2018 lt  td gt  x000D           lt td data-t  n  data-num-fmt    0 00  gt 2210 00 lt  td gt  x000D       lt  tr gt  x000D   x000D       lt tr gt  x000D           lt td rowspan  2  data-t  n  gt 2 lt  td gt  x000D           lt td rowspan  2  data-fill-color  FFFF0000  data-f-color  FFFFFFFF  gt  x000D              ABC 2 x000D           lt  td gt  x000D           lt td rowspan  2  data-a-indent  3  gt Merged cell lt  td gt  x000D           lt td data-t  d  gt 05-21-2018 lt  td gt  x000D           lt td data-t  n  data-b-a-s  dashed  data-num-fmt    0 00  gt 230 00 lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td data-t  d  gt 05-22-2018 lt  td gt  x000D   x000D           lt td data-t  n  data-num-fmt    0 00  gt 2493 00 lt  td gt  x000D       lt  tr gt  x000D   x000D       lt tr gt  x000D           lt td colspan  4  align  right  data-f-bold  true  data-a-h  right  data-hyperlink  https   google com  gt  x000D               lt b gt  lt a href  https   google com  gt Hyperlink lt  a gt  lt  b gt  x000D           lt  td gt  x000D           lt td colspan  1  align  right  data-t  n  data-f-bold  true  data-num-fmt    0 00  gt  x000D               lt b gt 4933 00 lt  b gt  x000D           lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td colspan  4  align  right  data-f-bold  true  data-a-rtl  true  gt  x000D                    x000D           lt  td gt  x000D           lt td colspan  1  align  right  data-t  n  data-f-bold  true  data-num-fmt    0 00  gt  x000D               lt b gt 2009 00 lt  b gt  x000D           lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td data-b-a-s  dashed  data-b-a-c  FFFF0000  gt All borders lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td data-t  b  gt true lt  td gt  x000D           lt td data-t  b  gt false lt  td gt  x000D           lt td data-t  b  gt 1 lt  td gt  x000D           lt td data-t  b  gt 0 lt  td gt  x000D           lt td data-error   VALUE   gt Value Error lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td data-b-t-s  thick  data-b-l-s  thick  data-b-b-s  thick  data-b-r-s  thick  data-b-t-c  FF00FF00  data-b-l-c  FF00FF00  data-b-b-c  FF00FF00  data-b-r-c  FF00FF00  gt  x000D              All borders separately x000D           lt  td gt  x000D       lt  tr gt  x000D       lt tr data-exclude  true  gt  x000D           lt td gt Excluded row lt  td gt  x000D           lt td gt Something lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D           lt td gt Included Cell lt  td gt  x000D           lt td data-exclude  true  gt Excluded Cell lt  td gt  x000D           lt td gt Included Cell lt  td gt  x000D       lt  tr gt  x000D   lt  tbody gt  x000D   lt  table gt  x000D   x000D   x000D    This creates valid xlsx on the client side  Also supports some basic styling  Check https   codepen io rohithb pen YdjVbb for a working example

User · Answer

Excel Export Script works on IE7    Firefox and Chrome                                                                function fnExcelReport                      var tab text   lt table border  2px  gt  lt tr bgcolor   87AFC6  gt                 var textRange  var j 0            tab   document getElementById  headerTable       id of table             for j   0   j  lt  tab rows length   j                                      tab text tab text tab rows j  innerHTML   lt  tr gt                      tab text tab text   lt  tr gt                           tab text tab text   lt  table gt              tab text  tab text replace   lt A   gt    gt   lt   A gt  g        remove if u want links in your table           tab text  tab text replace   lt img   gt    gt  gi         remove if u want images in your table                       tab text  tab text replace   lt input   gt    gt   lt   input gt  gi          reomves input params                 var ua   window navigator userAgent                var msie   ua indexOf  MSIE                        if  msie  gt  0      navigator userAgent match  Trident  rv  11              If Internet Explorer                                                  txtArea1 document open  txt html   replace                               txtArea1 document write tab text                              txtArea1 document close                               txtArea1 focus                                 sa txtArea1 document execCommand  SaveAs  true  Say Thanks to Sumit xls                                                    else                   other browser not tested on IE 11                       sa   window open  data application vnd ms-excel     encodeURIComponent tab text                              return  sa                                      Just Create a blank iframe     enter code here          lt iframe id  txtArea1  style  display none  gt  lt  iframe gt       Call this function on           lt button id  btnExport  onclick  fnExcelReport     gt  EXPORT           lt  button gt

User · Answer

Check this out    I just got this working and it seems exactly what you are trying to do as well   2 functions  One to select the table and copy it to the clipboard  and the second writes it to excel en masse  Just call write to excel   and put in your table id  or modify it to take it as an argument      function selectElementContents el            var body   document body  range  sel          if  document createRange  amp  amp  window getSelection                range   document createRange                sel   window getSelection                sel removeAllRanges                try                   range selectNodeContents el                   sel addRange range                 catch  e                    range selectNode el                   sel addRange range                           else if  body createTextRange                range   body createTextRange                range moveToElementText el               range select                      range execCommand  Copy           function write to excel      var tableID    AllItems   selectElementContents  document getElementById tableID      var excel   new ActiveXObject  Excel Application       excel Application Visible   true   var wb excel WorkBooks Add    var ws wb Sheets  Sheet1    ws Cells 1 1  Select  ws Paste  ws DrawingObjects Delete  ws Range  A1   Select  excel Application Visible   true       Heavily influenced from  Select a complete table with Javascript  to be copied to clipboard

User · Answer

I try this with jquery   use this and have fun  D       jQuery printInExcel   function  DivID      var ExcelApp   new ActiveXObject  Excel Application     ExcelApp Workbooks Add   ExcelApp visible   true   var str        var tblcount   0   var trcount   0           DivID     table   each function         this  find  tr   each function       var tdcount   0    this  find  td   each function       str   str     this  text    ExcelApp Cells trcount   1  tdcount   1  Value   str   str       tdcount         trcount        tblcount              use this in your class and call it with   printInExcel your var

User · Answer

I would suggest using a different approach  Add a button on the webpage that will copy the content of the table to the clipboard  with TAB chars between columns and newlines between rows  This way the  paste  function in Excel should work correctly and your web application will also work with many browsers and on many operating systems  linux  mac  mobile  and users will be able to use the data also with other spreadsheets or word processing programs   The only tricky part is to copy to the clipboard because many browsers are security obsessed on this  A solution is to prepare the data already selected in a textarea  and show it to the user in a modal dialog box where you tell the user to copy the text  some will need to type Ctrl-C  others Command-c  others will use a  long touch  or a popup menu    It would be nicer to have a standard copy-to-clipboard function that possibly requests a user confirmation    but this is not the case  unfortunately

User · Answer

The reason the solution you found on the internet is no working is because of the line that starts var colCount  The variable mytable only has two elements being  lt thead gt  and  lt tbody gt   The var colCount line is looking for all the elements within mytable that are  lt tr gt   The best thing you can do is give an id to your  lt thead gt  and  lt tbody gt  and then grab all the values based on that  Say you had  lt thead id  headers  gt      function write headers to excel        str        var myTableHead   document getElementById  headers      var rowCount   myTableHead rows length    var colCount   myTableHead getElementsByTagName  tr   0  getElementsByTagName  th   length    var ExcelApp   new ActiveXObject  Excel Application    var ExcelSheet   new ActiveXObject  Excel Sheet    ExcelSheet Application Visible   true   for var i 0  i lt rowCount  i              for var j 0  j lt colCount  j                              str  myTableHead getElementsByTagName  tr   i  getElementsByTagName  th   j  innerHTML          ExcelSheet ActiveSheet Cells i 1 j 1  Value   str               and then do the same thing for the  lt tbody gt  tag    EDIT  I would also highly recommend using jQuery  It would shorten this up to   function write to excel       var ExcelApp   new ActiveXObject  Excel Application     var ExcelSheet   new ActiveXObject  Excel Sheet     ExcelSheet Application Visible   true         th  td   each function i       ExcelSheet ActiveSheet Cells i 1 i 1  Value   this innerHTML            Now  of course  this is going to give you some formatting issues but you can work out how you want it formatted in Excel    EDIT  To answer your question about how to do this for n number of tables  the jQuery will do this already  To do it in raw Javascript  grab all the tables and then alter the function to be able to pass in the table as a parameter  For instance   var tables   document getElementsByTagName  table    for var i   0  i  lt  tables length  i        write headers to excel tables i      write bodies to excel tables i        Then change the function write headers to excel   to function write headers to excel table   Then change var myTableHead   document getElementById  headers    to var myTableHead   table getElementsByTagName  thead   0    Same with your write bodies to excel   or however you want to set that up

User · Answer

function XLExport             try               var i              var j              var mycell              var tableID    tblInnerHTML                var objXL   new ActiveXObject  Excel Application                var objWB   objXL Workbooks Add                var objWS   objWB ActiveSheet               for  i   0  i  lt  document getElementById   lt    tblAuditReport ClientID   gt    rows length  i                      for  j   0  j  lt  document getElementById   lt    tblAuditReport ClientID   gt    rows i  cells length  j                          mycell   document getElementById   lt    tblAuditReport ClientID   gt    rows i  cells j                       objWS Cells i   1  j   1  Value   mycell innerText                                                 objWS Range  A1    L1   Font Bold   true               objWS Range  A1    Z1   EntireColumn AutoFit                   objWS Range  C1    C1   ColumnWidth   50               objXL Visible   true                     catch  err

User · Answer

This might be a better answer copied from this question  Please try it and give opinion here  Please vote up if found useful  Thank you    lt script type  text javascript  gt  function generate excel tableid      var table  document getElementById tableid     var html   table outerHTML    window open  data application vnd ms-excel base64     base64 encode html       function base64 encode  data         http   kevin vanzonneveld net          original by  Tyler Akins  http   rumkin com           improved by  Bayron Guevara          improved by  Thunder m          improved by  Kevin van Zonneveld  http   kevin vanzonneveld net           bugfixed by  Pellentesque Malesuada          improved by  Kevin van Zonneveld  http   kevin vanzonneveld net           improved by  Rafal Kukawski  http   kukawski pl             example 1  base64 encode  Kevin van Zonneveld               returns 1   S2V2aW4gdmFuIFpvbm5ldmVsZA         mozilla has this native      - but breaks in 2 0 0 12      if  typeof this window  btoa       function             return btoa data           var b64    ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789        var o1  o2  o3  h1  h2  h3  h4  bits  i   0      ac   0      enc           tmp arr          if   data        return data         do      pack three octets into four hexets     o1   data charCodeAt i         o2   data charCodeAt i         o3   data charCodeAt i          bits   o1  lt  lt  16   o2  lt  lt  8   o3       h1   bits  gt  gt  18  amp  0x3f      h2   bits  gt  gt  12  amp  0x3f      h3   bits  gt  gt  6  amp  0x3f      h4   bits  amp  0x3f          use hexets to index into b64  and append result to encoded string     tmp arr ac      b64 charAt h1    b64 charAt h2    b64 charAt h3    b64 charAt h4       while  i  lt  data length      enc   tmp arr join         var r   data length   3     return  r   enc slice 0  r - 3    enc          slice r    3       lt  script gt

User · Answer

Only works in Mozilla  Chrome and Safari    x000D   x000D    function          button   click function         var url    data application vnd ms-excel     encodeURIComponent     tableWrap   html        location href   url     return false          x000D   lt script src  https   cdnjs cloudflare com ajax libs jquery 1 9 0 jquery min js  gt  lt  script gt    lt  script gt   lt button gt click me lt  button gt   lt div id  tableWrap  gt     lt table gt       lt thead gt         lt tr gt           lt th gt A lt  th gt           lt th gt B lt  th gt           lt th gt C lt  th gt         lt  tr gt       lt  thead gt       lt tbody gt         lt tr gt           lt td gt 1 lt  td gt           lt td gt 2 lt  td gt           lt td gt 3 lt  td gt         lt  tr gt         lt tr gt           lt td gt 1 lt  td gt           lt td gt 2 lt  td gt           lt td gt 3 lt  td gt         lt  tr gt         lt tr gt           lt td gt 1 lt  td gt           lt td gt 2 lt  td gt           lt td gt 3 lt  td gt         lt  tr gt         lt tr gt           lt td gt 1 lt  td gt           lt td gt 2 lt  td gt           lt td gt 3 lt  td gt         lt  tr gt       lt  tbody gt     lt  table gt   lt  div gt  x000D   x000D   x000D

[javascript] How to export html table to excel using javascript

Examples related to javascript

Examples related to html