facebooktwittermenuarrow-up

GemBox Support Forum

Bottom border disappears if rows group is collapsed

Hi,

I’m trying to setup outline levels for rows with horizontal borders (top and bottom) and there is a problem with collapsing rows groups - the bottom border of the next row above the collapsed groups disappears.

It seems like a bug, because it works in package version 37 but doesn’t work starting from version 39.

Could you help me with that?

Code:

SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Conditional Formatting");

worksheet.ViewOptions.ShowGridLines = false;

int rowCount = 20;

// Specify sheet formatting.
worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
worksheet.Columns[0].Width = 5000;
worksheet.Columns[0].Width = 5000;
worksheet.Columns[1].Width = 5000;
worksheet.Columns[2].Width = 5000;
worksheet.Columns[3].Width = 5000;
worksheet.Columns[3].Style.NumberFormat = "[$$-409]#,##0.00";
worksheet.Columns[4].Width = 5000;
worksheet.Columns[4].Style.NumberFormat = "yyyy-mm-dd";

var cells = worksheet.Cells;

// Specify header row.
cells[0, 0].Value = "Departments";
cells[0, 1].Value = "Names";
cells[0, 2].Value = "Years of Service";
cells[0, 3].Value = "Salaries";
cells[0, 4].Value = "Deadlines";

// Insert random data to sheet.
var random = new Random();
var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };

for (int i = 0; i < rowCount; ++i)
{
    cells[i + 1, 0].Value = departments[random.Next(departments.Length)];
    cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString();
    cells[i + 1, 2].Value = random.Next(1, 31);
    cells[i + 1, 3].Value = random.Next(10, 101) * 100;
    cells[i + 1, 4].Value = DateTime.Now.AddDays(random.Next(-1, 2));

    var borderColor = Color.FromArgb(166, 166, 166);

    cells[i + 1, 0].Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);
    cells[i + 1, 1].Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);
    cells[i + 1, 2].Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);
    cells[i + 1, 3].Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);
    cells[i + 1, 4].Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);

    int outlineLevel = i / 4 + 1;

    worksheet.Rows[i + 1].OutlineLevel = outlineLevel;

    if (outlineLevel > 3)
    {
        worksheet.Rows[i + 1].Hidden = true;
        worksheet.Rows[i + 1].Collapsed = true;
    }
}

workbook.Save("Conditional Formatting.xlsx");

Hi Oleksii,

The difference occurred because of border resolution that was introduced together with some other memory optimizations, see:

In short, the problem is that by setting the top border of some cell, the border resolution will remove the bottom border of the top neighboring cell.

To resolve this, can you try using the following:

var range = cells.GetSubrangeRelative(i + 1, 0, 5, 1);
if (i == 0)
    range.Style.Borders.SetBorders(MultipleBorders.Horizontal, borderColor, LineStyle.Thin);
else
    range.Style.Borders.SetBorders(MultipleBorders.Bottom, borderColor, LineStyle.Thin);

Does this solve your issue?

Regards,
Mario

Yes, it works, thanks.