Export multiple ASPxGridView to an Excel with different sheets

Question

I use 2 different ASPxGridView in different tab. I want to export these ASPxGridViews with different sheet in the same excel file.

I can export multiple ASPxGridViews in 1 excel but also 1 sheet.

protected void ExportButton_Click(object sender, EventArgs e)
{
    PrintingSystem ps = new PrintingSystem();

    PrintableComponentLink link1 = new PrintableComponentLink(ps);
    link1.Component = GridExporter1;

    PrintableComponentLink link2 = new PrintableComponentLink(ps);
    link2.Component = GridExporter2;

    CompositeLink compositeLink = new CompositeLink(ps);
    compositeLink.Links.AddRange(new object[] { link1, link2 });

    compositeLink.CreateDocument();
    using (MemoryStream stream = new MemoryStream())
    {
        compositeLink.PrintingSystem.ExportToXls(stream);
        WriteToResponse("filename", true, "xls", stream);
    }
    ps.Dispose();

}
void WriteToResponse(string fileName, bool saveAsFile, string fileFormat, MemoryStream stream)
{
    if (Page == null || Page.Response == null)
        return;

    string disposition = saveAsFile ? "attachment" : "inline";
    Page.Response.Clear();
    Page.Response.Buffer = false;
    Page.Response.AppendHeader("Content-Type", string.Format("application/{0}", fileFormat));
    Page.Response.AppendHeader("Content-Transfer-Encoding", "binary");
    Page.Response.AppendHeader("Content-Disposition",string.Format("{0}; filename={1}.{2}", disposition, fileName, fileFormat));
    Page.Response.BinaryWrite(stream.GetBuffer());
    Page.Response.End();
}

this is my item:

<dx:ASPxGridView ID="ASPxGridView1" runat="server" Theme="DevEx">
    <Settings ShowGroupPanel="True" />
    <SettingsPager PageSize="15" />
</dx:ASPxGridView>

<dx:ASPxGridView ID="ASPxGridView2" runat="server" Theme="Office2010Silver">
</dx:ASPxGridView>

<dx:ASPxButton ID="ExportButton" runat="server" Text="Export both grids" Width="205px" OnClick="ExportButton_Click" />

<dx:ASPxGridViewExporter ID="GridExporter1" runat="server" GridViewID="ASPxGridView1" />

<dx:ASPxGridViewExporter ID="GridExporter2" runat="server" GridViewID="ASPxGridView2" /><br />

Answer 1

You have to specify the export mode to export

Syntax

public XlsExportMode ExportMode { get; set; }

XlsExportMode An XlsExportMode enumeration value, representing the XLS export mode. The default is XlsExportMode.SingleFile.

And various modes are:

Name                     Description
DifferentFiles           A document is exported to multiple files, page-by-page. In this mode every document page is exported to a single XLSX file.
SingleFile               A document is exported to a single file. Note that in this mode, page headers and footers are added to the resulting XLSX file only once, at the beginning and at the end of the document.
SingleFilePageByPage     A document is exported to a single file, page-by-page. In this mode, each page is exported to an individual sheet of the same XLSX file.

so you can do like this:

 using (MemoryStream stream = new MemoryStream())
    {
        XlsExportOptions options = new XlsExportOptions();
        options.ExportMode = XlsExportOptions.SingleFilePageByPage;
        compositeLink.PrintingSystemBase.ExportToXls(stream, options);
        WriteToResponse("filename", true, "xls", stream);
    }

Check for more details How to export the ASPxGridView and WebChartControl to the same print document