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?
@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.
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.
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.
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.
@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
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:
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:
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
The issues you have found earlier (filed as CELLSNET-47816) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao
I’ve tested it again and seems to be working fine now.
The second writeline
Console.WriteLine(ws.Cells.MaxColumn); // print 16383
prints 44 as the first one and we are not experiencing the file size increase either.
It is nice to know that your issue is resolved by the new version/fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon