[RESOLVED] Download to Excel from ASP.NET page

[MMock]

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:

            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?

[rjv_rnjn]

Try

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

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

[gep13]

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

[MMock]

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:

            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));

[rjv_rnjn]

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

[MMock]

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?

[MMock]

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

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

[MMock]

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?

[rjv_rnjn]

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.

[MMock]

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]?

[rjv_rnjn]

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.

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

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

Thank you for this.

[MMock]

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

[rjv_rnjn]

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

I had like that.

[MMock]

At least I added to your reputation! Thanks again.