Formatting string columns


#1

Hi,

I am importing two dimension string array to the Excel and after data is imported I want to format some of the columns as date, some as currency, and some as numbers. Bellow is my current code, but it will not convert imported values to the desired format. This custom formatting will only work when I import object array. Is there any way to format string array?

string[,] objData = new string[20,4];

objData[0,0]=”100”;

objData[0,1]=” 5/2/2002 12:00:00 AM”;

objData[0,2]=” 500000”;

objData[0,3]=” Some text”;
objData[1,0]=........

….

objSheet.Cells.ImportTwoDimensionArray(objData,0,0);

objSheet.Cells.Columns[0].Style.Custom= "$###,##0.00;[Red]$###,##0.00";

objSheet.Cells.Columns[0].Style.Custom= “mm/dd/yyyy";

objSheet.Cells.Columns[0].Style.Custom= "###,##0;[Red]###,##0";

Thanks,
Aleksandra


#2

Number format won't work with string data. To make it works, please convert string to numeric data first.

You can use Cell.PutValue(string stringValue, bool isConverted) method to convert string.


#3

Your suggestion works only for a single value. In my case, I need to import two dimension array of strings and convert some columns after the import. Is there any way to convert whole column to numeric after the import?


#4

A good suggestion. I will add a new method: Cells.ConvertStringToNumbericValue.