Gridview Add Header and Footer to the Exported Document

[Jhon]

Dear, apologies for the case since I find myself using the google translate.

I'm trying to export information from a gridview by adding to this headers and footer at the time of launching the xlsx, so following the thread http://docs.telerik.com/devtools/winforms/gridview/exporting-data/how -to / add-header-and-footer-to-the-exported-document, I have not been able to do it.

Attached code, please give me a hand. Thank you

 

Private Sub cmdExportarGestion_Click(sender As System.Object, e As System.EventArgs) Handles cmdExportarGestion.Click
        'Try


        Dim sfd As New FolderBrowserDialog()
        Dim ret As DialogResult

        With sfd
            .Reset()
            .Description = " Seleccionar una carpeta "
            .SelectedPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            .ShowNewFolderButton = False
            ret = .ShowDialog()
        End With

        If ret = Windows.Forms.DialogResult.OK Then
            gbGeneral.Enabled = False
            Me.Cursor = Cursors.WaitCursor
            'For Each item In cboVendedores.Items
            'CargarFDetallePorcentaje(dgExportar, cboGestiones.Text, item("CODIGO").ToString)
            CargarFDetallePorcentaje(dgExportar, cboGestiones.Text, cboVendedores.Text)
            Select Case chkCabeceras.CheckState
                Case CheckState.Checked
                    Dim spreadExporter As New GridViewSpreadExport(dgMaestroDetalle)
                    Dim exportRenderer As New SpreadExportRenderer()
                    AddHandler exportRenderer.WorkbookCreated, AddressOf exportRenderer.CreateWorkbook
                    spreadExporter.SheetName = "COMISION VENDEDOR 05"
                    spreadExporter.RunExport(sfd.SelectedPath & "\123.xlsx", exportRenderer)

                    Dim solidPatternFill As New PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(46, 204, 113), Colors.Transparent)
                    Dim textFormat As New CellValueFormat("@")


                    Dim workbook As New Workbook()
                    'Dim worksheet As Worksheet = workbook.Worksheets.Add("COMISION")




                    Dim worksheet As Worksheet = workbook.Sheets.Add(0) 'TryCast(workbook.Sheets(0), Worksheet)
                    Dim range As New CellRange(0, 0, 1, dgMaestroDetalle.Columns.Count - 4)
                    Dim header As CellSelection = worksheet.Cells(range)
                    If header.CanInsertOrRemove(range, ShiftType.Down) Then
                        header.Insert(InsertShiftType.Down)
                    End If
                    header.Merge()
                    header.SetFormat(textFormat)
                    header.SetHorizontalAlignment(Telerik.WinControls.UI.RadHorizontalAlignment.Center)
                    header.SetVerticalAlignment(Telerik.WinControls.UI.RadVerticalAlignment.Center)
                    header.SetFontFamily(New ThemableFontFamily("Rockwell"))
                    header.SetFontSize(24)
                    header.SetFill(solidPatternFill)
                    header.SetValue("Nortwind Products Details")


                Case CheckState.Unchecked
                    'Dim spreadExporter As New ExportToExcelML(dgMaestroDetalle)
                    'spreadExporter.ExportHierarchy = True
                    'spreadExporter.ExportVisualSettings = True
                    'spreadExporter.RunExport(sfd.SelectedPath & "\Gestion_" & cboGestiones.Text & "_Vendedor_" & item("CODIGO").ToString & ".xls")
            End Select

            'Next
        Else
            Exit Sub
        End If
        Me.Cursor = Cursors.Default
        sfd.Dispose()
        MsgBox("Exportacion exitosa..", MsgBoxStyle.Information, MsgboxTitle)
        gbGeneral.Enabled = True
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.Critical, MsgboxError)
        'End Try
    End Sub
End Class

[Dimitar]

Hi Jhon,

All code except the first snippet should be in the WorkbookCreated event handler. 

I hope this will be useful. Let me know if you have additional questions.

Regards,
Dimitar
Progress Telerik

[Jhon]

Dear, I've managed to add the desired headers. So I present an error when adding them when the gridview is grouped and using the summary row this replaces the headers in data.
Attached code and captures.

 

