Excel Oledb Engine and VB.NET

vb.net , excel 2007 Madurai, India
  • 12 years ago
    I'm developing .NET application which reads excel sheet information and updates the database and also writes the DB information back to the excel sheet.I have some issues in writing to the excel sheet. The excelsheet contains nearly 10 columns of datatypes: integer, text and boolean. I'm using Oledb engine for reading and writing to the excel sheet. Problems with Numeric values: While writing to the excel sheet, the cells having numeric values are marked with a green triangle at the corner of the cell implying the as "the number in this column is formatted as text or preceded by an apostrophe". Even though i'm inserting the values as integer only.Why does it happens? How to solve it? Problems with Text values: Some of the rows may not contain values in DB.While writing to excel, the text values as follows Int-column Text-Column Boolean-Column 123 AAA True 234 (novalue) False '432 'BBB 'True After writing the empty column, the subsequent values in the excel cell are preceded by an apostrophe or single quoute. why does this happens? Problem with Boolean values while reading data from excel: While reading from excel, the boolean value(True or False) are not read. The respective cell values are Null. Can anyone help me in solving this problem?
  • 12 years ago
    use cast to convert the particular column as varchar ---for numeric values use case statement to check the particular column as null or not.if it is null then put empty string. for boolean just convert the resulted output as varchar and u got the result.. these things has to be done in query.... if the problem has not solved......... send the query...we can try........ happy coding....
  • 12 years ago
    HI, I already have coded in the way what you have told me. I'm using Oledbparameters to insert data into excel sheet. **The code to Build parameters is as follows:** Public Function BuildNetAccessParams() As OleDbCommand If olecomm Is Nothing Then olecomm = New OleDbCommand() End If olecomm.CommandText = "Insert into [" & sheetname & "$] " & _ "(ObjectID,ObjectName,ObjectType,Parent,UserID,UserName,UserType,Inherited,NetAccess,Category) values (@ObjectID,@ObjectName,@ObjectType,@Parent,@UserID,@UserName,@UserType,@Inherited,@NetAccess,@Category)" 'olecon.ConnectionString = connstring olecomm.Connection = olecon Param = olecomm.Parameters.Add("@ObjectID", OleDbType.Integer) Param.SourceColumn = "ObjectID" Param = olecomm.Parameters.Add("@ObjectName", OleDbType.VarChar) Param.SourceColumn = "ObjectName" Param = olecomm.Parameters.Add("@ObjectType", OleDbType.VarChar) Param.SourceColumn = "ObjectType" Param = olecomm.Parameters.Add("@Parent", OleDbType.VarChar) Param.SourceColumn = "Parent" Param = olecomm.Parameters.Add("@UserID", OleDbType.Integer) Param.SourceColumn = "UserID" Param = olecomm.Parameters.Add("@UserName", OleDbType.VarChar) Param.SourceColumn = "UserName" Param = olecomm.Parameters.Add("@UserType", OleDbType.VarChar) Param.SourceColumn = "UserType" Param = olecomm.Parameters.Add("@Inherited", OleDbType.VarChar) Param.SourceColumn = "Inherited" Param = olecomm.Parameters.Add("@NetAccess", OleDbType.VarChar) Param.SourceColumn = "NetAccess" Param = olecomm.Parameters.Add("@Category", OleDbType.VarChar) Param.SourceColumn = "Category" Return olecomm End Function **The connection string is as:** "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelFilePath & ";Extended Properties=""Excel 8.0;HDR=YES;""" **The code to insert data:** Public Sub InsertNetAccessToExcel(ByRef righttable As DataTable, ByRef olecomm As OleDbCommand) Dim ds As New DataSet oleAdap = New OleDbDataAdapter((New OleDbCommand("Select * from [" & sheetname & "$]", olecon))) oleAdap.Fill(ds, sheetname) For Each row As DataRow In righttable.Rows Dim dr As DataRow = ds.Tables(sheetname).NewRow dr(Utilities.RightsStr.objid) = row(Utilities.RightsStr.objid) ** 'Integer column** dr(Utilities.RightsStr.ObjectName) = row(Utilities.RightsStr.ObjectName) dr(Utilities.RightsStr.ObjectType) = row(Utilities.RightsStr.ObjectType) dr(Utilities.RightsStr.Parent) = row(Utilities.RightsStr.Parent) dr(Utilities.RightsStr.UserID) = row(Utilities.RightsStr.UserID) ** 'Integer column** dr(Utilities.RightsStr.UserName) = row(Utilities.RightsStr.UserName) dr(Utilities.RightsStr.UserType) = row(Utilities.RightsStr.UserType) If row(Utilities.RightsStr.Inherited).ToString.ToLower = "true" Then dr(Utilities.RightsStr.Inherited) = Boolean.TrueString ** 'Boolean column** Else dr(Utilities.RightsStr.Inherited) = Boolean.FalseString End If dr(Utilities.RightsStr.NetAccess) = row(Utilities.RightsStr.NetAccess) dr(Utilities.RightsStr.Category) = row(Utilities.RightsStr.Category) ds.Tables(sheetname).Rows.Add(dr) oleAdap = New OleDbDataAdapter() oleAdap.InsertCommand = olecomm oleAdap.Update(ds, sheetname) Next End Sub This is the way I have coded. I have no problem in inserting data. But the numeric cell values are marked as error column as I have mentioned in my previous post. Thanks, Bhuvan R

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger