C# 将简单数据从 .Net 插入 Excel 文件的最简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12148111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easiest way to insert simple data into an Excel file from .Net
提问by PFranchise
I have an excel file that has ~10 columns and 1-20 rows. I need to insert 1-20 rows with various data elements.
我有一个大约 10 列和 1-20 行的 excel 文件。我需要插入 1-20 行包含各种数据元素。
I was wondering if there was a way I could put some tags in the excel file so they could be found and replaced. Something that marks a column as "Name". That way in code I could just say:
我想知道是否有一种方法可以在 excel 文件中放置一些标签，以便可以找到和替换它们。将列标记为“名称”的东西。这样在代码中我可以说：
Name[0] = object.name;
Name[0] = object.name;
I'm not sure if this exact method is possible, but I really don't need any heavy lifting and I rather not hard code the cell locations as the excel file might change over time.
我不确定这种确切的方法是否可行，但我真的不需要任何繁重的工作，而且我宁愿不对单元格位置进行硬编码，因为 excel 文件可能会随着时间的推移而改变。
I will also have to add a hidden 'ID' cell in row. I imagine I can cross that bridge later though.
我还必须在行中添加一个隐藏的“ID”单元格。我想我以后可以过那座桥。
采纳答案by Steve
Using ADO.NET is easy to add a row to an Excel Sheet
使用 ADO.NET 很容易在 Excel 工作表中添加一行
string fileName = @"D:\test.xlsx";
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName);
using(OleDbConnection cn = new OleDbConnection(connectionString))
{
cn.Open();
OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " +
"([Column1],[Column2],[Column3],[Column4]) " +
"VALUES(@value1, @value2, @value3, @value4)", cn);
cmd1.Parameters.AddWithValue("@value1", "Key1");
cmd1.Parameters.AddWithValue("@value2", "Sample1");
cmd1.Parameters.AddWithValue("@value3", 1);
cmd1.Parameters.AddWithValue("@value4", 9);
cmd1.ExecuteNonQuery();
}
The code above assumes that you have a first row with an header with Column1... as column names. Also, the code use the ACE OleDB provider for Excel 2007 or 2010 instead of Microsoft.Jet.OleDb.4.0.
上面的代码假设您有一个标题为 Column1... 作为列名的第一行。此外，代码使用 Excel 2007 或 2010 的 ACE OleDB 提供程序，而不是 Microsoft.Jet.OleDb.4.0。
EDIT: To refer to a Named Range you could change the sql command to this one
编辑：要引用命名范围，您可以将 sql 命令更改为此
OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [yourNamedRange] " +
"VALUES(@value1, @value2, @value3, @value4)", cn);
Alas, I can't find a way to refer to the individual columns.
唉，我找不到引用各个列的方法。
回答by Hassan Boutougha
private Excel.Application app = null;
private Excel.Workbook workbook = null;
private Excel.Worksheet worksheet = null;
private Excel.Range workSheet_range = null;
private const int FIRTSCOLUMN= 0 //Here const you will use to select good column
private const int FIRSTROW= 0
private const int FIRSTSHEET= 1
app = new Excel.Application();
app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Excel.Worksheet)workbook.Sheets[FIRSTSHEET];
addData(FIRSTROW,FIRTSCOLUMN,"yourdata");
public void addData(int row, int col, string data)
{
worksheet.Cells[row, col] = data;
}
