Excel Export multiple pivot tables into one workbook

[Michael Salzlechner]

i have 2 pivot tables one on each tab in an SfTab

i would like to export each pivot table on a separate sheet in the workbook

also would like to insert a first sheet to show generic information 

is that possible?

[Michael Salzlechner]

looks like there is no feature to support this. I opted exporting into memory and then reading and combining the two sheets into one excel file and also adding another sheet

[Karthickraja MuthuRaman]

Hi Michael,

Thanks for contacting our Syncfusion support.

We have validated your query and understand that you are looking for support to export multiple Pivot Tables into a single Excel workbook. If so, we would like to clarify that this capability is not currently supported in the Pivot Table component. However, we recognize the value of this requirement and have already logged it as a feature request named: "Support for Exporting Multiple Pivot Tables to Excel in Blazor".

At present, our team is focused on refining the product roadmap and addressing other high-priority initiatives, so we are unable to provide a specific timeline for its implementation. That said, this feature is planned for inclusion in one of our upcoming releases. Once it is scheduled, we will notify you through the feedback link provided below.

Feedback: https://www.syncfusion.com/feedback/62845/support-for-exporting-multiple-pivot-tables-to-excel-in-blazor

However, we are reviewing the possibility of handling this requirement as a workaround. We will share further details within two business days.

If you have any further questions or require additional assistance, please do not hesitate to reach out to our support team. We are always happy to help.

Regards,
Karthickraja M

[Karthickraja MuthuRaman]

Hi Michael,

Thank you for your patience.

We have carefully reviewed the possible approaches to meet your requirement of exporting two Pivot Tables (Visualization and Clients) into a single Excel workbook, with each Pivot Table placed on a separate worksheet. Based on our analysis, we recommend exporting each SfPivotView instance individually using ExportToExcelAsync with the asMemoryStream parameter set to true, and then merging the exported workbooks into a single workbook using Syncfusion.XlsIO.ExcelEngine.

The MemoryStream returned by SfPivotView.ExportToExcelAsync is automatically disposed of by the component once the asynchronous export operation is completed. Therefore, immediately after each export, the stream content must be copied into a new MemoryStream. By creating copyOfStreamDoc1 and copyOfStreamDoc2, the exported data remains available and can be safely used within ExcelEngine for further processing.

After opening the copied streams, the worksheets can be renamed to Visualization and Clients, respectively. These worksheets can then be merged into a single workbook using the AddCopy method and saved as PivotTables.xlsx. As a result, the generated Excel file will contain both Pivot Tables on separate worksheets within the same workbook.

Please refer to the following code example.

Code Example:

[Index.razor]

private async Task ExportBothTables()

{

    if (PivotTab1Ref == null || PivotTab2Ref == null)

    {

        System.Diagnostics.Debug.WriteLine("Export error: PivotView references not available");

        return;

    }

 

    try

    {

        //Merging two memory stream for Excel export.

        MemoryStream mergedStream = new MemoryStream();

 

        //Export the first pivot table.

        MemoryStream streamDoc1 = await PivotTab1Ref.ExportToExcelAsync(true);

 

        //Create a copy of streamDoc1 to access the memory stream

        //(the original stream is disposed by the PivotView after export).

        MemoryStream copyOfStreamDoc1 = new MemoryStream(streamDoc1.ToArray());

 

        //Export the second pivot table.

        MemoryStream streamDoc2 = await PivotTab2Ref.ExportToExcelAsync(true);

 

        //Create a copy of streamDoc2 to access the memory stream.

        MemoryStream copyOfStreamDoc2 = new MemoryStream(streamDoc2.ToArray());

 

        using (ExcelEngine excelEngine = new ExcelEngine())

        {

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Xlsx;

            IWorkbook workbook1 = application.Workbooks.Open(copyOfStreamDoc1);

            IWorkbook workbook2 = application.Workbooks.Open(copyOfStreamDoc2);

 

            //Rename the sheets for clarity.

            workbook1.Worksheets[0].Name = "Visualization";

            workbook2.Worksheets[0].Name = "Clients";

 

            //Copy the first worksheet from workbook1 into workbook2.

            workbook2.Worksheets.AddCopy(workbook1.Worksheets[0]);

            workbook2.ActiveSheetIndex = 1;

 

            //Saving merged workbook as memory stream.

            workbook2.SaveAs(mergedStream);

        }

 

        await JSRuntime.InvokeVoidAsync("saveAsFile", new object[] { "PivotTables.xlsx", Convert.ToBase64String(mergedStream.ToArray()), true });

    }

    catch (Exception ex)

    {

        System.Diagnostics.Debug.WriteLine($"Export error: {ex.Message}");

    }

}

Output:

In the meantime, we have prepared a sample for your reference. You can find it in the attachment below.

If you have any further questions or require additional assistance, please do not hesitate to contact our support team. We are always happy to help.

Regards,

Karthickraja M

Attachment: PivottableExport_de4c84f8.zip