Private Sub CargarFDetallePorcentaje(ByVal dg As RadGridView, ByVal gestion As Integer, ByVal vendedor As Integer)
        Try
            Me.EXPORTAR_PASO2TableAdapter.Fill(Me.DsExportarPaso02.EXPORTAR_PASO2, gestion, vendedor)
            dg.DataSource = DsExportarPaso02
            dg.DataMember = "EXPORTAR_PASO2"
            dg.AutoGenerateHierarchy = True
            dg.EnableFiltering = False
            dg.EnableGrouping = True
            'dg.AutoExpandGroups = True
            dg.ShowGroupPanel = False

            dg.BestFitColumns()

            dg.Columns(0).IsVisible = False
            dg.Columns(2).IsVisible = False
            dg.Columns(3).IsVisible = False

            Dim descriptor As New GroupDescriptor()
            descriptor.GroupNames.Add("CODIGO", ListSortDirection.Ascending)
            dg.GroupDescriptors.Add(descriptor)

            'dg.MasterTemplate.BottomPinnedRowsMode = GridViewBottomPinnedRowsMode.Float
            dg.MasterTemplate.ShowTotals = True
            dg.MasterTemplate.SummaryRowsBottom.Clear()
            Dim subtotalNeto As New GridViewSummaryItem("NETO", "", GridAggregateFunction.Sum)
            Dim subtotalPorcentaje As New GridViewSummaryItem("PORCENTAJE", "", GridAggregateFunction.Sum)
            Dim subtotalComision As New GridViewSummaryItem("COMISION", "", GridAggregateFunction.Sum)
            Dim subtotalRow As New GridViewSummaryRowItem(New GridViewSummaryItem() {subtotalNeto, subtotalPorcentaje, subtotalComision})
            dg.MasterTemplate.SummaryRowsBottom.Add(subtotalRow)

            lblRegistros.Text = "Registros Encontrados : " & dg.Rows.Count
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, MsgboxError)
        End Try
    End Sub

Private Sub exportRenderer_WorkbookCreated(sender As Object, e As WorkbookCreatedEventArgs)
        Try
            Dim solidPatternFill As New PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(46, 204, 113), Colors.Transparent)
            Dim textFormat As New CellValueFormat("@")

            Dim sheet As Worksheet = DirectCast(e.Workbook.ActiveSheet, Worksheet)
            Dim range As New CellRange(0, 0, 1, dgExportar.Columns.Count - 1)
            '######## AGREGAR FECHA ##########
            Dim fecha As CellSelection = sheet.Cells(range)
            If fecha.CanInsertOrRemove(range, ShiftType.Down) Then
                fecha.Insert(InsertShiftType.Down)
            End If
            fecha.Merge()
            fecha.SetFormat(textFormat)
            fecha.SetFontFamily(New ThemableFontFamily("Rockwell"))
            fecha.SetFontSize(18)
            fecha.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center)
            fecha.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center)
            'fecha.SetFill(solidPatternFill)
            fecha.SetValue(Obtener_fecha)
            '######## AGREGAR FECHA ##########
            '######## AGREGAR TITULO ##########
            Dim header As CellSelection = sheet.Cells(range)
            If header.CanInsertOrRemove(range, ShiftType.Down) Then
                header.Insert(InsertShiftType.Down)
            End If
            header.Merge()
            header.SetFormat(textFormat)
            header.SetFontFamily(New ThemableFontFamily("Rockwell"))
            header.SetFontSize(24)
            header.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center)
            header.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center)
            header.SetFill(solidPatternFill)
            header.SetValue(lblVendedor.Text)
            '######## AGREGAR TITULO ##########
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, MsgboxError)
        End Try
    End Sub

Dim exporter As New GridViewSpreadExport(dg)
AddHandler exporter.CellFormatting, AddressOf exporter_CellFormatting
exporter.ExportVisualSettings = True
 exporter.SheetName = "COMISION"
Dim renderer As New SpreadExportRenderer()
AddHandler renderer.WorkbookCreated, AddressOf exportRenderer_WorkbookCreated
exporter.RunExport(sfd.SelectedPath & "\Gestion" & cboGestiones.Text & " [Vendedor " & cboVendedores.Text & "]" & ".xlsx", renderer)

 

[Dimitar]

Hi Jhon,

When there are groups the column count is different. The group will add one extra column and this breaks the insert operation. Add one more column in the CellRange and this will work as expected.

I hope this will be useful. 

Regards,
Dimitar
Progress Telerik

[Jhon]

Dear,
According to the above, add one more column to the range and solve the first reported error, it also creates a line that combines the detail of the report in each row of the groups. Attachment capture. Thank you

 

