Pivot table could not work

[Fox2726]

Everything is fine in flat table. After we try to add <> to the same template file and set up column define like below picture. But it not works.

When we run in the process.
System had a error in Saveas. And it show "source" parameter could not be null.

error message as below
於 System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
於 ClosedXML.Excel.XLWorkbook.GeneratePivotTableCacheDefinitionPartContent(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt, SaveContext context)
於 ClosedXML.Excel.XLWorkbook.GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context)
於 ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document, SaveOptions options)
於 ClosedXML.Excel.XLWorkbook.CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, SaveOptions options)
於 ClosedXML.Excel.XLWorkbook.SaveAs(String file, SaveOptions options)
於 ClosedXML.Excel.XLWorkbook.SaveAs(String file, Boolean validate, Boolean evaluateFormulae)
於 ClosedXML.Excel.XLWorkbook.SaveAs(String file)
於 ClosedXML.Report.XLTemplate.SaveAs(String file)

Thanks in advance.

[ming-me]

Same trouble with pivot at try call SaveAs().

               using (var memory = new MemoryStream())
                {
                    template.SaveAs(memory);
                    memory.Position = 0;

                    var response = new XmlReportResponse
                    {
                        File = memory.ToArray()
                    };
                    return response;
                }

Значение не может быть неопределенным.
Имя параметра: source

в System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
в ClosedXML.Excel.XLWorkbook.GeneratePivotTableCacheDefinitionPartContent(PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt, SaveContext context)
в ClosedXML.Excel.XLWorkbook.GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context)
в ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document, SaveOptions options)
в ClosedXML.Excel.XLWorkbook.CreatePackage(Stream stream, Boolean newStream, SpreadsheetDocumentType spreadsheetDocumentType, SaveOptions options)
в ClosedXML.Excel.XLWorkbook.SaveAs(Stream stream, SaveOptions options)
в ClosedXML.Excel.XLWorkbook.SaveAs(Stream stream, Boolean validate, Boolean evaluateFormulae)
в ClosedXML.Excel.XLWorkbook.SaveAs(Stream stream)
в ClosedXML.Report.XLTemplate.SaveAs(Stream stream)

[b0bi79]

@Fox2726 @MiNGRotten Can you send your template and sample code so that I can repeat this issue? Tests with Pivot available in ClosedXML.Report work as it should.

[Fox2726]

    public void Report(string file_path)
    {
        string outputFile = file_path + "\\xxx.xlsx";
        var template = new XLTemplate(file_path+ "\\tPivot1.xlsx");
        try
        {

            Customer cust = new Customer();
            //cust.CustNo = 1;
            //cust.Company = "Company ABC";
            List<order> orders = new List<order>();
            for (int i = 0; i < 10; i++)
            {
                order myorder = new order();
                myorder.OrderNo = i + 1;
                //myorder.ShipToAddr1 = "AA " + i;
                myorder.AmountPaid = i;
                orders.Add(myorder);

            }
            cust.Orders = orders;
            template.AddVariable(new { orders = orders });
            template.Generate();
            template.SaveAs(outputFile);

        }
        catch(Exception ex)
        {

        }
    }

Here is the sample code for test. We use the same template file "tPivot1.xlsx" in test project.
tPivot1.xlsx

[ming-me]

public byte[] GenerateXmlReport(XmlReportGeneratorRequestDTO data)
{
    string templateName = data.TemplateName ?? throw new ArgumentNullException("Отсутствует имя шаблона.");

    var dataSets = new Dictionary<string, object[]>();

    if (data.Data == null || data.Data.Count == 0) throw new ArgumentNullException("Нет данных.");

    foreach (var item in data.Data.Select((d, i) => new { d, i }))
    {
        dataSets.Add($"data{item.i}", item.d.ToArray());
    }

    var template = FindAndLoadTemplate(templateName);

    foreach (var ds in dataSets)
    {
        template.AddVariable(ds.Key, ds.Value);
    }

    var res = template.Generate();

    if (res.HasErrors == true)
    {
        throw new Exception("Во время генерерации отчёта произошла ошибка. Проверьте корректность шаблона.");
    }

    using (var memory = new MemoryStream())
    {
        template.SaveAs(memory);
        memory.Position = 0;
        return memory.ToArray();
    }
}

