csv datatable 乱码 导出_DataTable内容导出为CSV文件

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Web;6 usingSystem.Threading;7 usingSystem.IO;8 usingSystem.Data;9 usingSystem.Windows.Forms;10

11 namespaceIMSCommonFunction12 {13 public classCSVHelper14 {15 public static string FilterCSVCell(stringcellContent)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 returncellContent;29 }30

31 public static void ExportCSVFile(HttpResponse response, string fullPath, stringContent)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(stringFullFileName)55 {56 FileInfo DownloadFile = newFileInfo(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 ".xml":65 System.Web.HttpContext.Current.Response.ContentType = "text/xml";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, stringstrFileName)86 {87 string style = @"

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 //绑定数据到DataGrid1

106 System.Web.UI.WebControls.DataGrid DataGrid1 = newSystem.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 = newSystem.IO.StringWriter();112 System.Web.UI.HtmlTextWriter hw = newSystem.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, stringfilename)122 {123 try

124 {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 = newStringBuilder();132 StringBuilder items = newStringBuilder();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 else

146 {147 colHeaders.Append(dt.Columns[i].Caption.ToString() + "\t");148 }149 }150 response.Write(colHeaders.ToString());151

152 foreach (DataRow row indr)153 {154 for (i = 0; i < clength; i++)155 {156 if (i == clength - 1)157 {158 items.Append(row[i].ToString() + "\n");159 }160 else

161 {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 finally

173 {174 response.Flush();175 response.End();176 }177 }178

179 public static void DataTableToCSV(DataTable table, stringfile)180 {181 string title = "";182 FileStream fs = newFileStream(file, FileMode.Create);183 StreamWriter sw = new StreamWriter(newBufferedStream(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 intable.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 }