none
Excel import/export from/to ADO.NET dataset RRS feed

  • Question

  • I'm adding a feature to import/export from/to Excel of the ADO.NET tables in my Visual Basic 2008 Express Windows Forms app.  The DB (if that matters) is SQL Server CE 3.5.  I'd like not to have to restrict what version of Excel the user has installed.  No formatting in Excel is expected or required (other than the right data in the right columns).

    I've found some links on MSDN but they seem a bit dated.  Before I start putting a lot of effort into this I'd like to see if anyone has a pointer to the best method to use in this situation.

     

    http://support.microsoft.com/kb/306022/en-us

     

    http://support.microsoft.com/kb/278973/

     

    http://support.microsoft.com/kb/257819/EN-US/

    Thanks,
    Eric

    Update:

    Here's what I have so far to facilitate the export function.
    It is expected that the Excel file being exported to is new (no data).

    Even though the source table has rows, no rows are exported.
    Also, the command builder did not add Insert, Update or Delete commands.  Probably because the Select command reveals no schema.  I want the schema (column names) to be inherited from the source table.

     Private Sub ExportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportButton.Click
    
      Dim saveResult As DialogResult = SaveFileDialog1.ShowDialog
    
      Select Case saveResult
    
       Case Windows.Forms.DialogResult.OK
    
        ExportFileNameLabel.Text = SaveFileDialog1.FileName
    
        Dim strConn As String = "Driver={Microsoft Excel Driver (*.xls)};" & _
    
         "DBQ=" & ExportFileNameLabel.Text & ";;"
    
        Try
    
         Using odbConn As New OdbcConnection(strConn)
    
          Dim strCmd As String = "SELECT * FROM [Sheet1$]"
    
          Dim odbAdptr As New OdbcDataAdapter(strCmd, odbConn)
    
          Dim odbBldr As New OdbcCommandBuilder(odbAdptr)
    
          odbConn.Open()
    
          Dim rows As Integer = odbAdptr.Update(MyDataSet.Table1)
    
          MessageBox.Show(rows.ToString & " rows added", "Table - Export")
    
         End Using
    
        Catch ex As Exception
    
         MessageBox.Show("Unable to open " & ExportGroupBox.Text & vbCrLf & "Error: " & ex.Message, "Table - Export")
    
        End Try
    
       Case Else
    
        ExportFileNameLabel.Text = saveResult.ToString
    
      End Select
    
     End Sub
    
    
    
    

    Any pointers on how to proceed would be appreciated.

    Eric

    Update2:

    I borrowed the column names from the source table as follows but the command builder still does not generate Insert, Update or Delete commands.  Also, no exceptions are thrown.

    Dim strCmd As String = Table1TableAdapter.Adapter.SelectCommand.CommandText
    strCmd = strCmd.Remove(strCmd.IndexOf("FROM [")) & "FROM [Sheet1$]"
    

    Still needing help.
    Eric

    • Edited by Eric-67220 Friday, June 25, 2010 8:14 PM More information
    Friday, June 25, 2010 2:23 PM

Answers

  • You need refer excel dll.

    My computer has installed office 2007. After I add References, then my win app can read both xls and xlsx. You aslo can do this with office 2007 or other version COM as picture

     


    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    • Marked as answer by Eric-67220 Wednesday, June 30, 2010 4:19 PM
    Wednesday, June 30, 2010 12:09 AM

