Exporting the Infragistics UltraChart to Excel

Here is a simple example of exporting the UltraChart to an Excel spreadsheet.  It uses Office Interop so you need to include a reference to the Microsoft Office 11 Object Library in your project.

   1:  Public Class Export
   2:  
   3:      Private Sub ChartToExcel(ByVal chart As Infragistics.Win.UltraWinChart.UltraChart)
   4:          Try
   5:   
   6:              Dim xl As New Excel.Application
   7:              Dim wb As Excel.Workbook = xl.Workbooks.Add
   8:              Dim sheet As Excel.Worksheet = wb.Worksheets(1)
   9:              sheet.Visible = Excel.XlSheetVisibility.xlSheetVisible
  10:   
  11:              'add the top title to the sheet as the header
  12:              sheet.Range("A1").Value = chart.TitleTop.Text
  13:              sheet.Range("A1").Font.Bold = True
  14:              sheet.Range("A1").Font.Size = 14
  15:   
  16:              Dim dt As DataTable = CType(chart.DataSource, DataSet).Tables("Data")
  17:   
  18:              'output the headers.
  19:              Dim intCol As Integer = 1
  20:              For Each col As DataColumn In dt.Columns
  21:                  sheet.Cells(2, intCol) = col.ColumnName
  22:                  intCol += 1
  23:              Next
  24:   
  25:              'Bold the headers.
  26:              sheet.Range("A2:" & Chr(64 + intCol) & "2").Font.Bold = True
  27:              sheet.Range("A2:" & Chr(64 + intCol) & "2").Font.Size = 12
  28:   
  29:              'start our data on row 3 of the worksheet.
  30:              Dim intRow As Integer = 3
  31:              For Each row As DataRow In dt.Rows
  32:                  intCol = 1
  33:                  For Each col As DataColumn In dt.Columns
  34:                      sheet.Cells(intRow, intCol) = row.Item(col.ColumnName)
  35:                      intCol += 1
  36:                  Next
  37:                  intRow += 1
  38:              Next
  39:   
  40:              wb.Application.Visible = True
  41:   
  42:              Dim oChart As Excel.Chart
  43:              Dim xlsAxisCategory, xlsAxisValue As Excel.Axes
  44:              Dim charts As Excel.ChartObjects = sheet.ChartObjects(Type.Missing)
  45:   
  46:              ' Adds a chart at x = 0, y = 0, 500 points wide and 300 tall.
  47:              Dim chartObj As Excel.ChartObject = charts.Add(0, 0, 400, 300)
  48:              oChart = chartObj.Chart
  49:   
  50:              Dim xlsSerie As Excel.SeriesCollection = oChart.SeriesCollection
  51:              oChart.ChartType = Excel.XlChartType.xlLine
  52:   
  53:              'add 64 to intCol since ASCII A is 65 and then take the Chr() of it.BR>
  54:              Dim chartRange As Excel.Range = sheet.Range("A1", Chr((intCol - 1) + 64) & intRow)
  55:              oChart.SetSourceData(chartRange, Type.Missing)
  56:   
  57:              wb.Application.Visible = True
  58:   
  59:          Catch ex As Exception
  60:              Console.WriteLine("Doh!")
  61:          End Try
  62:   
  63:      End Sub
  64:  End Class

---

---