private void BuildFormatting(PivotTag pivotTag, FieldPivotTag tag, IXLPivotField pf)
{
foreach (var func in tag.SubtotalFunction)
{
pf.AddSubtotal(func);
}
// TODO pivot field formatting
/*
* ' Rem Build page fields' + vbCR +
* ' For i = 1 To PageFieldsCount' + vbCR +
* ' V = Pages(i)' + vbCR +
* ' Set PF = PT.PivotFields(V(2))' + vbCR +
* ' PF.Subtotals = V(3)' + vbCR +
* ' If Args(14) = True Then' + vbCR +
* ' Set FmtRange = SrcRange.Cells(2, V(1) - 1)' + vbCR +
* ' If (PF.DataType = xlDate) Or (PF.DataType = xlNumber) Then' + vbCR +
* ' PF.NumberFormat = FmtRange.NumberFormat' + vbCR +
* ' End If' + vbCR +
* ' PF.DataRange.Interior.ColorIndex = FmtRange.Interior.ColorIndex' + vbCR +
* ' PF.DataRange.Font.Name = FmtRange.Font.Name' + vbCR +
* ' PF.DataRange.Font.Color = FmtRange.Font.Color' + vbCR +
* ' PF.DataRange.Font.Size = FmtRange.Font.Size' + vbCR +
* ' PF.DataRange.Font.FontStyle = FmtRange.Font.FontStyle' + vbCR +
* ' PF.DataRange.HorizontalAlignment = FmtRange.HorizontalAlignment' + vbCR +
* ' PF.DataRange.VerticalAlignment = FmtRange.VerticalAlignment' + vbCR +
* ' End If' + vbCR +
* ' Next' + vbCR +
*/
}
private (string, IXLWorksheet, IXLCell) GetDestination(PivotTag pivot, XLWorkbook wb, IEnumerable <OptionTag> pageTags)
{
string tableName = pivot.GetParameter("name");
if (tableName.IsNullOrWhiteSpace())
{
tableName = "PivotTable";
}
IXLWorksheet dstSheet;
IXLCell dstCell;
var dstStr = pivot.GetParameter("dst");
if (!dstStr.IsNullOrWhiteSpace())
{
var shSp = dstStr.IndexOf("!", StringComparison.Ordinal);
dstSheet = wb.Worksheet(dstStr.Substring(0, shSp));
if (dstSheet == null)
{
throw new ArgumentException($"Can\'t find pivot destination sheet \'{dstStr.Substring(0, shSp)}\'");
}
dstStr = dstStr.Substring(shSp + 1, dstStr.Length - shSp - 1);
dstCell = dstSheet.Cell(dstStr);
if (dstCell == null)
{
throw new ArgumentException($"Can\'t find pivot destination cell \'{dstStr}\'");
}
}
else
{
dstSheet = wb.AddWorksheet(tableName);
dstCell = dstSheet.Cell(pageTags.Count() + 3, 2);
}
return(tableName, dstSheet, dstCell);
}
private IXLPivotTable CreatePivot(PivotTag pivot, ProcessingContext context, IXLWorksheet targetSheet, string tableName, IXLCell targetCell)
{
IXLRange srcRange = context.Range.Offset(-1, 1, context.Range.RowCount(), context.Range.ColumnCount() - 1);
var pt = targetSheet.PivotTables.AddNew(tableName, targetCell, srcRange);
pt.MergeAndCenterWithLabels = pivot.HasParameter("MergeLabels");
pt.ShowExpandCollapseButtons = pivot.HasParameter("ShowButtons");
pt.ClassicPivotTableLayout = !pivot.HasParameter("TreeLayout");
pt.AutofitColumns = pivot.HasParameter("AutofitColumns");
pt.SortFieldsAtoZ = !pivot.HasParameter("NoSort");
pt.PreserveCellFormatting = !pivot.HasParameter("NoPreserveFormatting");
pt.ShowGrandTotalsColumns = pivot.HasParameter("ColumnGrand");
pt.ShowGrandTotalsRows = pivot.HasParameter("RowGrand");
pt.SaveSourceData = true;
pt.FilterAreaOrder = XLFilterAreaOrder.DownThenOver;
pt.RefreshDataOnOpen = true;
return(pt);
}
private IXLPivotTable CreatePivot(PivotTag pivot, ProcessingContext context, XLPivotTableDestination destination)
{
var rowOffset = context.Range.RangeAddress.FirstAddress.RowNumber > 1 ? -1 : 0;
IXLRange srcRange = context.Range.Offset(rowOffset, 1, context.Range.RowCount(), context.Range.ColumnCount() - 1);
var pt = destination.TargetWorksheet.PivotTables.Add(destination.TableName, destination.TargetCell, srcRange);
pt.MergeAndCenterWithLabels = pivot.HasParameter("MergeLabels");
pt.ShowExpandCollapseButtons = pivot.HasParameter("ShowButtons");
pt.ClassicPivotTableLayout = !pivot.HasParameter("TreeLayout");
pt.AutofitColumns = pivot.HasParameter("AutofitColumns");
pt.SortFieldsAtoZ = !pivot.HasParameter("NoSort");
pt.PreserveCellFormatting = !pivot.HasParameter("NoPreserveFormatting");
pt.ShowGrandTotalsColumns = pivot.HasParameter("ColumnGrand");
pt.ShowGrandTotalsRows = pivot.HasParameter("RowGrand");
pt.SaveSourceData = true;
pt.FilterAreaOrder = XLFilterAreaOrder.DownThenOver;
pt.RefreshDataOnOpen = true;
return(pt);
}
private void BuildFormatting(PivotTag pivot, DataPivotTag tag, IXLPivotValue pf)
{
if (pivot.HasParameter("NoPreserveFormatting") && pivot.HasParameter("CaptionNoFormatting"))
{
var fmtRange = tag.Cell;
}
// TODO pivot value formatting
/*
* ' If (Args(14) = True) And (Args(17) = True) Then' + vbCR +
* ' On Error Resume Next' + vbCR +
* ' For i = 1 To DataFieldsCount' + vbCR +
* ' V = Datas(i)' + vbCR +
* ' Set PF = PT.DataFields(i)' + vbCR + // V(2) & " ")' + vbCR +
* ' Set FmtRange = SrcRange.Cells(2, V(1) - 1)' + vbCR +
* ' If PF.DataType <> xlText Then' + vbCR +
* ' PF.NumberFormat = FmtRange.NumberFormat' + vbCR +
* ' End If' + vbCR +
* ' PF.DataRange.Interior.ColorIndex = FmtRange.Interior.ColorIndex' + vbCR +
* ' PF.DataRange.Font.Name = FmtRange.Font.Name' + vbCR +
* ' PF.DataRange.Font.Color = FmtRange.Font.Color' + vbCR +
* ' PF.DataRange.Font.Size = FmtRange.Font.Size' + vbCR +
* ' PF.DataRange.Font.FontStyle = FmtRange.Font.FontStyle' + vbCR +
* ' PF.DataRange.HorizontalAlignment = FmtRange.HorizontalAlignment' + vbCR +
* ' PF.DataRange.VerticalAlignment = FmtRange.VerticalAlignment' + vbCR +
* ' If Args(17) = True Then' + vbCR +
* ' Set CaptionRange = SrcRange.Cells(1, V(1) - 1)' + vbCR +
* ' PF.LabelRange.Interior.ColorIndex = CaptionRange.Interior.ColorIndex' + vbCR +
* ' PF.LabelRange.Font.Name = CaptionRange.Font.Name' + vbCR +
* ' PF.LabelRange.Font.Color = CaptionRange.Font.Color' + vbCR +
* ' PF.LabelRange.Font.Size = CaptionRange.Font.Size' + vbCR +
* ' PF.LabelRange.Font.FontStyle = CaptionRange.Font.FontStyle' + vbCR +
* ' PF.LabelRange.HorizontalAlignment = CaptionRange.HorizontalAlignment' + vbCR +
* ' PF.LabelRange.VerticalAlignment = CaptionRange.VerticalAlignment' + vbCR +
* ' End If' + vbCR +
* ' Next' + vbCR +
* ' End If' + vbCR +
*/
}