Apply formatting to a range of cells in .NET

Hi,

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?


Thanks,

Shane Parker



Hi,

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.


For further reference, please check the document:

Thank you.

Thank you for the swift reply.

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 delete all the ranges before saving the spreadsheet, since they will not be needed again?

Hi,

Well, some information is stored in the workbook for the named range your create. You can delete the named ranges if you want.

Thank you.


But Cells.CreateRange(“A6”, “P216”) does not create a named range does it? (where is the name?)

Hi,

Well, it will create the named range when you use: Range.Name property in your code to specify the name of the named range.

Thank you.


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?

Thanks,

Shane

Hi Shane,

Please use:

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.


Thanks for your understanding!

Excellent, that satisfies my needs perfectly - thanks for your patience!

Shane