C# Excel row delete

[darkocean]

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,

[darkocean]

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

[darkocean]

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

        }

[sknake]

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

[darkocean]

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

[sknake]

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

[darkocean]

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

[sknake]

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

[darkocean]

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.

[sknake]

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

[darkocean]

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

[sknake]

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

[darkocean]

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...

[sknake]

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

[darkocean]

No I could not solve it :(

[sknake]

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

    }

[darkocean]

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

[santiagoivan.santana]

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