C# Excel 将数据从 CSV 导入 Excel

C# Excel import data from CSV into Excel

提问by akif

How do I import data in Excel from a CSV file using C#? Actually, what I want to achieve is similar to what we do in Excel, you go to the Datatab and then select From Textoption and then use the Text to columnsoption and select CSV and it does the magic, and all that stuff. I want to automate it.

如何使用 C# 从 CSV 文件中导入 Excel 中的数据？实际上，我想要实现的与我们在 Excel 中所做的类似，您转到“数据”选项卡，然后选择“从文本”选项，然后使用“文本到列”选项并选择 CSV，它就发挥了神奇作用，等等。我想自动化它。

If you could head me in the right direction, I'll really appreciate that.

如果你能引导我朝着正确的方向前进，我会非常感激。

EDIT:I guess I didn't explained well. What I want to do is something like

编辑：我想我没有很好地解释。我想做的是

Excel.Application excelApp;
Excel.Workbook excelWorkbook;

// open excel
excelApp = new Excel.Application();

// something like
excelWorkbook.ImportFromTextFile(); // is what I need

I want to import that data into Excel, not my own application. As far as I know, I don't think I would have to parse the CSV myself and then insert them in Excel. Excel does that for us. I simply need to know how to automate that process.

我想将该数据导入 Excel，而不是我自己的应用程序。据我所知，我认为我不必自己解析 CSV，然后将它们插入 Excel。Excel 为我们做到了这一点。我只需要知道如何使该过程自动化。

采纳答案by Lance Fisher

You could open Excel, start recording a macro, do what you want, then see what the macro recorded. That should tell you what objects to use and how to use them.

您可以打开 Excel，开始录制宏，执行您想要的操作，然后查看宏录制的内容。这应该会告诉您要使用哪些对象以及如何使用它们。

回答by Cyril Gupta

Well, importing from CSV shouldn't be a big deal. I think the most basic method would be to do it using string operations. You could build a pretty fine parser using simple Split() command, and getting the stuff in arrays.

好吧，从 CSV 导入应该没什么大不了的。我认为最基本的方法是使用字符串操作来完成。您可以使用简单的 Split() 命令构建一个非常好的解析器，并在数组中获取内容。

回答by Kevin Nisbet

I beleive there are two parts, one is the split operation for the csv that the other responder has already picked up on, which I don't think is essential but I'll include anyways. And the big one is the writing to the excel file, which I was able to get working, but under specific circumstances and it was a pain to accomplish.

我相信有两个部分，一个是另一个响应者已经接受的 csv 的拆分操作，我认为这不是必不可少的，但无论如何我都会包括在内。最重要的是写入 excel 文件，我能够开始工作，但在特定情况下，完成它很痛苦。

CSV is pretty simple, you can do a string.split on a comma seperator if you want. However, this method is horribly broken, albeit I'll admit I've used it myself, mainly because I also have control over the source data, and know that no quotes or escape characters will ever appear. I've included a link to an article on proper csv parsing, however, I have never tested the source or fully audited the code myself. I have used other code by the same author with success. http://www.boyet.com/articles/csvparser.html

CSV 非常简单，如果需要，您可以在逗号分隔符上执行 string.split。然而，这种方法非常糟糕，尽管我承认我自己使用过它，主要是因为我也可以控制源数据，并且知道不会出现引号或转义字符。我已经包含了一篇关于正确解析 csv 的文章的链接，但是，我自己从未测试过源代码或完全审核过代码。我成功地使用了同一作者的其他代码。http://www.boyet.com/articles/csvparser.html

The second part is alot more complex, and was a huge pain for me. The approach I took was to use the jet driver to treat the excel file like a database, and then run SQL queries against it. There are a few limitations, which may cause this to not fit you're goal. I was looking to use prebuilt excel file templates to basically display data and some preset functions and graphs. To accomplish this I have several tabs of report data, and one tab which is raw_data. My program writes to the raw_data tab, and all the other tabs calculations point to cells in this table. I'll go into some of the reasoning for this behavior after the code:

