CODEWITHSUNDEEP
c#excelconnection-stringoledb
MoonKnight
MoonKnight

Reputation: 23831

C# Excel INSERT Error using OleDb and the Access Database Engine

All, there are many question on this subject but none solve my problem. I have written a fairly involved routine to export a passed DataSet/DataTable to Excel (I have Office 2010 running under Win7) using OleDb and the Access Database Engine. The problem is no matter how I define the columns to be written to Excel all values are exported as TEXT/STRING fields.

I am using the OleDbConnection string

string fileName = @"F:\SomePath\MyExcel.xlsx";
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
    {0};Extended Properties=""Excel 12.0 Xml;HDR=YES;MaxScanRows=0;IMEX=0""", fileName);

and have tried numerous other connection string options with no luck.

I generate the Excel definitions in code, but explicitly these are produced as

CREATE TABLE [MetaTab] ([Table] TEXT,[Field] TEXT,[Seq] NUMBER,[DataLevel] NUMBER)

I then generate the code for inserts, for the example above this is

INSERT INTO [MetaTab$]([Table],[Field],[Seq],[DataLevel])VALUES('B1A','EstabID','1','9')

This works, but all values are written as TEXT. How can I force Excel to take other data formats?

Note: I have tried removing the apostrophes for non-string but this does not work either. I am genuinely stuck and any ideas would be greatly appreciated. Thanks for your time.

Upvotes: 2

Views: 2899

Answers (2)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

It is not possible to give the any hint(syntax) to OleDbCommand to insert numeric values while inserting values in excel sheet. It will not display the numeric values as numeric in sheet when you open the sheet.

There is an work around, when first record is created in the excel file then go to first record and reenter the same value in the cell (where numeric data want) of already existing records.

or

You can put a default record with numeric values in that excel sheet.

Sample code is mentioned below to convert the data type to numeric after first record has been created in sheet. Call once the ChangeFormat function when any of the record has been inserted in sheet, further data will be kept in proper format.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {


        System.Data.DataTable xlsData = new System.Data.DataTable();
        string xlsConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\romil.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;\"";
        System.Data.OleDb.OleDbConnection xlsCon = new System.Data.OleDb.OleDbConnection(xlsConnectionString);
        System.Data.OleDb.OleDbCommand xlsCommand;
        int recUpdate;
        int recordsinSheet;

        xlsCommand = new System.Data.OleDb.OleDbCommand("Select count(*) as RecCount from [Sheet1$]");
        xlsCommand.Connection = xlsCon;
        xlsCon.Open();
        recordsinSheet =Convert.ToInt32( xlsCommand.ExecuteScalar());

        xlsCommand=   new System.Data.OleDb.OleDbCommand("Insert into [Sheet1$] (Field1,Field2) values ('123',2)");
        xlsCommand.Connection = xlsCon;

        recUpdate = xlsCommand.ExecuteNonQuery();
        xlsCon.Close();

        if ((recordsinSheet + recUpdate) == 1)
            ChangeFormat();

        Console.ReadKey();

    }

    private static void ChangeFormat()
    {
        string filename = "d:\\romil.xlsx";

        object missing = System.Reflection.Missing.Value ;

        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

        Microsoft.Office.Interop.Excel.Worksheet wsh=null;
        foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Sheets)
        {
            if (sheet.Name == "Sheet1")
            {
                wsh = sheet;
                break;
            }
        }

        for (int rCnt = 2; rCnt <= wsh.Rows.Count; rCnt++)
        {

            if  ( wsh.Cells[rCnt, 2].Value== null)
                break;

            wsh.Cells[rCnt, 2] = wsh.Cells[rCnt, 2].Value;
        }    

        wb.SaveAs(filename, missing,
            missing, missing, missing, missing,
           Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing,
            missing, missing);
        wb.Close();
    }
}

}

Upvotes: 1

Andras Zoltan
Andras Zoltan

Reputation: 42363

I don't think you can. An excel format is not actually analagous to a database column data type - ultimately the underlying value is always either a number or a string; and then the format determines how it is displayed.

Even if I'm wrong - I personally much prefer to use http://epplus.codeplex.com/ to generate my Excel spreadsheets - you can do a lot of very advanced stuff in it as well as simple stuff like formatting.

Upvotes: 1

Related Questions