I am trying to import and export to/from excel.

This needs to be able to be done on a weekly basis.

I have managed to find a couple of ways of doing this, but I need a way to set column width and row colours and wondered if anyone might know of a way of doing this?

Re: Import/Export Excel 80 80

what approach are you using? Is it PIA's or COM?

Re: Import/Export Excel 80 80

I have a new problem now!

I am trying to use bulk copy to import the excel spreadsheet, but I keep getting the following error:

The given ColumnMapping does not match up with any column in the source or destination.

I have tries a blank table and one that matches the headers used in the spreadsheet, but this has not made a difference.

What am I missing?

I am using Vista and have Office 2007 installed although the spreadsheet is on Office 2003 format.

The code I am using is:

Dim opendlg As New OpenFileDialog
opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
If opendlg.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim pathandfile As String = opendlg.FileName
Dim connection As OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & pathandfile & ";Extended Properties='Excel 8.0;'")
Dim Command = New OleDb.OleDbCommand("select * from [Cornwall$]", connection)
connection.Open()
Dim dr As Data.IDataReader = Command.ExecuteReader
Dim bulkCopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy("Data Source=.\SQLEXPRESS;Initial Catalog=CoaST GTBS Database;Integrated Security=True")
bulkCopy.DestinationTableName = "LocalIn"
bulkCopy.WriteToServer(dr)
Dim adapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Cornwall$]", connection)
adapter.Fill(Me.CoaST_GTBS_DatabaseDataSet.LocalIn)
connection.Close()

The error is shown at the bulkcopy.writetoserver(dr).

Thank you for any assistance.

Re: Import/Export Excel 80 80

You are missing the field mapping just before the Destination Table.

' Set up the column mappings by name.
Dim mapID As New _
SqlBulkCopyColumnMapping("ProductID", "ProdID")
bulkCopy.ColumnMappings.Add(mapID)

You have to do it for all the fields

Re: Import/Export Excel 80 80

There are diff. overloads for function (SqlBulkCopyColumnMapping)

Re: Import/Export Excel 80 80

Thank for this binoj_daniel, will will start typing.

Is there anything in the copy I am using that would allow the colour of rows to be able to be picked up?

I need to be able to pick these up from the spreadsheet and also copy back to a spreadsheet.

Re: Import/Export Excel 80 80

binoj-daniel, have just tried adding the code you gave me, but am still showing the same error.

When I had a few spelling mistakes I was give the error showing that there wasn't X column.

Do you have any idea where to start looking?

Re: Import/Export Excel 80 80

so you mapped all the columns? I would suggest you to create a test table with 2 columns and try this first. This way you can figure out where is the problem.

Re: Import/Export Excel 80 80

Thank you for your suggestion, if only I had been thinking this would have been obvious.

I also found some sizing problems.

Can something along the lines about be used to pick up the row colours?

Re: Import/Export Excel 80 80

Iam not sure if the bulk copy supports formatting the row colors, But you can do whatever you want with the excel file using .NET PIA's. This gives you the most control over the excel application.

Re: Import/Export Excel 80 80

This looks fun. I will try and get my head around these.

Thank you for all you help binoj_daniel.

Re: Import/Export Excel 80 80

Not a problem. Let me know how it goes.

Re: Import/Export Excel 80 80

I will be looking at this over the weekend, so will let you know how I get on. That you for pointing me in the right direction.

Re: Import/Export Excel 80 80

I will be glad to help. I beleive in sharing knowledge. Thats the reason i launched this site www.coderewind.com

Re: Import/Export Excel 80 80

When using bulk copy you can setup your table by importing the spreadsheet into SQL. Then there is no mapping. If you receive the error, it is usually caused by the table not matching the input columns. I found that the table can have more columns but not less, this includes columns that may be blank in the spreadsheet.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.