C#导出.csv格式的excel表

.cs文件直接贴代码:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;

public class DataToCsv
{
    public DataToCsv()
    {
    }

    /// <summary>
    /// 将DataTable导出CSV表格
    /// </summary>
    /// <param name="dataTable">DataTable数据源</param>
    /// <param name="ColumnName">标题列(英文逗号","分割)</param>
    /// <param name="ColumnValue">内容列参数名称</param>
    /// <param name="CsvName">导出的CSV表格名称</param>
    /// <returns></returns>
    public static StringBuilder Data_To_Csv(DataTable dataTable, string ColumnName, string[] ColumnValue, string CsvName)
    {
        DataTable dt = dataTable;
        try
        {
            StringWriter swCSV = new StringWriter();
            //列名
            swCSV.WriteLine(ColumnName);//"工单编号,工单标题,工单类型,创建时间,当前状态,当前节点名称,当前处理人"
                                        //遍历datatable导出数据
            foreach (DataRow drTemp in dt.Rows)
            {
                StringBuilder sbText = new StringBuilder();

                for (int i = 0; i < ColumnValue.Length; i++)  // ---------- 字段循环  
                {
                    sbText = AppendCSVFields(sbText, drTemp[ColumnValue[i].ToString()].ToString());
                }
                //去掉尾部的逗号
                sbText.Remove(sbText.Length - 1, 1);
                //写datatable的一行
                swCSV.WriteLine(sbText.ToString());
            }
            swCSV.Close();
            return swCSV.GetStringBuilder();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// csv添加逗号 用来区分列
    /// </summary>
    /// <param name="argFields">字段</param>
    /// <returns>添加后内容</returns>
    public static StringBuilder AppendCSVFields(StringBuilder argSource, string argFields)
    {
        return argSource.Append(argFields.Replace(",", " ").Trim()).Append(",");
    }

    /// <summary>
    /// 弹出下载框
    /// </summary>
    /// <param name="argResp">弹出页面</param>
    /// <param name="argFileStream">文件流</param>
    /// <param name="strFileName">文件名</param>
    public static void DownloadFile(HttpResponse argResp, StringBuilder argFileStream, string strFileName)
    {
        try
        {
            string strResHeader = "attachment; filename=" + Guid.NewGuid().ToString() + ".csv";
            if (!string.IsNullOrEmpty(strFileName))
            {
                strResHeader = "inline; filename=" + strFileName;
            }
            argResp.AppendHeader("Content-Disposition", strResHeader);//attachment说明以附件下载,inline说明在线打开
            argResp.ContentType = "application/ms-excel";
            argResp.ContentEncoding = Encoding.GetEncoding("GB2312"); // Encoding.UTF8;//
            argResp.Write(argFileStream);

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
View Code

调用示例:

DataSet ds = (DataSet)Session["data"];
                DataTable dt = ds.Tables[0];
                string[] values = { "S_ID", "S_Name", "S_Grade", "S_Class" };
                DataToCsv.DownloadFile(Response, DataToCsv.Data_To_Csv(dt, "学号,姓名,年级,班级", values, ViewState["Title"].ToString() + "报名表"), ViewState["Title"].ToString() + "报名表.csv");
                Response.End();

C#导出.csv格式的excel表