CellFormat.Number Property

I downloaded evaluation copy and am trying to format some cells, does the CellFormat property work in the eval version.

Can you point me to some code on your web site that can provide me with sample code for setting this property.

Hi Bob,

Please refer to
List of Supported Number Formats
Data Formatting

Excel excel = new Excel();

Cells cells = excel.Worksheets[0].Cells;
cells[“A1”].PutValue(12.345);
cells[“A1”].Style.Number = 2; //Set cell number format to 0.00.

Hi,

Concerning this point, I encounter problems using the custom property to set the cell format. My goal is to format numbers this way :

123456789 becomes 123 456 789

I have set the string format to “# ##0” or “## ##0” but it seems a \ char is added before space.

How can I manage to use this format ?

Thanks,
Laurent.

Have you tried “##0 ##0 ##0”? You can set in MS Excel to test it. It works as you expected in my machine. But I don’t know if it can work on your French version of MS Excel. Please have a try.

Yes it works !

Thanks,
Laurent.

Hi Lawrence,
I need your help for formatting columns/cells. With Microsoft Excel the following code does my formatting for columns:

Public Sub FormatColumn(ByVal Column As String, ByVal NoDecimals As Long)
Try
Select Case NoDecimals
Case 1
ExcelApp.Worksheets(1).Columns(Column).NumberFormat = “0.0”
Case 2
ExcelApp.Worksheets(1).Columns(Column).NumberFormat = “#,##0.00”
Case 3
ExcelApp.Worksheets(1).Columns(Column).NumberFormat = “#,##0.000”
Case 4
ExcelApp.Worksheets(1).Columns(Column).NumberFormat = “#,##0.0000”
End Select
Catch ex As Exception
MsgBox(ex.ToString) ’ Show friendly error message.
End Try
End Sub

Do you have the ability to format an entire column with you object model?

Can you provide me with code for your model which can hanle this.

I am thinking that I would need to set a range with your model and then apply a format to that range.

I am having a problem setting up a range and then formatting a range. Can you provide me with vb.net code to do this.

I did not find code examples for doing this under your api reference code examples and need your help.

This is the last test I need to do before purchasing, and of course you have promised to have the other enhancements done by mid March.

Really like your product, good job.

Thanks,
Bob Modrich

Hi Bob,

You can try the following code:
'set number format of “0.0” to Column B

Dim range as Range = cells.CreateRange(1, 1, true)

Dim styleIndex as Style = excel.Styles.Add()
Dim style as Style = excel.Styles(styleIndex)
style.Custom = "0.0"

range.Style = style

Hi Laurence,
Code does not work, looks like it is these two lines

Dim styleIndex as Style = excel.Styles.Add()
Dim style as Style = excel.Styles(styleIndex)

have you tested this? These two lines are not acceptable.

Could you please test this code and advise. Also I am running 1.8.0.0 for eval copy could this make a difference?
Thanks
Bob Modrich

Hi Bob,

I misprinted and please change first line of code you mentioned here to:

Dim styleIndex as Integer = excel.Styles.Add()

I have test it and it works fine.

Please download fix 1.8.5.

It fixed bug in 1.8.0 and was added some new features described in https://forum.aspose.com/t/119368.

That that did it thanks, I’ll be working on my formatting now.

@bobm,
We are glad to share that we have introduced a new product Aspose.Cells which has replaced the discarded version Aspose.Excel. This new product contains a variety of new features and is much better in terms of performance.

Aspose.Cells supports a large set of number formats available in the latest versions of MS Excel. You can format the data using this product as follows:

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);

// Getting the Style of the A1 Cell
Style style = worksheet.Cells["A1"].GetStyle();

// Setting the display format to number 15 to show date as "d-mmm-yy"
style.Number = 15;

// Applying the style to the A1 cell
worksheet.Cells["A1"].SetStyle(style);

// Adding a numeric value to "A2" cell
worksheet.Cells["A2"].PutValue(20);

// Getting the Style of the A2 Cell
style = worksheet.Cells["A2"].GetStyle();

// Setting the display format to number 9 to show value as percentage
style.Number = 9;

// Applying the style to the A2 cell
worksheet.Cells["A2"].SetStyle(style);

// Adding a numeric value to "A3" cell
worksheet.Cells["A3"].PutValue(2546);

// Getting the Style of the A3 Cell
style = worksheet.Cells["A3"].GetStyle();

// Setting the display format to number 6 to show value as currency
style.Number = 6;

// Applying the style to the A3 cell
worksheet.Cells["A3"].SetStyle(style);

// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

For detailed information on this, you may visit the following articles:
List of Supported Number Formats
Data Formatting

The latest version of this product can be downloaded :
Aspose.Cells for .NET (Latest Version)

Running solution containing hundreds of examples can be downloaded here.