使用Excel Interop C＃过滤出数据透视表中的（空白）行

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.

我有一个excel有三个工作表,原始数据,数据透视表和数据透视表。由于原始数据将由具有未知行数的代码更新,因此我选择整列作为数据源。刷新数据透视表时,它始终在原始数据中获得一些空行,因此数据透视表中将有一个(空白)行。

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.

我想过滤掉同一程序中的(空白)行。到目前为止,我已经阅读了excel文件,但我不知道如何过滤(空白)行,谢谢。

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

1 个解决方案

#1

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.

使原始数据成为表而不是标准范围。单击数据内的任意位置,然后从功能区中的“插入”选项卡中选择“表格”,Excel将自动将您的范围转换为表格。

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

表在一个范围内具有许多优点,其中最重要的是在添加或删除行时自动调整大小。如果你的自动刷新以某种方式不删除行(只是内容),那就是通过这样的方式解决(假设“Table1”是你的表的名称):

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.

将数据透视表的数据源更改为表(在Excel中 - 永久)。这应该省略任何空白行。
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:

下面是一个如何动态更改数据透视表的范围的示例,使用A1:C8作为示例范围:

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

如何在excel表中获取被占用单元格的范围

#1

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.