LocalReport to Excel

Question

I'm using below code to render a simple report to Excel or PDF, it works fine in PDF, however in Excel I get an error if dtTable has more than 64K rows. I'm using ReportDefinition which Microsoft has published to allow dynamically creation of rdlc from DataTable, available from here http://www.gotreportviewer.com/

Anybody know how to setup the Report Generator to split the spreadsheet out into a new tab every 64K row?

        var localReport = new LocalReport();

        var availableFields = new List<string>();
        for (var i = 0; i < dtTable.Columns.Count; i++)
        {
            if (dtTable.Columns[i].ColumnName != "RowID")
            availableFields.Add(dtTable.Columns[i].ColumnName);
        }

        MemoryStream m_rdl = new MemoryStream();
        RdlGenerator gen = new RdlGenerator();

        gen.AllFields = availableFields;
        gen.SelectedFields = availableFields;
        gen.WriteXml(m_rdl);
        m_rdl.Position = 0;

        localReport.LoadReportDefinition(m_rdl);

        ReportDataSource reportDataSource = new ReportDataSource("MyData", dtTable);
        localReport.DataSources.Add(reportDataSource);
        string reportType = "Excel";
        string encoding;
        string fileNameExtension;
        string mimeType;

        string deviceInfo = "<DeviceInfo><OutputFormat>Excel</OutputFormat></DeviceInfo>"; Warning[] warnings; string[] streams; byte[] renderedBytes;
        renderedBytes = localReport.Render(reportType, deviceInfo, out mimeType, out encoding, out fileNameExtension, out streams, out warnings);

Answer 1

To place every 64K rows in a separate worksheet, create a page break in the report based on the row number.

How to Export Data in Excel of SSRS Report if data exceeds greater than 65k
Export to EXCEL error in SSRS 2005

The error occurs because the Report Viewer control uses the old BIFF Excel format that handles only 65536 rows per worksheet.

Answer 2

Just in case somebody else run into the same scenario, here is what I modified/added to TableRdlGenerator, in order to get it to output any number of rows in Excel. Took about 5min to generate a report with 2M rows.

    public Rdl.TableType CreateTableExcel()
    {
        Rdl.TableType table = new Rdl.TableType();
        table.Name = "Table1";
        table.Items = new object[]
            {
                CreateTableColumns(),
                CreateHeader(),
                CreateDetails(),
                CreateTableGroups(),
            };
        table.ItemsElementName = new Rdl.ItemsChoiceType21[]
            {
                Rdl.ItemsChoiceType21.TableColumns,
                Rdl.ItemsChoiceType21.Header,
                Rdl.ItemsChoiceType21.Details,
                Rdl.ItemsChoiceType21.TableGroups,
            };
        return table;
    }

    private Rdl.TableGroupsType CreateTableGroups()
    {
        Rdl.TableGroupsType tableGroups = new Rdl.TableGroupsType();
        tableGroups.TableGroup = new TableGroupType[]
        {
            CreateTableGroup(),
        };
        return tableGroups;
    }

    private Rdl.TableGroupType CreateTableGroup()
    {
        Rdl.TableGroupType tableGroup = new TableGroupType();
        tableGroup.Items = new object[]
        {
            CreateGrouping(),
        };
        return tableGroup;
    }

    private Rdl.GroupingType CreateGrouping()
    {
        Rdl.GroupingType groupingType = new GroupingType();

        groupingType.Name = "pagebreak";
        groupingType.Items = new object[]
        {
            true,
            CreateGroupExpressions(),
        };
        groupingType.ItemsElementName = new ItemsChoiceType17[]
        {
            ItemsChoiceType17.PageBreakAtEnd,
            ItemsChoiceType17.GroupExpressions
        };
        return groupingType;
    }

    private Rdl.GroupExpressionsType CreateGroupExpressions()
    {
        Rdl.GroupExpressionsType groupExpressions = new GroupExpressionsType();

        groupExpressions.GroupExpression = new string[] { "=Int((RowNumber(Nothing)-1)/65000)" };

        return groupExpressions;
    }