CODEWITHSUNDEEP
javascripthtmlexcelexport
124697
124697

Reputation: 21893

Export to excel function not working

Somsone have recommanded i use this function to export my data table to excel but it exports the HTML not just the data in the table. How can I make it export the data and the formatting(width, colour...) only?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script>
function ExportHTMLTableToExcel()
{
   var thisTable = document.getElementById("table").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;
   alert('test');

}
</script>
  <title>Java Friends</title>
</head>
<body>
  <table id="table" style="font-weight: bold">
    <tr style="background-color:red"><td>a</td><td>b</td></tr>
    <tr><td>1</td><td>2</td></tr>
    <tr>
      <td colspan="2">
        <button onclick="ExportHTMLTableToExcel()">
            Get as Excel spreadsheet
        </button>
      </td>
    </tr>
  </table>    
</body>
</html>

Note: this function only works in IE if the security options download unsigned activex control and download signed activex control are set to 'enable'

Upvotes: 0

Views: 3454

Answers (2)

Sharique Hussain Ansari
Sharique Hussain Ansari

Reputation: 1456

Please use this code, it is working fine for me

public void ExportToSpreadsheet(DataTable table, string name)
        {
            try
            {
                HttpContext context = HttpContext.Current;
                context.Response.Clear();
                context.Response.ClearHeaders();
                String sr = string.Empty;
                sr += "<html><body><table>";
                sr += "<tr style=\"background-color:gray;color:white;\">";
                foreach (DataColumn column in table.Columns)
                {
                    sr += "<th>";
                    sr += column.ColumnName;
                    sr += "</th>";
                }
                sr += "</tr>";
                sr += Environment.NewLine;
                foreach (DataRow row in table.Rows)
                {
                    sr += "<tr>";
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        sr += "<td>";
                        sr += row.ItemArray[i];
                        sr += "</td>";
                    }
                    sr += "</tr>";
                    sr += Environment.NewLine;
                }
                sr += "</table></body></html>";               
                context.Response.ContentType = "application/vnd.ms-excel";               
                context.Response.AddHeader("Content-Disposition", "attachment; filename=" + name + ".xls");                        
                context.Response.Write(sr);
                context.Response.Flush();
                context.Response.End();
                context.Response.Close();
            }
            catch (Exception ex)
            {

            }
        }

Hope this help.

Upvotes: 1

NakedBrunch
NakedBrunch

Reputation: 49413

The jQuery DataTables TableTools plug-in will let you very easily export an HTML table to Excel but it won't handle the formatting.

You can see an example of the plug-in running here: http://www.datatables.net/extras/tabletools/

It may not handle formatting but it does make exporting the HTML table super easy.

If formatting is absolutely required then you'll need to look into sending the data back to the server and then having the server process the HTML into Excel. If you're using ASP.net then it is actually quite easy to do.

Upvotes: 0

Related Questions