Excel + vb.net + Category (x) axis labels [SOLVED]

• • Reply

  15 years ago

  by computerg33k

  Dana Marie Dillman

  Somewhere over the Rainbow, United States

  Joined 16 years ago
  I want to export a dataset from vb.net to excel.  So far everything looks good, but I want to put the category (x) axis labels as the date in which the user inputted information (ie: the weight is located within column A and the date that the weight was entered is located within column B).  Below is my code.
  
  Code:

  
  Private Sub mmProgressWeightCrt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mmProgressWeightCrt.Click
         Dim xlsapp As New Excel.Application()
         Dim xlsBook As Excel.Workbook
         Dim xlsdoc As Excel.Worksheet
         Dim xlschart As Excel.Chart
         Dim oRng As Excel.Range
  
         xlsapp.Visible = True
  
         xlsBook = xlsapp.Workbooks.Add
         xlsdoc = xlsBook.Worksheets.Add
         xlschart = xlsBook.Charts.Add
  
         Try
             Dim ds As New System.Data.DataSet()
             Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
             Dim MyConnection As System.Data.OleDb.OleDbConnection
  
             MyConnection = New System.Data.OleDb.OleDbConnection(MyConn)
  
             MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select Weight, goalweight, WeighInDt as Dt from [" & Trim(iName) & "$] where name = " & Chr(39) & Trim(iName) & Chr(39), MyConnection)
  
             MyConnection.Open()
  
             ds = New System.Data.DataSet()
             MyCommand.Fill(ds)
  
  
             Dim dt As New System.Data.DataTable()
  
             dt = ds.Tables(0)
  
             Dim dtR As System.Data.DataRow()
  
             'fill cells with values to build our chart
             With xlsdoc
                 Dim i As Integer = 0
                 Dim r As Integer = 1
                 Dim j As Integer
                 Dim iRows As Integer = dt.Rows.Count
  
                 For j = 0 To iRows - 1
                     Dim Arangerow As String = "A" & r
                     Dim Brangerow As String = "B" & r
                     Dim Crangerow As String = "C" & r
                     .Range(Brangerow).Value = dt.Rows(i).Item("dt").ToString
                     .Range(Arangerow).Value = dt.Rows(i).Item("weight").ToString
                     r += 1
                     Arangerow = "A" & r
                     .Range(Arangerow).Value = dt.Rows(i).Item("goalweight").ToString
                     i += 1
                 Next j
             End With
             MyConnection.Close()
         Catch ex As Exception
             MsgBox(ex.ToString)
         End Try
  
         Dim xlsAxisCategory, xlsAxisValue As Excel.Axes
         Dim xlsSerie As Excel.SeriesCollection = xlschart.SeriesCollection
         Dim mySeries As Excel.Series
  
         With xlschart
             .ChartType = Excel.XlChartType.xlLine
             'Here we are setting the type of chart we want
             .SetSourceData(xlsdoc.Range("A1:a4"), Excel.XlRowCol.xlColumns)
  
             .ApplyDataLabels()
             .Legend.Delete()
  
             'This is the range from where the chart will be built
  
             xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary)
             xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
             xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text() = "Date"
             xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMajorGridlines = False
             xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMinorGridlines = False
  
             xlsAxisValue = .Axes(, Excel.XlAxisGroup.xlPrimary)
             xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
             xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text() = "Weight"
             xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = False
             xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMinorGridlines = False
  
  
             .PlotArea.Interior.ColorIndex = 2
             .ChartArea.Interior.ColorIndex = 2
             .ChartArea.Interior.PatternColorIndex = 1
  
             ''for statement to determine how many weight in days there are
             ''do case statement to determine the date
             Dim x As Integer = 1
             Dim y As Integer = 1
  
             Dim ds As New System.Data.DataSet()
             Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
             Dim MyConnection As System.Data.OleDb.OleDbConnection
  
             MyConnection = New System.Data.OleDb.OleDbConnection(MyConn)
             MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select Weight, goalweight, WeighInDt as Dt from [" & Trim(iName) & "$] where name = " & Chr(39) & Trim(iName) & Chr(39), MyConnection)
             MyConnection.Open()
  
             ds = New System.Data.DataSet()
             MyCommand.Fill(ds)
  
             Dim dt As New System.Data.DataTable()
             dt = ds.Tables(0)
  
             Dim dtR As System.Data.DataRow()
  
             For Each dtR In dt.Rows
                 xlsSerie.Item(x).Name = dt.Rows(y).Item("dt").ToString
                 x += 1
                 xlsSerie.Item(x).Name = dt.Rows(y).Item("weight").ToString
             Next
  
             .Location(Excel.XlChartLocation.xlLocationAsNewSheet)
             xlsBook.SaveAs(Application.StartupPath & "\MyNewChart.xls")
  
             ' AutoFit columns A.
             oRng = xlsdoc.Range("A1", "IV65536")
             oRng.EntireColumn.AutoFit()
             ' Make sure Excel is visible and give the user control
             ' of Excel's lifetime.
             xlsapp.Visible = True
             xlsapp.UserControl = True
  
             ' Make sure that you release object references.
             oRng = Nothing
             xlsdoc = Nothing
             xlsBook = Nothing
             xlsapp = Nothing
         End With
  
         'xlsapp.Quit()
         ' xlsapp = Nothing
  
     End Sub
  

  
  
  
  JUST HAVE TO SWITCH THE WAY THE DATA WENT INTO EXCEL--IF THE DATES CAME FIRST, THEN THE WEIGHT, IT WAS JUST FINE :P
  Report abuse