Hi,
What's the best way to format number columns in excel after export? I am using following which is a quick and dirty way of doing it I suppose:
for (int i = 0; i < 25; i++){ws1.Columns[i].CellFormat.FormatString = "#,##0.00_);[Red](#,##0.00)";}
Where the numbers are displayed with comma notation and negative numbers are displayed in red in parenthese. The 25 is random, I am just using it since I know I don't have more columns than that. I am trying to avoid using a hardcoded value like that. Instead I would prefer to enumerate through the WorksheetColumn collection. It is not possible to do it on this collection. I would love to do something like below:
foreach(NSExcel.WorksheetColumn col in ws1.Columns){col.CellFormat.FormatString = "#,##0.00_);[Red](#,##0.00)";}
Is there a way to accomplish something like this instead of using hardcoded column count?
Thanks.
Rajiv.
You can iterate the Columns collection in a foreach statement, but it will only return columns which have been accessed before. For example, if Column A never had any formatting changed and you have never gotten a reference to it, by asking for ws1.Columns[0], it will be skipped in the foreach. Also, if data is exported to a cell, it does not force the column to be created (unlike its row, which it does force to be created). If you don't want any columns skipped and you are unsure about whether they have all been created, you will have to stay with the for loop.
Thanks for the reply. In fact I get a build error if I try to use the foreach statement saying "foreach statement cannot operate on variables of type 'Infragistics.Excel.WorksheetColumnCollection' because 'Infragistics.Excel.WorksheetColumnCollection' does not contain a public definition for 'GetEnumerator'. I would like to see an example that iterates over the columns or rows collection. This is what I was trying to do:
NSExcel.Worksheet ws1 = wb.Worksheets.Add("IFRS");
{
}
When I got the above error. The columns have not been accessed, of course, since it is a newly created worksheet. Thanks again.
Thanks for the response again. Makes sense now. I am using version 6.3 for asp.net 2.0.
I believe support for the foreach statement on that collection was added with version 7.2. If you are using an earlier version, you will have to use the for loop.