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");