import excel file to datagridview

[rola theo]

Good day guys I need help for adding 2 column in datagridview
my project is :
the import and the varibles are :

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices


Public Class Form2
    Friend xlApp As New Excel.Application
    Friend xlWorkBook As Excel.Workbook
    Friend xlWorkSheet As Excel.Worksheet
    Friend ch As String
    Friend ExcelSheetName As Object
    Friend exWS2 As Microsoft.Office.Interop.Excel.Worksheet

i open any file excel with openfiledialog this is the code:

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

        OpenFileDialog1.ShowDialog()

        TextBox1.Text = OpenFileDialog1.FileName

        ch = OpenFileDialog1.FileName

        Process.Start("Excel", TextBox1.Text)

        ComboBox1.Items.Clear()
    End Sub

i open any sheet excel this is the code:

     Public Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    Dim x1 As String

    xlApp.Visible = False
    xlWorkBook = xlApp.Workbooks.Open(ch)
    xlWorkSheet = CType(xlWorkBook.Sheets(1), Worksheet)

    If ComboBox1.Items.Count = Nothing Then
        With xlWorkSheet


            For Each Me.xlWorkSheet In xlWorkBook.Worksheets
                x1 = xlWorkSheet.Name

                ComboBox1.Items.Add(x1)

            Next xlWorkSheet

        End With
    Else
        MsgBox("Déja Pleine!", CType(MessageBoxIcon.Error, MsgBoxStyle))


    End If
End Sub

i open any column excel this is the code:

