CSVHelper.cs内容：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Threading;
using System.IO;
using System.Data;
using System.Windows.Forms; namespace IMSCommonFunction
{
public class CSVHelper
{
public static string FilterCSVCell(string cellContent)
{
bool isAddFlag = false;
if (cellContent.IndexOf("\"") != -)
{
cellContent = cellContent.Replace("\"", "\"\"");
cellContent = "\"" + cellContent + "\"";
isAddFlag = true;
}
if (cellContent.IndexOf(",") != - && isAddFlag != true)
{
cellContent = "\"" + cellContent + "\"";
}
return cellContent;
} public static void ExportCSVFile(HttpResponse response, string fullPath, string Content)
{
try
{
response.Buffer = true;
response.Clear();
response.Charset = System.Text.Encoding.Default.BodyName;
response.ContentEncoding = System.Text.Encoding.UTF8;// System.Text.Encoding.GetEncoding("GB2312");//GB2312用Excel打开时，没有乱码。
response.AppendHeader("Content-Disposition", "attachment;filename=" + fullPath);
response.ContentType = "application/ms-excel";
response.Output.Write(Content);
response.Flush();
response.End();
}
catch (ThreadAbortException)
{
}
catch (Exception ex)
{
throw new ApplicationException(string.Format("Export CSV file have a error: {0}", fullPath), ex);
}
} public static void FileDownload(string FullFileName)
{
FileInfo DownloadFile = new FileInfo(FullFileName);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.Buffer = false;
string extension = Path.GetExtension(FullFileName);
extension = string.IsNullOrEmpty(extension) ? extension : extension.ToLower();
switch (extension)
{
case ".xml":
System.Web.HttpContext.Current.Response.ContentType = "text/xml";
break;
default:
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
break;
}
string browser = System.Web.HttpContext.Current.Request.UserAgent.ToUpper();
bool isNeedEncode = !browser.Contains("FIREFOX");
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" +
(isNeedEncode ? System.Web.HttpUtility.UrlEncode(DownloadFile.Name, System.Text.Encoding.UTF8) : DownloadFile.Name));
System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
System.Web.HttpContext.Current.Response.Flush();
if (System.Web.HttpContext.Current.Response.IsClientConnected)
System.Web.HttpContext.Current.Response.WriteFile(DownloadFile.FullName); //出错
System.Web.HttpContext.Current.Response.End();
System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
} public static void DataHtmlToExcel(HttpResponse response, DataTable dt, string strFileName)
{
string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式
//设置Response
response.Clear();
response.Buffer = true;
response.Charset = "utf-8";
response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
//Response.Charset = "utf-8";
if (strFileName.Length > )
{
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(strFileName)));
}
else
{
response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");
}
//Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
HttpContext.Current.Response.ContentType = "application/ms-excel"; //绑定数据到DataGrid1
System.Web.UI.WebControls.DataGrid DataGrid1 = new System.Web.UI.WebControls.DataGrid();
DataGrid1.DataSource = dt.DefaultView;
DataGrid1.DataBind();
//将DataGrid1构成的html代码写进StringWriter
//DataGrid1.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid1.RenderControl(hw); response.Write(style);//注意
response.Write(tw.ToString());
response.Flush();
response.End();
} public static void ExportExcel(HttpResponse response, DataTable dt, string filename)
{
try
{
response.Clear();
response.BufferOutput = true;
response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
response.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(filename)));
response.ContentType = "application/ms-excel";
StringBuilder colHeaders = new StringBuilder();
StringBuilder items = new StringBuilder(); DataRow[] dr = dt.Select(); int i = ;
int clength = dt.Columns.Count; for (i = ; i < clength; i++)
{
if (i == clength - )
{
colHeaders.Append(dt.Columns[i].Caption.ToString() + "\n");
}
else
{
colHeaders.Append(dt.Columns[i].Caption.ToString() + "\t");
}
}
response.Write(colHeaders.ToString()); foreach (DataRow row in dr)
{
for (i = ; i < clength; i++)
{
if (i == clength - )
{
items.Append(row[i].ToString() + "\n");
}
else
{
items.Append(row[i].ToString() + "\t");
}
}
}
response.Write(items.ToString());
}
catch (Exception ex)
{
response.Write(ex.Message);
}
finally
{
response.Flush();
response.End();
}
} public static void DataTableToCSV(DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, FileMode.Create);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = ; i < table.Columns.Count; i++)
{
title += table.Columns[i].ColumnName + ",";
}
title = title.Substring(, title.Length - ) + "\n";
sw.Write(title);
foreach (DataRow row in table.Rows)
{
string line = "";
for (int i = ; i < table.Columns.Count; i++)
{
line += row[i].ToString() + ",";
}
line = line.Substring(, line.Length - ) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
}
}
页面后台按钮事件处理：
protected void btnExportCSV_Click(object sender, EventArgs e)
{
try
{
string sql = Server.UrlDecode(Request["Sql"]);
DataTable dt = Bll.Report.CustomReport.GetCustomReport(sql);
StringBuilder sbHeader = new StringBuilder();
StringBuilder sbContent = new StringBuilder();
DateTime tempDateTime = DateTime.MinValue;
string tempVal = "";
for (int i = , len = dt.Rows.Count; i < len; i++)
{
for (int j = , len2 = dt.Columns.Count; j < len2; j++)
{
if (i == )
{
sbHeader.AppendFormat("{0},", dt.Columns[j].ColumnName);
}
tempVal = dt.Rows[i][j].ToString();
if(DateTime.TryParse(tempVal,out tempDateTime))
tempVal = tempDateTime.ToString("dd-MM-yyyy HH:mm:ss");
sbContent.AppendFormat("{0},", IMSCommonFunction.CSVHelper.FilterCSVCell(tempVal));
}
sbContent.Remove(sbContent.Length - , );
sbContent.AppendLine();
}
sbHeader.Remove(sbHeader.Length - , );
sbHeader.AppendLine();
IMSCommonFunction.CSVHelper.ExportCSVFile(this.Response,
string.Format("CustomReport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmmss")),
sbHeader.ToString() + sbContent.ToString());
}
catch (Exception ex)
{
IMSCommonFunction.SystemEventLog.LogEvent("CustomReport.aspx,export csv file Errormsg", ex, "common", this.CurrentUserId);
this.ShowErrorMsg(ex);
}
}