Excel Exporting Options

[Amit Saraf]

When I'm Converting Code given in your documentation (copy pasted here)

Styling cells based on CellType in Excel

You can customize the cell styles based on CellType by using ExportingEventHandler.

• C#

var options = new ExcelExportingOptions();
options.ExportingEventHandler = ExportingHandler;
options.AllowOutlining = true;
var excelEngine = dataGrid.ExportToExcel(dataGrid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
workBook.SaveAs("Sample.xlsx");

private static void ExportingHandler(object sender, GridExcelExportingEventArgs e)
{
      if (e.CellType == ExportCellType.HeaderCell)
      {
          e.CellStyle.BackGroundBrush = new SolidColorBrush(Colors.LightPink);
          e.CellStyle.ForeGroundBrush = new SolidColorBrush(Colors.White);
          e.Handled = true;
      }
      else if (e.CellType == ExportCellType.RecordCell)
      {
          e.CellStyle.BackGroundBrush = new SolidColorBrush(Colors.LightSkyBlue);
          e.Handled = true;
      }
      else if (e.CellType == ExportCellType.GroupCaptionCell)
      {
          e.CellStyle.BackGroundBrush = new SolidColorBrush(Colors.Wheat);
          e.Handled = true;
      } 

} 

I'm Getting Error on this line

options.ExportingEventHandler = ExportingHandler;

options.ExportingEventHandler = ExportingHandler()

can you please convert the above code in VB.Net 

Thanks in advance

Amit Saraf

[Jai Ganesh S]

Hi Amit, 

 

The ExportingEventHander is a delegate method and it can be used in VB.Net like below, 

 

Private Sub Button_Click(sender As Object, e As RoutedEventArgs) 

        Dim dataGrid = Me.dataGrid 

 

        If dataGrid Is Nothing Then 

            Return 

        End If 

        Try 

            Dim options = New ExcelExportingOptions() 

            options.ExcelVersion = ExcelVersion.Excel2010 

            options.ExportingEventHandler = AddressOf ExportingHandler 

 

            Dim excelEngine = dataGrid.ExportToExcel(dataGrid.View, options) 

 

            Dim workBook = excelEngine.Excel.Workbooks(0) 

 

           Dim sfd = New SaveFileDialog() 

            sfd.FilterIndex = 2 

 

            sfd.Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx" 

 

            sfd.FileName = "Book1" 

 

 

 

 

            If sfd.ShowDialog() = True Then 

                Using stream As Stream = sfd.OpenFile() 

                    If sfd.FilterIndex = 1 Then 

                        workBook.Version = ExcelVersion.Excel97to2003 

                    Else 

                        workBook.Version = ExcelVersion.Excel2010 

                    End If 

                    workBook.SaveAs(stream) 

                End Using 

 

                'Message box confirmation to view the created spreadsheet. 

                If MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButton.YesNo, MessageBoxImage.Information) = MessageBoxResult.Yes Then 

                    'Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] 

                    System.Diagnostics.Process.Start(sfd.FileName) 

                End If 

            End If 

 

        Catch generatedExceptionName As Exception 

        End Try 

 

    End Sub 

 

    

    Private Shared Sub ExportingHandler(ByVal sender As Object, ByVal e As GridExcelExportingEventArgs) 

        If e.CellType = ExportCellType.HeaderCell Then 

            e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.LightSteelBlue) 

            e.CellStyle.ForeGroundBrush = New SolidColorBrush(Colors.DarkRed) 

            e.CellStyle.FontInfo.Bold = True 

        ElseIf e.CellType = ExportCellType.GroupCaptionCell Then 

            e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.LightSlateGray) 

            e.CellStyle.ForeGroundBrush = New SolidColorBrush(Colors.LightYellow) 

        ElseIf e.CellType = ExportCellType.GroupSummaryCell Then 

            e.CellStyle.BackGroundBrush = New SolidColorBrush(Colors.LightGray) 

        End If 

        e.CellStyle.FontInfo.Size = 12 

        e.CellStyle.FontInfo.FontName = "Segoe UI" 

    End Sub 

 

Sample: http://www.syncfusion.com/downloads/support/directtrac/158133/ze/ExportingDemo-_Vb-2091270223 

 

Regards, 

Jai Ganesh S 

[Amit Saraf]

Thanks a lot, I was using

AddHandler options.ExportingEventHandler, AddressOf ExportingHandler

which did not worked or was giving err

'ExportingEventHandler' is not an event of 'ExcelExportingOptions'.

now replaced with

options.ExportingEventHandler = AddressOf ExportingHandler 

is working properly as expected.

Thanks again

[Jai Ganesh S]

Hi Amit, 

 

Thank you for the update. 

 

Please let us know if you need further assistance on this. 

 

Regards, 

Jai Ganesh S