C＃Excel INSERT使用OleDb和Access数据库引擎时出错

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.

总之，关于这个问题有很多问题，但没有一个能解决我的问题。我编写了一个相当复杂的例程，使用OleDb和Access数据库引擎将传递的DataSet / DataTable导出到Excel（我在Win7下运行Office 2010）。问题是无论我如何定义要写入Excel的列，所有值都将导出为TEXT / STRING字段。

I am using the OleDbConnection string

我正在使用OleDbConnection字符串

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

我在代码中生成Excel定义，但明确地将它们生成为

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?

这有效，但所有值都写为TEXT。如何强制Excel采用其他数据格式？

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.

注意：我已经尝试删除非字符串的撇号，但这也不起作用。我真的被困住了，任何想法都会受到高度赞赏。谢谢你的时间。

2 个解决方案

#1

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.

我认为你不能。 excel格式实际上与数据库列数据类型无关 - 最终底层值始终是数字或字符串;然后格式确定它的显示方式。

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.

即使我错了 - 我个人更喜欢使用http://epplus.codeplex.com/来生成我的Excel电子表格 - 你可以在其中做很多非常高级的东西以及格式化等简单的东西。

#2

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.

在excel表中插入值时，无法向OleDbCommand提供任何提示（语法）以插入数值。打开工作表时，它不会在工作表中将数值显示为数字。

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.

有一个解决方法，当在excel文件中创建第一个记录然后转到第一个记录并重新输入已存在记录的单元格（数字数据需要）中的相同值。

要么

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

您可以在该Excel工作表中放置带有数值的默认记录。

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.

下面提到示例代码，以便在工作表中创建第一个记录后将数据类型转换为数字。当任何记录插入到工作表中时，调用一次ChangeFormat函数，进一步的数据将保持适当的格式。

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();
    }
}

}

#1