How to: Add Custom Information to the Exported Excel Workbook using CustomizeExportDocument

Mar 31, 2018
4 minutes to read

The ASPxDashboard.CustomizeExportDocument event allows you to obtain the stream of the exported document using the CustomizeExportDocumentEventArgs.Stream property and customize the document’s layout according to your requirements. For instance, Excel documents may be loaded into the Workbook component for further processing.

This example shows how to add a custom header to each sheet for the exported workbook.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/web-dashboard-how-to-add-custom-information-to-the-exported-excel-document-t504794.

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebDashboardForm.aspx.vb" Inherits="WebDashboard_CustomizeExport.WebDashboardForm" %>
<%@ Register assembly="DevExpress.Dashboard.v17.1.Web, Version=17.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.DashboardWeb" tagprefix="dx" %>
<%@ Register assembly="DevExpress.Web.v17.1, Version=17.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.Web" tagprefix="dx" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body >
    <form id="form1" runat="server">
    <div style="position:absolute; left:0; right:0; top:0; bottom:0;">
        <dx:ASPxDashboard ID="ASPxDashboard1" runat="server" 
            AllowExportDashboardItems="True" Height="100%" Width="100%" WorkingMode="ViewerOnly"
            OnCustomizeExportDocument="ASPxWebDashboard1_CustomizeExportDocument" 
            OnConfigureDataConnection="ASPxDashboard1_ConfigureDataConnection">
        </dx:ASPxDashboard>
    </div>
    </form>
</body>
</html>

Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.Spreadsheet
Imports System
Imports System.Drawing
Imports System.IO

Namespace WebDashboard_CustomizeExport
    Partial Public Class WebDashboardForm
        Inherits System.Web.UI.Page

        Private fileStorage As New DashboardFileStorage("App_Data/Dashboards")
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ASPxDashboard1.SetDashboardStorage(fileStorage)
        End Sub

        Protected Sub ASPxWebDashboard1_CustomizeExportDocument(ByVal sender As Object, ByVal e As CustomizeExportDocumentWebEventArgs)
                If e.ExportAction = DashboardExportAction.ExportToExcel Then
                    Dim ms As New MemoryStream()
                    e.Stream.Position = 0

                    Dim workbook As New Workbook()
                    If e.ExcelExportOptions.Format = ExcelFormat.Xlsx Then
                        workbook.LoadDocument(e.Stream, DocumentFormat.Xlsx)
                    ElseIf e.ExcelExportOptions.Format = ExcelFormat.Xls Then
                        workbook.LoadDocument(e.Stream, DocumentFormat.Xls)
                    ElseIf e.ExcelExportOptions.Format = ExcelFormat.Csv Then
                        workbook.LoadDocument(e.Stream, DocumentFormat.Csv)
                    End If
                    For Each sheet As Worksheet In workbook.Worksheets
                        ' Export to CSV without images, cell merging and coloring. 
                        If e.ExcelExportOptions.Format = ExcelFormat.Csv Then
                            sheet.Rows.Insert(0)
                            Dim textCell As Cell = sheet.Cells(0, 0)
                            textCell.Value = "Custom Document Header"
                        ' Export to the Excel workbook with images, cell merging and coloring.
                        Else
                            sheet.Rows.Insert(0, 3)
                            sheet.Pictures.AddPicture(My.Resources.dxLogo, sheet.Range.FromLTRB(0, 0, 5, 2), True)
                            Dim textCell As Cell = sheet.Cells(0, 5)
                            textCell.Value = "Custom Document Header"

                            sheet.MergeCells(sheet.Range.FromLTRB(5, 0, 8, 0))
                            Dim formatting As Formatting = textCell.BeginUpdateFormatting()
                            formatting.Fill.BackgroundColor = Color.LightBlue
                            textCell.EndUpdateFormatting(formatting)
                            textCell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
                        End If

                    Next sheet
                    If e.ExcelExportOptions.Format = ExcelFormat.Xlsx Then
                        workbook.SaveDocument(ms, DocumentFormat.Xlsx)
                    ElseIf e.ExcelExportOptions.Format = ExcelFormat.Xls Then
                        workbook.SaveDocument(ms, DocumentFormat.Xls)
                    ElseIf e.ExcelExportOptions.Format = ExcelFormat.Csv Then
                        workbook.SaveDocument(ms, DocumentFormat.Csv)
                    End If
                    e.Stream = ms
                End If
        End Sub

        Protected Sub ASPxDashboard1_ConfigureDataConnection(ByVal sender As Object, ByVal e As ConfigureDataConnectionWebEventArgs)
            If e.DataSourceName = "SQL Data Source" Then
                Dim connectionParams As New Access97ConnectionParameters()
                connectionParams.FileName = Server.MapPath("~/App_Data/nwind.mdb")
                e.ConnectionParameters = connectionParams
            End If
        End Sub
    End Class
