export datagrid's data to excel

 

    protected void export_Click(object sender, EventArgs e)
    {
        BusinessLayer bl = new BusinessLayer();
        DataTable dt = bl.GetAllUnpaidOrders().Tables[0];
        byte[] dataBuffer =  GetTimesheetReporteStream(dt);
        string fileName = "TimesheetDefaulterReport.csv";
        this.Response.Clear();
        this.Response.Charset = "GB2312";
        this.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        this.Response.ContentType = "application/vnd.ms-excel"; // "application/octet-stream"; //"application/ms-excel"; //vnd.
        this.Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8).Replace("+", "%20"));
        this.Response.OutputStream.Write(dataBuffer, 0, dataBuffer.Length);
        this.Response.Flush();
        this.Response.End();
    }
    public byte[] GetTimesheetReporteStream(DataTable dt)
    {
        StringBuilder builder = new StringBuilder();
        foreach (DataColumn column in dt.Columns)
        {
            builder.Append(column.ColumnName + ",");
        }
        builder.Append(Environment.NewLine);
        foreach (DataRow row in dt.Rows)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                builder.Append(ConvertToCsvCell(row[i].ToString()) + ",");
            }
            builder.Append(Environment.NewLine);
        }
        byte[] dataBuffer = Encoding.Default.GetBytes(builder.ToString());
        return dataBuffer;
    }
    public static string ConvertToCsvCell(string cell)
    {
        cell = SBCToDBC(cell);
        if (cell.Contains(@",") || cell.Contains("\""))
        {
            cell = cell.Replace("\"", "\"\"");
            cell = "\"" + cell + "\"";
        }
        else
        {
            if (cell.Contains("\r") || cell.Contains("\n"))
            {
                cell = "\"" + cell + "\"";
            }
        }
        return cell;
    }
    public static string SBCToDBC(string input)
    {
        char[] cc = input.ToCharArray();
        for (int i = 0; i < cc.Length; i++)
        {
            if (cc[i] == 8220 || cc[i] == 8221)
            {
                cc[i] = (char)34;
                continue;
            }
            if (cc[i] == 8216 || cc[i] == 8217)
            {
                cc[i] = (char)39;
                continue;
            }
            if (cc[i] == 12288)
            {
                cc[i] = (char)32;
                continue;
            }
            if (cc[i] > 65280 && cc[i] < 65375)
            {
                cc[i] = (char)(cc[i] - 65248);
            }

        }
        return new string(cc);
    }