How to easily convert a cell to value

Aspose.Cells Product Family
1

I have a .net DataTable that has a column that holds both numeric and a string “N/A” values.
I create the Data column without declaring the data type like:
dt.columns.Add(“MyColumnName”)
e.g., this can contain some market share information or “N/A” if the data source is Null in the database.

I use smart marker (&=dt.MyColumnName) to dump the value into excel to create a excel report.
The problem is that the numeric value in the report is not shown in % format that I specify because excel
does not know it is numeric so treat it as text. A little green triangle appear in the upper left corner of the cell. If I click the cell and put my cursor in the entry box and hit return then the cell is converted the text to value and the number will be shown in % format.

The question is how can I convert a cell to value? Does Aspose.cells have an equivalent excel Value() function? Do I have to loop through every cell and change it to value?

I just did an experiment and found that one way to solve the problem is to loop through
all the data cells. If a cell’s value is not “N/A” then convert it to double like this:
wks.Cells(“K5”).Value = Convert.ToDouble(wks.Cells(“K5”).Value)

If I convert all the numeric cells this way then they will display in the proper format (in percent) that I want.

But I am wonder if there is an easier way to do this.


Thanks.

2

Hi,

Thanks for your question.

Well, I will recommend you to apply style to column. It will convert your entire column to some display format like number.

For more help please see Setting Display Formats of Numbers & Dates

For your help, I have written a sample code and source xlsx file. When you run the code, it converts the text inside the source.xlsx file into number format as shown in source.xlsx.out.xlsx file.

The code is fully commented, so you will not have a trouble getting it. Also see the screenshot.

C#


string filePath = “f:\downloads\source.xlsx”;


//Open the source workbook

Workbook workbook = new Workbook(filePath);


//Access the worksheet

Worksheet worksheet = workbook.Worksheets[0];



//Access the first column

Column col = worksheet.Cells.Columns[0];


//Format the column as number

Style colStyle = col.Style;


//decimal format, for more help please see,

//Aspose.Total for .NET|Documentation

colStyle.Number = 2;


//Flag to set all of the properties

StyleFlag flag = new StyleFlag();

flag.All = true;


//Apply the style on column

col.ApplyStyle(colStyle, flag);


//Save the workbook

workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Screenshot:

3

Thank you for the suggestion.

I tried it but it does not really work. The cell had already been set to the proper format when I dynamically write the smart marker to the cell. I think the problem is that when I create my column this way (without specifying the data type):
dataTable.columns.Add(“columnName”)
Excel does not know the type so it does not know it is numeric. I let .net to handle the type because it must accommodate string (like “N/A”) and numeric value in the DataTable.
I can manually fix the cell value if I select the cell, fit the F2 key, hit the Enter key.
Excel’s “value( )” function can also translate the problem cell value into numeric value – if you go to cell and write a formula like =value(address of problem cell). The cell using this value formula will have a proper numeric value and can be formatted correctly.

So now the only way I know how to make it work dynamically using code is what I mentioned in my last posting by converting the cell value to a double.

Below is what I tried but does not really work for me.
. . .
wbDesigner.SetDataSource(dtData)
wbDesigner.Process()
Dim col As Aspose.Cells.Column = wks.Cells.Columns(10)
Dim colStyle As Aspose.Cells.Style = col.Style
colStyle.Number = 10
Dim flag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
flag.All = True
col.ApplyStyle(colStyle, flag)
. . .



4

Hi,

Well, ok, can you please provide us your sample project and any other input file you are using? Please use some hard-coded values for your dataset to simplify everything.

5

Hi,

Well, I think while specifying your smart markers for those columns, you may utilize “numeric” parameter. So, your final smart marker would look like this:
&=dt.MyColumnName(numeric).

Now, Aspose.Cells would try to convert the values to numeric values (if possible) and it may work for you.

Let us know if it works fine now.

Thank you.

6

I tried your suggestion below:
&=dt.MyColumnName(numeric).

This works very well for me. It is exactly what I was looking for.

Many thanks!.

7

Hi,

Good to know that your issue is sorted out.

Thank you.