Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Introduction

 

In the creation of an Excel workbook, sometimes a column contains numerical values stored as strings. For example, a percent column can display percents as strings. This poses a problem if you want to use these values for equations, or if you want to clean up the display of the file, as Excel will mark these strings with a green corner indicating that they can be displayed as numerals, along with a warning message, "The number in this cell is stored as text or preceded by an apostrophe."

Excerpt

ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.

Hence, converting the values for an entire column requires three steps:

...

The following sample method takes a column of strings of the form "00%" and display them as percentages with no decimals.

Code

Code Block
  private static void ConvertToPercent(Workbook wb, Worksheet ws,  int Column, int FirstRow)
  {
       //--- String object to hold the original value
       String value;

       //--- Separator to parse the original string
       String[] separator = { "%" };

       //--- ArrayList to hold the values
       ArrayList values = new ArrayList();

       //--- Cycle from the first row of data to the 
       //--- last populated cell
       for (int i = FirstRow; i < ws.PopulatedCells.RowCount; i++)
       {
            //--- Check for null values
            if (ws[i, Column].Value != null)
            {
                 //--- Grab the original string value for each cell
                 value = ws[i, Column].Value.ToString();

                 //--- Remove the percentage sign to 
                 //--- return the percentage, a number 
                 //--- represented by a string
                 value = value.Split(separator,  
                StringSplitOptions.None)[0];

                 //--- Convert the number to a double 
                 //--- (dividing by 100 because of 
                 //--- the percentage conversion) 
                 //--- and add to the ArrayList
                 values.Add((Convert.ToDouble(value) / 100));
            }
       }

       //--- Create a new style to apply the new NumberFormat
       Style style = wb.CreateStyle();

       //--- Set the number format to percentage
       style.NumberFormat = "0%";

       //--- Apply the style change to the column's cells
       ws.GetColumnProperties(Column).ApplyStyle(style);

       //--- Create an index for the ArrayList in the next loop
       int index = 0;

       //--- Cycle from the first row of data to the 
       //--- last populated cell
       for (int i = FirstRow; i < ws.PopulatedCells.RowCount; i++)
       {
            //--- Handle for null values
            if (ws[i, Column].Value != null)
            {
                 //--- Put the values back in as doubles 
                 //--- and raise the index
                 ws[i, Column].Value = values[index];
                 index++;
            }
       }
  }