Private Sub exportRenderer_WorkbookCreated(sender As Object, e As WorkbookCreatedEventArgs)

        Try

            Dim solidPatternFill As New PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(46, 204, 113), Colors.Transparent)

            Dim textFormat As New CellValueFormat("@")

 

            Dim sheet As Worksheet = DirectCast(e.Workbook.ActiveSheet, Worksheet)

            Dim range As New CellRange(0, 0, 1, dgMaestroDetalle.Columns.Count)


            '######## AGREGAR FECHA ##########

            'Dim fecha As CellSelection = sheet.Cells(range)

            'If fecha.CanInsertOrRemove(range, ShiftType.Down) Then

            '    fecha.Insert(InsertShiftType.Down)

            'End If

            'fecha.Merge()

            'fecha.SetFormat(textFormat)

            'fecha.SetFontFamily(New ThemableFontFamily("Rockwell"))

            'fecha.SetFontSize(18)

            'fecha.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center)

            'fecha.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center)

            ''fecha.SetFill(solidPatternFill)

            'fecha.SetValue(Obtener_fecha)

            '######## AGREGAR FECHA ##########

            '######## AGREGAR TITULO ##########

            Dim header As CellSelection = sheet.Cells(range)

            If header.CanInsertOrRemove(range, ShiftType.Down) Then

                header.Insert(InsertShiftType.Down)

            End If

            header.Merge()

            header.SetFormat(textFormat)

            header.SetFontFamily(New ThemableFontFamily("Rockwell"))

            header.SetFontSize(24)

            header.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center)

            header.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center)

            header.SetFill(solidPatternFill)

            header.SetValue(lblVendedor.Text)

            '######## AGREGAR TITULO ##########

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical, MsgboxError)

        End Try

    End Sub

[Dimitar]

Hello Jhon,

This appears to be an issue with the exporter. I have logged it in our Feedback Portal. You can track its progress, subscribe for status changes and add your comment to it here. I have also updated your Telerik Points.

To workaround this you should add the header after the file is created:

var provider = new XlsxFormatProvider();
var workbook = new Workbook();
using (var stream = File.OpenRead(@"D:\123.xlsx"))
{
    workbook = provider.Import(stream);
}

PatternFill solidPatternFill = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(46, 204, 113), Colors.Transparent);
CellValueFormat textFormat = new CellValueFormat("@");

Worksheet sheet = workbook.ActiveWorksheet;
CellRange range = new CellRange(0, 0, 1, 4);

CellSelection header = sheet.Cells[range];
if (header.CanInsertOrRemove(range, ShiftType.Down))
{
    header.Insert(InsertShiftType.Down);
}
header.Merge();
header.SetFormat(textFormat);
header.SetFontFamily(new ThemableFontFamily("Rockwell"));
header.SetFontSize(24);
header.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center);
header.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center);
header.SetFill(solidPatternFill);
header.SetValue("Test");

using (var stream = File.OpenWrite("result.xlsx"))
{
    provider.Export(workbook, stream);
}

Process.Start("result.xlsx");

Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

[Jhon]

Dear,

I managed to solve the problem according to its indications, I also attached the code used to serve other users. Thank you for supporting Dimitar.

Private Sub exportRenderer_WorkbookCreated()
        'Try
        Dim provider = New XlsxFormatProvider()
        Dim workbook = New Workbook()
        Using stream = IO.File.OpenRead("C:\Users\sistemas\Desktop\123.xlsx")
            workbook = provider.Import(stream)
        End Using

        Dim solidPatternFill As New PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(46, 204, 113), Colors.Transparent)
        Dim textFormat As New CellValueFormat("@")

        Dim sheet As Worksheet = workbook.ActiveSheet
        Dim range As New CellRange(0, 0, 1, dgMaestroDetalle.Columns.Count)

        Dim header As CellSelection = sheet.Cells(range)
        If header.CanInsertOrRemove(range, ShiftType.Down) Then
            header.Insert(InsertShiftType.Down)
        End If
        header.Merge()
        header.SetFormat(textFormat)
        header.SetFontFamily(New ThemableFontFamily("Rockwell"))
        header.SetFontSize(24)
        header.SetHorizontalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadHorizontalAlignment.Center)
        header.SetVerticalAlignment(Telerik.Windows.Documents.Spreadsheet.Model.RadVerticalAlignment.Center)
        header.SetFill(solidPatternFill)
        header.SetValue(lblVendedor.Text)

        Using stream = IO.File.OpenWrite("C:\Users\sistemas\Desktop\123.xlsx")
            provider.Export(workbook, stream)
        End Using

        Process.Start("C:\Users\sistemas\Desktop\123.xlsx")
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.Critical, MsgboxError)
        'End Try
    End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim exporter As New GridViewSpreadExport(dgMaestroDetalle)
        AddHandler exporter.CellFormatting, AddressOf exporter_CellFormatting
        exporter.ExportVisualSettings = True
        exporter.SheetName = "COMISION"
        Dim renderer As New SpreadExportRenderer()

        exporter.RunExport("C:\Users\sistemas\Desktop\123.xlsx", renderer)
        exportRenderer_WorkbookCreated()
        MsgBox("Exportacion exitosa..", MsgBoxStyle.Information, MsgboxTitle)
    End Sub

[Dimitar]

Hello Jhon,

I am glad that this is working fine now. Do not hesitate to contact us if you have other questions.

Regards,
Dimitar
Progress Telerik