C# To Insert 3 Rows in Excel

Question

I am running the below code attempting to insert 3 rows in Excel, but it gives me the debug error of:
COMException was unhandled
private static _Application docExcel;
public static void AddThreeRows()
{
long i;
for(i = (int)(docExcel.Cells[docExcel.Rows.Count, "e"].End(XlDirection.XxlUp).Row); i >= 3; i--)
{
string e1 = docExcel.Cells[i, "E"].Value;
if (!string.IsNullOrEmpty(e1.ToUpper()))
{
string em1 = docExcel.Cells[i - 1, "e"].Value;
if (e1 != em1)
{
docExcel.Rows.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
}
}
}
}

What I am trying to do here is each time there is a different value in "E" add 3 rows....so for example if Column E reads:
Joseph
Joseph

Brian
Brian
Brian
Brian
Brian

Mark
Mark
Mark

etc

richard smith · Answer

I actually have my code working now as I need it.....HOURS of googling and trial and error later :) pricate _Application docExcel; public static void AddThreeRows() { long i; for(i = (int)(docExcel.Cells[docExcel.Rows.Count e].End(XlDirection.XxlUp).Row); i >= 3; i--) { string e1 = docExcel.Cells[i E].Value; if (!string.IsNullOrEmpty(e1.ToUpper())) { string em1 = docExcel.Cells[i - 1 e].Value; if (e1 != em1) { docExcel.Rows[i].Insert(); docExcel.Rows[i].Insert(); docExcel.Rows[i].Insert(); } } } }

richard smith · Answer

What method is it that you use? I have only used the Microsoft.Interop to communicate with C# and Excel. (My chrome keeps telling me it is unable to open the link you provided in your initial post if it would I am sure I would know what you are referencing.)

Sharon White · Answer

Hello I am sorry for tell you that I don't use Microsoft.Office.Interop but another library to realize this function more more easily. So you cannot find InsertRow method if you use Microsoft.Office.Interop instead of my library dll.

richard smith · Answer

Thank you for the response. When I input that code I get the following compile error: Microsoft.Office.Interop.Excel.Worksheet doesnot contain a definition for 'InsertRow' and no extension method 'InsertRow' acceping a 1st argument of type 'Microsoft.Office.Interop.Excel.Worksheet' could be found

Sharon White · Answer

Hello Do you want to insert whole rows or just cells of column E? If whole rows the following code may be a little helpful. //Load excel Workbook book = new Workbook(); book.LoadFromFile(test.xls); Worksheet sheet = book.Worksheets[0]; //Insert first 3 rows after the second row. sheet.InsertRow(3 3); //Because the first rows has been inserted so insert the next 3 rows after the eighth row which is the original fifth row. sheet.InsertRow(8 3); And the above code is based on a .NET Excel Library . Hope Helpful.

richard smith · Answer

Which piece of that adds the 3 rows? I am wanting to search cell E and each time there is a seperate value in cell E...add 3 spaces between them. So again it would look like this: Jorge Pasada Jorge Pasada ....Space 1.... ....Space 2.... ....Space 3.... George Jones George Jones ....Space 1.... ....Space 2.... ....Space 3.... Mitch Smith Mitch Smith

Imtiyaz Ansari · Answer

namespace add Microsoft.Office.Interop.Excel.dll all gridview data create Excel File ...... try { string filename1 = Damage Product List; filename1 = D:\Report\ + filename1 + .xls; Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (j = 0; j { xlWorkSheet.Cells[1 j + 1] = dataGridView1.Columns[j].Name; } for (i = 0; i { for (j = 0; j { DataGridViewCell cell = dataGridView1[j i]; xlWorkSheet.Cells[i + 2 j + 1] = cell.Value; } } xlWorkBook.SaveAs(filename1 Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal misValue misValue misValue misValue Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive misValue misValue misValue misValue misValue); xlWorkBook.Close(true misValue misValue); xlApp.Quit(); MessageBox.Show(Excel file created  you can find the file); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); return; }