To apply the same formatting options for cells in your worksheets, you consider creating a new formatting style object (A combination of formatting characteristics, such as font, font size, indentation, number, border, patterns etc. that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.) or utilize an existing one that you can save with your workbook and use whenever you format information with the same attributes.
If you don't format cells, the Normal style (default style for a workbook) will be applied to the cells in your workbook. MS Excel predefines several styles in addition to the Normal style including e.g., Comma, Currency, Percent styles. Aspose.Cells allows to modify any of these styles or any other style that you define with your desired attributes.
Using MS Excel
In MS Excel 97-2003, you can achive this as follows:
- On the Format menu, click Style.
- In the Style name box, select the style you want to modify.
- Click Modify.
- Select the style options that you want using the tabs in the Format Cells dialog box.
- Click OK.
- Under Style includes, specify the style features you want.
- Click OK to save the style and apply it to the selected range.
Using Aspose.Cells
Aspose.Cells provides Style.Update method to update an existing style. For a named style (whether it is created dynamically using Aspose.Cells API or it belongs to Predefined styles list in MS Excel), if you want to change the Style, you may call Style.Update method or otherwise the style of cell / cells range (to whom you have applied formattings) will not be reflected. The Style.Update method behaves like an OK button of the Style dialog box i.e., when you have done your modifications with an existing style, you may call it for the final implementation. So, if you have already applied some style to a range of cell(s), then you modify the style attributes and finally call the method, the style formattings of those cells would be upldated too.
The following examples demonstrate how we can use Style.Update method.
Example1:
In this example, we create a style object, apply the style to a range of cells in the worksheet and finaly modify the style object. The modifications (changes in formatting) are automatically done for the cell and the range in the worksheet on which the style was applied.
[C#]
//Create a workbook.
Workbook workbook = new Workbook();
//Create a new style object.
Style style = workbook.Styles[workbook.Styles.Add()];
//Set the number format.
style.Number = 14;
//Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
//Name the style.
style.Name = "Date1";
//Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
//Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
//Create a range (B1:D1).
Range range = cells.CreateRange("B1", "D1");
//Initialize styleflag struct.
StyleFlag flag = new StyleFlag();
//Set all formatting attributes on.
flag.All = true;
//Apply the style (described above)to the range.
range.ApplyStyle(style, flag);
//Modify the style (described above) and change the font color from red to black.
style.Font.Color = System.Drawing.Color.Black;
//Done! Since the named style (described above) has been set to a cell and range,
//the change would be Reflected(new modification is implemented) to cell(A1) and //range (B1:D1).
style.Update();
//Save the excel file.
workbook.Save(@"d:\test\book_styles.xls");
[VB]
'Create a workbook.
Dim workbook As Workbook = New Workbook
'Create a new style object.
Dim style As Style = workbook.Styles(workbook.Styles.Add())
'Set the number format.
style.Number = 14
'Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red
'Name the style.
style.Name = "Date1"
'Get the first worksheet cells.
Dim cells As Cells = workbook.Worksheets(0).Cells
'Specify the style (described above) to A1 cell.
Cells("A1").SetStyle(style)
'Create a range (B1:D1).
Dim range As Range = cells.CreateRange("B1", "D1")
'Initialize styleflag struct.
Dim flag As StyleFlag = New StyleFlag
'Set all formatting attributes on.
flag.All = True
'Apply the style (described above)to the range.
range.ApplyStyle(style, flag)
'Modify the style (described above) and change the font color from red to black.
style.Font.Color = System.Drawing.Color.Black
'Done! Since the named style (described above) has been set to a cell and range,
'the change would be Reflected(new modification is implemented) to cell(A1) and 'range (B1:D1).
style.Update()
'Save the excel file.
workbook.Save("d:\test\book_styles.xls")
Example2:
In this example, we utilized a simple template excel file in which we have already applied Percent named style to a range. We get the style, create a style object and modify some style formattings. The modifications (changes in formatting) are automatically done for the range on which the style was applied.
[C#]
//Create a workbook.
Workbook workbook = new Workbook();
//Open a template file.
//In the book1.xls file, we have applied Ms Excel's
//Named style i.e., "Percent" to the range "A1:C8".
workbook.Open(@"F:\FileTemp\book1.xls");
//We get the Percent style and create a style object.
Style style = workbook.Styles["Percent"];
//Change the number format to "0.00%".
style.Number = 10;
//Set the font color.
style.Font.Color = System.Drawing.Color.Red;
//Update the style. so, the style of range "A1:C8" will be changed too.
style.Update();
//Save the excel file.
workbook.Save(@"F:\FileTemp\book2.xls");
[VB]
'Create a workbook.
Dim workbook As Workbook = New Workbook
'Open a template file.
'In the book1.xls file, we have applied Ms Excel's
'Named style i.e., "Percent" to the range "A1:C8".
workbook.Open("F:\FileTemp\book1.xls")
'We get the Percent style and create a style object.
Dim style As Style = workbook.Styles("Percent")
'Change the number format to "0.00%".
style.Number = 10
'Set the font color.
style.Font.Color = System.Drawing.Color.Red
'Update the style. so, the style of range "A1:C8" will be changed too.
style.Update()
'Save the excel file.
workbook.Save("F:\FileTemp\book2.xls")