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
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>
The following sample method takes a column of strings of the form "= 00%" and display them as percentages with no decimals.
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