MiniExcel
MiniExcel copied to clipboard
mini-software
Feature: Adding Conditional formatting
Excel Type
- [ x ] XLSX
- [ ] XLSM
- [ ] CSV
- [ ] OTHER
Description
I really like the idea of this libary and I decided to use it at work, but I could only add conditional formatting by using templates, which is not that dynamic.
So I looked at the source code and figured that this is actually something that can be implemented. My plan involves multiple things
Part 1 Fluent api instead of Dynamic Columns
Adding the option to use method chaining to configure the Spreadsheet. This can be used alongside the attribute based configuration. The reason for adding method chaining, is because I think it is more familiar syntax for .NET developers (for example EF Core, FluentValidation). This method chaining solution would replace and thus we would slowly depreciate the current Dynamic Excel sheet option, but it would allow a better, more natural way of configuring Conditional formatting. Would look like this after migrating from Dynamic Excel sheet/columns: Before (Dynamic API)
var config = new OpenXmlConfiguration
{
DynamicColumns = new[]
{
new DynamicExcelColumn("id") ,
new DynamicExcelColumn("name") { Name = "Full Name", Width = 20 },
},
DynamicSheets = new[]
{
new DynamicExcelSheet("Users") { Name = "Employee Data", State = SheetState.Hidden }
}
};
After (Fluent Api)
var config = new OpenXmlConfiguration()
.ConfigureSheet<Employee>( s => s
.HasName("Employee Data")
.IsHidden()
.ConfigureColumns(c => c
.Property(x => x.Name).HasColumnName("Full Name").HasWidth(20)
)
);
Now technically speaking, it would be possible to keep the current DynamicSheet, DynamicColumns arrays. And I could even make it so that the fluentApi gets translated into those Dynamic arrays. But my issue with that is that, in that case we would have 3 different ways of configuring and that has to be maintained. I much rather keep just 2 (the attributes and the fluent api)
Part 2 Adding Column specific information to OpenXmlConfiguration
So basically like the title said, I would like to have a single source of truth for this to work, and the current OpenXmlConfiguration is a good candidate. It could have all of the information about the columns and what kind of attributes were, the sheets and those configs. the configuration of those would be there. This would make it easier in the future for us. I have 2 scenarios:
Options 1:
When calling SaveAs("excelSheetname", values, sheets, configuration), it would add the values and sheets information to the config. so configuration would know how many columns there are, which columns are in which sheet and thus we have a single source of truth, which is in my opinion necessary for future expansion.
Option 2: this would be a bit more radical, I prefer this, but it would change the structure more
Instead of passing values, sheets seperately to SaveAs, we would add it to configuration. So:
var users = new[] { new { Name = "Jack", Age = 25 }, new { Name = "Mike", Age = 44 } };
var department = new[] { new { ID = "01", Name = "HR" }, new { ID = "02", Name = "IT" } };
var sheets = new Dictionary<string, object>
{
["users"] = users,
["department"] = department
};
new OpenXmlConfiguration{
Sheets = sheets
}
This would keep things nice and centralised, and would help me greatly with my next part.
Part 3 Adding Conditional formatting
The reason why I wanted to add fluent api because it would be much easier to configure conditional formatting that way. Like this:
.ConfigureColumns(c => c
.Property(x => x.OrderStatus)
.WithConditionalFormat()
.WhenValue().Equals("Cancelled")
.WithBackground(Color.Red)
.WithFont(color: Color.White)
.WhenValue().Equals("Pending")
.WithBackground(Color.Yellow)
)
A number of operators could be used (the supported ones) so GreaterThan, LessThan, later on even formulas could be introduced. Obviously not only method chaining would be possible. and it could be used like this and would be translated into this under the hood:
var config = new OpenXmlConfiguration()
.ConfigureSheet<Employee>(s => s
.ConfigureColumns(c => c
.Property(x => x.Salary)
.WithConditionalFormat(new ConditionalFormat
{
Conditions = new[]
{
new Condition
{
Rule = ConditionRule.GreaterThan(100),
Format = new Format
{
Background = Color.LightGreen,
FontWeight = FontWeight.Bold
}
},
new Condition
{
Rule = ConditionRule.LessThan(50),
Format = new Format
{
Background = Color.Pink,
FontStyle = FontStyle.Italic
}
}
}
})
)
);
this would allow it to be used like this as well:
var redAlertFormat = new Format {
Background = Color.Red,
FontWeight = FontWeight.Bold
};
// Later...
.WhenValue().Equals("URGENT").WithFormat(redAlertFormat)
The great thing about this is that we would allow it to be used in a very flexible way, while because all of this would be translated into the same thing we would barely get any maintanence issues. This Format can even be used for basic, non conditional formatting as well. But I would go even further
Part 4 Making conditional formatting work with other columns
This one will be tricky part. I want to make sure that conditional formatting can work by referencing other columns. So how would I go about this? A constant value is pretty straighforward it can be placed into the xml without any issue. If we want to reference an other column, then we gotta use a formula for it. This is what I'm talking about btw:
var config = new OpenXmlConfiguration()
.ConfigureSheet<Employee>(s => s
.ConfigureColumns(c => c
.Property(x => x.Performance)
.WithConditionalFormat()
.WhenValue().GreaterThan(x => x.MinimumPerformance)
.WithBackground(Color.LightGreen)
.WithBold()
.WhenValue().LessThan(x => x.MinimumPerformance)
.WithBackground(Color.Red)
.WithItalic()
)
);
And here's how I would do it:
Scenario 1 - The column reference (MinimumPerformance in the example) is a column that is included in the sheet:
This one is easier, but this is why I wanted to include the columns infos into the OpenXmlConfig. This way we can easily look up which column has which index, thus we can point the condition to the reference column easily.
Scenario 2 - The column reference is not included in the sheet
This can happen when the reference column isn't included in the sheet. The developer used [ExcelIgnore] or .Ignore() in fluent Api. This would mean that we won't write that reference into the sheet. So in this case what I think would be the best is to include that column, but in another hidden sheet that can be used for calculations. That hidden sheet would have the column with all the values and the conditionalFormatting could reference that. Yep this would come with an overhead, because of writing a seperate sheet, so I think this is something that has to be enabled explicitly in the configuration, so the develoeper will know exactly what's about to happen.
Part 5 - future ideas
-
The conditional formatting could even be taken further and we could have instead of
.GreaterThan,.LessThan. We could use a Linq-like.Where()method and translate the operation inside the.Where()into a formule if possible, automatically. -
Other than for columns we can do the same thing for the Rows. so it would format a row with a specific index for example.
Summary
Now I know this was pretty detailed issue, I seriously would like to contribute and do all of the things I wrote down. But I'm not sure whether there is a need for this, maybe the MiniExcel package isn't meant for my plans. I would obviously do my best to make sure that the package uses as little memory as possible, and as fast as possible. I would run the benchmarks and optimise if necessary or abort if the features would put too much strain on the package. I'm curios about your opinions and whether you need this and have some guidelines that I should follow. I would do all of these features anyway , because for my work it would be useful, but I would be more happy if I can share it with everyone else who uses this package as well.
@hegedus-mark Thank you for the feedback and the very detailed proposal, it's very interesting and I personally think it would definitely be a step in the right direction for the library to integrate a fluent api / builder pattern in the way you have described. As things currently stand though we are focusing on other goals, so I don't think this is likely to be worked on in the near future, but it's certainly something we will consider when a refactoring opportunity arises. Regarding the conditional formatting, it's something that's been asked before, but also something that in my opinion goes a bit beyond the core scope of the library, so even though there's already some foundations laid for it and we might deliver something relatively soon, it will be a while before we get to implement a full fledged version. Thanks again for your feedback, and if you do end up working on these aspects by yourself please feel free to share, every contribution is always appreciated!
Excel Type
- [ x ] XLSX* [ ] XLSM* [ ] CSV* [ ] OTHER
Description
I really like the idea of this libary and I decided to use it at work, but I could only add conditional formatting by using templates, which is not that dynamic.
So I looked at the source code and figured that this is actually something that can be implemented. My plan involves multiple things
Part 1 Fluent api instead of Dynamic Columns
Adding the option to use method chaining to configure the Spreadsheet. This can be used alongside the attribute based configuration. The reason for adding method chaining, is because I think it is more familiar syntax for .NET developers (for example EF Core, FluentValidation). This method chaining solution would replace and thus we would slowly depreciate the current Dynamic Excel sheet option, but it would allow a better, more natural way of configuring Conditional formatting. Would look like this after migrating from Dynamic Excel sheet/columns: Before (Dynamic API)
var config = new OpenXmlConfiguration { DynamicColumns = new[] { new DynamicExcelColumn("id") , new DynamicExcelColumn("name") { Name = "Full Name", Width = 20 }, }, DynamicSheets = new[] { new DynamicExcelSheet("Users") { Name = "Employee Data", State = SheetState.Hidden } } }; After (Fluent Api)
var config = new OpenXmlConfiguration() .ConfigureSheet<Employee>( s => s .HasName("Employee Data") .IsHidden() .ConfigureColumns(c => c .Property(x => x.Name).HasColumnName("Full Name").HasWidth(20) ) ); Now technically speaking, it would be possible to keep the current DynamicSheet, DynamicColumns arrays. And I could even make it so that the fluentApi gets translated into those Dynamic arrays. But my issue with that is that, in that case we would have 3 different ways of configuring and that has to be maintained. I much rather keep just 2 (the attributes and the fluent api)
Part 2 Adding Column specific information to OpenXmlConfiguration
So basically like the title said, I would like to have a single source of truth for this to work, and the current OpenXmlConfiguration is a good candidate. It could have all of the information about the columns and what kind of attributes were, the sheets and those configs. the configuration of those would be there. This would make it easier in the future for us. I have 2 scenarios:
Options 1: When calling
SaveAs("excelSheetname", values, sheets, configuration), it would add the values and sheets information to the config. so configuration would know how many columns there are, which columns are in which sheet and thus we have a single source of truth, which is in my opinion necessary for future expansion.Option 2: this would be a bit more radical, I prefer this, but it would change the structure more
Instead of passing values, sheets seperately to SaveAs, we would add it to configuration. So:
var users = new[] { new { Name = "Jack", Age = 25 }, new { Name = "Mike", Age = 44 } }; var department = new[] { new { ID = "01", Name = "HR" }, new { ID = "02", Name = "IT" } }; var sheets = new Dictionary<string, object> { ["users"] = users, ["department"] = department };
new OpenXmlConfiguration{ Sheets = sheets } This would keep things nice and centralised, and would help me greatly with my next part.
Part 3 Adding Conditional formatting
The reason why I wanted to add fluent api because it would be much easier to configure conditional formatting that way. Like this:
.ConfigureColumns(c => c .Property(x => x.OrderStatus) .WithConditionalFormat() .WhenValue().Equals("Cancelled") .WithBackground(Color.Red) .WithFont(color: Color.White) .WhenValue().Equals("Pending") .WithBackground(Color.Yellow) ) A number of operators could be used (the supported ones) so GreaterThan, LessThan, later on even formulas could be introduced. Obviously not only method chaining would be possible. and it could be used like this and would be translated into this under the hood:
var config = new OpenXmlConfiguration() .ConfigureSheet<Employee>(s => s .ConfigureColumns(c => c .Property(x => x.Salary) .WithConditionalFormat(new ConditionalFormat { Conditions = new[] { new Condition { Rule = ConditionRule.GreaterThan(100), Format = new Format { Background = Color.LightGreen, FontWeight = FontWeight.Bold } }, new Condition { Rule = ConditionRule.LessThan(50), Format = new Format { Background = Color.Pink, FontStyle = FontStyle.Italic } } } }) ) ); this would allow it to be used like this as well:
var redAlertFormat = new Format { Background = Color.Red, FontWeight = FontWeight.Bold };
// Later... .WhenValue().Equals("URGENT").WithFormat(redAlertFormat) The great thing about this is that we would allow it to be used in a very flexible way, while because all of this would be translated into the same thing we would barely get any maintanence issues. This Format can even be used for basic, non conditional formatting as well. But I would go even further
Part 4 Making conditional formatting work with other columns
This one will be tricky part. I want to make sure that conditional formatting can work by referencing other columns. So how would I go about this? A constant value is pretty straighforward it can be placed into the xml without any issue. If we want to reference an other column, then we gotta use a formula for it. This is what I'm talking about btw:
var config = new OpenXmlConfiguration() .ConfigureSheet<Employee>(s => s .ConfigureColumns(c => c .Property(x => x.Performance) .WithConditionalFormat() .WhenValue().GreaterThan(x => x.MinimumPerformance) .WithBackground(Color.LightGreen) .WithBold() .WhenValue().LessThan(x => x.MinimumPerformance) .WithBackground(Color.Red) .WithItalic() ) ); And here's how I would do it:
Scenario 1 - The column reference (MinimumPerformance in the example) is a column that is included in the sheet:
This one is easier, but this is why I wanted to include the columns infos into the OpenXmlConfig. This way we can easily look up which column has which index, thus we can point the condition to the reference column easily.
Scenario 2 - The column reference is not included in the sheet
This can happen when the reference column isn't included in the sheet. The developer used [ExcelIgnore] or .Ignore() in fluent Api. This would mean that we won't write that reference into the sheet. So in this case what I think would be the best is to include that column, but in another hidden sheet that can be used for calculations. That hidden sheet would have the column with all the values and the conditionalFormatting could reference that. Yep this would come with an overhead, because of writing a seperate sheet, so I think this is something that has to be enabled explicitly in the configuration, so the develoeper will know exactly what's about to happen.
Part 5 - future ideas
- The conditional formatting could even be taken further and we could have instead of
.GreaterThan,.LessThan. We could use a Linq-like.Where()method and translate the operation inside the.Where()into a formule if possible, automatically.- Other than for columns we can do the same thing for the Rows. so it would format a row with a specific index for example.
Summary
Now I know this was pretty detailed issue, I seriously would like to contribute and do all of the things I wrote down. But I'm not sure whether there is a need for this, maybe the MiniExcel package isn't meant for my plans. I would obviously do my best to make sure that the package uses as little memory as possible, and as fast as possible. I would run the benchmarks and optimise if necessary or abort if the features would put too much strain on the package. I'm curios about your opinions and whether you need this and have some guidelines that I should follow. I would do all of these features anyway , because for my work it would be useful, but I would be more happy if I can share it with everyone else who uses this package as well.
Very exciting! Can't wait to see it in action!
Thanks!
