internal SortState ToSortState()
{
var ss = new SortState();
if ((ColumnSort != null) && ColumnSort.Value)
{
ss.ColumnSort = ColumnSort.Value;
}
if ((CaseSensitive != null) && CaseSensitive.Value)
{
ss.CaseSensitive = CaseSensitive.Value;
}
if (HasSortMethod)
{
ss.SortMethod = SortMethod;
}
if ((StartRowIndex == EndRowIndex) && (StartColumnIndex == EndColumnIndex))
{
ss.Reference = SLTool.ToCellReference(StartRowIndex, StartColumnIndex);
}
else
{
ss.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(StartRowIndex, StartColumnIndex),
SLTool.ToCellReference(EndRowIndex, EndColumnIndex));
}
if (SortConditions.Count > 0)
{
for (var i = 0; i < SortConditions.Count; ++i)
{
ss.Append(SortConditions[i].ToSortCondition());
}
}
return(ss);
}
internal SortState ToSortState()
{
SortState ss = new SortState();
if (this.ColumnSort != null && this.ColumnSort.Value)
{
ss.ColumnSort = this.ColumnSort.Value;
}
if (this.CaseSensitive != null && this.CaseSensitive.Value)
{
ss.CaseSensitive = this.CaseSensitive.Value;
}
if (HasSortMethod)
{
ss.SortMethod = this.SortMethod;
}
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
ss.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
}
else
{
ss.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
}
if (this.SortConditions.Count > 0)
{
for (int i = 0; i < this.SortConditions.Count; ++i)
{
ss.Append(this.SortConditions[i].ToSortCondition());
}
}
return(ss);
}
internal SortState ToSortState()
{
SortState ss = new SortState();
if (this.ColumnSort != null && this.ColumnSort.Value) ss.ColumnSort = this.ColumnSort.Value;
if (this.CaseSensitive != null && this.CaseSensitive.Value) ss.CaseSensitive = this.CaseSensitive.Value;
if (HasSortMethod) ss.SortMethod = this.SortMethod;
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
ss.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
}
else
{
ss.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
}
if (this.SortConditions.Count > 0)
{
for (int i = 0; i < this.SortConditions.Count; ++i)
{
ss.Append(this.SortConditions[i].ToSortCondition());
}
}
return ss;
}
private static void PopulateAutoFilter(XLAutoFilter xlAutoFilter, AutoFilter autoFilter)
{
var filterRange = xlAutoFilter.Range;
autoFilter.Reference = filterRange.RangeAddress.ToString();
foreach (var kp in xlAutoFilter.Filters)
{
var filterColumn = new FilterColumn {ColumnId = (UInt32)kp.Key - 1};
var xlFilterColumn = xlAutoFilter.Column(kp.Key);
var filterType = xlFilterColumn.FilterType;
if (filterType == XLFilterType.Custom)
{
var customFilters = new CustomFilters();
foreach (var filter in kp.Value)
{
var customFilter = new CustomFilter {Val = filter.Value.ToString()};
if (filter.Operator != XLFilterOperator.Equal)
customFilter.Operator = filter.Operator.ToOpenXml();
if (filter.Connector == XLConnector.And)
customFilters.And = true;
customFilters.Append(customFilter);
}
filterColumn.Append(customFilters);
}
else if (filterType == XLFilterType.TopBottom)
{
var top101 = new Top10 {Val = (double)xlFilterColumn.TopBottomValue};
if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent)
top101.Percent = true;
if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom)
top101.Top = false;
filterColumn.Append(top101);
}
else if (filterType == XLFilterType.Dynamic)
{
var dynamicFilter = new DynamicFilter
{Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue};
filterColumn.Append(dynamicFilter);
}
else
{
var filters = new Filters();
foreach (var filter in kp.Value)
{
filters.Append(new Filter {Val = filter.Value.ToString()});
}
filterColumn.Append(filters);
}
autoFilter.Append(filterColumn);
}
if (xlAutoFilter.Sorted)
{
var sortState = new SortState
{
Reference =
filterRange.Range(filterRange.FirstCell().CellBelow(), filterRange.LastCell()).RangeAddress.
ToString()
};
var sortCondition = new SortCondition
{
Reference =
filterRange.Range(1, xlAutoFilter.SortColumn, filterRange.RowCount(),
xlAutoFilter.SortColumn).RangeAddress.ToString()
};
if (xlAutoFilter.SortOrder == XLSortOrder.Descending)
sortCondition.Descending = true;
sortState.Append(sortCondition);
autoFilter.Append(sortState);
}
}
// Generates content of tableDefinitionPart6.
private void GenerateTableDefinitionPart6Content(TableDefinitionPart tableDefinitionPart6)
{
Table table6 = new Table(){ Id = (UInt32Value)1U, Name = "DataTable1", DisplayName = "DataTable1", Reference = "A1:F5", TotalsRowShown = false };
AutoFilter autoFilter6 = new AutoFilter(){ Reference = "A1:F5" };
SortState sortState2 = new SortState(){ Reference = "A2:F2001" };
SortCondition sortCondition2 = new SortCondition(){ Reference = "A2:A2001" };
sortState2.Append(sortCondition2);
TableColumns tableColumns6 = new TableColumns(){ Count = (UInt32Value)6U };
TableColumn tableColumn13 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Date", DataFormatId = (UInt32Value)13U };
TableColumn tableColumn14 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Product" };
TableColumn tableColumn15 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Quantity" };
TableColumn tableColumn16 = new TableColumn(){ Id = (UInt32Value)4U, Name = "Price" };
TableColumn tableColumn17 = new TableColumn(){ Id = (UInt32Value)5U, Name = "DeliveryDate", DataFormatId = (UInt32Value)12U };
TableColumn tableColumn18 = new TableColumn(){ Id = (UInt32Value)6U, Name = "Employee" };
tableColumns6.Append(tableColumn13);
tableColumns6.Append(tableColumn14);
tableColumns6.Append(tableColumn15);
tableColumns6.Append(tableColumn16);
tableColumns6.Append(tableColumn17);
tableColumns6.Append(tableColumn18);
TableStyleInfo tableStyleInfo6 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table6.Append(autoFilter6);
table6.Append(sortState2);
table6.Append(tableColumns6);
table6.Append(tableStyleInfo6);
tableDefinitionPart6.Table = table6;
}
// Generates content of tableDefinitionPart3.
private void GenerateTableDefinitionPart3Content(TableDefinitionPart tableDefinitionPart3)
{
Table table3 = new Table(){ Id = (UInt32Value)4U, Name = "DataTable2", DisplayName = "DataTable2", Reference = "A1:F6", TotalsRowShown = false };
AutoFilter autoFilter3 = new AutoFilter(){ Reference = "A1:F6" };
SortState sortState1 = new SortState(){ Reference = "A2:F2001" };
SortCondition sortCondition1 = new SortCondition(){ Reference = "A2" };
sortState1.Append(sortCondition1);
TableColumns tableColumns3 = new TableColumns(){ Count = (UInt32Value)6U };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Date", DataFormatId = (UInt32Value)8U };
TableColumn tableColumn5 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Product", DataFormatId = (UInt32Value)7U };
TableColumn tableColumn6 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Quantity", DataFormatId = (UInt32Value)6U };
TableColumn tableColumn7 = new TableColumn(){ Id = (UInt32Value)4U, Name = "Price", DataFormatId = (UInt32Value)5U };
TableColumn tableColumn8 = new TableColumn(){ Id = (UInt32Value)5U, Name = "DeliveryDate", DataFormatId = (UInt32Value)4U };
TableColumn tableColumn9 = new TableColumn(){ Id = (UInt32Value)6U, Name = "Employee", DataFormatId = (UInt32Value)3U };
tableColumns3.Append(tableColumn4);
tableColumns3.Append(tableColumn5);
tableColumns3.Append(tableColumn6);
tableColumns3.Append(tableColumn7);
tableColumns3.Append(tableColumn8);
tableColumns3.Append(tableColumn9);
TableStyleInfo tableStyleInfo3 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table3.Append(autoFilter3);
table3.Append(sortState1);
table3.Append(tableColumns3);
table3.Append(tableStyleInfo3);
tableDefinitionPart3.Table = table3;
}
