private bool FieldExistsInPivotTable(string field, MSExcel.PivotCell cell)
{
MSExcel.PivotFields pFields = cell.PivotTable.PivotFields();
foreach (MSExcel.PivotField pField in pFields)
{
if (pField.Name.Equals(field))
{
return(true);
}
}
return(false);
}
public static bool IsPivotDataCell(Excel.Range rngCell)
{
try
{
Excel.PivotCell pc = rngCell.PivotCell;
Excel.PivotField pf = pc.PivotField;
return(pf.Orientation == Excel.XlPivotFieldOrientation.xlDataField);
}
catch
{
return(false);
}
}
private bool PivotCellTypeIsAmount(MSExcel.PivotCell pivotCell)
{
if (pivotCell.PivotCellType == MSExcel.XlPivotCellType.xlPivotCellCustomSubtotal ||
pivotCell.PivotCellType == MSExcel.XlPivotCellType.xlPivotCellGrandTotal ||
pivotCell.PivotCellType == MSExcel.XlPivotCellType.xlPivotCellSubtotal ||
pivotCell.PivotCellType == MSExcel.XlPivotCellType.xlPivotCellValue)
{
return(true);
}
else
{
return(false);
}
}
private bool IsPivotTableDataField(MSExcel.PivotCell cell, string fieldName)
{
MSExcel.PivotFields dataFields = cell.PivotTable.DataFields;
foreach (MSExcel.PivotField dataField in dataFields)
{
if (dataField.SourceName == fieldName)
{
return(true);
}
}
return(false);
}
public static PivotCellDictionary GetPivotCellQuery(Excel.Range rngCell)
{
Excel.PivotTable pt = rngCell.PivotTable;
Excel.PivotCell pc = rngCell.PivotCell; //Field values
Excel.PivotFields pgfs = (Excel.PivotFields)(pt.PageFields);
var pivotCellDic = new PivotCellDictionary();
AddSingleAxisFiltersToDic(pc, pivotCellDic);
AddSinglePageFieldFiltersToDic(pgfs, pivotCellDic);
AddMultiplePageFieldFilterToDic(pt, pivotCellDic);
return(pivotCellDic);
}
private static void AddSingleAxisFiltersToDic(Excel.PivotCell pc, PivotCellDictionary pivotCellDic)
{
Dictionary <string, string> singDic = pivotCellDic.SingleSelectDictionary;
//Filter by Row and ColumnFields - note, we don't need a loop here but will use one just in case
foreach (Excel.PivotItem pi in pc.RowItems)
{
Excel.PivotField pf = (Excel.PivotField)pi.Parent;
singDic.Add(pf.Name, pi.SourceName.ToString());
}
foreach (Excel.PivotItem pi in pc.ColumnItems)
{
Excel.PivotField pf = (Excel.PivotField)pi.Parent;
singDic.Add(pf.Name, pi.SourceName.ToString());
}
}