Import/Export Excel

[TaoistTotty]

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?

[binoj_daniel]

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

[TaoistTotty]

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.

[binoj_daniel]

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

[binoj_daniel]

There are diff. overloads for function (SqlBulkCopyColumnMapping)

[TaoistTotty]

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.

[TaoistTotty]

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?

[binoj_daniel]

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.

[TaoistTotty]

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?

[binoj_daniel]

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.

[TaoistTotty]

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

Thank you for all you help binoj_daniel.

[binoj_daniel]

Not a problem. Let me know how it goes.

[TaoistTotty]

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.

[binoj_daniel]

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

[aacinc]

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.