Feature Request: Worksheet per item in List

[mutzl]

I'd like to create one worksheet per item from a list.

For example in case I pass an IEnumerable to the template template.AddVariable(customers); and the name of the template worksheet is something like {{ item.Name }} the report generator should iterate through the customers list and create one worksheet for every item in the list.

Another approach: passing a variable data that contains an IEnumerable<Customer> Customers template.AddVariable(data); and worksheet with label {{ Customers.Name }} should trigger the generation of a single worksheet per customer.

[b0bi79]

I have long thought that someone would need this feature and need to do it. Unfortunately, this is not yet implemented. I will try to find time in the next couple of weeks and implement it.

[mutzl]

I am surprised that this wasn't requested before. :)

In the meantime I have to create the worksheets per code with pure ClosedXML, I guess.

[b0bi79]

I think now you could do this:

1. using ClosedXML to read the template
2. create copies of the template sheet for each Customer
3. rename named ranges to customer.Name + "_Range" on the created sheet
4. pass the workbook to ClosedXML.Report
5. pass the variables to ClosedXML.Report template.AddVariable (customer.Name +"_Range", customer.Data);
6. call Generate for Template

Maybe it is difficult, but I would do so.

[fingers10]

I was also having a similar requirement. This is how I ended up doing, I used the same excel template while generating multiple sheets. This way we dont need to rename ranges in our template.

private (bool, byte[]) GetExcelBytesFromTemplate<T>(string templatePath, ExcelReportModel<T> templateVariable, bool createSeparateSheets = false)
        {
            byte[] excelBytes = null;
            var result = true;

            var finaltemplate = new XLWorkbook(templatePath); // load your template
            finaltemplate.Worksheets.Delete(1); // delete the first sheet. Now excel is clean.

            try
            {
                if (createSeparateSheets) // if you need as separate sheet
                {
                    foreach (var record in templateVariable.Records) // your records
                    {
                        var template = new XLTemplate(templatePath); // loading same template again
                        templateVariable.Records = new List<T> { record };
                        template.AddVariable(templateVariable);
                        template.Generate();
                        template.Workbook.Worksheet(1).Name = record.ToString(); // name the sheet
                        template.Workbook.Worksheet(1).CopyTo(finaltemplate, record.ToString()); // copy to final excel
                    }
                }
                else // else is self explanatory as shown in copy except I copy to my final excel
                {
                    var template = new XLTemplate(templatePath);
                    template.AddVariable(templateVariable);
                    template.Generate();
                    var worksheet = template.Workbook.Worksheet(1);
                    worksheet.CopyTo(finaltemplate, worksheet.Name);
                }

                using (var stream = new MemoryStream())
                {
                    finaltemplate.SaveAs(stream);
                    excelBytes = stream.ToArray();
                }
            }
            catch (Exception e)
            {
                result = false;
            }

            return (result, excelBytes);
        }

Then use the excel bytes to download as excel,

public IActionResult OnGetExcelReport()
        {
            return new ExcelReportFromTemplate(excelBytes, fileName);
        }

ExcelReportFromTemplate Action Result:

public class ExcelReportFromTemplate : IActionResult
    {
        public ExcelReportFromTemplate(byte[] excelBytes, string fileName)
        {
            ExcelBytes = excelBytes;
            FileName = fileName;
        }

        public byte[] ExcelBytes { get; set; }
        public string FileName { get; set; }

        public async Task ExecuteResultAsync(ActionContext context)
        {
            context.HttpContext.Response.Headers["content-disposition"] =
                $"attachment; filename={FileName}{Constants.ExcelFileFormat}";

            await context.HttpContext.Response.Body.WriteAsync(ExcelBytes, 0, ExcelBytes.Length);
        }
    }

Hope this helps someone.

Thanks, Abdul

[The-MAZZTer]

This would also be useful for me. I have the following scenario:

• Customer wants first "tab" (worksheet) to be a list of other worksheets (this is simple to do with a normal table).
• Customer wants other "tabs" to be based off of a single template with different data in each.

This second bit is not possible to do. I should be able to define a worksheet which associates with an array, doing so will create one sheet per array item.

For example maybe the worksheet name is:

<<array Tables>> {{item.WorksheetName}}

Something like this would cause the Tables property on the root variable to be used as an array. Each element would result ina new sheet. Templating would be applied to the worksheet name in order to name it,