Range.style.number = 5

Why won’t this work? I get an error stating ‘not set to an instance of an object’

Dim rg As Range = Excel1.Worksheets(0).Cells.CreateRange(m, c, True)

rg.Style.Number = 5

Thanks,
ed

Setting style to a range is different with setting style to a cell. You should create a style then set it to the range.

Dim style1 as Style = Excel1.Styles(Excel1.Styles.Add())
style1.Number = 5
Dim rg As Range = Excel1.Worksheets(0).Cells.CreateRange(m, c, True)
rg.Style = style1

When I enter
Dim style1 as Style = Excel1.Styles(Excel1.Styles.Add())

Style has a wavy blue line under it and I get a tip saying that Style is ambiquous. ??

ed

Hello Laurence, I came acorss this same problem and from the documentation, it was not clear that styles for ranges work this way, perhaps you can augment the documentation with the example above.

In the future, will we be able to get the style for the range ? I realize that this is difficult to do but getting the default style (the styles that are in common for the range of cells selected) is how you use Excel, i.e. it does not disallow reading styles for a range.

Just wondering why that was hard to implement ?

thanks
Marty

Dear Marty,

Thanks for your suggestion on the document. Yes, I will update it.

If you set the style for the range at run time, you can get the style. However, currently you cannot get the style of range in the designer file. There may be different style setting for a range of cells, so we have to check every cell to find the common style setting. It’s a time-consuming function and not easy to implement.

Why do you want to get the style?

Laurence,
I do not want to GET the style. I’m trying to find a way to SET the style for a cell from vb.net . I can set it but it does not affect the way the cell displays. So I was trying to define the cell as a range and see if I could do it that way.

Can you help?

thanks,
earlofroberts

Hi,

You can use the following method to set a cell’s number format.

cell.Style.Number = 5;

Do you have any trouble using above code?

Thanks for the reply. I figured it was just one of the features you have not had a chance to implement yet. Why would I want to get the style for a range ? Since the idea is to model Excel, you would want your API to be as intuitive as Excel (as you have done on all your products wery well!) - I’m just saying and I think you agree - the component should auto create a range and auto define the styles if it does not already have a range created - this way you don’t require developers to set a range before getting the range (not intuitive). Someone please comment if you know what I mean. Put another way, I read a template and I want to get the style for a range - right now that is not supported. I have to for each through every cell - no big deal.



take care - keep up the good work guys

Hi ed,

The ambiquous is caused by System.Web.UI.WebControls.Style in the web page.

So please change your code to:

Dim style1 as Aspose.Excel.Style = Excel1.Styles(Excel1.Styles.Add())

Laurence
I have tried this syntax. It does not error out, but it seems to have no impact.
The value assigned to the cell is a number and I would like to have a $ in the cell(and a little other stuff).

thanks,
ed

Hi ed,

Dim style1 as Aspose.Excel.Style = Excel1.Styles(Excel1.Styles.Add())
style1.Number = 5
Dim rg As Range = Excel1.Worksheets(0).Cells.CreateRange(m, c, True)
rg.Style = style1

If the above code doesn’t has impact, please check if your cell’s value is a number.

If you use cell.PutValue(123), the number format will work. If you use cell.PutValue(“123”), the number format won’t work for it thinks the cell value is a string.


Could you send your code and result file to excel@aspose.com? I will check it.



Hi Laurence,

You suggested:
If you use cell.PutValue(123), the number format will work. If you use cell.PutValue(“123”), the number format won’t work for it thinks the cell value is a string.

Converting my variable to a number before doing the PutValue(x) worked.

Thanks for your help.

vaya con Dios,
ed



@earlofroberts,
Aspose.Excel is discontinued now and no more active development is done for it. Aspose.Cells has replaced it and contains all the latest features in MS Excel as well as features supported by its predecessor. Aspose.Cells provides setting the number formats as demonstrated in the following sample code.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Instantiate a new Workbook.
Workbook workbook = new Workbook();
Style style = workbook.CreateStyle();
style.Number = 5;
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;

// Fill some sample data into the cells.
for (int i = 0; i < 50; i++)
{
    for (int j = 0; j < 10; j++)
    {
        cells[i, j].PutValue((float)i*1.1);
    }

}

// Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");
range.ApplyStyle(style, new StyleFlag() { NumberFormat = true });
          
// Save the excel file.
workbook.Save("output.xlsx");

You can get more information about setting the number formats in the following document:
Setting Number Format

Following link can be used to download the free trial version of this product:
Aspose.Cells for .NET(Latest version)

A detailed runnable solution containing lot of ready to run examples is available here.