datatable to csv

 

    /// <summary>
    /// DataTableToCsv
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="fileName">文件名称，不用后缀</param>
    /// <remarks>
    /// 文件格式
    ///每条记录占一行 
    ///以逗号为分隔符 
    ///逗号前后的空格会被忽略 
    ///字段中包含有逗号，该字段必须用双引号括起来 
    ///字段中包含有换行符，该字段必须用双引号括起来 
    ///字段前后包含有空格，该字段必须用双引号括起来 
    ///字段中的双引号用两个双引号表示 
    ///字段中如果有双引号，该字段必须用双引号括起来 
    ///第一条记录，可以是字段名 
    /// </remarks>
    public static void DataTableToCsv(DataTable dt, string fileName)
    {
        //Clear <div  id="loading" ..
        HttpContext.Current.Response.Clear();

        #region Export Grid to CSV
        // Create the CSV file to which grid data will be exported.
        System.IO.StringWriter sw = new System.IO.StringWriter();
        // First we will write the headers.
        int iColCount = dt.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write("\"" + dt.Columns[i] +"\"");
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);
        // Now write all the rows.
        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                    sw.Write("\"" + dr[i].ToString() + "\"");
                else
                    sw.Write("\"\"");

                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
        #endregion

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + string.Format("{0:yyyyMMddhhmmss}", DateTime.Now) + ".csv");
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        HttpContext.Current.Response.Write(sw);
        HttpContext.Current.Response.End();
    }

    /// <summary>
    /// 该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表；
    /// </summary>
    /// <param name="filepath">filepath</param>
    /// <param name="sheetname">Sheet1</param>
    /// <returns></returns>
    public DataSet ExcelDataSource( string filepath , string sheetname )
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter ( "select * from [" + sheetname + "$]", strConn );
        DataSet ds = new DataSet ();
        oada.Fill (ds);
        return ds ;
    }