代码之家 › 专栏 › 技术社区 › badmaash

从C刷新Excel数据透视表#

.net c#

3

badmaash · 技术社区 · 15 年前

我正在尝试刷新Excel工作表中的数据透视表,并获取以下异常:

Item method in the PivotTables class failed

代码如下:

pivotSheet.Activate();
Microsoft.Office.Interop.Excel.PivotTables pivotTables = 
        (Microsoft.Office.Interop.Excel.PivotTables)pivotSheet.PivotTables(missing);
int pivotTablesCount = pivotTables.Count;  
    if (pivotTablesCount > 0)
    {
        for (int i = 0; i <= pivotTablesCount; i++)
        {
            pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
        }
    }

知道吗?

2 回复 | 直到 15 年前

1

8

Richard Smith 15 年前

假设索引从零开始,将用循环溢出集合。

尝试:

for (int i = 0; i < pivotTablesCount; i++)

如果这不起作用,Excel可能在1开始索引,而不是在0开始索引。

尝试:

for (int i = 1; i <= pivotTablesCount; i++)

2

Ashish Rathore 12 年前

这会帮你工作的。

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel.Workbook mWorkBook;
Microsoft.Office.Interop.Excel.Sheets mWorkSheets;

oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
foreach (Microsoft.Office.Interop.Excel.Worksheet pivotSheet in mWorkSheets)
{
    Microsoft.Office.Interop.Excel.PivotTables pivotTables = pivotSheet.PivotTables();
    int pivotTablesCount = pivotTables.Count;
    if (pivotTablesCount > 0)
    {
        for (int i = 1; i <= pivotTablesCount; i++)
        {
            pivotTables.Item(i).RefreshTable(); //The Item method throws an exception
        }
    }
}

3

0

FreeBird 6 年前

private void RefreshSheets(string filePath)
    {
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
        Excel.Workbook xlWorkbook = xlWorkbooks.Open(filePath);

        foreach (Excel.Worksheet xlworksheet in xlWorkbook.Worksheets)
        {
            Excel.PivotTables pivotTbls = (Excel.PivotTables)xlworksheet.PivotTables(Type.Missing);
            if (pivotTbls.Count > 0)
            {
                for (int i = 1; i <= pivotTbls.Count; j++)
                {
                    pivotTbls.Item(i).RefreshTable();
                }
            }
        }
        xlWorkbook.Save();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        xlWorkbook.Close(0);
        Marshal.ReleaseComObject(xlWorkbook);
        Marshal.ReleaseComObject(xlWorkbooks);
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);
    }