Message-ID: <768850836.9449.1711691349578.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_9448_162189897.1711691349578" ------=_Part_9448_162189897.1711691349578 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Convert strings to formatted numerical values

Convert strings to formatted numerical values

In= troduction

 

In the creation of an Excel workbook, sometimes a column contains numeri= cal values stored as strings. For example, a percent column can display per= cents 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 wil= l mark these strings with a green corner indicating that they can be displa= yed as numerals, along with a warning message, "The number in this cel= l is stored as text or preceded by an apostrophe."

ExcelApplication= allows you to change the NumberFormat of a column so that it will automati= cally 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:<= /p>

  1. Gather the values from the column into a collection and convert them to= numeric values.
  2. Apply the desired Number Format to the styles of column.
  3. Cycle through the collection and add the values back to each cell of th= e column.

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

Code

=20
 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 =3D { "%" };

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

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

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

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

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

       //--- Set the number format to percentage
       style.NumberFormat =3D "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 =3D 0;

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