C# .NET - Deleting a row in excel sheet using C#

Deleting a row in excel sheet using C# Programmatically

You could check here for sample code to accomplish this using reference to the Microsoft Excel 10.0 Object Library...

http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/920180bf-1c84-40f7-b547-ba9532e309cd

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);

Hi

yes its easy with MS office 12.0 object library and C#

Here sample code to do this

http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/920180bf-1c84-40f7-b547-ba9532e309cd

thank you
http://www.codegain.com

Hi Sakshi:

it seems that you forgot to add .EntireRow in the line that defines range. Therefore your code only deletes cell A7 not row 7. I didn't check the rest of your code, but that line should definitely be changed to:

Excel.Range range = _sheet.get_Range("A7:A7", Type.Missing).EntireRow;

Hope this helped,
Rolf

Hi,

use this code "Excel.Range range = _sheet.get_Range("A7:A7", Type.Missing).EntireRow;"  insted of "Excel.Range range = _sheet.get_Range("A7:A7", Type.Missing);"

Below is a sample code which shows how to delete a row from excel using Microsoft.Office.Interop.Excel class library.

Microsoft.Office.Interop.Excel.ApplicationClass clsExcel = null;
Microsoft.Office.Interop.Excel.Workbook clsWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet clsWorksheet = null;
// open the template...
clsExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
clsExcel.Visible = false;
clsWorkbook = clsExcel.Workbooks.Open("C:\mySpreadsheet.xls", 2, false, 5,
"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindow s, "", false,
true, 0, false, true,
Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNor malLoad);
// get worksheet...
clsWorksheet =
((Microsoft.Office.Interop.Excel.Worksheet)clsWork book.Worksheets);
clsWorksheet.get_Range("A1", "A1").EntireRow.Delete();
// save...
clsWorkbook.Save();
clsWorkbook.Close(false, "", false);
// release...
clsExcel.Quit();

Hi,

you can easily delete a row and do all other editing on Excel file with this excellent http://www.gemboxsoftware.com/spreadsheet/overview library.

Here is a sample http://www.gemboxsoftware.com/spreadsheet/overview code how to delete a row:

var excelFile = new ExcelFile();

 

excelFile.LoadXls("MyData.xls");

 

// Delete first row.

excelFile.Worksheets[0].Rows[0].Delete();

 

excelFile.SaveXls("MyDataOut.xls");