Last post Aug 02, 2012 12:36 AM by Paul Linton
Aug 01, 2012 09:59 PM|rankone|LINK
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;
int store = (int)Session["Subst"];
DataSet ds = DataAccess.GetExcel(store);
search = "%%Anchor";
cell = ws.Cells.Find(search, null, FindLookIn.Values, LookAt.Whole, SearchOrder.ByColumns, SearchDirection.Next, false);
int columns = Convert.ToInt32(ds.Tables.Columns.Count);
for (int i = 0; i < ds.Tables.Rows.Count; i++)
for (int j = 0; j < ds.Tables.Columns.Count; j++)
if (cell != null)
cell.Value = ds.Tables.Rows[i][j].ToString();
cell = cell.Offset(0, 1);
cell = cell.Offset(1, -columns);
Any help would be much appreciated.
Aug 02, 2012 12:33 AM|Paul Linton|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.
Aug 02, 2012 12:36 AM|Paul Linton|LINK
But, my psychic powers tell me that the column is formatted as a percentage. You want to format it as currency.