Free Support Forum - aspose.com

Number formatted as text or preceded by an apostrophe

Hi,

Using excel 2007

I am having problems with a report that saves a number as string , and that is exactly what I want but excel uses the annoying green triangle in each cell warning that a "number formatted as text or preceded by apostrophe"

I could turn off the flag for Error checking rules for the entire workbook but I don't want that because it maybe usefull elsewhere.

How can I achieve that: Ignore the error for each concerned cell programatically ?

Thanks,

Hi,

Well, I am afraid, this is the limitation put forth by MS Excel, so if you have (so called) numeric data in strings, it will not be formatted, you may confirm it in MS Excel manually. Therefore, you need to have pure numeric data to be formatted properly. You can check if your source data columns are numeric or strings, so you may change their type accordingly. Alternatively, you have to convert the data manually after the data is inserted/exported to the Excel worksheet and before applying any formatting.

The error "Numbers stored as text" will be always popped up in the Cells, if the numeric data is inserted as string/text.

By the way, you may iterate though all those cells in the column and use:
cell.PutValue(cell.StringValue, true) to make sure that the data should be converted properly to numeric formats to avoid this annoying pop up.

e.g.., You may change/extend the code accordingly.

[C#]
Cells cells = worksheet.Cells;
//Format the column B to numeric column.
for (int i = 1; i < cells.MaxDataRowInColumn(1); i++)
{

cells[i, 1].PutValue(cells[i, 1].StringValue, true);
}
......................

[VB]
Dim cells As Cells = worksheet.Cells
'Format the column B to numeric column.
For i As Integer = 1 To cells.MaxDataRowInColumn(1) - 1

cells(i, 1).PutValue(cells(i, 1).StringValue, True)

Next i
.............................


Also, if you use: Cells.ImportDataTable() method to import data from the source data table on the back end, if this is the case, then you may use:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/aspose.cells.cells.importdatatable_overload_5.html

(Note: Put true for "convertStringToNumber" last parameter)


Thank you.

Is there a way to access Error checking rules from aspose?

Error checking rules is under excel options.

Is there a way to access excel options from aspose?

Thanks

Hi,


Yes, you may use error checking options APIs, see the following sample code:

Workbook wb = new Workbook(“e:\test2\booker.xls”);
Worksheet sheet = wb.Worksheets[0];

Aspose.Cells.ErrorCheckOptionCollection opts = sheet.ErrorCheckOptions;
int i = opts.Add();
Aspose.Cells.ErrorCheckOption opt = opts[i];
opt.SetErrorCheck(Aspose.Cells.ErrorCheckType.TextNumber, false);
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = 65535;
ca.EndColumn = 255;
opt.AddRange(ca);
wb.Save(“e:\test2\outbook.xls”);

Thank you.

Thank you ,

This helps alot