javascript - Export Html table to Excel file - getting incorrect format - Stack Overflow

admin管理员组

文章数量:1418400

I'm trying to add a export button to my jsp page which will export the table data to Excel. The function works but my output file doesn't show the correct format. Is there another way to do this Also I need something that will work in IE.

see below... HELP!

  <script Language="javascript">
  function ExportHTMLTableToExcel()
 {
 var thisTable = document.getElementById("myTable").innerHTML;
 window.clipboardData.setData("Text", thisTable);
 var objExcel = new ActiveXObject ("Excel.Application");
 objExcel.visible = true;

 var objWorkbook = objExcel.Workbooks.Add;
 var objWorksheet = objWorkbook.Worksheets(1);
 objWorksheet.Paste;
  }
 </script>

<table id="myTable" class="display">
<thead>
    <tr>
        <th>&nbsp;</th>
        <th> Bar Code </th>
        <th>Origin</th>



    </tr>
</thead>
<tbody>
    <c:forEach items="${summary}" var="summary">
        <tr>
    <td ><c:outvalue="${summary.eventDesc}" /></td>
    <td><a href="AnalysisController?value=${summary.labelNbr}"> <c:out
        value="${summary.labelNbr}" /> </a></td>
    <td><c:outvalue="${summary.origin}" /></td>

        </tr>
    </c:forEach>
</tbody>
 </table>

 <input type="button" onclick="ExportHTMLTableToExcel()"value="Export">

I'm trying to add a export button to my jsp page which will export the table data to Excel. The function works but my output file doesn't show the correct format. Is there another way to do this Also I need something that will work in IE.

see below... HELP!

  <script Language="javascript">
  function ExportHTMLTableToExcel()
 {
 var thisTable = document.getElementById("myTable").innerHTML;
 window.clipboardData.setData("Text", thisTable);
 var objExcel = new ActiveXObject ("Excel.Application");
 objExcel.visible = true;

 var objWorkbook = objExcel.Workbooks.Add;
 var objWorksheet = objWorkbook.Worksheets(1);
 objWorksheet.Paste;
  }
 </script>

<table id="myTable" class="display">
<thead>
    <tr>
        <th>&nbsp;</th>
        <th> Bar Code </th>
        <th>Origin</th>



    </tr>
</thead>
<tbody>
    <c:forEach items="${summary}" var="summary">
        <tr>
    <td ><c:outvalue="${summary.eventDesc}" /></td>
    <td><a href="AnalysisController?value=${summary.labelNbr}"> <c:out
        value="${summary.labelNbr}" /> </a></td>
    <td><c:outvalue="${summary.origin}" /></td>

        </tr>
    </c:forEach>
</tbody>
 </table>

 <input type="button" onclick="ExportHTMLTableToExcel()"value="Export">

• javascript
• jsp
• html-table
• export-to-excel

Share Improve this question Follow edited Sep 18, 2017 at 19:06 Brian Tompsett - 汤莱恩 5,8937272 gold badges6161 silver badges133133 bronze badges asked Mar 20, 2013 at 19:50 FahadFahad 40333 gold badges1313 silver badges3434 bronze badges

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 2

This is my working JavaScript code on IE. You can change the formatting according to your need.

function write_to_excel() 
{
    str="";
    var myTable = document.getElementById('myTable');
    var rows = myTable.getElementsByTagName('tr');
    var rowCount = myTable.rows.length;
    var colCount = myTable.getElementsByTagName("tr")[0].getElementsByTagName("th").length; 

    var ExcelApp = new ActiveXObject("Excel.Application");
    var ExcelWorkbook = ExcelApp.Workbooks.Add();
    var ExcelSheet = ExcelWorkbook.ActiveSheet;//new ActiveXObject("Excel.Sheet"); 
    //ExcelSheet.Application.Visible = true;
    ExcelApp.Visible = true;

    ExcelSheet.Range("A1", "Z1").Font.Bold = true;
    ExcelSheet.Range("A1", "Z1").Font.ColorIndex = 23;     

    //Format table headers
    for(var i=0; i<1; i++) 
    {   
        for(var j=0; j<colCount-2; j++) 
        {           
            str= myTable.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].innerHTML;
            ExcelSheet.Cells(i+1,j+1).Value = str;
        }
        ExcelSheet.Range("A1", "Z1").EntireColumn.AutoFit();
    }
    for(var i=1; i<rowCount; i++) 
    {
        for(var k=0; k<colCount-2; k++) 
        {
            str= rows[i].getElementsByTagName('td')[k].innerHTML;
            ExcelSheet.Cells(i+1,k+1).Value = myTable.rows[i].cells[k].innerText;
        }
        ExcelSheet.Range("A"+i, "Z"+i).WrapText = true;
        ExcelSheet.Range("A"+1, "Z"+i).EntireColumn.AutoFit();
    }

    //ExcelSheet.SaveAs("C:\\TEST.XLS");
    //ExcelSheet.Application.Quit();

    return; 
}

Use CSV format that is readable for MS Excel, OpenOffice or LibreOffice etc...

Imagine the table:

| H1 | H2 | H3 | H4 |

| x1 | x2 | x3 | x4 |
   
| y1 | y2 | y3 | y4 |

CSV should look like

H1,H2,H3,H4
x1,x2,x3,x4
y1,y2,y3,y4

--> file.csv

I would write some simple script that will loop through each row and write values separated by ,.

I hope this is what you're looking for

just pass html string to htmlData variable, couple of problems there like its taking default name and while you open excel it says invalid format but data is in correct format.

var htmlData = "<some html tble string here />";

http://jsfiddle/DYqXa/

You are exporting the "contents" of the table, but not the actual table tags themselves. If you put the table inside a div, and export the contents of the DIV it should work as expected.

Just a couple of other notes though. First, You can assume that this code won't be used in a browser other than IE as it requires clipboard access. Second, you'll need to move the window.clipboardData.setData part to AFTER the Excel object has been instantiated to cover newer versions of Excel (that don't access clipboard contents created before they were).

<script Language="javascript">
    function ExportHTMLTableToExcel()
    {
       var thisTable = document.getElementById("myDiv").innerHTML;
       window.clipboardData.setData("Text", thisTable);
       var objExcel = new ActiveXObject ("Excel.Application");
       objExcel.visible = true;
       var objWorkbook = objExcel.Workbooks.Add;
       var objWorksheet = objWorkbook.Worksheets(1);
       objWorksheet.Paste;
    }
</script>

<div id="myDiv">
    <table id="myTable" class="display">
        <tr>
            <th>Col1</th>
            <th>Col2</th>
            <th>Col3</th>
        </tr>
        <tr>
            <td>1</td>
            <td>2</td>
            <td>3</td>
        </tr>
        <tr>
            <td>4</td>
            <td>5</td>
            <td>6</td>
        </tr>
    </table>
</div>

<input type="button" onclick="ExportHTMLTableToExcel()"value="Export">

本文标签： javascriptExport Html table to Excel filegetting incorrect formatStack Overflow