你的位置:首页 > ASP.net教程

[ASP.net教程]DataTable内容导出为CSV文件


CSVHelper.cs内容:

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Web; 6 using System.Threading; 7 using System.IO; 8 using System.Data; 9 using System.Windows.Forms; 10  11 namespace IMSCommonFunction 12 { 13   public class CSVHelper 14   { 15     public static string FilterCSVCell(string cellContent) 16     { 17       bool isAddFlag = false; 18       if (cellContent.IndexOf("\"") != -1) 19       { 20         cellContent = cellContent.Replace("\"", "\"\""); 21         cellContent = "\"" + cellContent + "\""; 22         isAddFlag = true; 23       } 24       if (cellContent.IndexOf(",") != -1 && isAddFlag != true) 25       { 26         cellContent = "\"" + cellContent + "\""; 27       } 28       return cellContent; 29     } 30  31     public static void ExportCSVFile(HttpResponse response, string fullPath, string Content) 32     { 33       try 34       { 35         response.Buffer = true; 36         response.Clear(); 37         response.Charset = System.Text.Encoding.Default.BodyName; 38         response.ContentEncoding = System.Text.Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312");//GB2312用Excel打开时,没有乱码。 39         response.AppendHeader("Content-Disposition", "attachment;filename=" + fullPath); 40         response.ContentType = "application/ms-excel"; 41         response.Output.Write(Content); 42         response.Flush(); 43         response.End(); 44       } 45       catch (ThreadAbortException) 46       { 47       } 48       catch (Exception ex) 49       { 50         throw new ApplicationException(string.Format("Export CSV file have a error: {0}", fullPath), ex); 51       } 52     } 53  54     public static void FileDownload(string FullFileName) 55     { 56       FileInfo DownloadFile = new FileInfo(FullFileName); 57       System.Web.HttpContext.Current.Response.Clear(); 58       System.Web.HttpContext.Current.Response.ClearHeaders(); 59       System.Web.HttpContext.Current.Response.Buffer = false; 60       string extension = Path.GetExtension(FullFileName); 61       extension = string.IsNullOrEmpty(extension) ? extension : extension.ToLower(); 62       switch (extension) 63       { 64         case ".": 65           System.Web.HttpContext.Current.Response.ContentType = "text/"; 66           break; 67         default: 68           System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream"; 69           break; 70       } 71       string browser = System.Web.HttpContext.Current.Request.UserAgent.ToUpper(); 72       bool isNeedEncode = !browser.Contains("FIREFOX"); 73       System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + 74         (isNeedEncode ? System.Web.HttpUtility.UrlEncode(DownloadFile.Name, System.Text.Encoding.UTF8) : DownloadFile.Name)); 75       System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); 76       System.Web.HttpContext.Current.Response.Flush(); 77       if (System.Web.HttpContext.Current.Response.IsClientConnected) 78         System.Web.HttpContext.Current.Response.WriteFile(DownloadFile.FullName); 79  80       //出错 81       System.Web.HttpContext.Current.Response.End(); 82       System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest(); 83     } 84  85     public static void DataHtmlToExcel(HttpResponse response, DataTable dt, string strFileName) 86     { 87       string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式 88       //设置Response 89       response.Clear(); 90       response.Buffer = true; 91       response.Charset = "utf-8"; 92       response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); 93       //Response.Charset = "utf-8"; 94       if (strFileName.Length > 0) 95       { 96         response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(strFileName))); 97       } 98       else 99       {100         response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");101       }102       //Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");103       HttpContext.Current.Response.ContentType = "application/ms-excel";104 105       //绑定数据到DataGrid1106       System.Web.UI.WebControls.DataGrid DataGrid1 = new System.Web.UI.WebControls.DataGrid();107       DataGrid1.DataSource = dt.DefaultView;108       DataGrid1.DataBind();109       //将DataGrid1构成的html代码写进StringWriter110       //DataGrid1.Page.EnableViewState = false;111       System.IO.StringWriter tw = new System.IO.StringWriter();112       System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);113       DataGrid1.RenderControl(hw);114 115       response.Write(style);//注意116       response.Write(tw.ToString());117       response.Flush();118       response.End();119     }120 121     public static void ExportExcel(HttpResponse response, DataTable dt, string filename)122     {123       try124       {125         response.Clear();126         response.BufferOutput = true;127         response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");128         response.AppendHeader("Content-Disposition", "attachment;filename=" +129           HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(filename)));130         response.ContentType = "application/ms-excel";131         StringBuilder colHeaders = new StringBuilder();132         StringBuilder items = new StringBuilder();133 134         DataRow[] dr = dt.Select();135 136         int i = 0;137         int clength = dt.Columns.Count;138 139         for (i = 0; i < clength; i++)140         {141           if (i == clength - 1)142           {143             colHeaders.Append(dt.Columns[i].Caption.ToString() + "\n");144           }145           else146           {147             colHeaders.Append(dt.Columns[i].Caption.ToString() + "\t");148           }149         }150         response.Write(colHeaders.ToString());151 152         foreach (DataRow row in dr)153         {154           for (i = 0; i < clength; i++)155           {156             if (i == clength - 1)157             {158               items.Append(row[i].ToString() + "\n");159             }160             else161             {162               items.Append(row[i].ToString() + "\t");163             }164           }165         }166         response.Write(items.ToString());167       }168       catch (Exception ex)169       {170         response.Write(ex.Message);171       }172       finally173       {174         response.Flush();175         response.End();176       }177     }178 179     public static void DataTableToCSV(DataTable table, string file)180     {181       string title = "";182       FileStream fs = new FileStream(file, FileMode.Create);183       StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);184       for (int i = 0; i < table.Columns.Count; i++)185       {186         title += table.Columns[i].ColumnName + ",";187       }188       title = title.Substring(0, title.Length - 1) + "\n";189       sw.Write(title);190       foreach (DataRow row in table.Rows)191       {192         string line = "";193         for (int i = 0; i < table.Columns.Count; i++)194         {195           line += row[i].ToString() + ",";196         }197         line = line.Substring(0, line.Length - 1) + "\n";198         sw.Write(line);199       }200       sw.Close();201       fs.Close();202     }204   }205 }

 

页面后台按钮事件处理:

 1 protected void btnExportCSV_Click(object sender, EventArgs e) 2 { 3   try 4   { 5     string sql = Server.UrlDecode(Request["Sql"]); 6     DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql); 7     StringBuilder sbHeader = new StringBuilder(); 8     StringBuilder sbContent = new StringBuilder(); 9     DateTime tempDateTime = DateTime.MinValue;10     string tempVal = "";11     for (int i = 0, len = dt.Rows.Count; i < len; i++)12     {13       for (int j = 0, len2 = dt.Columns.Count; j < len2; j++)14       {15         if (i == 0)16         {17           sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName);18         }19         tempVal = dt.Rows[i][j].ToString();20         if(DateTime.TryParse(tempVal,out tempDateTime))21           tempVal = tempDateTime.ToString("dd-MM-yyyy HH:mm:ss");22         sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal));23       }24       sbContent.Remove(sbContent.Length - 1, 1);25       sbContent.AppendLine();26     }27     sbHeader.Remove(sbHeader.Length - 1, 1);28     sbHeader.AppendLine();29     IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response,30                          string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")),31                          sbHeader.ToString() + sbContent.ToString());32   }33   catch (Exception ex)34   {35     IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId);36     this.ShowErrorMsg(ex);37   }38 }