3

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?

Damien
  • 13,927
  • 14
  • 55
  • 88
  • You might want to consider using an external library for manipulating Excel files like http://npoi.codeplex.com/ instead of OleDB. – ipavlic Jan 20 '12 at 16:02

2 Answers2

2

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.

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Cheers, Actually, I can write fine, the problem is I need to insert a row rather than adding data to existing ones. – Damien Jan 20 '12 at 17:02
  • Sorry, I do not get what you mean. I though I was referring to inserting a new row ( http://stackoverflow.com/questions/5027899/excel-db-operation-must-use-an-updateable-query/5027963#5027963 ) – Fionnuala Jan 20 '12 at 17:05
  • Sorry, I am not articulating this very well. The code I provided works fine, my connection string allows me to read/write when inserting data. I need to add a row, shift all existing rows/content downwards. – Damien Jan 20 '12 at 17:07
  • @Damien Ah, sorry! I do not think ODBC is going to work for you, I think you will need interop (http://stackoverflow.com/questions/1111935/c-sharp-and-excel-interop) – Fionnuala Jan 20 '12 at 17:14
  • If you want to add that as a answer (use interop) I can accept it. Might make more sense than me accepted this one. – Damien Jan 23 '12 at 13:15
0

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:

public void InsertRows(int rowNumber, int rowCount)
Eisbaer
  • 189
  • 1
  • 6