none
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

     

     

    Friday, January 06, 2006 7:02 AM

Answers

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

     

     

    Friday, January 06, 2006 7:55 AM

All replies

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

     

     

    Friday, January 06, 2006 7:55 AM
  • 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

    Friday, January 13, 2006 12:17 PM
  • Nice job.
    Thanks

    Monday, October 26, 2009 9:58 AM
  • 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.

     

     

    Thursday, March 18, 2010 5:54 AM
  • @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).

    Tuesday, February 12, 2013 12:09 AM