Free Support Forum - aspose.com

File size and MaxColumn of the workbook is increased after setting outline border

Hi,

I noticed when I run Range.SetOutlineBorder(), the MaxColumn is increased from 44 to 16383 and the file size is enlarged by 100%.

Code:

var workbook = new Workbook(@".\test.xlsx"); // max column in the original file is AT, file size 53KB
var ws = workbook.Worksheets[0];
var row = ws.Cells.CreateRange(“21:21”);
Console.WriteLine(ws.Cells.MaxColumn); // print 44
row.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.None, Color.Black);
Console.WriteLine(ws.Cells.MaxColumn); // print 16383
workbook.Save(@".\output.xlsx"); // max column in the output is changed to XFD, file size 97KB

This causes a problem because we do not intend to change the MaxColumn value and we rely on it for some rendering/printing (where we couldn’t use MaxDataColumn), and the file size increase is also a concern.

I’ve attached the test file and the output for your reference. Could you please help me check?

test.zip (98.0 KB)

Thanks,

@ServerSide527,
It seems to be expected behavior as ws.Cells.MaxColumn returns maximum column index which contains data or style. Here a range is created, which contains 16384 columns and setting the outline border of this range sets the style of the last column’s cell. Hence when ws.Cells.MaxColumn is called it returns 16383 which is the index of the last cell having a style.

Hope it clarifies the scenario.

@ahsaniqbalsidiqui thanks for your reply.

I actually compared the behaviour in MS Excel before raising this issue. In Excel, when you select the row and you can apply the borders on all the cells in the row, without creating extra columns. Which means the horizontal scrollbar is not changed, size is not increased, and the maximum column appears in Excel is not changed.

Since Aspose.Cells mimics Excel behaviour, I think applying the whole row should not change those properties as MS Excel doesn’t do so.

@ServerSide527,
We have logged the issue as “CELLSNET-47816” in our database for investigations. Once we will have some news for you, we will update you in this topic.

Thank you @ahsaniqbalsidiqui

I saw the ticket status is ‘Won’t fix’. Could you help me clarify this status, is this issue going to be fixed?

I’ve also attached the equivalent operation in MS Excel to add outline borders, where it does not increase the max column or size.

record.gif (565.5 KB)

Thanks,

@ServerSide527,

Yes, your issue will not be fixed as it is not an issue with the APIs. Please note, the corresponding API to your described operation is Row.ApplyStyle(Style,StyleFlag). So, please try to use this method to apply the border instead of setting border to a range of cells.

Hope, this helps a bit.

Hi @Amjad_Sahi

I don’t find a way to create an outline border style with Row.ApplyStyle(), could you provide an example of the equivalent of

row.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Double, Color.Black);

using Row.ApplyStyle()?

Note that outline border is not the same as the border style on a cell, also even if I used Range.ApplyStyle() (which doesn’t seem to provide outline style option), it still creates multiple empty columns and increased size.

Again please refer to the recording in my previous comments, I would like to mimic that Office behaviour in Aspose, where in MS Excel, setting an outline doesn’t give you extra columns or increased file sizes.

Thanks

@ServerSide527,
Please give a try to the following sample code and share the feedback.

var workbook = new Workbook(@"test.xlsx"); // max column in the original file is AT, file size 53KB
var ws = workbook.Worksheets[0];
//var range = ws.Cells.CreateRange("21:21");
Console.WriteLine($"Cells.MaxColumn = {ws.Cells.MaxColumn}"); // print 44
Style style = workbook.CreateStyle();
style.SetBorder(BorderType.BottomBorder, CellBorderType.None, Color.Black);
Row row = ws.Cells.Rows[21];
StyleFlag flag = new StyleFlag();
flag.Borders = true;
row.ApplyStyle(style, flag);
Console.WriteLine(ws.Cells.MaxColumn); // print 44
workbook.Save(@"output_20.12.7.xlsx"); // max column in the output is changed to XFD, file size 97KB

Hi,

Thanks for your suggestions.

I would like to reiterate that outline border is fundamentally different than cell style border. Please try the following two code blocks and you may understand more about the difference on the two APIs:

Style style = workbook.CreateStyle();
style.SetBorder(BorderType.LeftBorder, CellBorderType.Double, Color.Black);
style.SetBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);
Row row = ws.Cells.Rows[21];
StyleFlag flag = new StyleFlag();
flag.Borders = true;
row.ApplyStyle(style, flag);

var row = ws.Cells.CreateRange(“21:21”);
row.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Double, Color.Black);
row.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);

Code block 1 is the solution you proposed and code block 2 is to mimic the MS Excel ‘outline border’ settings.

The results are very different because code block 1 would add the left borders to every single cell while code block 2 will only add it to the leftmost cell of the range. Please see the screenshot for the difference:

image.png (54.5 KB)

Please also find the test files with the outputs from both codes:

test files.zip (148.7 KB)

Thanks,

@ServerSide527,
We have observed the difference and logged it with the ticket. We will share our feedback after further investigation.