我有了〜10列,1-20行的Excel文件。我需要插入1-20行,各种数据元素。
I have an excel file that has ~10 columns and 1-20 rows. I need to insert 1-20 rows with various data elements.
我在想,如果有一种方法,我可以把一些标签在Excel文件中,以便他们能够被发现和更换。东西标志着一个列作为名称。这样,在code我只能说:
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:
名称[0] = object.name;
我不知道这确切的方法是可行的,但我真的不需要任何繁重和c的单元格位置的Excel文件我宁愿不硬$ C $可能会随时间而改变。
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.
我还必须添加行一个隐藏的ID细胞。我想我能走过这座桥后来虽然。
I will also have to add a hidden 'ID' cell in row. I imagine I can cross that bridge later though.
使用ADO.NET易行添加到一个Excel工作表
Using ADO.NET is easy to add a row to an Excel Sheet
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();
}
在code以上假设你有一个第一行与列1列名的头......。 此外,code使用ACE OLEDB提供Excel中,而不是Microsoft.Jet.OleDb.4.0 2007年或2010年。
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.
编辑:要引用一个命名范围,你可以更改SQL命令,这一个
To refer to a Named Range you could change the sql command to this one
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.