I'm having a bit a of a problem writing to a spreadsheet using OLEDB. I can insert fine, but all the number i insert come up as strings in the spreadsheet. I tried converting to a double using Cdbl() however the number still shows up as a string. Here is a simple implementation of my class:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
namespace ConsoleApplication2
{
class MyClass
{
private DbConnection mDbConnection = null;
public MyClass()
{
Connect();
}
public void WriteSomething()
{
DbCommand dbCommand = null;
try
{
if (mDbConnection != null)
{
dbCommand = mDbConnection.CreateCommand();
dbCommand.CommandText = "INSERT INTO [UploadSheet$] (Field1, Field2, Field3) VALUES(Cdbl(4.3), \"Test1\",\"Test2\")";
dbCommand.ExecuteNonQuery();
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
dbCommand.Dispose();
Disconnect();
}
}
protected void Connect()
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='template.xls';Extended Properties='Excel 8.0;HDR=YES'";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
try
{
mDbConnection = factory.CreateConnection();
mDbConnection.ConnectionString = connectionString;
mDbConnection.Open();
}
catch (Exception exp)
{
throw exp;
}
}
protected void Disconnect()
{
try
{
if (mDbConnection != null)
{
mDbConnection.Close();
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
mDbConnection.Dispose();
}
}
}
}
Does anybody have a suggestion?