[Solved] Export ASP.Net GridView To CSV changes Number (Price) Formats in C# and VB.Net

Last Reply one month ago By dharmendr

Posted one month ago

Hello Sir,

I am trying to export the gridview in CSV format but here the price format is gettinbg changed.

if the price is 100.00 in DB but after export to CSV its getting exported as 100 only for values like 102.90 i am getting only 102.9 the format in CSV file for price is 2 digits after decimal point

Please help how to get the value as 100.00 in CSV file

Thanks

<asp:GridView ID="GridView1" runat="server" EmptyDataText="No Record Found" class="table table-bordered table-striped table-hover"
    AutoGenerateColumns="False" OnPageIndexChanging="GridView1_PageIndexChanging"
    AllowPaging="True" Width="1024px" PagerSettings-Mode="NumericFirstLast" PageSize="15"
    RowStyle-HorizontalAlign="Center">
    <Columns>
        <asp:BoundField HeaderText="UnitPrice" DataField="unit_price" SortExpression="unit_price" />
    </Columns>
    <PagerSettings FirstPageText="First" LastPageText="Last" />
    <PagerStyle CssClass="pagination-ys"></PagerStyle>
    <RowStyle></RowStyle>
</asp:GridView>

 

Response.Clear();
Response.Buffer = true;

string strFileName = "fileupload" + DateTime.Now.ToShortDateString() + ".csv";
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);

Response.Charset = "";
Response.ContentType = "application/text";
DataTable dt = ViewState["Data"] as DataTable;
DataTable dt2 = new DataTable();
for (int i = 0; i < dt.Columns.Count; i++)
{
    dt2.Columns.Add(dt.Columns[i].ColumnName, typeof(string));
}
for (int i = 0; i < dt.Rows.Count; i++)
{
    dt2.ImportRow(dt.Rows[i]);

    dt2.Rows[i]["unit_price"] = (dt2.Rows[i]["unit_price"]).ToString("0.00");
}

this.GridView1.DataSource = dt2;
this.GridView1.DataBind();

StringBuilder sb = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
    sb.Append(GridView1.Columns[k].HeaderText + ',');
}
sb.Append("\r\n");

for (int i = 0; i < GridView1.Rows.Count; i++)
{
    for (int k = 0; k < GridView1.Columns.Count; k++)
    {
        string cell = GridView1.Rows[i].Cells[k].Text.Trim().Replace("&nbsp;", "").Replace("&quot;", "\"");
        sb.Append(" " + cell + ',');
    }
    sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
Posted one month ago Modified on one month ago

Hi democloud,

Put a equal symbol with double quote before the field and double quote after the field.

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField HeaderText="UnitPrice" DataField="unit_price" />
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" runat="server" OnClick="Export" />

Namepsaces

C#

using System.Data;
using System.Text;

VB.Net

Imports System.Data
Imports System.Text

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("unit_price", typeof(string)) });
        dt.Rows.Add("100.00");
        dt.Rows.Add("200.25");
        dt.Rows.Add("300.59");
        dt.Rows.Add("400.50");
        GridView1.DataSource = dt;
        GridView1.DataBind();
        ViewState["Data"] = dt;
    }
}
protected void Export(object sender, EventArgs e)
{
    DataTable dt = ViewState["Data"] as DataTable;
    DataTable dt2 = new DataTable();
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        dt2.Columns.Add(dt.Columns[i].ColumnName, typeof(string));
    }
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        dt2.ImportRow(dt.Rows[i]);
        dt2.Rows[i]["unit_price"] = Convert.ToDouble(dt2.Rows[i]["unit_price"]).ToString("0.00");
    }

    this.GridView1.DataSource = dt2;
    this.GridView1.DataBind();

    StringBuilder sb = new StringBuilder();
    for (int k = 0; k < GridView1.Columns.Count; k++)
    {
        sb.Append(GridView1.Columns[k].HeaderText + ',');
    }
    sb.Append("\r\n");
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        for (int k = 0; k < GridView1.Columns.Count; k++)
        {
            string cell = GridView1.Rows[i].Cells[k].Text.Trim().Replace("&nbsp;", "").Replace("&quot;", "\"");
            sb.Append("=\"" + cell + "\",");
        }
        sb.Append("\r\n");
    }

    Response.Clear();
    Response.Buffer = true;
    string strFileName = "fileupload" + DateTime.Now.ToShortDateString() + ".csv";
    Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
    Response.Charset = "";
    Response.ContentType = "application/text";
    Response.Output.Write(sb.ToString());
    Response.Flush();
    Response.End();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("unit_price", GetType(String))})
        dt.Rows.Add("100.00")
        dt.Rows.Add("200.25")
        dt.Rows.Add("300.59")
        dt.Rows.Add("400.50")
        GridView1.DataSource = dt
        GridView1.DataBind()
        ViewState("Data") = dt
    End If
End Sub

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = TryCast(ViewState("Data"), DataTable)
    Dim dt2 As DataTable = New DataTable()
    For i As Integer = 0 To dt.Columns.Count - 1
        dt2.Columns.Add(dt.Columns(i).ColumnName, GetType(String))
    Next
    For i As Integer = 0 To dt.Rows.Count - 1
        dt2.ImportRow(dt.Rows(i))
        dt2.Rows(i)("unit_price") = Convert.ToDouble(dt2.Rows(i)("unit_price")).ToString("0.00")
    Next

    Me.GridView1.DataSource = dt2
    Me.GridView1.DataBind()

    Dim sb As StringBuilder = New StringBuilder()
    For k As Integer = 0 To GridView1.Columns.Count - 1
        sb.Append(GridView1.Columns(k).HeaderText + ","c)
    Next
    sb.Append(vbCrLf)
    For i As Integer = 0 To GridView1.Rows.Count - 1
        For k As Integer = 0 To GridView1.Columns.Count - 1
            Dim cell As String = GridView1.Rows(i).Cells(k).Text.Trim().Replace("&nbsp;", "").Replace("&quot;", """")
            sb.Append("=""" & cell & """,")
        Next
        sb.Append(vbCrLf)
    Next

    Response.Clear()
    Response.Buffer = True
    Dim strFileName As String = "fileupload" & DateTime.Now.ToShortDateString() & ".csv"
    Response.AddHeader("content-disposition", "attachment;filename=" & strFileName)
    Response.Charset = ""
    Response.ContentType = "application/text"
    Response.Output.Write(sb.ToString())
    Response.Flush()
    Response.End()
End Sub

Screenshot