使用OLEDB /的Microsoft Jet驱动程序会导致"号码存储为文本"在Excel文件 [英] Using OLEDB / Microsoft Jet driver causes "Number stored as text" in excel file

问题描述

我在使用OLDEDB从C＃写条目到一个Excel文件有问题。基本上我想用一个Excel模板文件格式化的一些信息。这个想法是，用户可以运行一个查询时，它会填充结果到一个Excel文件，用户就可以继续使用Excel来maniulate结果。

I'm having a problem using OLDEDB from C# to write entries into an excel file. Essentially I want to use an excel template file for formatting some information. The idea is that users can run a query, it'll populate the results into an excel file, where the user can then continue to use excel to maniulate the results.

要做到这一点，我创建了一个raw_data选项卡，并使用OLEDB写它像一个表。但是我遇到了一个问题，预设计算。一些列的文字，其他都是数字和forumals应尽快片被打开引用这些列。

To do this, I create a "raw_data" tab and use OLEDB to write to it like a table. However I'm running into a problem where the preset calculations. Some columns are text, others are numbers and forumals should reference these columns as soon as the sheet is opened.

的Excel版本2003
C＃/。NET 3.5

Excel Version 2003 C#/.Net 3.5

下面是我的连接字符串

string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filename};Extended Properties=""Excel 8.0;HDR=YES;IMEX=0""";

{文件名}得到后来与路径的代码文件替换

{filename} get's replaced later in the code with the path to the file

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
  connection.ConnectionString = connectString;
  using (DbCommand command = connection.CreateCommand())
  {

    connection.Open();
    query = "INSERT INTO [raw_prod$] (correlationid, created, ipaddr, nai, started, delta, csid, bytesin, bytesout, cause, bsid, servopt, svzone) VALUES (\"{correlationid}\", \"{created}\", \"{ipaddr}\", \"{nai}\", \"{started}\", {delta}, \"{csid}\", {bytesin}, {bytesout}, {cause}, \"{bsid}\", \"{servopt}\", \"{zone}\")";
    //Replace {} code with actual values skipped
    command.CommandText = query;
    command.ExecuteNonQuery();
  }
}

在此运行的数据获取的人口不如预期，但Excel将数据作为文本，所以这样的东西SUM（C2：C100）不工作。如果我只是强调一个条目并按下回车键，它会自动变成文字。我试着在模板格式化列数，你可以看到我删除了SQL中的双引号。

When this run's the data get's populated as expected, but excel treats the data as text, so stuff like sum(c2:c100) don't work. If I simply highlight an entry and hit enter, it will automatically become text. I tried formatting the column to number in the template, and as you can see I remove the double quotes in the sql.

我怎样才能练成对待这些条目的数字马上，让所有的计算是否行得通呢？

How can I get excel to treat these entries as numeric right off, so that all the calculations will work?

感谢

推荐答案

我想它了，它实际上是一种愚蠢讨厌。在行2我的Excel文件 - 8任何列，我想成为一个数字我只是填写一些虚拟的数字。这样一来，当它扫描这些列将看到数字，并考虑这些列号喷气驱动程序。然后我从C＃刀片开始第9行，我因此编辑该模板只能从第9行开始计算起。一个真正的痛苦，但它似乎工作。

I figured it out, it was actually kind of stupidly annoying. In my excel file for rows 2 - 8 for any column I wanted to be a number I just filled in some dummy numbers. This way the Jet driver when it scans those columns will see the numbers and consider those columns numbers. My inserts from C# are then started on row 9, and I edited the template accordingly to only start calculations from row 9 onward. Really a pain, but it seems to work.

这篇关于使用OLEDB /的Microsoft Jet驱动程序会导致"号码存储为文本"在Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！