All replies

  • //Export
    
    
    Public Function AutoExportExcel(ds As DataSet, isShowExcle As Boolean, path As String) As Boolean
    	Dim dt As System.Data.DataTable = ds.Tables(0)
    
    
    	If dt.Rows.Count = 0 Then
    		Return False
    	End If
    	Dim excel As New Microsoft.Office.Interop.Excel.Application()
    	excel.Application.Workbooks.Add(True)
    	excel.Visible = isShowExcle
    
    	Dim excelPrint As Workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
    	Dim excelSheePrint As Worksheet = DirectCast(excelPrint.Worksheets(1), Worksheet)
    
    	Dim maxCol As Integer = dt.Columns.Count
    	Dim maxRow As Integer = dt.Rows.Count
    
    	excelSheePrint.get_Range(excelSheePrint.Cells(1, 1), excelSheePrint.Cells(1, maxCol)).Font.Name = "Arial"
    	excelSheePrint.get_Range(excelSheePrint.Cells(1, 1), excelSheePrint.Cells(1, maxCol)).Font.Bold = True
    	excelSheePrint.get_Range(excelSheePrint.Cells(1, 1), excelSheePrint.Cells(1, maxCol)).Font.Size = 10
    	excelSheePrint.get_Range(excelSheePrint.Cells(1, 1), excelSheePrint.Cells(maxRow + 1, maxCol)).Borders.LineStyle = 1
    
    	'Title
    
    	'coumn name
    	For i As Integer = 0 To dt.Columns.Count - 1
    		excel.Cells(1, i + 1) = dt.Columns(i).ColumnName.ToString()
    	Next
    
    	'fill data
    	For i As Integer = 0 To dt.Rows.Count - 1
    		For j As Integer = 0 To dt.Columns.Count - 1
    			Dim dtType As Object = dt.Rows(i)(j).[GetType]()
    			If dt.Rows(i)(j).[GetType]() = GetType(String) Then
    				excel.Cells(i + 2, j + 1) = "'" + dt.Rows(i)(j).ToString()
    			Else
    				Dim temp As String = Convert.ToString(dt.Rows(i)(j))
    
    				excel.Cells(i + 2, j + 1) = Convert.ToString(dt.Rows(i)(j))
    			End If
    		Next
    	Next
    
    
    	Dim dtime As String = DateTime.Now.ToString()
    
    
    	filename = path & "\" & dtime & ".xls"
    	excelSheePrint.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, True, False, _
    		XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing)
    	excel.Quit()
    	GC.Collect()
    	Return True
    End Function
    

    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    Sunday, June 27, 2010 2:27 AM
  • 'import to sql server datatable
    
    Public Sub CopyTable(filename As String, sql As String, tableName As String)
    	' Connection String to Excel Workbook
    	Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"
    
    	Using con As New OleDbConnection(excelConnectionString)
    		Dim sqlcom As New OleDbCommand(sql & " FROM [Sheet1$]", con)
    		con.Open()
    
    		' Create DbDataReader to Data Worksheet
    		Using dr As DbDataReader = sqlcom.ExecuteReader()
    			Dim M_str_sqlcon As String = "Data Source=(local);Database=YangZiDB;user Id=sa;PWD=060125"
    
    			'Bulk Copy to SQL Server 
    			Using bulkCopy As New SqlBulkCopy(M_str_sqlcon)
    				bulkCopy.DestinationTableName = tableName
    				bulkCopy.WriteToServer(dr)
    			End Using
    
    		End Using
    	End Using
    End Sub
    

    • Edited by Huan Li Sunday, June 27, 2010 2:37 AM
    Sunday, June 27, 2010 2:30 AM
  • //import to datatable
    
    Public Function ImportExcelToDataTable_R(path As [String]) As System.Data.DataTable
    	Dim app As New Microsoft.Office.Interop.Excel.ApplicationClass()
    	Dim workBook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(path, 0, True, 5, "", "", _
    		True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, vbTab, False, False, 0, _
    		True, 1, 0)
    
    	Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(workBook.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
    
    	Dim index As Integer = 0
    	Dim rowIndex As Object = 2
    
    	dt = New System.Data.DataTable()
    
        'column1~column4 is your excel sheet column name
    	dt.Columns.Add("column1")
    	dt.Columns.Add("column2")
    	dt.Columns.Add("column3")
    	dt.Columns.Add("column4")
    
    
    
    	Dim row As DataRow
    
    	While DirectCast(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2 IsNot Nothing
    
    		row = dt.NewRow()
    
    
    		row(0) = Convert.ToString(DirectCast(workSheet.Cells(rowIndex, 1), Microsoft.Office.Interop.Excel.Range).Value2)
    		row(1) = Convert.ToString(DirectCast(workSheet.Cells(rowIndex, 2), Microsoft.Office.Interop.Excel.Range).Value2)
    		row(2) = Convert.ToString(DirectCast(workSheet.Cells(rowIndex, 5), Microsoft.Office.Interop.Excel.Range).Value2)
    		row(3) = Convert.ToString(DirectCast(workSheet.Cells(rowIndex, 6), Microsoft.Office.Interop.Excel.Range).Value2)
    
    		index += 1
    		rowIndex = 2 + index
    		dt.Rows.Add(row)
    	End While
    	app.Quit()
    	GC.Collect()
    	app.Workbooks.Close()
    	Return dt
    End Function
    
    
    If this is helpful
     { Please Mark as Answered }
    else if it is not helpful
     { Un-Mark as Answered }
    
    Best Regards
    Huan Li, Code Blog: Small is New Big In C#

    Sunday, June 27, 2010 2:36 AM
  • Huan,

    Thanks for taking time to reply with the idea.
    I see you are using the automation approach with a cell-by-cell transfer.
    I can give this a try but was hoping for something to effect transfer on a row or table level.

    Once again thank-you.
    Eric

    Monday, June 28, 2010 3:29 PM
  • Huan,

    Thanks also for your kind reply.
    I see you are using the SqlBulkCopy Class to accomplish the transfer.
    This looks very efficient.  However, I do not find the SqlBulkCopy Class in SqlServerCE.

    It may be wise to edit out the password info in this post.

    Thanks,
    Eric

    Monday, June 28, 2010 3:33 PM
  • Huan,

    Thanks for another informative idea.
    I see this uses automation once again which can certainly get the job done.
    I like the fact that this transfers a row at a time.
    I'll look into this method a bit more deeply to see how it can work with non-string data types.

    Thank you for your time and insight.
    Eric

    Monday, June 28, 2010 3:40 PM
  • Huan,

    I'm having trouble with the namespace.
    xlwbatemplate isn't found.
    Also, workbooks isn't found as a property of ExcelApp.
    I tried adding a Imports statement (first line below) which fails and I looked for Microsoft.Office.Interop as a reference to add to the project but didn't find it.

    Any suggestions?

    Imports Microsoft.office.interop.excel
    Imports System.Data.Odbc, System.Data.SqlServerCe
    Public Class frmImprtExprt
    
      Dim ExcelApp = CreateObject("Excel.Application")
    
      Private Sub ExportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportButton.Click
            Dim ExcelWB = ExcelApp.workbooks.add(xlwbatemplate.xlwbatworksheet)
      End Sub
    End Class

    Eric

    Tuesday, June 29, 2010 4:38 PM
  • You need refer excel dll.

    My computer has installed office 2007. After I add References, then my win app can read both xls and xlsx. You aslo can do this with office 2007 or other version COM as picture

     


    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    • Marked as answer by Eric-67220 Wednesday, June 30, 2010 4:19 PM
    Wednesday, June 30, 2010 12:09 AM
  • Huan,

    Thanks.

    I have a few related questions.

    1) How does that work with deployment?

    2) Once I include Excel.EXE as a COM reference does it get included in the deployment files?

    3) Assuming it is included in the deployment, is it legal to distribute Excel in this way?

    4) Since I'll be compiling with the version of Excel I have, does this put a restriction on what version of Excel the user has installed?

    Thanks again for all your help.

    Eric

    Wednesday, June 30, 2010 1:16 PM
  • //1) & 2) Like Picture Step by Step First Click mouse right on solution

    Sencond

    Third Choose Project Output



    Fourth



    Fifth Choose primary output Final You'll find excel.dll inclued



    //3)Yes,it's legal
    //4)It doesn't matter. Your program just uses excel.dll to run project. But the key is that if your office version is 2007,but user computer installed lower version. So project can not read .xlsx file

    Wednesday, June 30, 2010 2:26 PM
  • Thanks again.

    I also found this useful reference.

    http://msdn.microsoft.com/en-us/library/aa168292(office.11).aspx

    I wish I could find one for VB 2008 to be sure I'm using the latest recommend methods.

    Eric

    Wednesday, June 30, 2010 4:18 PM
  • Cika,

    Thanks for the post.  Sorry for the slow response on my part.  I've been away from the project for a while.

    What you post would be great.  However, Dim ef = ExcelFile() doesn't work on my system. 
    It must require a reference to the library (Excel.Net) you mention. 
    I see the link points to www.gemboxsoftware.com which must be a third-party add-in that I don't have a license for.

    Other than that, you have captured the intent of what I am trying to do. 
    Once I figure that out, my next step will be to import a set of Excel files to fill a dataset.

    Eric

    Friday, July 23, 2010 6:16 PM
  • Hi,

    yes this is a code from 3rd party software. If you are working on small Excel files (150 rows, 5 worksheets) you can use this product freely in your commercial app.

    For unlimited Excel functionality you have to buy professional version.

    Monday, May 2, 2011 8:48 AM
  • Hello again Cika,

    I was able to get the functionality I needed using an OdbcDataAdapter.
    For Export I use a SELECT INTO command to establish the schema then use a parameterized INSERT INTO with DataAdapter.Update(DS_Table) to fill the Excel WorkSheet.
    Import requires the user to add their data to a previously exported worksheet.  This is so the worksheet will have the necessary schema.  A SELECT * FROM [TableName] command is built for the DataAdapter and the DataSet table is filled using DataAdapter.Fill(DS_Table).

    Eric

    Monday, May 2, 2011 1:31 PM
  • Use ExcelDataReader Codeplex project. It is very useful and fast.

     

    http://exceldatareader.codeplex.com/


    Alone man
    Tuesday, May 3, 2011 12:40 PM
  • Well, I am glad that you found solution that satisfies your application's requirements.

    Keep up the good work!

    Monday, May 9, 2011 9:02 AM
  • Evidently I am missing something here.  Eric if you receive this, could you please expand on how you used dataadater to fill the Excel worksheet?

    I have downloaded the data from a SQL server procedure to a datatable.  I would like to insert this data into an Excel file.

    Friday, May 25, 2012 9:06 PM
  • Hello C Rogala.

    I hope I can help you out as the forum has been very helpful to me in the past.

    To start with please check out these related threads that I had going related to the Excel import/export function I was developing at the time.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/0e97748c-dad2-4f68-b7fc-b54a54c76abc
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/39f63fef-3c4b-4b6f-8c04-61ccb145bef2
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/7f65d473-fad7-4cd6-a18b-1dd57cf1aaad

    Please reply if these get you the insight you need and if not I'll dig back into that project to try to remember just how I was able to accomplish it.

    When you reply back please let me know at what stage you are in developing your solution so I'll better know how I may be able to help.  For instance do you have a solution designed but have a specific issue or are you still formulating an approach?

    Eric

    Saturday, May 26, 2012 2:54 AM