protected virtual void DoNewPivot(object dataSource, PivotOptions options)
{
options ??= new PivotOptions();
var spread = options.Spreadsheet?.Workbook ?? Workbook;
PivotTable pivotTable;
using (new UsingProcessor(() => spread.BeginUpdate(), () => spread.EndUpdate()))
{
//Disable automatic show of pivot table field list.
spread.DocumentSettings.ShowPivotTableFieldList = false;
var table = FindDataTable();
Worksheet pivotSheet = null;
if (!string.IsNullOrWhiteSpace(options.PivotSheetName))
{
for (int i = 0; i < spread.Worksheets.Count; i++)
{
var sheet = spread.Worksheets[i];
if (string.Compare(sheet.Name, options.PivotSheetName, true) == 0)
{
if (options.Replace)
{
spread.Worksheets.RemoveAt(i);
var newSheet = spread.Worksheets.Insert(i, options.PivotSheetName);
pivotSheet = newSheet;
}
else
{
throw new Exception($"Cannot create pivot sheet table: sheet '{options.PivotSheetName}' already exists.");
}
break;
}
}
}
if (pivotSheet == null)
{
pivotSheet = spread.Worksheets.Add(options.PivotSheetName);
}
pivotTable = pivotSheet.PivotTables.Add(table.Range, pivotSheet[0, 0], options.PivotTableName);
using (new UsingProcessor(() => pivotTable.BeginUpdate(), () => pivotTable.EndUpdate()))
{
if (options.RowFields != null && options.RowFields.Length > 0)
{
foreach (var fieldName in options.RowFields)
{
pivotTable.RowFields.Add(pivotTable.Fields[fieldName]);
}
}
if (options.ColumnFields != null && options.ColumnFields.Length > 0)
{
foreach (var fieldName in options.ColumnFields)
{
pivotTable.ColumnFields.Add(pivotTable.Fields[fieldName]);
}
}
if (options.PageFields != null && options.PageFields.Length > 0)
{
foreach (var fieldName in options.PageFields)
{
pivotTable.PageFields.Add(pivotTable.Fields[fieldName]);
}
}
if (options.DataFields != null && options.DataFields.Length > 0)
{
foreach (var fieldName in options.DataFields)
{
pivotTable.DataFields.Add(pivotTable.Fields[fieldName]);
}
}
if (options.CalculatedRowFields != null && options.CalculatedRowFields.Length > 0)
{
foreach (var fieldDefinition in options.CalculatedRowFields)
{
var calcField = AddCalculatedField(pivotTable, fieldDefinition);
pivotTable.RowFields.Add(calcField);
}
}
if (options.CalculatedColumnFields != null && options.CalculatedColumnFields.Length > 0)
{
foreach (var fieldDefinition in options.CalculatedColumnFields)
{
var calcField = AddCalculatedField(pivotTable, fieldDefinition);
pivotTable.ColumnFields.Add(calcField);
}
}
if (options.CalculatedPageFields != null && options.CalculatedPageFields.Length > 0)
{
foreach (var fieldDefinition in options.CalculatedPageFields)
{
var calcField = AddCalculatedField(pivotTable, fieldDefinition);
pivotTable.PageFields.Add(calcField);
}
}
if (options.CalculatedDataFields != null && options.CalculatedDataFields.Length > 0)
{
foreach (var fieldDefinition in options.CalculatedDataFields)
{
var calcField = AddCalculatedField(pivotTable, fieldDefinition);
pivotTable.DataFields.Add(calcField);
}
}
if (options.RowFieldNumberFormats != null && options.RowFieldNumberFormats.Length > 0)
{
for (int i = 0; i < Math.Min(options.RowFieldNumberFormats.Length, pivotTable.RowFields.Count); i++)
{
pivotTable.RowFields[i].Field.NumberFormat = options.RowFieldNumberFormats[i];
}
}
if (options.ColumnFieldNumberFormats != null && options.ColumnFieldNumberFormats.Length > 0)
{
for (int i = 0; i < Math.Min(options.ColumnFieldNumberFormats.Length, pivotTable.ColumnFields.Count); i++)
{
pivotTable.ColumnFields[i].Field.NumberFormat = options.ColumnFieldNumberFormats[i];
}
}
if (options.PageFieldNumberFormats != null && options.PageFieldNumberFormats.Length > 0)
{
for (int i = 0; i < Math.Min(options.PageFieldNumberFormats.Length, pivotTable.PageFields.Count); i++)
{
pivotTable.PageFields[i].Field.NumberFormat = options.PageFieldNumberFormats[i];
}
}
if (options.DataFieldNumberFormats != null && options.DataFieldNumberFormats.Length > 0)
{
for (int i = 0; i < Math.Min(options.DataFieldNumberFormats.Length, pivotTable.DataFields.Count); i++)
{
pivotTable.DataFields[i].Field.NumberFormat = options.DataFieldNumberFormats[i];
}
}
if (options.ShowValuesAs != null && options.ShowValuesAs.Length > 0)
{
for (int i = 0; i < Math.Min(options.ShowValuesAs.Length, pivotTable.DataFields.Count); i++)
{
var baseFieldName = options.ShowValuesAsBaseFields != null && i < options.ShowValuesAsBaseFields.Length ? options.ShowValuesAsBaseFields[i] : null;
var baseType = options.ShowValuesAsBaseTypes != null && i < options.ShowValuesAsBaseTypes.Length ? options.ShowValuesAsBaseTypes[i] : PivotShowValueAsItemType.Previous;
var baseField = !string.IsNullOrWhiteSpace(baseFieldName) ? pivotTable.Fields[baseFieldName] : null;
if (!string.IsNullOrWhiteSpace(baseFieldName) && baseField == null)
{
throw new Exception($"Cannot configure pivot table: cannot find base field '{baseFieldName}'.");
}
var dataField = pivotTable.DataFields[i];
dataField.ShowValuesWithCalculation((DevExpress.Spreadsheet.PivotShowValuesAsType)options.ShowValuesAs[i], baseField, (PivotBaseItemType)(int)baseType);
}
}
if (options.SummarizeValuesBy != null && options.SummarizeValuesBy.Length > 0)
{
for (int i = 0; i < Math.Min(options.SummarizeValuesBy.Length, pivotTable.DataFields.Count); i++)
{
pivotTable.DataFields[i].SummarizeValuesBy = (DevExpress.Spreadsheet.PivotDataConsolidationFunction)options.SummarizeValuesBy[i];
}
}
pivotTable.Layout.SetReportLayout((DevExpress.Spreadsheet.PivotReportLayout)(options.Layout ?? PivotReportLayout.Compact));
if (options.CompactNewFields)
{
pivotTable.Layout.CompactNewFields = true;
}
if (options.DataOnRows)
{
pivotTable.Layout.DataOnRows = true;
}
if (options.IndentInCompactForm.HasValue)
{
pivotTable.Layout.IndentInCompactForm = options.IndentInCompactForm.Value;
}
if (options.MergeTitles)
{
pivotTable.Layout.MergeTitles = true;
}
if (options.OutlineNewFields)
{
pivotTable.Layout.OutlineNewFields = true;
}
if (options.PageOrder.HasValue)
{
pivotTable.Layout.PageOrder = (DevExpress.Spreadsheet.PivotPageOrder)options.PageOrder.Value;
}
if (options.PageWrap.HasValue)
{
pivotTable.Layout.PageWrap = options.PageWrap.Value;
}
if (options.HideAllSubtotals)
{
pivotTable.Layout.HideAllSubtotals();
pivotTable.Layout.ShowColumnGrandTotals = false;
pivotTable.Layout.ShowRowGrandTotals = false;
}
if (options.HideColumnGrandTotals)
{
pivotTable.Layout.ShowColumnGrandTotals = false;
}
if (options.HideRowGrandTotals)
{
pivotTable.Layout.ShowRowGrandTotals = false;
}
if (options.SubtotalIncludeHiddenItems)
{
pivotTable.Layout.SubtotalIncludeHiddenItems = true;
}
if (options.HideColumnHeaders)
{
pivotTable.ShowColumnHeaders = false;
}
if (options.HideRowHeaders)
{
pivotTable.ShowRowHeaders = false;
}
if (options.Style != PivotTableStyleId.None)
{
pivotTable.Style = spread.TableStyles[(BuiltInPivotStyleId)options.Style];
}
if (options.AltTextDescription != null)
{
pivotTable.View.AltTextDescription = options.AltTextDescription;
}
if (options.AltTextTitle != null)
{
pivotTable.View.AltTextTitle = options.AltTextTitle;
}
if (options.ColumnHeaderCaption != null)
{
pivotTable.View.ColumnHeaderCaption = options.ColumnHeaderCaption;
}
if (options.DataCaption != null)
{
pivotTable.View.DataCaption = options.DataCaption;
}
if (options.ErrorCaption != null)
{
pivotTable.View.ErrorCaption = options.ErrorCaption;
}
if (options.GrandTotalCaption != null)
{
pivotTable.View.GrandTotalCaption = options.GrandTotalCaption;
}
if (options.MissingCaption != null)
{
pivotTable.View.MissingCaption = options.MissingCaption;
}
if (options.RowHeaderCaption != null)
{
pivotTable.View.RowHeaderCaption = options.RowHeaderCaption;
}
if (options.HideDrillIndicators)
{
pivotTable.View.ShowDrillIndicators = false;
}
if (options.ShowError)
{
pivotTable.View.ShowError = true;
}
if (options.HideFieldHeaders)
{
pivotTable.View.ShowFieldHeaders = false;
}
if (options.HideMissing)
{
pivotTable.View.ShowMissing = false;
}
if (options.HideMultipleLabels)
{
pivotTable.View.ShowMultipleLabels = false;
}
if (options.HideValuesRow)
{
pivotTable.View.ShowValuesRow = false;
}
if (options.AllowMultipleFieldFilters)
{
pivotTable.Behavior.AllowMultipleFieldFilters = true;
}
if (options.DontAutoFitColumns)
{
pivotTable.Behavior.AutoFitColumns = false;
}
}
if (!string.IsNullOrWhiteSpace(options.Formatting))
{
var scanner = new Scanner();
var parser = new Parser(scanner);
var tree = parser.Parse(options.Formatting);
if (tree.Errors.Count > 0)
{
var strErrors = new StringBuilder();
foreach (var error in tree.Errors)
{
if (strErrors.Length > 0)
{
strErrors.AppendLine();
}
strErrors.Append(error.Message);
}
throw new Exception(strErrors.ToString());
}
List <BaseCommand> commands = null;
try
{
commands = tree.Eval() as List <BaseCommand>;
}
catch (Exception)
{
//Do nothing, skip invalid commands
}
if (commands != null)
{
var formatRange = pivotTable.Location.DataRange;
var gridFormatConditions = GridData.LoadGridFormatConditions(commands);
foreach (var gridFormatCondition in gridFormatConditions)
{
SpreadsheetUtils.ApplyGridFormatCondition(pivotSheet, gridFormatCondition, formatRange);
}
}
}
}
CopyRangeToBook(options.CopyToBookDataOnly ? pivotTable.Location.DataRange : pivotTable.Location.Range, options);
AddComments(pivotTable.Location.WholeRange, options.Comment);
DevExpress.Spreadsheet.Table FindDataTable()
{
Worksheet sheet = null;
DevExpress.Spreadsheet.Table table = null;
if (!string.IsNullOrWhiteSpace(options.DataSheetName))
{
sheet = spread.Worksheets[options.DataSheetName];
if (sheet == null)
{
throw new Exception($"Cannot find sheet '{options.DataSheetName}'.");
}
}
if (!string.IsNullOrWhiteSpace(options.DataTableName))
{
if (sheet != null)
{
table = sheet.Tables.Where(t => string.Compare(t.Name, options.DataTableName, true) == 0).FirstOrDefault();
}
else
{
foreach (var worksheet in spread.Worksheets)
{
table = worksheet.Tables.Where(t => string.Compare(t.Name, options.DataTableName, true) == 0).FirstOrDefault();
if (table != null)
{
break;
}
}
}
}
if (table == null)
{
throw new Exception($"Cannot find table '{options.DataTableName}'.");
}
return(table);
}
public SCSpreadsheet NewPivot(object dataSource, PivotOptions options = null)
{
ExecuteSynchronized(options, () => DoNewPivot(dataSource, options));
return(this);
}
