Filter out (blank) row in pivot table with Excel Interop C#

Question

I have an excel with three worksheets, raw data, pivot table and pivot chart. Since the raw data will be updated by code with unknown number of rows, I selected the whole column to be the data source. When the pivot table is refreshed, it always get some empty rows in the raw data thus there will be a (blank) row in the pivot table.

I want to filter out the (blank) row in the same program. So far I have read the excel file, but I have no idea on how to filter just the (blank) row, thanks.

        Application exApp = new Application();
        Workbook exWbk = exApp.Workbooks.Open(targetPath);
        PivotTable pivotTb = exWbk.Sheets[1].pivotWorkSheet.PivotTables("Report");

Hambone Hambone · Accepted Answer · 2016-11-04T02:14:39

Make the raw data a Table instead of a standard range. Click anywhere inside the data, and from the "Insert" tab in the ribbon, select "Table," and Excel will automatically turn your range into a table.

A Table has many advantages over a range, not the least of which is it automatically sizes when rows are added or deleted. If your automatic refresh is somehow not deleting the rows (just the content), that's resolved by something like this (assume "Table1" is the name of your Table):

Excel.ListObject lo = exWbk.Worksheets["Sheet1"].ListObjects["Table1"];
foreach (Excel.ListRow rw in lo.ListRows)
{
    if (exApp.WorksheetFunction.CountA(rw.Range) == 0)
        rw.Delete();
}

Change the data source for your pivot table to be the Table (within Excel -- permanently). This should omit any blank rows.
Refresh your pivot table:

pivotTb.RefreshTable();

If you REALLY don't want to convert your range to a table, then I suggest you dynamically modify the source data for your pivot table.

Here's an example of how you could dynamically change the range of your pivot table, using A1:C8 as an example range:

Excel.Range r = exWbk.Worksheets["Sheet1"].Range["A1:C8"];
Excel.PivotCache pc = exWbk.PivotCaches().Create(
    Excel.XlPivotTableSourceType.xlDatabase, r);
pivotTb.ChangePivotCache(pc);
pivotTb.RefreshTable();

There are no shortage of suggestions on how to define the used range, but just in case you don't have a way:

How to get the range of occupied cells in excel sheet

Filter out (blank) row in pivot table with Excel Interop C#

1 Answers