locked
Excel to DataGridView RRS feed

  • Question

  • Hi

     

    I'm trying to load an excel file to a DataGridView control.

    It works fine using the following code:

     

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & OpenFileDialog.FileName & ";Extended Properties=Excel 8.0;"

    Dim ExcelConnection As New OleDbConnection(ConnectionString)

    Dim ad As New OleDbDataAdapter

    Dim dt As New DataTable

     

    ad.SelectCommand = New OleDbCommand("SELECT * FROM [Foglio1$]", ExcelConnection)

    'Foglio1 is the name of the excel worksheet

    ad.Fill(dt)

    <DataGridView>.DataSource = dt

     

    Columns are created automatically but I would like them to be mapped to my existing DataGridView columns.

    Additionally, and most important, my excel file contains a column where first cell values are numeric, while the last ones are alphanumeric. Thus the DataTable <dt> has a column of type "Double" instead of an alphanumeric type, and when the DataTable is bounded to the grid, I correctly see first cells with the numeric value inside, but last cells contain blanks (where in the source excel file there were alphanumeric values)

     

    How can I change the type of the column in order to display correct values instead of blanks?

    I hope the scenario is clear enough.

     

    Thanx

    Pietro

    Thursday, September 6, 2007 1:48 PM

Answers

  • Change the cell format to Text in the excel file would fix this problem, if you stick to the General type or Numeric type of the cell, you have to read the excel file content into the DataTable manually.

    Wednesday, September 12, 2007 9:01 AM

All replies

  • Hi,

    1.  set the autogeneratecolumn property of the dgv to false. (do it programmatically).

    2. then right click on the grid.. go to columns...

       now add the columns. 

           the most important property to set is the dataproperty of the column.

           set it to the same name as a column in your datatable. this map the datagridviewcolumn to the datatable column.

           and in this same window where you set the properties of the columns, you would easily find a column Type property.

    let know if this is what you are looking for okay,

    enjoy coding.

     

     

     

    Thursday, September 6, 2007 2:05 PM
  • In the property page of the columns I find the 'ColumnType' property, and I set it to 'DataGridViewTextBoxColumn'.

    The column bounds to the excel column, but it's still of type double and still I only see numeric values in it

     

    edit: when the DataTable is filled, I already loose the alphanumeric values, so I should find I way to manipulate the DataTable object before I bound it to the grid

    Thursday, September 6, 2007 2:17 PM
  • My mistake sorry.

    Since the DataTable is auto generating the columns and thereby auto setting the type to double, overcome that by already adding a datacolumn by the same name as the column in the excel sheet. please verify as name is case sensitive and is used as a mapping -- > the same way i was talking about datapropertyname above.  do that before the datatable is filled.

     

     

    Thursday, September 6, 2007 2:42 PM
  • <DataTable>.Columns.Add(<ExcelColumnName>, System.Type.GetType("System.String"))

    <Adapter>.Fill(<DataTable>)

     

    The new created column is mapped, but although the type is String, looks like it's still handled as Double.

    What about if is Excel that provides the data already casted to double? In this case I should create an OLE object and access the Excel in that way Sad

    Thursday, September 6, 2007 3:12 PM
  •  

    i had this problem before and i simply changed the first record to a string value ... e.g  1.2  to test1.2   but this was just a one time test program i was running... basically it is the first row that determines the column type i guess try and see it yourself.
    Thursday, September 6, 2007 3:54 PM
  • Already tried that way Smile and the first cell is not displayed like the others!

    I checked the cell type in excel and that's Generic.

     

    I'll look deeply tomorrow... this is strange!
    Thursday, September 6, 2007 4:25 PM
  • Change the cell format to Text in the excel file would fix this problem, if you stick to the General type or Numeric type of the cell, you have to read the excel file content into the DataTable manually.

    Wednesday, September 12, 2007 9:01 AM
  • I started from scratch with another example, and this is the code

     

    ' Excel Objects

    Dim ExcelObj As New Excel.Application

    Dim WBook As Excel.Workbook

    Dim WSheet As Excel.Worksheet

    Dim Selection As Excel.Range

     

    WBook = ExcelObj.Workbooks.Open("d:\excelsample.xls", , False)

    ExcelObj.DisplayAlerts = False

    ExcelObj.Visible = True

    WSheet = WBook.Worksheets(1)

     

    'Conversion

    Selection = WSheet.Cells

    Selection.Select()

    Selection.NumberFormat = "@"

    WBook.Save()

     

    'Connection to the file

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\excelsample.xls;Extended Properties=Excel 8.0;")

    Dim ad As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand("SELECT * FROM [" & WSheet.Name & "$]", conn))

    Dim dt As New DataTable

     

    ad.Fill(dt)

    dgv.DataSource = dt

     

    ExcelObj.Quit()

     

    This is my excel test file

    c1 c2 c3
    a 1 1
    b 2 2
    c 3 3
    d 4 a4
    e 5 b5

     

    The program sets the entire sheet to Text format but nothing changes, I still see a situation like this in my DataGrid...

    c1 c2 c3
    a 1 1
    b 2 2
    c 3 3
    d 4
    e 5

     

    But I notice one thing: if I write again one by one in column c3 each single value (1, 2, 3), it's like the cell format is applied in that moment not before when for example the program does it.

    Wednesday, September 12, 2007 9:45 AM
  • I test with the same logic as yours, no problem.
    PS: I'm using Excel 2007.
    Below is my testing code

    Code Snippet

    private void Form4_Load(object sender, EventArgs e)

            {

                object oMissing = Missing.Value;

                Microsoft.Office.Interop.Excel.Application ExcelObj =
                    new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbook WBook;

                Microsoft.Office.Interop.Excel.Worksheet WSheet;

                Microsoft.Office.Interop.Excel.Range Selection;

                WBook = ExcelObj.Workbooks.Open(@"c:\test\1.xlsx",

                    oMissing, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing, oMissing);

     

                WSheet = (Microsoft.Office.Interop.Excel.Worksheet)WBook.Sheets.get_Item(1);

                // Conversion

                Selection = WSheet.Cells;

                Selection.Select();

                Selection.NumberFormat = "@";

                WBook.Save();

     

                string ConnectionString =

                    "Provider=Microsoft.ACE.OLEDB.12.0;" +

                    "Data Source=" + @"c:\test\1.xlsx;" +

                    "Excel 12.0;HDR=YES;";

     

                OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString);

                OleDbDataAdapter ad = new OleDbDataAdapter();

     

                ad.SelectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", ExcelConnection);

                DataTable dt = new DataTable();

                ad.Fill(dt);

     

                this.dataGridView1.DataSource = dt;

            }

     

     

    Wednesday, September 12, 2007 10:17 AM