Formatting excel cells as number and currency not working in workbook

Aspose.Cells Product Family
1

i'm using the code below to format a range of cells and rows as number and current in excel workbook. But after saving and opening it these cells have the little square in the top left corner saying number stored as text, i either have to double click each cell or change or click convert to number on each one to fix it. i need it stored a number and other as currency so my formula's work correctly. This is supposed to format rows 17 and 18, any help is greatly appreciated.

//Formatting

Aspose.Cells.Style stl4 = workbook.Styles[workbook.Styles.Add()];

//Set custom number format.

stl4.Custom = "$#,##0.00";

Aspose.Cells.StyleFlag flag = new StyleFlag();

//Apply custom number format.

flag.NumberFormat = true;

Range range = workbook.Worksheets[2].Cells.CreateRange("C17", "AG17");

range.Name = "MyRange";

//Apply the style to cells in the named range.

range.ApplyStyle(stl4, flag);

Aspose.Cells.Row row = workbook.Worksheets[2].Cells.Rows[17];

row.ApplyStyle(stl4, flag);

2

Hi,

Thank you for considering Aspose.

I checked your style code and it works fine. Please see the following sample code,

Sample Code:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Obtaining the reference of the first worksheet

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells["B1"].Style.Number = 15;

worksheet.Cells["B2"].PutValue(20);

worksheet.Cells["B3"].PutValue("2546",true);

worksheet.Cells["B4"].PutValue(34560000);

worksheet.Cells["B6"].PutValue(43247.95);

worksheet.Cells["B7"].PutValue(45000);

//Formatting

Aspose.Cells.Style stl4 = workbook.Styles[workbook.Styles.Add()];

//Set custom number format.

stl4.Custom = "$#,##0.00";

Aspose.Cells.StyleFlag flag = new StyleFlag();

//Apply custom number format.

flag.NumberFormat = true;

Range range = worksheet.Cells.CreateRange("B2", "B7");

range.Name = "MyRange";

//Apply the style to cells in the named range.

range.ApplyStyle(stl4, flag);

//Saving the Excel file

workbook.Save("C:\\Number_Format.xls");

Please check if you are using text value (as the input) in the Cells.PutValue method. Please use numeric values Or use the overloaded method of Cells.PutValue(string,bool) and pass the second parameter as true to convert string to other data type. If you are using a template file, please share the file and we will check your issue soon.

Thank You & Best Regards,

3

Hi,

Thanks for your inquiry.

Well, I think your code is fine but the problem is with your data. Either you are filling it dynamically by Cell.PutValue() method or you have imported data from some source and the data was saved in string/text type in columns or objects. It is also possible that you have template file which has data stored as string/Text format. For your info, this is default behaviour of MS Excel, the reason is there is an option remains checked/on in MS Excel Options/Settings. So, if the numbers are stored as text, you will get green triangles onto those cells attached with the info (!) error that defines "numbers stored as text". To eliminate this info error, you need to uncheck that option manually in MS Excel. e.g.. in MS Excel 2003, click menu option.... Tools|Options|Error Checking tab, now uncheck the checkbox i.e.., "Number stored as text". I think you should convert the data to numbers to work fine with your formulas in the sheets. Following are some scenarios if any one is related to yours.

If you are inserting data into Cells dynamically, t it might be possible that you are inserting numbers as text like using Cell.PutValue("123") instead of Cell.PutValue(123). To rectify this, you may also try as: Cell.PutValue("123", true); to convert the text to numbers. Also, Nausherwan has provided an example with some description and explanation, you can also refer to it.

It is also possible that your so called numeric data stored in the datatable, is actually coming from string fields(columns).. Well, if so, and you are importing data from some table in some source, you may try the following overloaded method to convert data to numbers while filling the worksheet form data source:

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
byte firstColumn,
bool insertRows,
bool convertStringToNumber
);

Note: use true for the last parameter.

Kindly let us know if you need further clarifications, if you have some doubts, you can give us more information and post your template file here. We will be happy to sort it out.

Thank you.

4

That was my problem, i was leaving the true out on the conversion like below. All works now, thanks!

cells3[strCellCombo].PutValue(ugExpenses.Rows[i].Cells[x].Value.ToString(),true);