ByPeriodMotivationExport.xlsx

[b0bi79]

@Fox2726 Your template does not match the data you pass. ClosedXML cannot fill a pivot table, so it throws an exception. In order for your code to work with this template, the order class must have the fields {Company, PaymentMethod, OrderNo, ShipDate, ItemsTotal, TaxRate, AmountPaid} filled in

[b0bi79]

@MiNGRotten Artem, could you send me a code that I can execute? It is not clear from the presented code which fields the row object has and how they are filled.

[ming-me]

@b0bi79, i can send dto which is used with pivot.

    [DataContract(Name = nameof(MotivationExportByPeriodDto))]
    public class MotivationExportByPeriodDto
    {
        [DataMember]
        public string Rdc { get; set; }
        [DataMember]
        public long? EmployeeId { get; set; }
        [DataMember]
        public int? CrmId { get; set; }
        [DataMember]
        public string Position { get; set; }
        [DataMember]
        public string Fio { get; set; }
        [DataMember]
        public DateTime RepDate { get; set; }
        [DataMember]
        public decimal? Amount { get; set; }
        [DataMember]
        public string MotivationType { get; set; }
        [DataMember]
        public string MotivationDescription { get; set; }
        [DataMember]
        public long? DRDCId { get; set; }
        [DataMember]
        public string DRDCFio { get; set; }
        [DataMember]
        public long? PMId { get; set; }
        [DataMember]
        public string PMFio { get; set; }
        [DataMember]
        public long? TMId { get; set; }
        [DataMember]
        public string TMFio { get; set; }
        [DataMember]
        public long? SVId { get; set; }
        [DataMember]
        public string SVFio { get; set; }
        [DataMember]
        public int? Hour { get; set; }
        [DataMember]
        public string Message { get; set; }
    }

[Fox2726]

@b0bi79 Thanks. i add the code like you say. And i got a new error message.
The cells B0 and XFD1048575 are outside the range 'Sheet1'.

new code:
public void Report(string file_path)
{
string outputFile = file_path + "\xxx.xlsx";
var template = new XLTemplate(file_path+ "\tPivot1.xlsx");
try
{

            Customer cust = new Customer();
            //cust.CustNo = 1;
            //cust.Company = "Company ABC";
            List<order> orders = new List<order>();
            for (int i = 0; i < 10; i++)
            {
                order myorder = new order();
                myorder.OrderNo = i + 1;
                //myorder.ShipToAddr1 = "AA " + i;
                myorder.Company = "ABC";
                myorder.PaymentMethod = "XYZ";
                myorder.ShipDate = DateTime.Now;
                myorder.ItemsTotal = 1;
                myorder.TaxRate = 5;
                myorder.AmountPaid = i;
                orders.Add(myorder);

            }
            cust.Orders = orders;
            template.AddVariable(new { orders = orders });
            template.Generate();
            template.SaveAs(outputFile);

        }
        catch(Exception ex)
        {

        }
    }

}

[b0bi79]

@Fox2726
Replace the line of
template.AddVariable(new { orders = orders });
to
template.AddVariable(new { Orders = orders });

In your template, the table is called Orders with a capital letter.

[b0bi79]

@MiNGRotten In the data for which there is a grouping (tags <<row>>, <<column>>, <<page>>) there should not be null or empty lines. Blank lines must be replaced by at least a space. The used version of ClosedXML (v0.92) did not correctly process such data. I think that in the new version of ClosedXML this problem should be fixed.

[Fox2726]

@b0bi79 many thanks. it works.
by the way, did it possible pass datatable as data source and use table column name to mapping template file?

[b0bi79]

Yes it is possible.

template.AddVariable("table", dataTable.Rows.Cast<DataRow>())

In the template you can refer to the fields so: item["Name"]

[ming-me]

@b0bi79, understood thanks.
Fix planned to produce, together with a new release?

[Fox2726]

@b0bi79 thanks.
using datatable as source also works.

And I had a suggestion about column name from datatable to write in template directly.
It could save time without to make multiple template file only column name was in difference language.

[b0bi79]

@MiNGRotten This issue is not in ClosedXML.Report, but in ClosedXML. ClosedXML.Report currently uses ClosedXML version 0.92, but ClosedXML has already been released version 0.94. Now I am working on the transition to the new version of ClosedXML