One Worksheet per Row of data performance

FLX TMS FlexCel Studio for .NET
1

We have a .net core application running a report from a template. The excel template has the worksheet named like <#Table.ID>. This creates one worksheet tab per row of data in the excel output. We've used this for a while and have noticed the performance is not great when running it against 300+ rows. Is there a different way to do the same thing differently?

2

Hi,
With 300 rows you are referring to 300 sheets, or to some sheets with 300 rows on it? 300 rows in a sheet should be very fast, but 300 sheets can be indeed slow since Excel and the file format is not designed for so many sheets. And it also depends on how many rows you have in every sheet, you might end up with many millions of cells.

3

It's Probably 40 rows per sheet. 300 sheets. It does complete, but it takes forever (45 minutes). We are planning on moving away from this set up, but I wanted to check to see if there was a solution or if it was a known issue.

4

40 rows per sheet and 300 sheets (As long as the column number is reasonable) should be fast, unless the SQL query is taking too long. Do you have many master-detail reports inside every sheet (multiple levels of master-detail can indeed make FlexCel slow).

I've tested it with this code (in a new console .net 8 app):

using System.Diagnostics;
using FlexCel.Report;

var db = new List<DataBase>();
for (int sheet = 0; sheet < 300; sheet++)
{
    var dataBaseItem = new DataBase(){Sheet = "Sheet" + sheet};
    for (int row = 0; row < 40; row++)
    {
        dataBaseItem.Rows.Add(new Row()
        {
            col1 = "column 1 in sheet " + sheet,
            col2 = "row " + row,
            col3 = row.ToString(),
            col4 = sheet.ToString(),
            col5 = "this is more text",
            col6 = "and more in " + sheet,
            col7 = "in row " + row,
            col8 = "always using different messages " + sheet * row,
            col9 = "so it is slower " + sheet + row,
            col10 = "finally " + row *sheet,
            col11 = "some " + sheet,
            col12 = "more text " + sheet
        });
        
    }
    db.Add(dataBaseItem);
}

var sw = new Stopwatch();
sw.Start();
using var report = new FlexCelReport(true);
report.AddTable("db", db);
report.Run("../../../template.xlsx", "../../../result.xlsx");
sw.Stop();
TimeSpan ts = sw.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
    ts.Hours, ts.Minutes, ts.Seconds,
    ts.Milliseconds / 10);
Console.WriteLine("Elapsed time: " + elapsedTime);

class Row
{
    public string col1 { get; set; }
    public string col2 { get; set; }
    public string col3 { get; set; }
    public string col4 { get; set; }
    public string col5 { get; set; }
    public string col6 { get; set; }
    public string col7 { get; set; }
    public string col8 { get; set; }
    public string col9 { get; set; }
    public string col10 { get; set; }
    public string col11 { get; set; }
    public string col12 { get; set; }
}

class DataBase
{
    public string Sheet { get; set; }
    public List<Row> Rows { get; set; } = new List<Row>();

}

and the template:
template.xlsx (8.9 KB)

And it finishes in 97 ms here. It is a little unmanageable with so many sheets, but it isn't that slow. I imagine in your cause the cause might be multiple levels of master-detail in each sheet?