CODEWITHSUNDEEP
c#excel
TheNoob
TheNoob

Reputation: 929

C# and excel deleting rows

I want to write from my form (in C#) to an excel spread sheet and delete certain rows if blank.

I can write perfectly fine to a speadsheet and save it, but lets say the user entered data into row a1, a2, a3, and a4, I now want to delete all the rows in between a4 and a29.

All I need is to find out how to delete a certain range of cells.

Thanks

Upvotes: 19

Views: 68447

Answers (6)

rajquest
rajquest

Reputation: 711

using Microsoft.office.interop.excel delete rows using workseet range

Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(srcFile);
        Excel.Worksheet workSheet = xlWorkbook.Sheets[1];
        Excel.Range range = workSheet.Rows[2];
        
        range.Delete();
        
        xlWorkbook.SaveAs(dstFile);
        xlWorkbook.Close();
        xlApp.Quit();

Upvotes: 0

daniele3004
daniele3004

Reputation: 13970

To remove a row you need an only simple row of code as a follow

xlWorkSheet.Rows[NUMBER_ROW].Delete();

Upvotes: 1

Akhila
Akhila

Reputation: 91

You can specify a cell (eg. A1) and find the entire row containing that cell as a range and then can delete the row.

excel.Worksheet sheet = (excel.Worksheet)excelWorkBook.Sheets["sheet1"];    
excel.Range cells = (excel.Range)sheet.Range["A1", Type.Missing];    
excel.Range del = cells.EntireRow;    
del.Delete();

The above given code will delete first row from sheet1

Upvotes: 3

KR Akhil
KR Akhil

Reputation: 1017

// Here is the answers to 
// 1. Delete entire row - Below rows will shift up
// 2. Delete few cells - Below cells will shift up
// 3. Clear few cells - No shifting

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application ExcelApp = new Excel.Application();
                  Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(ResultFilePath);
                  ExcelApp.Visible = true;
                  Excel.Worksheet ExcelWorksheet = ExcelWorkbook.Sheets[1];

Excel.Range TempRange = ExcelWorksheet.get_Range("H11", "J15");

// 1. To Delete Entire Row - below rows will shift up
TempRange.EntireRow.Delete(Type.Missing);

// 2. To Delete Cells - Below cells will shift up
TempRange.Cells.Delete(Type.Missing);

// 3. To clear Cells - No shifting
TempRange.Cells.Clear();

Upvotes: 15

Mahmut EFE
Mahmut EFE

Reputation: 5248

You can use this. İt's working ...

    _Application docExcel = new Microsoft.Office.Interop.Excel.Application { Visible = false };
   dynamic workbooksExcel = docExcel.Workbooks.Open(@"C:\Users\mahmut.efe\Desktop\Book4.xlsx");
   var worksheetExcel = (_Worksheet)workbooksExcel.ActiveSheet;

   ((Range)worksheetExcel.Rows[2, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);

   workbooksExcel.Save();
   workbooksExcel.Close(false);
   docExcel.Application.Quit();

For more information you can visit this web site

Upvotes: 11

DkAngelito
DkAngelito

Reputation: 1187

You can do it using a Range Object. I assume here that you are using Excel interop.

Let say you have your book open, then set the range then delete it It should look something like this

ApplicationClass excel = new ApplicationClass();
//Abrir libro y seleccionar la hoja adecuada aqui
//...

Microsoft.Office.Interop.Excel.Range cel = (Range)excel.Cells[rowIndex, columnIndex];
cel.Delete();

Upvotes: 10

Related Questions