通过 C# 和 OleDb 将行插入 Excel 电子表格
我需要以编程方式将一行多次插入 Excel 电子表格中。我需要实际插入一个新行而不是插入数据，也就是说，我需要实际将所有其他行向下移动一位。
我目前正在使用 OleDB 来插入数据本身，如下所示：
//Note I have missed some code out for simplicities sake, this all works fine however
OleDbConnection oledbConn = null;
OleDbCommand cmd = null;
OleDbConnection = new OleDbConnection(connString);
OleDbConnection.Open();
string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; \"", TargetFile);
sting InsertCommand = string.Format("INSERT INTO [{0}${1}:{1}] Values({2})", WorksheetName, Coord, valuestring);
cmd = new OleDbCommand(InsertCommand, oledbConn);
cmd.ExecuteNonQuery();
//close etc
我希望能够以类似的方式插入一行。这可能吗？
I need to programmatically insert a row into an Excel Spreadsheet multiple times. I need to actually insert a new row and not insert data, that is, I need to actually shift all other rows down by one.
I am currently using OleDB to insert the data itself like so:
//Note I have missed some code out for simplicities sake, this all works fine however
OleDbConnection oledbConn = null;
OleDbCommand cmd = null;
OleDbConnection = new OleDbConnection(connString);
OleDbConnection.Open();
string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; \"", TargetFile);
sting InsertCommand = string.Format("INSERT INTO [{0}${1}:{1}] Values({2})", WorksheetName, Coord, valuestring);
cmd = new OleDbCommand(InsertCommand, oledbConn);
cmd.ExecuteNonQuery();
//close etc
I want to be able to insert a row in a similar fashion. Is this possible?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一看就是需要指定读写，默认是只读。也许：
“提供商=Microsoft.Jet.OLEDB.4.0；” & _
“数据源=C:\Docs\Test.xls;” & _
"模式=读写；扩展属性=""Excel 8.0；HDR=否"""
再看一遍并重新评论，我认为 互操作可能是最好的选择。
At a glance, you need to specify read write, the default is read only. Perhaps:
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Docs\Test.xls;" & _
"Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=No"""
At a second glance and re comments, I think Interop might be the best bet.
ipavlic 是对的，为此您最好使用外部第三方库。有几种可用。 OfficeWriter 就是一个示例：
http://www.officewriter.com
使用 OfficeWriter 打开工作簿后，您可以使用Worksheet 类上的此方法用于插入行。它模仿 Excel 的行为，包括更新/拉伸公式和其他更新：
ipavlic is right, you will be better off using an external third-party library for this. There are several available. OfficeWriter is one example:
http://www.officewriter.com
Once you open a workbook with OfficeWriter, you can use this method on the Worksheet class to insert rows. It mimics Excel's behavior, including updating/stretching formulas and other updates: