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);
            }
        }