Deleting a row in excel sheet using C#

Question

Hi,

I am working on an C# Winforms application.
My question is how do I programmatically delete a particular row in excel sheet...?

Thanks in advance...!
livehed

 

 

Answer 1

reference the Microsoft Excel 10.0 Object Library in your project.

set the following using statement:

using Excel = Microsoft.Office.Interop.Excel;

In your class, declare the following variables:

private Excel.Application _app;
private Excel.Workbooks _books;
private Excel.Workbook _book;
protected Excel.Sheets _sheets;
protected Excel.Worksheet _sheet;

then open the workbook in the following method:

protected void OpenExcelWorkbook(string fileName)
{
_app = new Excel.Application();

if (_book == null)
{
_books = _app.Workbooks;
_book = _books.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_sheets = _book.Worksheets;
}
}

Make sure you have a method that will close the workbook

protected void CloseExcelWorkbook() {
_book.Save();
_book.Close(false, Type.Missing, Type.Missing);
}

and you need a method to clean up references to Excel objects (since they are COM), otherwise Excel will remain running.

protected void NAR(object o) {

try {
if(o != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}

finally {
o = null;
}
}

Now you can select the worksheet you want to remove a row on:

OpenExcelWorkbook(@"d:\temp\yourworkbook.xls");
_sheet = (Excel.Worksheet)_sheets[1];
_sheet.Select(Type.Missing);
Excel.Range range = _sheet.get_Range("A7:A7", Type.Missing);
range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
NAR(range);
NAR(_sheet);
CloseExcelWorkbook();
NAR(_book);
_app.Quit();
NAR(_app);

 

 

Answer 2

Hi,

I'm working on an C# winforms application.
I am binding a datatable to a datagrid.
The user can do update, insert and delete operations on this grid.
I wanted to know how do I flush this datagrid to an excel file using Interop.
And, I wanted to know how to delete an Active Worksheet from an excel file using Interop.

Thanks in advance,
livehed

Answer 3

Nice job.
Thanks

Answer 4

Hi,


The solution to the problem is as follows for .NET 1.1 and above :

//define these 3 in the packages and libraries :

using

 

 

 

 

System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;


//define the excel variables we are going to use in the properties region :

Microsoft.Office.Interop.Excel.ApplicationClass clsExcel = null;
Microsoft.Office.Interop.Excel.Workbook clsWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet clsWorksheet = null;

 

 

 

 

//in the method use in the following way :

try

{

string filePath = "C:\\sample.xls";
//give the path of our excel file here

clsExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//define excel class

clsExcel.Visible = false;
//set the visible property

clsWorkbook = clsExcel.Workbooks.Open(filePath, 2, false, 5,"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false,true, 0, false, true,Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//open the workbook for editing

clsWorksheet = (Excel.Worksheet)clsWorkbook.ActiveSheet;
//note that this will work only when you have one worksheet (which is obviously
//the active sheet), in case of multiple worksheets use the name property

Excel.Range range = clsWorksheet.get_Range("A3","A20".ToString());
//setting the range for deleting the rows

range.EntireRow.Delete(Excel.XlDirection.xlUp);
//for deleting the rows; in case of columns use: 'EntireColumn' instead of 'EntireRow'

clsWorkbook.Save();
//save the workbook

clsWorkbook.Close(false, "", false);
//close the workbook

clsExcel.Quit();
//quit the workook

 

}

catch(Exception e)
{
Console.WriteLine(e);
}


//In case you face errors while opening the excel file saying that it is already
//opened, go to taskmanager - take the processes tab and end the excel.exe
//processes.

 

 

Answer 5

@Extreme Spartan

@Sander_G

I have tried using the 

using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;

using System.Data.OleDb;

But I am getting the following error:

Error 1 The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

Error 2 The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

Any ideas what I am missing. Ultimately I am trying to clean up an excel sheet in order to extract data from and dump it into a csv file. (this is a combination of several files in order to save time and cleanup efforts).