Is there any way to set the formatting of more than one cell at once, similar
to using the properties of the Range object in the Excel Interop?
Specifically:
1) I am trying to set the width of a range of columns
without having to loop through each column and set its width individually.
2) Can I set a large range of cells to bold or
set the borders of all the cells in a range at once?
3) Also, I am hiding ranges of rows and columns - I see the HideRows method, but no
matching HideColumns method. Is there currently any method that allows the
hiding of multiple columns?
Yes, we do provide Aspose.Cells.Range object for your desired tasks. It has variety of methods, attributes to apply formatting for a range of cells.
See the sample code for your reference: Sample code:
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
//Create a range of cells.
Range range = cells.CreateRange(“A6”, “P216”);
//Declare style.
Style stl;
//Create the style adding to the style collection.
stl = workbook.Styles[workbook.Styles.Add()];
//Specify the font settings.
stl.Font.Name = “Arial”;
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
//Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
//Create the style flag object and specify/on your desired formatting.
StyleFlag flag = new StyleFlag();
flag.Font = true;
flag.Borders = true;
<b>//Apply the style with format setttings to the range.
range.ApplyStyle(stl, flag);</b>
**//Each column in the range would be with extended/modified width.**
**range.ColumnWidth = 12;**
//Save the excel file.
workbook.Save(@“e:\test\rangeofcells.xls”);
For your queries:
1) See the sample code above. We provide Range.ColumnWidth for it. 2) See the sample code above for your reference. 3) Please use Cells.HideColumns to hide multiple columns in a worksheet. We have also Cells.UnhideColumns method too. I think you might be using some older version, so, please try the attached latest version/fix for your need.
When you use CreateRange does this actually add anything to
the saved spreadsheet?
If for example I use CreateRange 1000 times to format various
parts of a spreadsheet, is it good practice to then deleteall the ranges before
saving the spreadsheet, since they will not be needed again?
I see - then perhaps if I do not name my range, it does not persist beyond my code…?
When I experimented with this, I created some named and unnamed ranges, and saved the workbook.
However, when I re-opened the workbook, the Worksheet.Cells.Ranges collection was empty- neither the named nor unnamed ranged showed up, although I open the sheet in Excel, you can see the named ranges that I had created.
Is this something that Aspose Cells does not currently support?
Range[] ranges = workbook.Worksheets.GetNamedRanges(); int totalranges = ranges.Length;
It will extract workbook (global) and worksheet specific named ranges. For your information Worksheet.Cells.Ranges is always a temporary list, so it should not be used.
Moreover, if the Range.Name is specified in the code, the component will store the range to Defined Names list (you may use Worksheets.Names to get them), so you can get them by Worksheets.GetNamedRanges() method.