What’s the best way to apply a style to multiple worksheets? The method I’ve been using is to manually create a range for each worksheet but I need to do this dynamically as the number of worksheets will vary. Can I dynamically create ranges?
Thanks
I am not very clear about your request.
1. Do you want to change the default style for all cells? If yes, please try Excel.DefaultStyle property.
2. If you want to change the style of ranges in several worksheets. You can access those worksheets and create ranges in them. Then you can set style for those ranges.
Below is an example of how I set the style of a specific range of cells. This requires me to create the range (eRange1) manually. Since I need to generate the worksheets dynamically I also need a way to set the style as show below dynamically. Any ideas?
int styleIndex1 = excel.Styles.Add();
Aspose.Excel.Style style1 = excel.Styles[styleIndex1];
Range eRange1 = sheets[intSheetIndex].Cells.CreateRange(0,(byte)0,0,6);
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.LeftBorder].Color = Color.Black;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.TopBorder].Color = Color.Black;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.RightBorder].Color = Color.Black;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.BottomBorder].Color = Color.Black;
style1.Font.IsBold=true;
style1.ForegroundColor = Color.Gainsboro;
style1.Pattern = BackgroundType.Solid;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.VerticalAlignment = TextAlignmentType.Center;
eRange1.Style = style1;
How about these code?
int styleIndex1 = excel.Styles.Add();
Aspose.Excel.Style style1 = excel.Styles[styleIndex1];
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.LeftBorder].Color = Color.Black;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.TopBorder].Color = Color.Black;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.RightBorder].Color = Color.Black;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
style1.Borders[BorderType.BottomBorder].Color = Color.Black;
style1.Font.IsBold=true;
style1.ForegroundColor = Color.Gainsboro;
style1.Pattern = BackgroundType.Solid;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.VerticalAlignment = TextAlignmentType.Center;
for(int i = 0; i < sheets.Count; i ++)
{
Range eRange1 = sheets[ i ].Cells.CreateRange(0,(byte)0,0,6);
eRange1.Style = style1;
}
Your example works perfectly! Thanks!
@heathde,
Aspose.Cells has replaced Aspose.Excel that is no more available now. This new product contains advanced features for working with ranges. You can create, access and copy named ranges along with setting formula for them. Ranges can be combined by their union and formatting is also possible. Here is a list of articles and runnable examples that can be used to work with ranges using Aspose.Cells.
- Create Access and Copy Named Ranges
- Format and Modify Named Ranges
- Setting Formula for Named Range
- Preserve Single Quote Prefix of Cell Value or Range
- Get Address Cell Count Offset Entire Column and Entire Row of the Range
- Create Union Range
A free latest version of this new product can be downloaded here:
Aspose.Cells for .NET (Latest Version)
A runnable solution can be downloaded here which can be used for testing the features of this new product without writing any code.