Exporting to Excel issues RSS

• • Reply
  

  rankone

  Member

  53 Points

  53 Posts

  Exporting to Excel issues

  Aug 02, 2012 01:59 AM|LINK

  Hey Everyone!

  I'm using Spreadsheet Gear in my .NET C# application to capture the dataset into an excel worksheet. The worksheet is getting populated just fine but I am having issues with the format of the currency. For instance, in the database the value is 3.19 but it populates in the Excel worksheet as 31,900.00 The currency I am trying to display in the Worksheet is the Euro so ideally it should display as €3,19 instead of €31,900.00

  I don't know how to go about this because it isn't throwing an error out. I was thinking if there was a way to divide the values of the dataset by 10,000 but I am not sure if that will solve the issue.

  Here is my code:

  protected void Excel_Click(object sender, EventArgs e)
  {
  
  Response.ContentType = "application/vnd.ms-excel";
  Response.ContentEncoding = System.Text.Encoding.GetEncoding("iso-8859-1");
  string filename = "Summary" + Session["Subst"].ToString() + ".xls";
  Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
  
  IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(Server.MapPath("Images/Excel.xls"));
  IWorksheet ws = workbook.Worksheets[0];
  
  int store = (int)Session["Subst"];
  DataSet ds = DataAccess.GetExcel(store);
  
  string search;
  IRange cell;
  search = "%%Anchor";
  cell = ws.Cells.Find(search, null, FindLookIn.Values, LookAt.Whole, SearchOrder.ByColumns, SearchDirection.Next, false);
  int columns = Convert.ToInt32(ds.Tables[0].Columns.Count);
  
  
  
  for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  {
  for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
  {
  if (cell != null)
  {
  cell.Value = ds.Tables[0].Rows[i][j].ToString();
  cell = cell.Offset(0, 1);
  
  }
  
  }
  cell = cell.Offset(1, -columns);
  }
  
  
  workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97);

  
  Any help would be much appreciated.

  Thanks

  • Moved by mbanavige on Aug 03, 2012 03:21 AM
• • Reply
  

  Paul Linton

  Star

  13403 Points

  2531 Posts

  Re: Exporting to Excel issues

  Aug 02, 2012 04:33 AM|LINK

  This is the ASP.Net C# forum, I'm pretty sure that your problem is not related to ASP.Net.

  I suggest you try Spreadsheet Gear support, did you pay them money?  If they can't help ask for your money back and then try an Excel help forum or one of the many broader programming forums.

  Got a c# problem? Try .NET Book Zero from Charles Petzold, it's a free pdf.
• • Reply
  

  Paul Linton

  Star

  13403 Points

  2531 Posts

  Re: Exporting to Excel issues

  Aug 02, 2012 04:36 AM|LINK

  But, my psychic powers tell me that the column is formatted as a percentage.  You want to format it as currency.

  Got a c# problem? Try .NET Book Zero from Charles Petzold, it's a free pdf.