正文
csharp: DataTable export to excel,word,csv etc
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
http://code.msdn.microsoft.com/office/Export-GridView-to-07c9f836
https://exporter.codeplex.com/
http://closedxml.codeplex.com/
http://epplus.codeplex.com/
/// <summary>
/// 塗聚文修改
/// </summary>
/// <param name="dt"></param>
/// <param name="Response"></param>
/// <param name="filename"></param>
public static void Convertword(DataTable dt, HttpResponse Response, string filename)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".doc");
Response.Charset = "utf-8";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.word";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.GridView dg = new System.Web.UI.WebControls.GridView();
dg.DataSource = dt;
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
//HttpContext.Current.ApplicationInstance.CompleteRequest(); }
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
/// <param name="Response"></param>
/// <param name="filename"></param>
public static void Convertexcel(DataTable dt, HttpResponse Response, string filename)
{ Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls");//
Response.Charset = "utf-8";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
dg.DataSource = dt;
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
//HttpContext.Current.ApplicationInstance.CompleteRequest(); }
/// <summary>
///
/// </summary>
/// <param name="dataTable"></param>
/// <param name="Response"></param>
/// <param name="filename"></param>
public static void ConvertCSV(DataTable dataTable, HttpResponse Response, string filename)
{ Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".csv");
Response.Charset = "utf-8";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "Application/x-msexcel";
StringBuilder sb = new StringBuilder();
if (dataTable.Columns.Count != 0)
{
foreach (DataColumn column in dataTable.Columns)
{
sb.Append(column.ColumnName + ',');
}
sb.Append("\r\n");
foreach (DataRow row in dataTable.Rows)
{
foreach (DataColumn column in dataTable.Columns)
{
if (row[column].ToString().Contains(",") == true)
{
row[column] = row[column].ToString().Replace(",", "");
}
sb.Append(row[column].ToString() + ',');
}
sb.Append("\r\n");
}
}
Response.Write(sb.ToString());
Response.End();
//HttpContext.Current.ApplicationInstance.CompleteRequest(); }
winform:
/// <summary>
/// winform
/// Encoding导出无问题
/// 涂聚文测试
/// </summary>
/// <param name="dgv"></param>
/// <param name="fileName"></param>
public static void ExportToCSV(DataGridView dgv, string fileName)
{
if (dgv.Rows.Count < 1)
{
MessageBox.Show("没有记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
} SaveFileDialog sfDialog = new SaveFileDialog();
sfDialog.Filter = "CSV文件(*.csv)|*.csv|文本文件(*.txt)|*.txt|所有文件(*.*)|*.*";
sfDialog.FilterIndex = 0;
sfDialog.FileName = fileName;
if (sfDialog.ShowDialog() == DialogResult.OK)
{ string strFileName = sfDialog.FileName;
StreamWriter sw = new StreamWriter(strFileName, false, Encoding.Unicode);
string strLine = "";
foreach (DataGridViewColumn col in dgv.Columns)
{
if (col.Visible)
{
strLine += "\"" + col.HeaderText.Trim().Replace("\"", "\\\"") + "\"" + "\t";
}
}
strLine = strLine.Substring(0, strLine.Length - 1);
sw.WriteLine(strLine);
sw.Flush(); foreach (DataGridViewRow dgvr in dgv.Rows)
{
strLine = "";
foreach (DataGridViewCell dgvc in dgvr.Cells)
{
if (dgvc.Visible)
{
if (dgvc.Value == null)
{
strLine += "\t";
}
else
{
strLine += "\"" + dgvc.Value.ToString().Trim().Replace("\"","\"\"") + "\"" + "\t";
}
}
}
sw.WriteLine(strLine);
sw.Flush();
}
sw.Close();
MessageBox.Show(string.Format("数据已成功导出至\n{0}\n文件中!", strFileName), "导出成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