Public Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    xlWorkSheet = CType(xlWorkBook.Sheets(ComboBox1.Text), Excel.Worksheet)
    xlWorkSheet.Activate()
    xlApp.Visible = True


    Dim key As String = CStr(DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Key)
    Dim value As String = DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Value
    Dim DoesSheetExists As Boolean = False

    For Each xs In xlApp.Sheets
        If xs.Name = value Then
            DoesSheetExists = True
        End If
    Next

    If DoesSheetExists = True Then
        MsgBox("Sheet already exists", CType(MessageBoxIcon.Error, MsgBoxStyle))
    Else

        With xlWorkSheet

            Dim lastrow As Integer = xlWorkSheet.Cells.Rows.End(XlDirection.xlDown).Row
            Dim colletter As String = ColumnIndexToColumnLetter(CInt(key))

            exWS2 = DirectCast(xlWorkBook.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
            exWS2.Name = value

            xlWorkSheet.Range("A1:A" & lastrow.ToString).Copy(exWS2.Range("A1"))

            xlWorkSheet.Range(colletter & "1:" & colletter & lastrow.ToString).Copy(exWS2.Range("B1"))

            exWS2.Range("A1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
            exWS2.Range("B1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
            exWS2.Range("A1").Interior.ColorIndex = 8
            exWS2.Range("B1").Interior.ColorIndex = 8
            exWS2.Range("A2:A" & lastrow.ToString).Interior.ColorIndex = 20
            exWS2.Range("A1:A" & lastrow.ToString).HorizontalAlignment = -4108
            exWS2.Range("B1:B" & lastrow.ToString).HorizontalAlignment = -4108
            exWS2.Range("A1").Font.Name = "Times New Roman"
            exWS2.Range("B1").Font.Name = "Times New Roman"
            exWS2.Range("B1").Font.FontStyle = "Gras"
            exWS2.Range("A1").Font.FontStyle = "Gras"

this column will be add in the new sheet which has the name of the column that i select of the combobox .
what i want is to add the column that i opened in datagridview1
Also i have to calculate sum , max and min of the column, I want to add also the sum , max and min in datagridview2
my form that open excel file and add the sheet :[Click Here
And my file excel :Click Here

please help me

[djjeavons]

Hi

I haven't looked all through your code and may be wrong but you mention doing SUM, MAX and MIN on the data. Can I suggest you use ADO.NET for this, much better than using COM for this scenario.

This snippet (https://www.daniweb.com/software-development/csharp/code/492766/display-excel-data-in-a-datagridview) shows how to do a basic SQL select on an Excel sheet which you can then combine MAX etc. statements with.

If this is not of help, can you describe exactly what you want to do.

HTH

[rola theo]

Hi , thanks for help,but
All i want is after selecting the column name and calculate sum , max and min;
i mean the sheet named the name of the column ( figure of excel file) will be added to datagridview
my problem is the connection between datagrid and excel because the datasource is variable; every time i select a column and a sheet will be added

[djjeavons]

Hi

I'm sorry but I don't understand what you are trying to achieve.

Can you explain without the code what you want to do and maybe I or someone else can help.

Sorry, don't mean to make you post a lot but just confused at the moment.

[rola theo]

ok
no mater djjeavons
it's simple i want a simple connection between datagrid and excel file

[djjeavons]

Ok, so the snippet I pointed you too does exactly that. It uses ADO.NET to query the contents of an Excel file (displays the worksheets in a combo) and upon selection of a worksheet will show the contents in a DataGridView.

My point earlier (if I haven't misunderstood) is that along with standard SQL to select the data you can combine this with aggregate functions such as MAX.

[rola theo]

yes you are almost there

[djjeavons]

Ok, that's great, I'm almost there. Don't keep me guessing, tell me the rest and we might be able to help you.

[rola theo]

when i select a column from combobox it will be added to datagrid
this 2 column of this worksheet which named the name of the column i select :
[Click Here]
thanks for help :)

[djjeavons]

when i select a column from combobox it will be added to datagrid
this 2 column of this worksheet which named the name of the column i select :

What do you mean by this. Do you only want to select data from the second column? For example, do a Max on the second column? If so, you could do SELECT MAX ([2]) As MaxOf2 FROM [KPI2$], is that what you mean, or did you mean something else.

[rola theo]

No,just the columns(cell and the name of the column i selected from combobox)
add to datagrid
datasource is variable because any time i select from the combobox, the column will be added to datagrid

[rola theo]

hello !!
whould you help me and give me this code with vb.net?
Click Here

[naz1234]

hello !!
whould you help me and give me this code with vb.net?
Click Here

Hi rola theo,

I just convert it from the other website, just googled how to convert c# to vb.net or c# to vb.net converter.

Here the converted code, hope this can help you

Imports System.Data.OleDb
Partial Public Class Form1

    Inherits Form
    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub openFileButton_Click(sender As Object, e As EventArgs) Handles openFileButton.Click
        Dim openDialog As New OpenFileDialog()
        openDialog.Filter = "Excel|*.xlsx"
        If openDialog.ShowDialog() = DialogResult.OK Then
            excelFileTextBox.Text = openDialog.FileName
            'Get all worksheet names from the Excel file selected using GetSchema of an OleDbConnection
            Dim sourceConnectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", excelFileTextBox.Text)
            Dim connection As New OleDbConnection(sourceConnectionString)
            connection.Open()
            Dim tables As DataTable = connection.GetSchema("Tables", New [String]() {Nothing, Nothing, Nothing, "TABLE"})
            connection.Dispose()
            'Add each table name to the combo box
            If tables IsNot Nothing AndAlso tables.Rows.Count > 0 Then
                worksheetsComboBox.Items.Clear()
                For Each row As DataRow In tables.Rows
                    worksheetsComboBox.Items.Add(row("TABLE_NAME").ToString())
                Next
            End If
        End If
    End Sub


    Private Sub closeButton_Click_1(sender As Object, e As EventArgs) Handles closeButton.Click
        Application.[Exit]()
    End Sub



    Private Sub worksheetsComboBox_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles worksheetsComboBox.SelectedIndexChanged
        'Display the data from the selected Worksheet
        Dim sourceConnectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", excelFileTextBox.Text)
        Dim adapter As New OleDbDataAdapter([String].Format("SELECT * FROM [{0}]", worksheetsComboBox.SelectedItem.ToString()), sourceConnectionString)
        Dim currentSheet As New DataTable()
        adapter.Fill(currentSheet)
        adapter.Dispose()
        excelDataGridView.DataSource = currentSheet
    End Sub
End Class

'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================

[rola theo]

thanks :)