Export to excel function not working_问答_开发者

开发者 https://www.devze.com 2023-03-04 00:18 出处：网络

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

<!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>
<scrip开发者_如何学Ct>
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'

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.

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.