Format Number - Change Value In Parentheses To Numeric Number With Negative Sign

Aspose.Cells Product Family
1

Hello,


I’m using ExportDataTableAsString method to copy data from a spreadsheet to a datatable. I have a column which contain negative and positive numbers. Negative number are in parentheses. I have trouble converting this column to a regular number with negative sign instead of parentheses. Can someone please help?

I tried to set the style to aspose built-in number format 2 and it doesn’t help remove the parantheses.

Thanks.

P.S:
I wanted to add that I tried this formula =SUBSTITUTE(MyColumn, “(”,"-" ) attempting to do a little trick to convert (###) to - ### and got an error: Recursive Reference! I tested this trick in Excel and it works.

Below is a sample code usage:

string formula = “=SUBSTITUTE(ColName, “(”,”-" )";
worksheet.Cells[ColName].SetSharedFormula(formula, worksheet.Cells.MaxDataRow, 1);

2

Hi,


Well, ExportDataTableAsString() method exports the worksheet formatted data based on what is shown in the cells to fill the DataTable. I think you may try to set/change the style on the relevant column accordingly before exporting data to fill the data table, e.g
You may apply the style to the cells in that column, or even apply the style to the whole column:
e.g
Sample code:

Style stl3 = workbook.Styles[workbook.Styles.Add()];
stl3.Custom = “0.00”;
StyleFlag flag3 = new StyleFlag();
flag3.NumberFormat = true;
worksheet.Cells.Columns[0].ApplyStyle(stl3 flag3);


Hope, this helps a bit.

Thank you.

3

This is exactly what I was looking for! Thank you for your help. It works!!!


I thought using the built-in number format #2 found here: Format cells|Documentation is the same as having a custom style “0.00” so I didn’t try your method before. I guess they are not the same. Anyway, your suggestion solves my problem. Thank you for your help!

4

Hi there,


Thank you for your confirmation. Its good to know that you are up and running again.

Please note, using the built-in number format ‘2’ or setting the custom format to “0.00” as shown in the code snippet shared by Amjad is the same thing therefore you may use both solutions alternatively. Please check the below provided code snippet and attached resultant file for your reference.

C#

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Style style = book.Styles[book.Styles.Add()];
style.Number = 2;
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
sheet.Cells.Columns[0].ApplyStyle(style, flag);
book.Save(myDir + “output.xlsx”);
5

Hi Babar,

I know what I did wrong now. When I tried the style.Number = 2 previously and it didn’t work because I only did the first half the code as follow.

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0]; Style style = book.Styles[book.Styles.Add()];
style.Number = 2;

I didn’t have any code to set the the StyleFlag. I was trying to copy the example shown on the documentation (see link I provided in previous post).

Thank you for taking your time to further explain this. I appreciate it!

6

Hi,

Thanks for your posting and using Aspose.Cells.

It is good to know that your issue is resolved now. Let us know if you face any other issue, we will be glad to look into it and help you further.