第二部分要复杂得多，对我来说是一个巨大的痛苦。我采取的方法是使用 jet 驱动程序将 excel 文件视为数据库，然后对其运行 SQL 查询。有一些限制，可能会导致这不符合您的目标。我希望使用预先构建的 excel 文件模板来基本上显示数据和一些预设函数和图形。为了实现这一点，我有几个报告数据选项卡，以及一个是 raw_data 的选项卡。我的程序写入 raw_data 选项卡，所有其他选项卡计算都指向此表中的单元格。我将在代码之后对这种行为进行一些推理：

First off, the imports (not all may be required, this is pulled from a larger class file and I didn't properly comment what was for what):

首先，导入（并非所有都需要，这是从一个更大的类文件中提取的，我没有正确评论什么是什么）：

using System.IO;
using System.Diagnostics;
using System.Data.Common;
using System.Globalization;

Next we need to define the connection string, my class already has a FileInfo reference at this point to the file I want to use, so that's what I pass on. It's possible to search on google what all the parameters are for, but basicaly use the Jet Driver (should be available on ANY windows install) to open an excel file like you're referring to a database.

接下来我们需要定义连接字符串，我的类此时已经有一个指向我要使用的文件的 FileInfo 引用，所以这就是我传递的内容。可以在 google 上搜索所有参数的用途，但基本上使用 Jet 驱动程序（应该在任何 Windows 安装上都可用）打开一个 excel 文件，就像您指的是数据库一样。

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

Now let's open up the connection to the DB, and be ready to run commands on the DB:

现在让我们打开与数据库的连接，并准备好在数据库上运行命令：

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
  connection.ConnectionString = connectString;
  using (DbCommand command = connection.CreateCommand())
  {
    connection.Open();

Next we need the actual logic for DB insertion. So basically throw queries into a loop or whatever you're logic is, and insert the data row-by-row.

接下来我们需要 DB 插入的实际逻辑。因此，基本上将查询放入循环或任何您的逻辑中，并逐行插入数据。

string query = "INSERT INTO [raw_aaa$] (correlationid, ipaddr, somenum) VALUES (\"abcdef", \"1.1.1.1", 10)";
command.CommandText = query;
command.ExecuteNonQuery();

Now here's the really annoying part, the excel driver tries to detect you're column type before insert, so even if you pass a proper integer value, if excel thinks the column type is text, it will insert all you're numbers as text, and it's very hard to get this treated like a number. As such, excel must already have the column type as the number. In order to accomplish this, for my template file I fill in the first 10 rows with dummy data, so that when you load the file in the jet driver, it can detect the proper types and use them. Then all my forumals that point at my csv table will operate properly since the values are of the right type. This may work for you if you're goals are similar to mine, and to use templates that already point to this data (just start at row 10 instead of row 2).

现在这是真正令人讨厌的部分，excel 驱动程序会在插入之前尝试检测您是列类型，因此即使您传递了正确的整数值，如果 excel 认为列类型是文本，它也会将您的所有数字作为文本插入，并且很难将其视为数字。因此，excel 必须已经将列类型作为数字。为了实现这一点，我在模板文件的前 10 行中填充了虚拟数据，这样当您在 jet 驱动程序中加载文件时，它可以检测到正确的类型并使用它们。然后我所有指向我的 csv 表的论坛都将正常运行，因为这些值是正确的类型。如果您的目标与我的相似，并且使用已经指向此数据的模板（只需从第 10 行而不是第 2 行开始），这可能对您有用。

Because of this, my raw_aaa tab in excel might look something like this: correlationid ipaddr somenum abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5

正因为如此，我在 excel 中的 raw_aaa 选项卡可能看起来像这样：correlationid ipaddr somenum abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1.1 5 abcdef 1.1.1b.1def1def1.15 abcdef 1.1.1.1.1 .1.1 5 abcdef 1.1.1.1 5

Note row 1 is the column names that I referenced in my sql queries. I think you can do without this, but that will require a little more research. By already having this data in the excel file, the somenum column will be detected as a number, and any data inserted will be properly treated as such.

注意第 1 行是我在 sql 查询中引用的列名。我认为你可以没有这个，但这需要更多的研究。通过在 excel 文件中已经有了这些数据，somenum 列将被检测为一个数字，并且插入的任何数据都将被正确处理。

Antoher note that makes this annoying, the Jet Driver is 32-bit only, so in my case where I had an explicit 64-bit program, I was unable to execute this directly. So I had the nasty hack of writing to a file, then launch a program that would insert the data in the file into my excel template.

另请注意，这很烦人，Jet 驱动程序只有 32 位，所以在我有一个明确的 64 位程序的情况下，我无法直接执行它。所以我有一个讨厌的黑客写入文件，然后启动一个程序，将文件中的数据插入到我的 excel 模板中。

All in all, I think the solution is pretty nasty, but thus far haven't found a better way to do this unfortunatly. Good luck!

总而言之，我认为该解决方案非常糟糕，但不幸的是，到目前为止还没有找到更好的方法。祝你好运！

回答by Ian

I think you're over complicating things. Excel automatically splits data into columns by comma delimiters if it's a CSV file. So all you should need to do is ensure your extension is CSV.

我认为你把事情复杂化了。如果数据是 CSV 文件，Excel 会自动按逗号分隔符将数据拆分为列。因此，您需要做的就是确保您的扩展名是 CSV。

I just tried opening a file quick in Excel and it works fine. So what you really need is just to call Workbook.Open() with a file with a CSV extension.

我只是尝试在 Excel 中快速打开一个文件，它工作正常。因此，您真正需要的只是使用带有 CSV 扩展名的文件调用 Workbook.Open() 。

回答by Captain Comic

You can use ADO.NET

您可以使用 ADO.NET

http://vbadud.blogspot.com/2008/09/opening-comma-separate-file-csv-through.html

http://vbadud.blogspot.com/2008/09/opening-comma-separate-file-csv-through.html

回答by balena

You can take a look at TakeIo.Spreadsheet .NET library. It accepts files from Excel 97-2003, Excel 2007 and newer, and CSV format (semicolon or comma separators).

您可以查看TakeIo.Spreadsheet .NET 库。它接受来自 Excel 97-2003、Excel 2007 和更新版本的文件，以及 CSV 格式（分号或逗号分隔符）。

Example:

例子：

var inputFile = new FileInfo("Book1.csv"); // could be .xls or .xlsx too
var sheet = Spreadsheet.Read(inputFile);

foreach (var row in sheet)
{
    foreach (var cell in row)
    {
        // do something
    }
}

You can remove beginning and trailing empty rows, and also beginning and trailing columns from the imported data using the Normalize()function:

您可以使用以下Normalize()函数从导入的数据中删除开头和结尾的空行，以及开头和结尾的列：

sheet.Normalize();

Sometimes you can find that your imported data contains empty rows between data, so you can use another helper for this case:

有时您会发现导入的数据在数据之间包含空行，因此您可以针对这种情况使用另一个助手：

sheet.RemoveEmptyRows();

There is a Serialize()function to convert any input to CSV too:

还有一个Serialize()功能可以将任何输入转换为 CSV：

var outfile = new StreamWriter("AllData.csv");
sheet.Serialize(outfile);

If you like to use comma instead of the default semicolon separator in your CSV file, do:

如果您想在 CSV 文件中使用逗号而不是默认的分号分隔符，请执行以下操作：

sheet.Serialize(outfile, ',');

And yes, there is also a ToString()function too...

是的，也有一个ToString()功能......

This package is available at NuGet too, just take a look at TakeIo.Spreadsheet.

这个包也可以在 NuGet 上找到，看看TakeIo.Spreadsheet。