public static bool IsPivotCell(Range range)
{
try
{
PivotCell pc = range.PivotCell;
return(true);
}
catch
{
return(false);
}
}
private static bool IsPivotValueCell(Range range)
{
try
{
PivotCell pc = range.PivotCell;
if (pc.PivotCellType == XlPivotCellType.xlPivotCellValue)
{
return(true);
}
}
catch
{
return(false);
}
return(false);
}
private static void ChangePivotCell(PivotCell Cell, string Value, ListObject sourceDataTable)
{
// get source range for the pivot table
string dataCol = null;
int colIndex = 0;
dataCol = Cell.DataField.SourceName;
colIndex = ListObjectHelper.GetColumnIndex(sourceDataTable, dataCol);
int baseColIndex = colIndex;
// go through the pivot items on the changed target
List <string> filterColNames = new List <string>();
List <int> filterColIndexes = new List <int>();
List <object> filterValues = new List <object>();
// look for Pivot Items the cell is linked to
string pfIds = null;
// a list of Pivot Field Ids have gone through, to be used when check additional filters
pfIds = ":";
// look in Column Items
foreach (PivotItem pi in Cell.ColumnItems)
{
PivotField pf = pi.Parent;
pfIds += pf.SourceName + ":";
filterColNames.Add(pf.SourceName);
filterColIndexes.Add(ListObjectHelper.GetColumnIndex(sourceDataTable, pf.SourceName));
if (pi.SourceNameStandard == "(blank)")
{
filterValues.Add("=");
}
else
{
string val = pi.Name;
if (GeneralSettings.IsNumeric(val))
{
val = Math.Round(Convert.ToDouble(val), 8).ToString();
}
filterValues.Add(val);
}
}
// look in Row Items
foreach (PivotItem pi in Cell.RowItems)
{
PivotField pf = pi.Parent;
pfIds += pf.SourceName + ":";
filterColNames.Add(pf.SourceName);
filterColIndexes.Add(ListObjectHelper.GetColumnIndex(sourceDataTable, pf.SourceName));
if (pi.SourceNameStandard == "(blank)")
{
filterValues.Add("=");
}
else
{
string val = pi.Name;
if (GeneralSettings.IsNumeric(val))
{
val = Math.Round(Convert.ToDouble(val), 8).ToString();
}
filterValues.Add(val);
}
}
// apply filters on the data source
int i = 0;
if (sourceDataTable.AutoFilter != null)
{
sourceDataTable.AutoFilter.ShowAllData();
}
for (i = 0; i <= filterColNames.Count - 1; i++)
{
sourceDataTable.Range.AutoFilter(Field: filterColIndexes[i], Criteria1: filterValues[i]);
}
// if nothing after filter, then exit
if (!ListObjectHelper.SpecialCellsExists(sourceDataTable, XlCellType.xlCellTypeVisible))
{
ListObjectHelper.ResetTable(sourceDataTable);
return;
}
else
{
//try to empty the field
if (string.IsNullOrEmpty(Value))
{
// loop through all visible rows and set value to ""
foreach (Range rowX in sourceDataTable.DataBodyRange.SpecialCells(XlCellType.xlCellTypeVisible).Rows)
{
rowX.Cells[1, colIndex].Value = "";
}
}
else if (Value == "0")
{
// loop through all visible rows and set value to ""
foreach (Range rowX in sourceDataTable.DataBodyRange.SpecialCells(XlCellType.xlCellTypeVisible).Rows)
{
rowX.Cells[1, colIndex].Value = 0;
}
}
else
{
// check if Total Range exist for the table
if (!sourceDataTable.ShowTotals)
{
sourceDataTable.ShowTotals = true;
}
double newTotal = 0;
newTotal = Math.Round(Convert.ToDouble(Value), 6);
// max decimal 6
// check total row count, if total row count is 1 then update directly
int ttlRowCnt = 0;
sourceDataTable.ListColumns[1].TotalsCalculation = XlTotalsCalculation.xlTotalsCalculationCount;
ttlRowCnt = Convert.ToInt32(sourceDataTable.TotalsRowRange.Cells[1, 1].Value);
if (ttlRowCnt == 1)
{
// loop through all visible rows and update value
foreach (Range rowX in sourceDataTable.DataBodyRange.SpecialCells(XlCellType.xlCellTypeVisible).Rows)
{
rowX.Cells[1, colIndex].Value = newTotal;
}
}
else
{
// sum up data source value, divided by new value, calculate change factor
double previousTotal = 0;
double multipleFactor = 0;
double addFactor = 0;
double baseTotal = 0;
// set calculation to SUM
sourceDataTable.ListColumns[colIndex].TotalsCalculation = XlTotalsCalculation.xlTotalsCalculationSum;
sourceDataTable.ListColumns[baseColIndex].TotalsCalculation = XlTotalsCalculation.xlTotalsCalculationSum;
// trigger Calculation
((_Worksheet)sourceDataTable.Range.Worksheet).Calculate();
previousTotal = Math.Round(sourceDataTable.TotalsRowRange.Cells[1, colIndex].Value, 6);
baseTotal = Math.Round(sourceDataTable.TotalsRowRange.Cells[1, baseColIndex].Value, 6);
bool addToAll = true;
if (baseTotal > 0)
{
multipleFactor = newTotal / baseTotal;
addFactor = 0;
// when Base Total value = 0 then get item counts to split evenly
}
else
{
multipleFactor = 0;
sourceDataTable.ListColumns[colIndex].TotalsCalculation = XlTotalsCalculation.xlTotalsCalculationCount;
sourceDataTable.ListColumns[baseColIndex].TotalsCalculation = XlTotalsCalculation.xlTotalsCalculationCount;
// trigger Calculation
((_Worksheet)sourceDataTable.Range.Worksheet).Calculate();
previousTotal = sourceDataTable.TotalsRowRange.Cells[1, colIndex].Value;
baseTotal = sourceDataTable.TotalsRowRange.Cells[1, baseColIndex].Value;
if (baseTotal == 0)
{
// King Sun 2013-03-22 if all rows are empty, then take FCST_UNIT count to disaggregate
baseTotal = sourceDataTable.TotalsRowRange.Cells[1, 1].Value;
}
else
{
addToAll = false;
}
addFactor = newTotal / baseTotal;
}
double sumValue = 0.0;
// loop through all visible rows and update value
foreach (Range rowX in sourceDataTable.DataBodyRange.SpecialCells(XlCellType.xlCellTypeVisible).Rows)
{
double cellValue = 0.0;
if (rowX.Cells[1, baseColIndex].Value != null)
{
cellValue = rowX.Cells[1, baseColIndex].Value;
}
if (addToAll || rowX.Cells[1, colIndex].Value != null)
{
cellValue = Math.Round(cellValue * multipleFactor + addFactor, 6);
rowX.Cells[1, colIndex].Value = cellValue;
sumValue = sumValue + cellValue;
}
}
// adjust rounding difference
double diff = 0;
diff = sumValue - newTotal;
// if rounding causes mismatch of value, add the difference on the first possible cell
if (diff != 0)
{
foreach (Range rowX in sourceDataTable.DataBodyRange.SpecialCells(XlCellType.xlCellTypeVisible).Rows)
{
if (!string.IsNullOrEmpty(rowX.Cells[1, colIndex].Value.ToString()))
{
if (rowX.Cells[1, colIndex].Value > diff)
{
rowX.Cells[1, colIndex].Value = rowX.Cells[1, colIndex].Value - diff;
break; // TODO: might not be correct. Was : Exit For
}
}
}
}
}
}
}
return;
}
