CODEWITHSUNDEEP
javascriptjsphtml-tableexport-to-excel
Fahad
Fahad

Reputation: 403

Export Html table to Excel file - getting incorrect format

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.

enter image description here

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">

Upvotes: 0

Views: 7059

Answers (4)

Mohsin
Mohsin

Reputation: 852

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; 
}

Upvotes: 2

Andy Rutter
Andy Rutter

Reputation: 1

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">

Upvotes: 0

Shoib Mohammed A
Shoib Mohammed A

Reputation: 328

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.net/DYqXa/

Upvotes: 1

Stepo
Stepo

Reputation: 1056

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

Upvotes: 1

Related Questions