C# To Insert 3 Rows in Excel

richard smith

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

richard smith13y ago
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();
}
}
}
}
0
richard smith13y ago
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.)
0
Sharon White13y ago
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.
0
richard smith13y ago
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
0
Sharon White13y ago
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.
0
richard smith13y ago
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
0
Imtiyaz Ansari13y ago

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 <= dataGridView1.ColumnCount - 1; j++)
{
xlWorkSheet.Cells[1, j + 1] = dataGridView1.Columns[j].Name;
}

for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; 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;
}
0