private void ExportToExcel(DataTable dt, string fileName, string worksheetName) { Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename="+ fileName + ""); Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "gb2312"; //编码设置解决乱码问题,当然也可以在html头部设定 Response.ContentEncoding = Encoding.GetEncoding("GB2312");
StringWriter stringWriter = new StringWriter(); HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter); DataGrid dataExportExcel = new DataGrid(); dataExportExcel.ItemDataBound += new DataGridItemEventHandler(dataExportExcel_ItemDataBound); dataExportExcel.DataSource = dt; dataExportExcel.DataBind(); dataExportExcel.RenderControl(htmlWrite); StringBuilder sbResponseString = new StringBuilder(); sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head><meta http-equiv=\"Content-Type\" content=\"text/html;\"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>"+ worksheetName +"</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>"); sbResponseString.Append(stringWriter + "</body></html>"); Response.Write(sbResponseString.ToString()); Response.End(); } void dataExportExcel_ItemDataBound(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Header) { //Header Text Format can be done as follows e.Item.Font.Bold = true; //Adding Filter/Sorting functionality for the Excel int cellIndex = 0; while (cellIndex < e.Item.Cells.Count) { e.Item.Cells[cellIndex].Attributes.Add("x:autofilter", "all"); e.Item.Cells[cellIndex].Width = 200; e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Center; cellIndex++; } } if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { int cellIndex = 0; while (cellIndex < e.Item.Cells.Count) { //Any Cell specific formatting should be done here e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Left; cellIndex++; } } }
文章转自:http://blog.weareon.net/export-datatable-to-excel-in-asp-net-without-using-excel-interop-part-ii/