CODEWITHSUNDEEP
c#excelclosedxml
AleV
AleV

Reputation: 345

ClosedXML: formatting and VBA code are not preserved

I'm trying to find an easy way to create an excel report using C# on a server (where Excel is not installed). I understand that there are several options (OpenXML, EEPLUS), I'm trying to use ClosedXML. My process is simple:

  1. First I manually create an Excel file, which is used as a template (with tables, pivot tables and some formatting)
  2. I use ClosedXML just for opening this template and filling the tables with data
  3. Optionally the template file could have some VBA code (but I don't need to modify it on the server)

ClosedXML actually fills my tables, but I'm facing several issues: they are too basic, I missing something for sure.

using ClosedXML.Excel;

// tpt.xlsm is a macro enabled workbook
// this workbook contains a single worksheet ("tpt"): 
// this worksheet only contains a table ("T_1") and a linked pivot table ("PT_report")
// this workbook also contains a VBA module ("Module1") where it is defined a test macro

// 'Module1 content
// Sub helloWorld()
//     MsgBox "hello world!"
// End Sub

using (var workbook = XLWorkbook.OpenFromTemplate("D:\\tmp\\closedXML\\tpt.xlsm"))
{

    var worksheet = workbook.Worksheets.Worksheet("tpt");
    var t_1 = worksheet.Tables.Table("T_1");
    var newData = new List<Person>(){
          new Person(23,"person 1")
        , new Person(24,"person 2")
        , new Person(25,"person 3")};

    t_1.ReplaceData(newData, propagateExtraColumns: true);

    workbook.SaveAs("D:\\tmp\\closedXML\\generatedReport.xlsm");
}
record Person(int age, string Name);

When I run the code above, I encounter several issues:

  1. the VBA module gets lost in the generated generatedReport.xlsm file
  2. the style of the table ("T_1") present in the "tpt" worksheet is not preserved (I'm using a standard color scheme in tpt.xlsm)
  3. the conditional formatting present in the PivotTable is lost

I'm using Microsoft 365 (Version 2301, Build 16130.20394) to generate the template. To run the code .net7 and ClosedXML Version="0.101.0"

Upvotes: 0

Views: 914

Answers (1)

AleV
AleV

Reputation: 345

After following Francois Botha suggestion I have opened 2 issues on the GitHub repo: I was using the wrong method for opening the file: instead of using (var workbook = XLWorkbook.OpenFromTemplate(tpt_m)) I should have written using (var workbook = new XLWorkbook(tpt_m)) (please refer to https://github.com/ClosedXML/ClosedXML/issues/2077 ).

Now the code works as expected, except for the last issue (Pivot Table conditional formatting gets lost), that should be addressed in a future release of ClosedXML (https://github.com/ClosedXML/ClosedXML/issues/2075)

Upvotes: 0

Related Questions