none
Visual studio 2008 not showing chart from excel file

    Question

  • Hello there,

    I am trying to show my excel chart in visual basic listbox and it does not work.

    I have added the reference of microsoft.office.interop.excel

    in my code i have put the range of column "A" and i do not know how to change the range to where the chart is in the excel.

    Here is my code!

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Form1
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim objXLApp As Excel.Application
            Dim intLoopCounter As Integer
            Dim objXLWb As Excel.Workbook
            Dim objXLWs As Excel.Worksheet
            Dim objRange As Excel.Range
            objXLApp = New Excel.Application
            objXLApp.Workbooks.Open("*****\Book1.xlsx")
            objXLWb = objXLApp.Workbooks(1)
            objXLWs = objXLWb.Worksheets(1)
            For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
                objRange = objXLWs.Range("A" & intLoopCounter)
                ListBox1.Items.Add(objRange.Value)
            Next intLoopCounter
            objXLApp.Quit()
        End Sub
    End Class

    I have attached the excel file screen shot if that can help in any way...So basically When i click on the button in VB than it should show me the excel chart. My code just shows me the column "A" which shows me the "data" column only from the excel file. I will be very thankful to any kind of help you guys can provide me with. Thanks in ADVANCE.

    Amman Anwar

    Tuesday, April 03, 2012 11:27 PM

Answers

  • Hi Amman,

    Welcome to the MSDN forum.

    To display multiple lines data, I’d suggest you use DataGridView. You can simply bind the worksheet to DGV like this:

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

            Try

                If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                    Dim myconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & OpenFileDialog1.FileName & ";Extended Properties=Excel 12.0 Xml;"

                    Dim MyConnection As New System.Data.OleDb.OleDbConnection(myconn)

                    Dim MyCommand As New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", MyConnection)

                    Dim MyAdapter As New System.Data.OleDb.OleDbDataAdapter

                    MyAdapter.SelectCommand = MyCommand

                    Dim DtSet As New System.Data.DataSet()

                    MyAdapter.Fill(DtSet)

                    DataGridView1.Show()

                    DataGridView1.DataSource = DtSet.Tables(0)

                    MyConnection.Close()

                End If

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

    End Sub

    Moreover, you can use Chart.Export Method to convert the chart to image and then display it.

    I hope this will be helpful.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us

    Thursday, April 05, 2012 8:14 AM
    Moderator

All replies

  • Hi Amman,

    Welcome to the MSDN forum.

    To display multiple lines data, I’d suggest you use DataGridView. You can simply bind the worksheet to DGV like this:

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

            Try

                If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                    Dim myconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & OpenFileDialog1.FileName & ";Extended Properties=Excel 12.0 Xml;"

                    Dim MyConnection As New System.Data.OleDb.OleDbConnection(myconn)

                    Dim MyCommand As New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", MyConnection)

                    Dim MyAdapter As New System.Data.OleDb.OleDbDataAdapter

                    MyAdapter.SelectCommand = MyCommand

                    Dim DtSet As New System.Data.DataSet()

                    MyAdapter.Fill(DtSet)

                    DataGridView1.Show()

                    DataGridView1.DataSource = DtSet.Tables(0)

                    MyConnection.Close()

                End If

            Catch ex As Exception

                MsgBox(ex.ToString)

            End Try

    End Sub

    Moreover, you can use Chart.Export Method to convert the chart to image and then display it.

    I hope this will be helpful.

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us

    Thursday, April 05, 2012 8:14 AM
    Moderator
  • Do I need to add any reference?

    Amman Anwar|MCTS|MOS|

    Tuesday, April 10, 2012 10:07 PM
  • You just need to reference to microsoft.office.interop.excel.

    Shanks Zen
    MSDN Community Support | Feedback to us

    Monday, April 16, 2012 9:09 AM
    Moderator