Consider allowing access to ClosedXML worksheet from ExcelWriter

[cocowalla]

I just opened a feature request, #3, requesting support for data validations.

As something else for considersation, I was wondering what you thought about permitting access to the active ClosedXML worksheet from an ExcelWriter? The motivation here would be to allow access to the full range of ClosedXML Excel-writing features, even if ExcelWriter hasn't (or hasn't yet) added support for such features within it's own API (such as data validations from #3).

using (var excel = new ExcelWriter(stream))
{
    // Returns ExcelWriter._sheet
    var worksheet = excel.GetWorksheet();
}

There is of course a risk that users would try to access the worksheet after the ExcelWriter has been disposed, but I'd consider this an "advanced" feature that users would only use for things not yet supported by ExcelWriter. I personally consider the risk small, and not different to any other faced when using streams, but I guess you could the method something like DangerousGetWorksheet if you see it differently.

Thanks again for this great library 😃

[kendallb]

As mentioned in the other thread we use the C1 library (commercial library) so I made this tool work in such a way that it abstracts the differences across the libraries so the Excel engine can be plugged into any reading/writing library. So I can’t really expose it via a strongly typed link in a generic fashion. So it would have to be something conditionally compiled just for that version, or perhaps it is returned as an object and the user needs to cast it to the correct type?

[cocowalla]

Hmm, returning a castable object feels a bit icky, but it's certainly simple, and would certainly work 😄

An alternative would be to offer 2 additional NuGet libraries, something like ExcelReader.ClosedXML and ExcelReader.C1, introduced specialised ClosedXMLExcelWriter/C1ExcelWriter classes that extend from the main ExcelWriter, or somehow otherwise added ClosedXML/C1 methods (like adding an extension method that simply does the cast for you), but these ways are obviously much more work.

[kendallb]

Yeah a castable would make it available for both versions, but it's a leaky abstraction. Simplest solution would be to have a conditional compilation around it so that when building for C1 it returns the internal C1 sheet, and for CloseXML the ClosedXML one. Kinda messy but it would work.

Do you still need this now that you can make your own converters? I generally take the approach of trying to not put anything specific in the API related to the underlying Excel library, and add more functionality to the public API to support what I need (like I just added alignment support to the WriteCell function to directly write to the Excel file).

[cocowalla]

I generally take the approach of trying to not put anything specific in the API related to the underlying Excel library, and add more functionality to the public API to support what I need

I think this is a good approach too, especially know I know ExcelHelper fronts 2 different backend libraries.

Do you still need this now that you can make your own converters?

I saw yoru comment over in #3 - appreciate it! I'll try this out tomorrow and see how I go.