Removing empty rows in excel using C#

Question

I want to remove empty rows at the end of program exection. In details I am inserting some results after calculation using C# in predefined excel sheets. At the end I need to delete/remove the empty rows programatically. Could any one suggest solution. My code is little big so I am unable to include here. For your understanding I am giving some input and output view of excel. In below Output Excel the rows D and E having empty row I want to remove programatically without mentioning the range.

Input Excel /Pre defined Excel Files

A   1   2   3   4
B
C
D
E

output Excel

A   1   2   3   4
B   ABc cde nAC 123
C   cdf fed x2  123
D
E

You havent even mentioned what library you use to create your excel file. — Tim Schmelter, May 08 '15 at 07:21
I am just inserting a data to predefined template excel and also I using the Microsoft.Office.Interop.Excel reference for importing excel. — Nachiappan R, May 08 '15 at 07:29
Look here: http://stackoverflow.com/questions/9951188/deleting-rows-from-an-excel-file-using-c-sharp — Sasha Truf, May 08 '15 at 07:31
I don't know the row number, only need to find the empty rows programmatically. — Nachiappan R, May 08 '15 at 08:07
@Nachiappan R excel sheet rows are around 1,048,576 rows in excel 2013 (Which initially are empty). you can do what you want but its a very overwhelming execution (1,048,576 times iteration!). but if your predefined excel sheet has limited row numbers, its more logical to do that. its easy to get row number of D (in your example) but if i did not misunderstand, row number of E is not clear, so the last row number is 1,048,576! if i misunderstand your need, correct me. — Technovation, May 11 '15 at 19:47
Also look here: https://stackoverflow.com/a/49232456/1114531 — Tomasz Kowalczyk, Mar 12 '18 at 10:03

score 2 · Accepted Answer · edited May 23 '17 at 12:18

2

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();
//...

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

You can also try using:

for(int i = 1; i <=20; i++)
{
   excelRange = (Excel.Range)excelWorkSheet.Cells[i, 1];
   if (!string.IsNullOrEmpty(excelRange.Text.ToString()))
   {
       ((Range)excelWorkSheet.Rows[i]).Delete(excelRange);
   }
}

Check out the link below

https://social.msdn.microsoft.com/Forums/office/en-US/469fdf10-35cc-46b2-a875-7b974deb5659/how-to-delete-all-empty-rows-from-a-excel-sheet-using-microsoftofficeinteropexcel?forum=exceldev

and

https://stackoverflow.com/a/9952004/4373895 Here "Something" is your null value.

Hope this helps.

edited May 23 '17 at 12:18

Community

1
1

answered May 08 '15 at 07:24

VikrantMore

903
7
25

While including (Range)excel.Cells[rowIndex, columnIndex]; I am facing error, could you help me on this. and in second solution I need to find null value or somestring. Are you trying to say somestring as null? – Nachiappan R May 08 '15 at 09:06
I tried the social msdn, but when i am trying this below lines I am getting this error "No overload for method 'CountA' takes '1' arguments" if (application.WorksheetFunction.CountA(exlwooksheet.Rows[i]) == 0) (exlwooksheet.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete(); – Nachiappan R May 11 '15 at 11:16
VikrantMore Can you look and help me on this issue? – Nachiappan R May 12 '15 at 15:04

score 1 · Answer 2 · answered Oct 18 '15 at 14:30

You can remove empty rows in Excel file using the following code

 xlApp = new Microsoft.Office.Interop.Excel.Application();
 Microsoft.Office.Interop.Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(fileName);
 Microsoft.Office.Interop.Excel._Worksheet sheet = excelWorkbook.Sheets[1];
 var LastRow = sheet.UsedRange.Rows.Count;
 LastRow = LastRow + sheet.UsedRange.Row - 1;
 for (int i = 1; i <= LastRow; i++)
 {
   if (application.WorksheetFunction.CountA(sheet.Rows[i]) == 0)
       (sheet.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete();
 }

score 0 · Answer 3 · answered Oct 16 '15 at 09:31

The below code works perfectly and creates a new blank row for a specified row and column number :

Excel.Range rng = (Excel.Range)xlWorkSheet1.Cells[RowNumber, ColumnNumber];
                            Excel.Range Row1 = rng.EntireRow;
                            Row1.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);

score 0 · Answer 4 · answered Nov 24 '15 at 10:54

Extension methods that deletes empty rows and columns from an Excel worksheet.

/// <summary>
/// Deletes empty rows and columns from the end of the given worksheet
/// </summary>
public static void Trim(this Excel.Worksheet worksheet)
{
    worksheet.TrimColumns();
    worksheet.TrimRows();
}

/// <summary>
/// Deletes empty rows from the end of the given worksheet
/// </summary>
public static void TrimRows(this Excel.Worksheet worksheet)
{
    Excel.Range range = worksheet.UsedRange;
    while(worksheet.Application.WorksheetFunction.CountA(range.Rows[range.Rows.Count]) == 0)
        (range.Rows[range.Rows.Count] as Excel.Range).Delete();
}

/// <summary>
/// Deletes empty columns from the end of the given worksheet
/// </summary>
public static void TrimColumns(this Excel.Worksheet worksheet)
{
    Excel.Range range = worksheet.UsedRange;
    while(worksheet.Application.WorksheetFunction.CountA(range.Columns[range.Columns.Count]) == 0)
        (range.Columns[range.Columns.Count] as Excel.Range).Delete();
}

Removing empty rows in excel using C#

4 Answers4