[RESOLVED] Download to Excel from ASP.NET page

I have a button on my aspx page to download the gridview data to Excel. I am using SpreadsheetGear.dll. It worked nicely until the customer asked me to format the currency fields.

So this is my loop to format:

Code:

---

            System.Data.DataTable dtWarranties;
            dtWarranties = ds.Tables[0];

            // Format currency fields.
            double doubleOut;
            for (int i = 0; i < dtWarranties.Rows.Count; ++i)
            {
                for (int j = 3; j < 6; ++j)
                {
                    if (Double.TryParse(Convert.ToString(dtWarranties.Rows[i].ItemArray[j]), out doubleOut))
                        dtWarranties.Rows[i].ItemArray[j] = String.Format("${0:0.00}", doubleOut); 
                }
            }

---

As I am stepping through debugging, I query this:
? String.Format("${0:0.00}", doubleOut)
"$1569.00
But I query the left side of the assignment, and it is still unformatted:
? dtWarranties.Rows[i].ItemArray[j]
"1569"

Why won't it "take" the format?

Try

Code:

---

dtWarranties.Rows[i][columnIndex]= String.Format("${0:0.00}", doubleOut);

---

I would also check what's the data type for the column in question.

Quote:

---

Originally Posted by rjv_rnjn

I would also check what's the data type for the column in question.

---

I think that this could be the issue.

Assuming the datatype is a numeric, you can't provide it a string, without it first "converting" it for you, hence losing the formatting.

I haven't used SpreadsheetGear before, but I would have thought that this is something that would be "baked" in. Is there not an option to provide formatting when the export happens?

Gary

Shoot - it already is a string!

I am getting so stressed out. This report is way over budget and I can't get this simplest last thing working.

Here is my dataset when I create it. Every column is a string:

Code:

---

            DataSet dsReport = new DataSet();
            DataTable dtReport = dsReport.Tables.Add();
            //writeLog(string.Format("WarrantyReport: datatable created"));

            dtReport.Columns.Add("Warranty Type", typeof(string));
            dtReport.Columns.Add("# of Warranties", typeof(string));
            dtReport.Columns.Add("# of Squares", typeof(string));
            dtReport.Columns.Add("Promo $", typeof(string));
            dtReport.Columns.Add("CC $", typeof(string));
            dtReport.Columns.Add("Total Warranty Value", typeof(string));

---

So did you try my suggested code -- assigning to [row][column] instead of ItemArray?

And by the way, Gary said, Assuming the datatype is a numeric, you can't provide it a string, without it first "converting" it for you, hence losing the formatting. doesn't this show the datatype is NOT numeric?

? dtWarranties.Rows[i].ItemArray[j]
"1569"

Wouldn't it display in the debugger as 1569 without quotation marks if it were numeric?

Quote:

---

Originally Posted by rjv_rnjn

So did you try my suggested code -- assigning to [row][column] instead of ItemArray?

---

No, I did not. I'll try that now!

Sorry I didn't jump at that right away! It worked. I don't understand why, what is the difference between that and the way I was doing it originally?

From what I understand, I would have expected the compiler to throw an error or maybe a runtime error. MSDN (http://msdn.microsoft.com/en-us/libr...itemarray.aspx) says the setter value has to be an object array, it does not discuss about individually setting values.

I know this is not a very correct/thorough technical explanation, but I am not good at that. :) I will try and see if I can find any blog post that has taken a swipe at this.

I am a VB.NET programmer and only very recently a C# programmer. Accessing elements in a dataset are different in the two languages. Did I just *mistakenly* stumble upon ItemArray and I should be double indexing my elements with [i][j]?

Stumbled upon the 3.5 version of the same page (http://msdn.microsoft.com/en-us/libr...(v=VS.90).aspx) and saw a user comment that the ItemArray get gives you a copy of the ItemArray. So any modifications would not show up in the original. And from what you've reported that comment seems to be verifiable.

Quote:

---

Originally Posted by MMock

Did I just *mistakenly* stumble upon ItemArray and I should be double indexing my elements with [i][j]?

---

Well I use ItemArray in watch windows (at times I don't like loading the whole datatable while debugging), so even if you stumbled across it, it's good. :)

You are not stuck accessing them through indices, you can use the column name too.

While looking more into this, I found a few extension methods called SetField<T> (http://msdn.microsoft.com/en-us/library/bb353039) and I think those seem to be better as they can handle nullable types better

Quote:

---

If value is null, the SetField method converts the null value to Value in the underlying DataColumn.

---

Thank you for this. :thumb:

If you give me your address, I will send you my paycheck for today :)

Quote:

---

Originally Posted by MMock

If you give me your address, I will send you my paycheck for today :)

---

I had like that. :D

At least I added to your reputation! Thanks again.