Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
85
Number Formatting on Excel Columns
posted

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. 

 

 

 

Parents
  • 44743
    Verified Answer
    posted

    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.

Reply Children