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 + */ }
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); }