Free Support Forum - aspose.com

ConvertStringToNumericValue

Is there a way to make this recognise bracketed numbers.

I've run this on the attached sheet and it works brilliantly except for the negative numbers - which are in brackets.

Regards

Chris

Hi Chris,

Well, you may try to set it in your MS Excel (97-2003) for your need.

E.g.,

Click Tool|Options| menu options and then in "Error Checking" tab, uncheck "Number Stored as Text" check box.

Thank you.

The purpose of me formating the text as numeric is so that the client does not have to do this.

If the cells are not numeric then they don;t work correctly with functions like SUM etc.

Workbook excel = new Workbook();

excel.Open(@"C:\ParsingTestFiles\Export.xls");

Cells cells = excel.Worksheets[0].Cells;

cells.ConvertStringToNumericValue();

excel.Save(@"C:\ParsingTestFiles\Export_New.xls", FileFormatType.Excel97);

You can format numeric cells so that negative numbers have brackets instead of a negative sign, and the cells remain numeric not text.

E.g. -12 = (12)

But Aspose Cells does not treat the negative values with brackets as a possible number - it ignores it, only changes the positive numbers from text to numeric.

Hi,

Thanks for providing us further internal details.

We will look into it and get back to you soon.

Thank you.

Hi,

We have checked your requirement, we will try to support it soon and provide a fix for you.

Thank you.

Hi Chris,

I think you can use the following approach to format the numeric values as your wish:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/setting-display-formats-of-numbers-dates.html.

Actually now we set numeric value as default number format.

Or maybe we can provide overload functions like this:

Cells.ConvertStringToNumericValue(int builtInFormat)

Cells.ConvertStringToNumericValue(string customFormat)

Will it serve your need?

I think the overload functions would be benificial to the API.

I got round the problem using the following...

Style style = excel.Styles[excel.Styles.Add()];

style.Custom = "#,##0;(#,##0)";

for (int row = 0; row <= cells.MaxDataRow; row++ )

{

for (int col = 0; col <= cells.MaxDataColumn; col++ )

{

string value = cells[row, col].StringValue;

value = value.Replace('(', '-');

value = value.Replace(")", "");

value = value.Replace(",", "");

if (IsNumeric(value))

{

cells[row, col].PutValue(Convert.ToDouble(value));

cells[row, col].SetStyle(style);

}

}

}

Hi,

Please try the fix in http://www.aspose.com/community/forums/thread/130137.aspx.

We only convert the string value to number value.We do not set the style for the cell.