Currency symbol gets lost when export to excel[Answered] RSS

• • Reply
  ASPbun

  Member

  48 Points

  159 Posts

  Currency symbol gets lost when export to excel

  Nov 03, 2016 07:10 PM|ASPbun|LINK

  HI,

  I have a gridview with a button, which exports grid data to excel. One of the columns is a 'Currency' datatype. In the exported excel file, the currency data is missing '$' symbol. What could be the reason? Can someone pls guide me through this?

    protected void btnExport_Click(object sender, EventArgs e)
          {
              Response.ClearContent();
              Response.Buffer = true;
              Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
              Response.ContentType = "application/ms-excel";
              DataTable dt = GetDataTable(txtDate.Text, txtDate1.Text);
              string str = string.Empty;
              foreach (DataColumn dtcol in dt.Columns)
              {
                  Response.Write(str + dtcol.ColumnName);
                  str = "\t";
              }
              Response.Write("\n");
              foreach (DataRow dr in dt.Rows)
              {
                  str = "";
                  for (int j = 0; j < dt.Columns.Count; j++)
                  {
                      Response.Write(str + Convert.ToString(dr[j]));
                      str = "\t";
                  }
                  Response.Write("\n");
              }
              Response.End();
          }

  Here is my boundfield of the currency column:

  <asp:BoundField DataField="Expense Amount" HeaderText="Expense Amount" ItemStyle-VerticalAlign="Top" SortExpression="Expense Amount" DataFormatString="{0:C}"/>

  Thanks for any help!

• • Reply
  codemovement...

  Contributor

  2340 Points

  807 Posts

  Re: Currency symbol gets lost when export to excel

  Nov 03, 2016 07:17 PM|codemovement.pk|LINK

  Hi Can you please take a look in to below mentioned thread. This thing is already discussed.

  https://forums.asp.net/t/1801133.aspx?Grid+to+excel+with+currency+format+symbol+

  If my solution works for you, please mark it as answer! It will help others.
  Get more information: http://codemovement.pk
  
  Thanks,
• • Reply
  Nan Yu

  All-Star

  18815 Points

  3831 Posts

  Re: Currency symbol gets lost when export to excel

  Nov 04, 2016 09:05 AM|Nan Yu|LINK

  Hi ASPbun .

  Base on your code ,please modify your code as :

              Response.ClearContent();
              Response.Buffer = true;
              Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
              Response.ContentType = "application/ms-excel";
              DataTable dt = new DataTable();
              dt.Columns.Add(new DataColumn("CustomerID", typeof(string)));
              dt.Columns.Add(new DataColumn("ContactName", typeof(string)));
              dt.Columns.Add(new DataColumn("Expense Amount", typeof(string)));
              dt.Rows.Add("1", "1", 11111);
              dt.Rows.Add("1", "1", 11111);
              dt.Rows.Add("3", "1", 11111);
              dt.Rows.Add("2", "1", 11111);
              string str = string.Empty;
              foreach (DataColumn dtcol in dt.Columns)
              {
                  Response.Write(str + dtcol.ColumnName);
                  str = "\t";
              }
              Response.Write("\n");
              foreach (DataRow dr in dt.Rows)
              {
                  str = "";
                  for (int j = 0; j < dt.Columns.Count; j++)
                  {
                      if (j==2)
                      {
                          //var a = string.Format("{0:#.00}", Convert.ToDecimal(Convert.ToString(dr[j])) / 100);
                          var a = string.Format("{0:C}", Convert.ToDecimal(dr[j]) );
                          Response.Write(str +  a);
                      }
                      else
                      {
                          Response.Write(str + Convert.ToString(dr[j]));
                      }
                   
                      str = "\t";
                  }
                  Response.Write("\n");
              }
              Response.End();

  Replace 2 with the column index which you want to show currency .

  Best Regards,

  Nan Yu

• • Reply
  ASPbun

  Member

  48 Points

  159 Posts

  Re: Currency symbol gets lost when export to excel

  Nov 17, 2016 02:43 PM|ASPbun|LINK

  Thank you so much. It worked!