How to insert data into an Excel File using VB.NET

excel-logo

Declaring the connection

If you are using Excel 2007 and above, create the file where you would like to save your data and add the following to your web.config

<appSettings>
<add value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:File.xlsx;Extended Properties='Excel 12.0;ReadOnly=False;HDR=YES;'" key="ExcelLink" />
</appSettings>

Read from an Excel file

First, we initialize the connection to the Excel File:

'connection details
Dim _connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ExcelLink")
'get last value of the id in excel file
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim strErrorsFound As New StringBuilder
Dim blnErrorFound As Boolean = False
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = _connectionString

Then, we create the command that will select information from the sheet:

Using command As DbCommand = connection.CreateCommand()
'Get the RefNo for the latest entry
command.CommandText = "SELECT TOP 1 ID FROM [Feedback$] ORDER BY ID DESC"

We will put our code in a Try-Catch block to prevent any errors bleeding into the Client-Side.

Try
connection.Open()

Using dr As DbDataReader = command.ExecuteReader()
If dr.HasRows Then

While dr.Read()
‘store last ref_number value
lastID = If(dr(“ID”).ToString() <> “”, Int32.Parse(dr(“ID”).ToString()), 0)
End While
End If
End Using

‘clean up- close first connection to the excel file
connection.Dispose()
connection.Close()

Catch ex As Exception
PanelServerError.Visible = True
Exit Sub
End Try

Don’t forget to close the Using Tags

End Using
End Using

Get data from SQL Server and save it into Excel

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CN").ConnectionString)

Dim myCommand As New SqlCommand("[Your SP]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader()

While myReader.Read()

IDNum = myReader.Item("ID")
contactName = myReader.Item("ContactName")
contactCompany = myReader.Item("ContactCompany")
contactPosition = myReader.Item("ContactPosition")
contactEmail = myReader.Item("ContactEmail")
project = myReader.Item("Project")

'insert into excel file
Dim factory2 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection2 As DbConnection = factory2.CreateConnection()
connection2.ConnectionString = _connectionString

Dim command2 As DbCommand = connection2.CreateCommand()

'Build SQL string
Dim SQLQuery As New StringBuilder()
SQLQuery.Append("INSERT INTO [Feedback$] ")
SQLQuery.Append("(ID, Name, Company, Position_, Email, Project, ")

SQLQuery.Append("VALUES ('" & IDNum & "', '" & contactName & "','" & contactCompany & "','" & contactPosition & "','" & contactEmail & "','" & project & "','")

Try
connection2.Open()

'insert data into excel spreadsheet
command2.CommandText = SQLQuery.ToString()
command2.ExecuteNonQuery()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Line / ID " + IDNum)
strErrorsFound.AppendLine(ex.Message)
strErrorsFound.AppendLine("Query: " + SQLQuery.ToString())
strErrorsFound.AppendLine("******************************************************************************")
Finally
connection2.Dispose()
connection2.Close()
End Try

'****************excel insert code *******************

End While

myReader.Close()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Generic error: " + ex.Message)
strErrorsFound.AppendLine("******************************************************************************")

Finally

'clean up
myConnection.Close()
myConnection.Dispose()
End Try

Errors

Operation must use an updateable query.

Resolution

Granting access to the file being written to (IISUSR User or the IIS App Pool User) solved this issue for me.

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Resolution

While importing from an Excel spread sheet you see the following error message “[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data”. This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..
Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….
A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let’s say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let’s say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
A column in a spreadsheet has numeric strings for the first 8 records, For example “123”, “456” etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example “Hello World”. The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing “hello world” is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.
In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.

Advertisements