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.
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.
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
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.
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.