c# - 如何在 C# 中使用 Microsoft.Office.Interop.Excel 添加数据透视表过滤器
问题描述
我基于 Excel (.xlsx) 文件创建了一个数据透视表。该程序将字段添加到行、值和过滤器。使用 PivotFilters.Add2 会导致 0x800a03ec 错误,从而终止程序。如何正确使用PivotFilters.Add2
?
我尝试过滤具有不同数据类型的不同字段。另外,我尝试Type.Missing
在未使用参数的地方使用。对于 VB,似乎有很多关于这种方法的信息,但对于 C# 来说没有那么多。
在过滤器中选择的项目应该在最后一行的两个日期之间。
var xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
var workBook = xlApp.Workbooks.Open(spreadsheetLocation);
var workSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets["Data"];
var workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.Add();
Microsoft.Office.Interop.Excel.Range last = workSheet1.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Microsoft.Office.Interop.Excel.Range range = workSheet1.get_Range("A1", last);
Microsoft.Office.Interop.Excel.PivotCaches pivotCaches = null;
Microsoft.Office.Interop.Excel.PivotCache pivotCache = null;
Microsoft.Office.Interop.Excel.PivotTable pivotTable = null;
Microsoft.Office.Interop.Excel.PivotFields pivotFields = null;
Microsoft.Office.Interop.Excel.PivotField filterField = null;
Microsoft.Office.Interop.Excel.PivotField accNumField = null;
Microsoft.Office.Interop.Excel.PivotField amountPaidField = null;
pivotCaches = workBook.PivotCaches();
pivotCache = pivotCaches.Create(XlPivotTableSourceType.xlDatabase, range);
pivotTable = pivotCache.CreatePivotTable(workSheet2.Cells[1,1]);
pivotFields = (Microsoft.Office.Interop.Excel.PivotFields)pivotTable.PivotFields();
amountPaidField = (Microsoft.Office.Interop.Excel.PivotField)pivotFields.Item("AmountPaid");
amountPaidField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
amountPaidField.NumberFormat = "$#,###,###.00";
accNumField = (Microsoft.Office.Interop.Excel.PivotField)pivotFields.Item("AccNumber");
accNumField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
filterField = (Microsoft.Office.Interop.Excel.PivotField)pivotFields.Item("AccDate");
filterField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;
filterField.EnableMultiplePageItems = true;
filterField.PivotFilters.Add2(XlPivotFilterType.xlDateBetween, Type.Missing, DateTime.Now.AddDays(-30), DateTime.Now.AddDays(-20));
解决方案
#First it deletes two rows and then it creates a pivot table#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
namespace ConsoleApplication4
{
class Program
{
public static string Column(int column)
{
column--;
if (column >= 0 && column < 26)
return ((char)('A' + column)).ToString();
else if (column > 25)
return Column(column / 26) + Column(column % 26 + 1);
else
throw new Exception("Invalid Column #" + (column + 1).ToString());
}
static void Main(string[] args)
{
try
{
string path = @"C:\Users\UX155512\Documents\Book1fd.xlsx";
var excelFile = new Application();
Workbook workBook = excelFile.Workbooks.Open(path);
Worksheet workSheet = workBook.Worksheets[1];
Worksheet pivotSheet = workBook.Worksheets.Add(
System.Reflection.Missing.Value,
workBook.Worksheets[workBook.Worksheets.Count],
1,
System.Reflection.Missing.Value);
Range last = workSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
int lastRow = last.Row;
int lastCol = last.Column;
string lastColVal = Column(lastCol);
string lastFilledCol = lastColVal + lastRow.ToString();
Console.WriteLine(lastFilledCol);
Console.WriteLine(lastColVal);
Console.WriteLine(lastRow);
Console.WriteLine(lastCol);
for (int i = 1; i <= lastRow; i++)
{
if (workSheet.Cells[1][i].Value == ("HDR"))
{
workSheet.Rows[i].Delete();
Console.WriteLine("The Row Containing HDR has been deleted");
}
if (workSheet.Cells[1][i].Value == ("TRL"))
{
workSheet.Rows[i].Delete();
Console.WriteLine("The Row Containing TLR has been deleted");
}
}
Range lastRange = workSheet.Range["A1", lastFilledCol];
pivotSheet.Name = "Pivot Table";
Range range = pivotSheet.Cells[1, 1];
PivotCache pivotCache = (PivotCache)workBook.PivotCaches().Add(XlPivotTableSourceType.xlDatabase, lastRange);
PivotTable pivotTable = (PivotTable)pivotSheet.PivotTables().Add(PivotCache: pivotCache, TableDestination: range);
PivotField pivotField = (PivotField)pivotTable.PivotFields("Plan Number");
pivotField.Orientation = XlPivotFieldOrientation.xlRowField;
PivotField pivotField2 = (PivotField)pivotTable.PivotFields("Source");
pivotField2.Orientation = XlPivotFieldOrientation.xlColumnField;
PivotField pivotField3 = (PivotField)pivotTable.PivotFields("Total");
pivotField3.Orientation = XlPivotFieldOrientation.xlDataField;
pivotField3.Function = XlConsolidationFunction.xlSum;
workBook.SaveAs(@"C:\Users\UX155512\Documents\Excel Dump\Trial9.xlsx");
workBook.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
Console.Read();
}
}
}
推荐阅读
- c# - C#如何将double数组转换为float数组或int数组?
- github - Github-pull 请求冲突
- ruby - 过滤器链停止为 :authenticate_user!呈现或重定向 app_1 已完成 401 Unauthorized
- azure - Elasticsearch xpack RSA_verify_PKCS1_PSS_mgf1:第一个八位字节无效
- python - 小批量反向传播澄清
- python-3.x - Python 模块因信任而出错
- python - 如何在python中使用groupby更新原始数组
- pdf - 使用 PDFBOX 标记 PDF 时,我无法看到标记下的内容?
- spring - Spring My-batis MapperScannerConfigurer 不解析数据源占位符值
- facebook - facebook graph api 完成视频洞察数据处理多长时间