Named Cell Styles - association with cell, and formatting chnage at cell level without affecting style

Aspose.Cells Product Family
, ,
1

what is StyleFlag used for?

When I create a workbook style, name it and set a cell’s style to that style object, I see that the formatting is applied as intended. However when I select that cell, Excel still shows it as Normal style. How do I make sure it associates the named cell style with that (and other cells)? When I right click a cell style and modify the style using excel style–> modify formatting , I see that the cells actually remain unchagged. So there is no association between the cell style and cell.

There are some cases where I need a cell style to work this way.
There are some cases where I need to apply additional formatting (but not update cell style). I want to chnage only one cell for example and apply indent to it.
Are there are any reliable examples to do this in aspose cell java?

This is what I woudl liek to do:

  1. create a cell style called “myStyle” with font = bold, font size 14.
  2. Apply this style to a range of cells A1…C10 (30 cells in this range)
  3. Change only one cell - A1 to have indentation of level 3. (other 29 cells shoudl not change their indentation)
  4. When in excel if I pull down Styles menu and rt click “myStyle” --> modify and chnage font color to Red, and save, I would like to see all 30 cells have font color Red, and only 1 cell have indent 3 (while the color is red)

thanks.

2

@redred,
Could you please try the following code for applying a style on a cell range and let us know your feedback.

// Instantiating an Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Range range = worksheet.Cells.CreateRange(0, 0, 9, 3);

// Defining new Style object
Style myStyle = workbook.CreateStyle();
myStyle.Name = "Custom";
myStyle.Font.Size = 14;
myStyle.Font.IsBold = true;

// Applying the style to the cell range
range.SetStyle(myStyle);

// Saving the Excel file
workbook.Save(dataDir + "Style.xlsx", SaveFormat.Xlsx);
3

@redred,

Also, we are not entirely sure about your issue. We appreciate if you could create a sample project (runnable), zip it and post us to demonstrate the issue. Moreover, attach some screenshots captured in MS Excel (for the output file(s)) to highlight the different behavior of Aspose.Cells for named styles when comparing the manually created files (having named styles) in MS Excel. This will help us really to understand your issue and we will be looking into it soon.

PS. we also recommend to use latest version of Aspose.Cells.

4

I am doing it the same way. However in Excel - if I click any cell in that rang, it shows that it still has basic style, as Basic style is what has the gray border around it in Styles dropdown. “Mystyle” (custom in your case) is not highlighted - even though that style is created and the cell seems to have same format as I defined. Why wouln’t it show grey border around “MyStyle” instead of “Basic”?

5

@redred,
I am afraid that I could not observe this issue here as it properly highlights the respective style in MS Excel as shown in theattached images. Could you please share which version of MS Excel are you using? Also share the operating system details and images showing issue in your MS Excel.

Custom Style
Custom

Normal Style
Normal

6

cellStyleShowsNormal_should_be_MR_11.jpg (158.4 KB)

Attached Screenshot where I have a cell formatted with style MR_11. However when I select the cell, Style is shown as “Normal” - notice the grey border around “Normal”. I was expecting “MR_11” to have gray border when the cell selected has that style applied using cell.setStyle(mr_11StyleObject).

I am using office 365 Excel (16.0…) 32bit desktop version.

thanks for looking into this.
P.S. Also is there api document that describes what cell.setStyle(newStyle, true) versus false does? What is difference between that method and cell.setStyle(newStyle, styleFlag)?

8

Updated the post.

What I am observing is that if the style is applied immediately after creating it, it shows up highlighted with grey border in styles drowpdown when cell is selected.
However if I create a number of named styles in a loop and then do cell.setStyle(styleCreated) - it looses the association with the named cell style. How do I make this work correctly?
Thank you!

9

@redred,

You may share this sample Excel file and a runnable console application that can be used to reproduce this issue. We will analyze the issue and share our feedback.

We have noted your query and logged a ticket to provide more details about it. You will be notified here once the documents are updated and ready for sharing.

This requirement is logged as:
CELLSJAVA-43424 - More details required in API documentation for Cell.setStyle()

Share your sample Excel file and runnable console application for our testing.

10

@redred,
Please see the details for the APIs:

  1. If you want to set named style, please use Cell.SetStyle(Style) or Range.SetStyle(Style) as the following codes:
    e.g.
    Sample code:

     Workbook workbook = new Workbook();
 Style style = workbook.CreateStyle();
 style.Name = "myStyle";
 style.Font.IsBold = true;
 style.Font.Size = 14;
 Cells cells = workbook.Worksheets[0](https://issue.nanjing.dynabic.com/issues/CELLSJAVA-43424#fn0).Cells;
 Range range = cells.CreateRange("A1:C10");
 range.SetStyle(style);
 cells["D2"].SetStyle(style);
 workbook.Save(dir + "dest.xlsx");

  2. cell.setStyle(newStyle, bool explicitFlag)
    If explicitFlag is false, this method works as Cell.SetStyle(Style) method. Otherwise, only copy the changed attribute of “newStyle” to the cell. The name of the style is ignored though, it’s changed in “newStyle”.

  3. cell.setStyle(newStyle, styleFlag)
    If styleFlag is flag.All, this method works as Cell.SetStyle(Style) method. Otherwise, only copy specified attribute of “newStyle” to the cell, the name of the style is ignored though, it’s changed in “newStyle”.

11

Thank you!! That was the exact issue. Passing second parameter in setStyle() causes the Style name to get ignored, and the cell loses its association with the Name of the style. Thank you for your help in clarifying this issue.

12

@redred,

Good to know that it clarifies you. Feel free to write us back if you have further comments or questions.