C# DataTable导出Excel 帮助方法

public JsonResult DataTableSqlDataExportToCSV(string sql)

        {

           try

            {

               #region 获取数据

                DataTable dt = impl.GetDataTableSql(sql);

               #endregion

 

               string dir = Server.MapPath("~/Content/TempFile");

               if (Directory.Exists(dir) == false)

                {

                    Directory.CreateDirectory(dir);

                }

               string fileName = DateTime.Now.Year + "-" + DateTime.Now.Month + "report" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";

               string path = dir + "\\" + fileName;

                CSVHelper.DataToCsv(dt, path);

               return Json(new { msg = "导出成功", result = true, data = "/Content/TempFile/" + fileName }, JsonRequestBehavior.AllowGet);

            }

           catch (Exception ex)

            {

               return Json(new { msg = "导出失败：" + ex.Message, result = false, data = "" }, JsonRequestBehavior.AllowGet);

            }

        }

 

 

 

 

 

using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

using System.Linq;

using System.Text;

using System.Web;

 

namespace aZaaS.KStar.Web.Areas.DSFA.Other

{

   public class CSVHelper

    {

       public static void DataToCsv(DataTable dt, string strName)

        {

           string strPath = strName;

           if (File.Exists(strPath))

            {

                File.Delete(strPath);

            }

 

            StringBuilder strColu = new StringBuilder();

            StringBuilder strValue = new StringBuilder();

           int i = 0;

 

            StreamWriter sw = new StreamWriter(new FileStream(strPath, FileMode.CreateNew), Encoding.GetEncoding("UTF-8"));

           先打印表头

           for (i = 0; i <= dt.Columns.Count - 1; i++)

            {

                strColu.Append(dt.Columns[i].ColumnName);

                strColu.Append(",");

            }

            strColu.Remove(strColu.Length - 1, 1);//移除掉最后一个,字符

            sw.WriteLine(strColu);

           //再逐行打印行数据

           foreach (DataRow dr in dt.Rows)

            {

                strValue.Remove(0, strValue.Length);//清空遗留数据

               for (i = 0; i <= dt.Columns.Count - 1; i++)

                {

                    strValue.Append(dr[i].ToString().Replace(",", ";"));

                    strValue.Append(",");

                }

                strValue.Remove(strValue.Length - 1, 1);//移除掉最后一个,字符

                sw.WriteLine(strValue);

            }

            sw.Close();

 

        }

    }

}