End Namespace

using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.Spreadsheet;
using System;
using System.Drawing;
using System.IO;

namespace WebDashboard_CustomizeExport
{
    public partial class WebDashboardForm : System.Web.UI.Page
    {
        DashboardFileStorage fileStorage = new DashboardFileStorage("App_Data/Dashboards");
        protected void Page_Load(object sender, EventArgs e)
        {
            ASPxDashboard1.SetDashboardStorage(fileStorage);
        }

        protected void ASPxWebDashboard1_CustomizeExportDocument(object sender, CustomizeExportDocumentWebEventArgs e)
        {
            {
                if (e.ExportAction == DashboardExportAction.ExportToExcel)
                {
                    MemoryStream ms = new MemoryStream();
                    e.Stream.Position = 0;

                    Workbook workbook = new Workbook();
                    if (e.ExcelExportOptions.Format == ExcelFormat.Xlsx)
                        workbook.LoadDocument(e.Stream, DocumentFormat.Xlsx);
                    else if (e.ExcelExportOptions.Format == ExcelFormat.Xls)
                        workbook.LoadDocument(e.Stream, DocumentFormat.Xls);
                    else if (e.ExcelExportOptions.Format == ExcelFormat.Csv)
                        workbook.LoadDocument(e.Stream, DocumentFormat.Csv);
                    foreach (Worksheet sheet in workbook.Worksheets)
                    {
                        // Export to CSV without images, cell merging and coloring. 
                        if (e.ExcelExportOptions.Format == ExcelFormat.Csv)
                        {
                            sheet.Rows.Insert(0);
                            Cell textCell = sheet.Cells[0, 0];
                            textCell.Value = "Custom Document Header";
                        }
                        // Export to the Excel workbook with images, cell merging and coloring.
                        else
                        {
                            sheet.Rows.Insert(0, 3);
                            sheet.Pictures.AddPicture(Properties.Resources.dxLogo, sheet.Range.FromLTRB(0, 0, 5, 2), true);
                            Cell textCell = sheet.Cells[0, 5];
                            textCell.Value = "Custom Document Header";

                            sheet.MergeCells(sheet.Range.FromLTRB(5, 0, 8, 0));
                            Formatting formatting = textCell.BeginUpdateFormatting();
                            formatting.Fill.BackgroundColor = Color.LightBlue;
                            textCell.EndUpdateFormatting(formatting);
                            textCell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
                        }

                    }
                    if (e.ExcelExportOptions.Format == ExcelFormat.Xlsx)
                        workbook.SaveDocument(ms, DocumentFormat.Xlsx);
                    else if (e.ExcelExportOptions.Format == ExcelFormat.Xls)
                        workbook.SaveDocument(ms, DocumentFormat.Xls);
                    else if (e.ExcelExportOptions.Format == ExcelFormat.Csv)
                        workbook.SaveDocument(ms, DocumentFormat.Csv);
                    e.Stream = ms;
                }
            }
        }

        protected void ASPxDashboard1_ConfigureDataConnection(object sender, ConfigureDataConnectionWebEventArgs e)
        {
            if (e.DataSourceName == "SQL Data Source")
            {
                Access97ConnectionParameters connectionParams = new Access97ConnectionParameters();
                connectionParams.FileName = Server.MapPath(@"~/App_Data/nwind.mdb");
                e.ConnectionParameters = connectionParams;
            }
        }
    }
}

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebDashboardForm.aspx.cs" Inherits="WebDashboard_CustomizeExport.WebDashboardForm" %>
<%@ Register assembly="DevExpress.Dashboard.v17.1.Web, Version=17.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.DashboardWeb" tagprefix="dx" %>
<%@ Register assembly="DevExpress.Web.v17.1, Version=17.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" 
    namespace="DevExpress.Web" tagprefix="dx" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body >
    <form id="form1" runat="server">
    <div style="position:absolute; left:0; right:0; top:0; bottom:0;">
        <dx:ASPxDashboard ID="ASPxDashboard1" runat="server" 
            AllowExportDashboardItems="True" Height="100%" Width="100%" WorkingMode="ViewerOnly"
            OnCustomizeExportDocument="ASPxWebDashboard1_CustomizeExportDocument" 
            OnConfigureDataConnection="ASPxDashboard1_ConfigureDataConnection">
        </dx:ASPxDashboard>
    </div>
    </form>
</body>
</html>

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode