none
C# reading data from excel columns RRS feed

  • Question

  • Hi,

    I'm using VS-2015 and working my way to develop a excel -2010 addin

    I need to read data from entire sheet and save it to database. I'm dealing with a larger dataset(5-10 million) so I was wondering if someone could point me to a reference article that reads all the data in one shot and not cell by cell because I'm dealing with a larger dataset.

    I intend to use excel object model or whichever is quicker.

    My code snippet... how do i populate data table ?

    For some reason get_range method is not visible to me... (https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range.aspx)

    _worksheet= Globals.ThisAddIn.Application.ActiveSheet;
                // update the table
                _workbook = Globals.ThisAddIn.Application.Workbooks.Add(Missing.Value);
                _allCellsRange = _worksheet.Range["$A$1:$AK$5"];
    
    // data table code ??

    Can this range stuff be more dynamic like used cell ranges...

    Also I don't intend to save all the columns displayed on excel, so can I pick just those columns

    Reference: 

    Reference - 1

    http://stackoverflow.com/questions/13759810/range-instead-of-get-rangehttps://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range.aspx

    Thanks for the help 

    S





    • Edited by StSingh Thursday, March 23, 2017 6:18 PM code clean up
    Thursday, March 23, 2017 5:06 PM

Answers

  • Hi StSingh,

    you had mentioned that," I was wondering if someone could point me to a reference article that reads all the data in one shot and not cell by cell "

    you can try to refer code below.

    C# code:

    Public Sub ImportDataFromExcel(excelFilePath As String) 
            'declare variables - edit these based on your particular situation 
            Dim ssqltable As String = "Table1" 
            ' make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
            Dim myexceldataquery As String = "select student,rollno,course from [sheet1$]" 
            Try 
                'create our connection strings 
                Dim sexcelconnectionstring As String = (Convert.ToString("provider=microsoft.jet.oledb.4.0;data source=") & excelFilePath) + ";extended properties=" + """excel 8.0;hdr=yes;""" 
                Dim ssqlconnectionstring As String = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True" 
                'execute a query to erase any previous data from our destination table 
                Dim sclearsql As String = Convert.ToString("delete from ") & ssqltable 
                Dim sqlconn As New SqlConnection(ssqlconnectionstring) 
                Dim sqlcmd As New SqlCommand(sclearsql, sqlconn) 
                sqlconn.Open() 
                sqlcmd.ExecuteNonQuery() 
                sqlconn.Close() 
                'series of commands to bulk copy data from the excel file into our sql table 
                Dim oledbconn As New OleDbConnection(sexcelconnectionstring) 
                Dim oledbcmd As New OleDbCommand(myexceldataquery, oledbconn) 
                oledbconn.Open() 
                Dim dr As OleDbDataReader = oledbcmd.ExecuteReader() 
                Dim bulkcopy As New SqlBulkCopy(ssqlconnectionstring) 
                bulkcopy.DestinationTableName = ssqltable 
                While dr.Read() 
                    bulkcopy.WriteToServer(dr) 
                End While 
                dr.Close() 
                oledbconn.Close() 
                Label1.Text = "File imported into sql server." 
                'handle exception 
            Catch ex As Exception 
            End Try 
        End Sub

    Sql code:

    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    SET ANSI_PADDING ON 
    GO 
    CREATE TABLE [dbo].[Table1]( 
        [student] [varchar](50) NULL, 
        [rollno] [int] NULL, 
        [course] [varchar](50) NULL 
    ) ON [PRIMARY] 
    GO 
    SET ANSI_PADDING OFF 
    GO 
    

    Reference:

    Import Excel Spreadsheet data into Sql Server table via C# and vb.net

    it will copy all the data of sheet in one time. you can choose the columns that you want to export. so you not need to export all the columns.

     you can try to test it on your side. It can work in most of the cases. but you had mentioned that you are trying to export millions of data. so you need to test it with your data because I don't have these much large sheet to test.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 1:17 AM
    Moderator

All replies

  • Hi StSingh,

    you had mentioned that," I was wondering if someone could point me to a reference article that reads all the data in one shot and not cell by cell "

    you can try to refer code below.

    C# code:

    Public Sub ImportDataFromExcel(excelFilePath As String) 
            'declare variables - edit these based on your particular situation 
            Dim ssqltable As String = "Table1" 
            ' make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
            Dim myexceldataquery As String = "select student,rollno,course from [sheet1$]" 
            Try 
                'create our connection strings 
                Dim sexcelconnectionstring As String = (Convert.ToString("provider=microsoft.jet.oledb.4.0;data source=") & excelFilePath) + ";extended properties=" + """excel 8.0;hdr=yes;""" 
                Dim ssqlconnectionstring As String = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True" 
                'execute a query to erase any previous data from our destination table 
                Dim sclearsql As String = Convert.ToString("delete from ") & ssqltable 
                Dim sqlconn As New SqlConnection(ssqlconnectionstring) 
                Dim sqlcmd As New SqlCommand(sclearsql, sqlconn) 
                sqlconn.Open() 
                sqlcmd.ExecuteNonQuery() 
                sqlconn.Close() 
                'series of commands to bulk copy data from the excel file into our sql table 
                Dim oledbconn As New OleDbConnection(sexcelconnectionstring) 
                Dim oledbcmd As New OleDbCommand(myexceldataquery, oledbconn) 
                oledbconn.Open() 
                Dim dr As OleDbDataReader = oledbcmd.ExecuteReader() 
                Dim bulkcopy As New SqlBulkCopy(ssqlconnectionstring) 
                bulkcopy.DestinationTableName = ssqltable 
                While dr.Read() 
                    bulkcopy.WriteToServer(dr) 
                End While 
                dr.Close() 
                oledbconn.Close() 
                Label1.Text = "File imported into sql server." 
                'handle exception 
            Catch ex As Exception 
            End Try 
        End Sub

    Sql code:

    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    SET ANSI_PADDING ON 
    GO 
    CREATE TABLE [dbo].[Table1]( 
        [student] [varchar](50) NULL, 
        [rollno] [int] NULL, 
        [course] [varchar](50) NULL 
    ) ON [PRIMARY] 
    GO 
    SET ANSI_PADDING OFF 
    GO 
    

    Reference:

    Import Excel Spreadsheet data into Sql Server table via C# and vb.net

    it will copy all the data of sheet in one time. you can choose the columns that you want to export. so you not need to export all the columns.

     you can try to test it on your side. It can work in most of the cases. but you had mentioned that you are trying to export millions of data. so you need to test it with your data because I don't have these much large sheet to test.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 1:17 AM
    Moderator
  • Thanks Deepak

    but since this is an excel addin, excel file will not be having a location since its not saved and should I be using Excel Object model since its VSTO.

    Thanks for your help

    Friday, March 24, 2017 8:14 AM
  • Hi StSingh,

    you can try to fetch location of file with code below in Excel Addin.

    string currentWorkbookFileName = this.Application.ActiveWorkbook.Name

    alternatively try "Globals.ThisAddin.Application" if it gives any error.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 8:29 AM
    Moderator
  • Thanks Deepak.

    For populating data onto excel I used this code so ( thinking out loud ) there must be a way to read the excel List object and save it in a data table (assuming it would contain all the updates made on excel). 

    How do I access this ListObject ?

    Code snippet for populating excel object.

      System.Windows.Forms.BindingSource bs = new System.Windows.Forms.BindingSource();
                    
      Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1];
                                    
      // Create a workhseet host item.
      Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                   
       Microsoft.Office.Interop.Excel.Range cell = extendedWorksheet.Range["$A$1:$AK$5"];                
    
       this.sListObject = extendedWorksheet.Controls.AddListObject(cell, "Bdetails");
    
      this.sListObject.AutoSetDataBoundColumnHeaders = true;               
    
      this.sListObject.SetDataBinding(datatable, "", ... columns)

    Googled further and found the list of events that I could use but there is nothing like text changed event... so i could grab that cell and column header!

    https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.listobject_events.aspx



    • Edited by StSingh Friday, March 24, 2017 12:47 PM
    Friday, March 24, 2017 11:01 AM
  • Hi StSingh,

    in your last reply, you had mentioned that,"For populating data onto excel I used this code so ( thinking out loud )"

    are you populating from Excel to database or database to excel?

    then you had mentioned that ,"there must be a way to read the excel List object and save it in a data table "

    but in your original post you want to export all the data of sheet in one shot, and now you are trying to access data by cell, column and list.

    it's totally an opposite approach from that you asked in original post.

    below is an example code to access values with listobject.

    private void ListObject_Range()
    {
        Microsoft.Office.Tools.Excel.ListObject list1 =
            this.Controls.AddListObject(
            this.Range["A1", "C4"], "list1");
    
        MessageBox.Show("The list object contains " +
            list1.Range.Cells.Count.ToString() + " cells.");
    }
    

    Reference:

    ListObject.Range Property

    ListObject Interface

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 8:18 AM
    Moderator
  • Thanks Deepak, you have been very helpful.

    I will mark your response as an answer because thats definately one way to read data from excel.

    Alternatively, I used a event to capture all the changes on excel using event

     sListObject.Change += new Microsoft.Office.Tools.Excel.ListObjectChangeHandler(SListChange_Handler);

    and populated into a list ..

    Finally using that list I updated database.


    Monday, March 27, 2017 9:35 AM
  • If my understanding is correct then ListObject should would contain all the changes on excel sheet. If this is correct then would't it be better to just garb the list object and send it back to database/ data table ?
    Monday, March 27, 2017 9:42 AM
  • Hi StSingh,

    you need to check that on your side.

    in documentation it is not describe that it contains all the changes or not.

    if it not contain all the changes then you need to assign the latest values again.

    you can try to fetch the value before change and after change and then match the result.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 28, 2017 5:09 AM
    Moderator