Whenever I want to delete row my excel file in c#. I have get error. I tried much much codes on the net but I can not solve my problem. Please solve my problem...

Regards,

Re: C# Excel row delete 80 80

I tried these codes but no solution.

public partial class Form1 : Form
    {

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

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenExcelWorkbook(@"C:\myfile.xls");
            _sheet = (Excel.Worksheet)_sheets[1];
            _sheet.Select(Type.Missing);
            //Excel.Range range = _sheet.get_Range("A1:A1", Type.Missing);
            Excel.Range range = _sheet.get_Range("A1:A3", Type.Missing).EntireRow;
            range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
            NAR(range);
            NAR(_sheet);
            CloseExcelWorkbook();
            NAR(_book);
            _app.Quit();
            NAR(_app);

        }

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

            if (_book == null)
            {
                _books = _app.Workbooks;
                _book = _books.Open(fileName, Type.Missing, false, 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;
            }
        }

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

        protected void NAR(object o)
        {

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

            finally
            {
                o = null;
            }
        }
Re: C# Excel row delete 80 80

Also tried this but I learnt oleDb does not sopport the delete command please help me. What can I do ?

public void deleteRowExcel(String myID)
        {

            DataTable dltXSL = new DataTable();
            try
            {
                string strFile = "C:\\myFile.xls";
                string strConnectionString = "";
                string bolge = aramaYap(bildirimNO);
                bolge = "ANKARA";
                if (strFile.Trim().EndsWith(".xlsx"))
                {
                    strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
                }
                else if (strFile.Trim().EndsWith(".xls"))
                {
                    strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
                }
                OleDbConnection baglanti = new OleDbConnection(strConnectionString);
                baglanti.Open();
                OleDbDataAdapter adaptor = new OleDbDataAdapter();
                string sil = String.Format("DELETE FROM ["+ bolge +"$] WHERE Bildirim='{0}'", myID);
                OleDbCommand selectCMD = new OleDbCommand(sil, baglanti);
                adaptor.SelectCommand = selectCMD;
                selectCMD.ExecuteNonQuery();
                adaptor.Fill(dltXSL);
                baglanti.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }
Re: C# Excel row delete 80 80

Welcome to Daniweb darkocean! Please use code tags when posting code

To answer your question you can delete a single row this way:

Excel.Range ran;
ran = (Excel.Range)this.Application.Rows[12, missing];
ran.Select();
ran.Delete(Excel.XlDirection.xlUp);

To delete multiple rows:

//Get a set of cells in the rows
Excel.Range rng = ws.get_Range("a12", "A14");
//Then act on the entire rows of the range
rng.EntireRow.Delete(Excel.XlDirection.xlUp);

Borrowed from:
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/8963526e-21d5-47b8-8001-d0dab9c21674

Re: C# Excel row delete 80 80

Thank you for your answer...
but When I tried this code I get this error :
COMException was unhandled
Exception result 0x80028018
and this error is in this line :

Application.Run(new Form1());
 // (program.cs)

I just use a button for trying this code.
Please help me in this situation because I tried these codes before and I met this error again.

Regards

Re: C# Excel row delete 80 80

Is your locale settings non-english?
Before creating Excel object:

System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

After closing Excel:

System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;

From:
http://www.gotdotnet.ru/Forums/Common/290167.aspx

Re: C# Excel row delete 80 80
ran = (Excel.Range)this.Application.Rows[5, missing];
//The visual studio shows error for Application and missing 
//Application does not contain a defination, no extension method for Application
Re: C# Excel row delete 80 80

Zip your project and upload it with the excel file in the .zip

Re: C# Excel row delete 80 80

Hello,

I will search the BildirimNo in LEFKOŞA,GİRNE,MAGOSA,GÜZELYURT sheets and found this BildirimNo (it is like ID) ,I dont know which sheet has this ID and which row. I want to find it and delete this row in this sheet...
Thank you.

PS : This application just test. I will add codes in my real application.

Re: C# Excel row delete 80 80

You forgot to open the workbook...

private void button1_Click(object sender, EventArgs e)
    {
      const string xlsPath = @"C:\dotnetwin\xlApp\WindowsFormsApplication1\teknoser.xls";
      Excel.Application excelApp = new Excel.ApplicationClass();
      try
      {
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(
          xlsPath,
          0,
          false,
          5,
          "",
          "",
          false,
          Excel.XlPlatform.xlWindows,
          "",
          true,
          false,
          0,
          true,
          false,
          false
          );

        Excel.Worksheet sheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item("TÜMÜ");
        Excel.Range ran = (Excel.Range)sheet.Rows[5, Type.Missing];
        ran.Select();
        ran.Delete(Excel.XlDirection.xlUp);
      }
      finally
      {
        excelApp.Visible = true;
      }
    }
Re: C# Excel row delete 80 80

currently running smoothly, but excel opens the original file and shows the line to be deleted, the question then is off and save the records say they want to be deleted if it is saved. I do not want to see the file open I'll save and not to ask directly access the file in the background and the lines are clear

Re: C# Excel row delete 80 80

Remove where I set the .Visible = true; and call .Save() before closing the application.

To save an existing Excel file see this thread:
http://www.daniweb.com/forums/thread208134.html

Re: C# Excel row delete 80 80
excelWorkbook.SaveAs(xlsPath, true, false, true, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, true, false, false, true, false); //or tried this :

excelWorkbook.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
//excelWorkbook.Close(false, missing, missing);
//excelWorkbook.Save(); 
//excelApp.Quit();

It gives the error...

Re: C# Excel row delete 80 80

You should be able to figure it out... you have two complete sets of code to merge.

Re: C# Excel row delete 80 80

No I could not solve it :(

Re: C# Excel row delete 80 80
private void button1_Click(object sender, EventArgs e)
    {
      const string xlsPath = @"C:\dotnetwin\xlApp\WindowsFormsApplication1\teknoser.xls";
      Excel.Application excelApp = new Excel.ApplicationClass();
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(
          xlsPath,
          0,
          false,
          5,
          "",
          "",
          false,
          Excel.XlPlatform.xlWindows,
          "",
          true,
          false,
          0,
          true,
          false,
          false
          );

        Excel.Worksheet sheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item("TÜMÜ");
        Excel.Range ran = (Excel.Range)sheet.Rows[5, Type.Missing];
        ran.Select();
        ran.Delete(Excel.XlDirection.xlUp);

        string tmpName = System.IO.Path.GetTempFileName();
        System.IO.File.Delete(tmpName);
        excelWorkbook.SaveAs(tmpName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        excelWorkbook.Close(false, Type.Missing, Type.Missing);
        excelApp.Quit();
        System.IO.File.Delete(xlsPath);
        System.IO.File.Move(tmpName, xlsPath);

    }
Re: C# Excel row delete 80 80

Thank you so much. it is working...you are perfect :)

Re: C# Excel row delete 80 80

"TÜMÜ" ??? wtf is this for

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.