// Adds child parts and generates content of the specified part.
public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
{
List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));
uint tableId = maxTableId + 1;
var tables = new TableParts { Count = 1U };
worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);
var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);
string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
var autoFilter1 = new AutoFilter { Reference = cellReference };
var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
for (int iColumn = 0; iColumn < columns.Length; iColumn++)
{
var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
}
var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);
newTableDefnPart.Table = table1;
var table = new TablePart { Id = relationshipId };
tables.Append((IEnumerable<OpenXmlElement>)table);
//TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
//worksheetPart.Worksheet.Append(tableStyles1);
}
public ISheetBuilder Decorate(ISheetBuilder target)
{
var originalFunc = target.BuildSheet;
target.BuildSheet = (models, sheetDefinition, doc) =>
{
originalFunc(models, sheetDefinition, doc);
var sheetName = sheetDefinition.Name;
var sheetData = doc.GetSheetDataByName(sheetName);
var firstRow = sheetData.Descendants<Row>().FirstOrDefault();
var numberOfColumns = firstRow == null ? 0 : firstRow.Descendants<Cell>().Count();
//No data, no filter will be created
if (numberOfColumns == 0) return;
var filter = new AutoFilter
{
Reference =
string.Format("{0}1:{1}1", GetColumnName(1), GetColumnName(numberOfColumns))
};
var worksheet = doc.GetWorksheetByName(sheetName);
worksheet.AppendChild(filter);
};
return target;
}
private void SheetDataSet(SheetData sheetData1, string reference, Worksheet worksheet1)
{
var sheetDimension1 = new SheetDimension()
{
Reference = reference
};
var sheetViews1 = new SheetViews();
var sheetView1 = new SheetView()
{
TabSelected = true, WorkbookViewId = 0
};
var sheetFormatProperties1 = new SheetFormatProperties {
DefaultRowHeight = 15D, DyDescent = 0.25D
};
var filter = new AutoFilter()
{
Reference = reference
};
var pageMargins1 = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
sheetViews1.AppendChild(sheetView1);
worksheet1.AppendChild(sheetDimension1);
worksheet1.AppendChild(sheetViews1);
worksheet1.AppendChild(sheetFormatProperties1);
worksheet1.AppendChild(sheetData1);
worksheet1.AppendChild(filter);
worksheet1.AppendChild(pageMargins1);
}
internal AutoFilter ToAutoFilter()
{
AutoFilter af = new AutoFilter();
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
af.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
}
else
{
af.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
}
foreach (SLFilterColumn fc in this.FilterColumns)
{
af.Append(fc.ToFilterColumn());
}
if (HasSortState)
{
af.Append(this.SortState.ToSortState());
}
return(af);
}
internal AutoFilter ToAutoFilter()
{
var af = new AutoFilter();
if ((StartRowIndex == EndRowIndex) && (StartColumnIndex == EndColumnIndex))
{
af.Reference = SLTool.ToCellReference(StartRowIndex, StartColumnIndex);
}
else
{
af.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(StartRowIndex, StartColumnIndex),
SLTool.ToCellReference(EndRowIndex, EndColumnIndex));
}
foreach (var fc in FilterColumns)
{
af.Append(fc.ToFilterColumn());
}
if (HasSortState)
{
af.Append(SortState.ToSortState());
}
return(af);
}
public void DynamicLinq_Tests()
{
var lp = new InMemoryLinqProvider(new[]
{
new Product()
{
Name = "123", Id = 1
}
, new Product()
{
Name = "234"
}
});
var val = lp.GetQueryable <Product>().Where("Name = \"123\"").ToArray();
var autoFilter = new AutoFilter <Product>();
autoFilter.Filter.Add("Name", "123");
autoFilter.Filter.Add("Id", 1);
var q = autoFilter.Apply(lp.GetQueryable <Product>());
var res = q.ToArray();
}
internal bool WriteLanguages(List <MappedLanguage> mappedLanguages, string path)
{
if (string.IsNullOrEmpty(path))
{
throw new Exception(PluginResources.Warning_FilePathNull);
}
if (mappedLanguages == null || mappedLanguages.Count == 0)
{
throw new NullReferenceException(PluginResources.Warning_LanguagesEmpty);
}
File.Delete(path);
var excelDocument = new Excel();
var spreadsheet = excelDocument.CreateWorkbook(path);
excelDocument.AddBasicStyles(spreadsheet);
var worksheet1 = excelDocument.AddWorksheet(spreadsheet, "Languages");
var rowIndex = Convert.ToUInt32(1);
// write the header row
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, "Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, "Language");
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, "Region");
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, "Mapped Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, "Custom Display Name");
// write the language rows
foreach (var langauge in mappedLanguages)
{
rowIndex++;
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, langauge.LanguageCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, langauge.LanguageName);
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, langauge.LanguageRegion);
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, langauge.MappedCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, langauge.CustomDisplayName);
}
// set the column widths
excelDocument.SetColumnWidth(worksheet1, 1, 20);
excelDocument.SetColumnWidth(worksheet1, 2, 25);
excelDocument.SetColumnWidth(worksheet1, 3, 35);
excelDocument.SetColumnWidth(worksheet1, 4, 20);
excelDocument.SetColumnWidth(worksheet1, 5, 35);
// add a table filter
var autoFilter = new AutoFilter {
Reference = "A1:E" + mappedLanguages.Count + 1
};
worksheet1.Append(autoFilter);
worksheet1.Save();
spreadsheet.Close();
return(true);
}
internal bool WriteLanguages(List <MappedLanguage> mappedLanguages, string path)
{
if (string.IsNullOrEmpty(path))
{
throw new Exception("File path cannot be null!");
}
if (mappedLanguages == null || mappedLanguages.Count == 0)
{
throw new NullReferenceException("Languages cannot be empty or null!");
}
File.Delete(path);
var excelDocument = new Excel();
var spreadsheet = excelDocument.CreateWorkbook(path);
excelDocument.AddBasicStyles(spreadsheet);
var worksheet1 = excelDocument.AddWorksheet(spreadsheet, "Languages");
var rowIndex = Convert.ToUInt32(1);
// write the header row
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, "Language");
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, "Region");
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, "Trados Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, "MT Code");
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, "MT Code (locale)");
// write the language rows
foreach (var langauge in mappedLanguages)
{
rowIndex++;
excelDocument.SetCellValue(spreadsheet, worksheet1, 1, rowIndex, langauge.Name);
excelDocument.SetCellValue(spreadsheet, worksheet1, 2, rowIndex, langauge.Region);
excelDocument.SetCellValue(spreadsheet, worksheet1, 3, rowIndex, langauge.TradosCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 4, rowIndex, langauge.MTCode);
excelDocument.SetCellValue(spreadsheet, worksheet1, 5, rowIndex, langauge.MTCodeLocale);
}
// set the column widths
excelDocument.SetColumnWidth(worksheet1, 1, 30);
excelDocument.SetColumnWidth(worksheet1, 2, 30);
excelDocument.SetColumnWidth(worksheet1, 3, 20);
excelDocument.SetColumnWidth(worksheet1, 4, 20);
excelDocument.SetColumnWidth(worksheet1, 5, 20);
// add a table filter
var autoFilter = new AutoFilter {
Reference = "A1:E" + mappedLanguages.Count + 1
};
worksheet1.Append(autoFilter);
worksheet1.Save();
spreadsheet.Close();
return(true);
}
public void CellsPivotTablesPutWorksheetPivotTableFilterTest()
{
string name = "TestCase.xlsx";
string sheetName = "SHEET4";
int? pivotTableIndex = 0;
PivotFilter pivotFilter = new PivotFilter();
pivotFilter.FieldIndex = 1;
pivotFilter.FilterType = "Count";
AutoFilter autoFilter = new AutoFilter();
autoFilter.FilterColumns = new List <FilterColumn>();
FilterColumn filterColumn = new FilterColumn();
filterColumn.FilterType = "Top10";
filterColumn.FieldIndex = 0;
filterColumn.Top10Filter = new Top10Filter();
filterColumn.Top10Filter.Items = 1;
filterColumn.Top10Filter.IsTop = true;
filterColumn.Top10Filter.IsPercent = false;
autoFilter.FilterColumns.Add(filterColumn);
pivotFilter.AutoFilter = autoFilter;
bool? needReCalculate = true;
string folder = "TEMPFOLDER";
new Config().UpdateDataFile(folder, name);
var response = instance.CellsPivotTablesPutWorksheetPivotTableFilter(name, sheetName, pivotTableIndex, pivotFilter, needReCalculate, folder);
Console.WriteLine(response);
}
public void CellsPivotTablesPutWorksheetPivotTableFilterTest()
{
// TODO uncomment below to test the method and replace null with proper value
string name = PivTestFile;
string sheetName = SHEET4;
int? pivotTableIndex = 0;
PivotFilter pivotFilter = new PivotFilter();
pivotFilter.FieldIndex = 1;
pivotFilter.FilterType = "Count";
AutoFilter autoFilter = new AutoFilter();
autoFilter.FilterColumns = new List <FilterColumn>();
FilterColumn filterColumn = new FilterColumn();
filterColumn.FilterType = "Top10";
filterColumn.FieldIndex = 0;
filterColumn.Top10Filter = new Top10Filter();
filterColumn.Top10Filter.Items = 1;
filterColumn.Top10Filter.IsTop = true;
filterColumn.Top10Filter.IsPercent = false;
autoFilter.FilterColumns.Add(filterColumn);
pivotFilter.AutoFilter = autoFilter;
bool? needReCalculate = true;
string folder = TEMPFOLDER;
UpdateDataFile(instance, folder, name);
var response = instance.CellsPivotTablesPutWorksheetPivotTableFilter(name, sheetName, pivotTableIndex, pivotFilter, needReCalculate, folder);
Assert.IsInstanceOf <CellsCloudResponse>(response, "response is CellsCloudResponse");
Assert.AreEqual(response.Code, 200);
}
internal SLTable Clone()
{
var t = new SLTable();
t.IsNewTable = IsNewTable;
t.RelationshipID = RelationshipID;
t.HasAutoFilter = HasAutoFilter;
t.AutoFilter = AutoFilter.Clone();
t.HasSortState = HasSortState;
t.SortState = SortState.Clone();
t.TableColumns = new List <SLTableColumn>();
for (var i = 0; i < TableColumns.Count; ++i)
{
t.TableColumns.Add(TableColumns[i].Clone());
}
t.TableNames = new HashSet <string>();
foreach (var s in TableNames)
{
t.TableNames.Add(s);
}
t.HasTableStyleInfo = HasTableStyleInfo;
t.TableStyleInfo = TableStyleInfo.Clone();
t.Id = Id;
t.Name = Name;
t.sDisplayName = sDisplayName;
t.Comment = Comment;
t.StartRowIndex = StartRowIndex;
t.StartColumnIndex = StartColumnIndex;
t.EndRowIndex = EndRowIndex;
t.EndColumnIndex = EndColumnIndex;
t.HasTableType = HasTableType;
t.vTableType = vTableType;
t.HeaderRowCount = HeaderRowCount;
t.InsertRow = InsertRow;
t.InsertRowShift = InsertRowShift;
t.TotalsRowCount = TotalsRowCount;
t.TotalsRowShown = TotalsRowShown;
t.Published = Published;
t.HeaderRowFormatId = HeaderRowFormatId;
t.DataFormatId = DataFormatId;
t.TotalsRowFormatId = TotalsRowFormatId;
t.HeaderRowBorderFormatId = HeaderRowBorderFormatId;
t.BorderFormatId = BorderFormatId;
t.TotalsRowBorderFormatId = TotalsRowBorderFormatId;
t.HeaderRowCellStyle = HeaderRowCellStyle;
t.DataCellStyle = DataCellStyle;
t.TotalsRowCellStyle = TotalsRowCellStyle;
t.ConnectionId = ConnectionId;
return(t);
}
public void Test2()
{
var filter = new AutoFilter <User, UserFilter>(new UserFilter {
Name = "Alpha", Age = 9
});
var list = this.DbContext.Users.Filter(filter).ToList();
list.Should().HaveCount(0);
}
public new void Dispose()
{
if (AutoFilter != null)
{
AutoFilter.Dispose();
}
base.Dispose();
}
public static void ConfigureAutoFilter(WorksheetPart worksheet, string reference)
{
var autoFilter = new AutoFilter()
{
Reference = reference
};
worksheet.Worksheet.Append(autoFilter);
}
public void Test1()
{
var filter = new AutoFilter <User, UserFilter>(new UserFilter {
Name = "lph"
});
var list = this.DbContext.Users.Filter(filter).ToList();
list.Should().HaveCount(1);
list.Should().OnlyContain(p => p.Name == "Alpha");
}
public void Test4()
{
var filter = new AutoFilter <User, UserFilter>(new UserFilter {
Email = "*****@*****.**"
});
var list = this.DbContext.Users.Filter(filter).ToList();
list.Should().HaveCount(1);
list.Should().OnlyContain(p => p.Email == "*****@*****.**");
}
internal void FromAutoFilter(AutoFilter af)
{
SetAllNull();
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (var oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
SortState = new SLSortState();
SortState.FromSortState((SortState)oxr.LoadCurrentElement());
HasSortState = true;
}
}
}
}
}
internal void FromPivotFilter(PivotFilter pf)
{
SetAllNull();
if (pf.Field != null)
{
Field = pf.Field.Value;
}
if (pf.MemberPropertyFieldId != null)
{
MemberPropertyFieldId = pf.MemberPropertyFieldId.Value;
}
if (pf.Type != null)
{
Type = pf.Type.Value;
}
if (pf.EvaluationOrder != null)
{
EvaluationOrder = pf.EvaluationOrder.Value;
}
if (pf.Id != null)
{
Id = pf.Id.Value;
}
if (pf.MeasureHierarchy != null)
{
MeasureHierarchy = pf.MeasureHierarchy.Value;
}
if (pf.MeasureField != null)
{
MeasureField = pf.MeasureField.Value;
}
if (pf.Name != null)
{
Name = pf.Name.Value;
}
if (pf.Description != null)
{
Description = pf.Description.Value;
}
if (pf.StringValue1 != null)
{
StringValue1 = pf.StringValue1.Value;
}
if (pf.StringValue2 != null)
{
StringValue2 = pf.StringValue2.Value;
}
if (pf.AutoFilter != null)
{
AutoFilter.FromAutoFilter(pf.AutoFilter);
}
}
public void CreateReport(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create($@"C:\Users\Volobuev\Desktop\DbToXML\DbToXML\App_Data\{fileName}.xlsx", SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var autoFilter = new AutoFilter {
Reference = "A1:C1"
};
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
///////////////////////////////
var sheetViews1 = new SheetViews();
var sheetView1 = new SheetView {
WorkbookViewId = 0U
};
var cellNumber = GetCellNumber(3, 20);
var selection1 = new Selection {
ActiveCell = cellNumber, SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = cellNumber
}
};
sheetView1.Append(selection1);
sheetViews1.Append(sheetView1);
worksheetPart.Worksheet.Append(sheetViews1);
///////////////////////////////
var stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); // Adding style
stylePart.Stylesheet = GenerateStylesheet();
var columns = new Columns(new Column {
Min = 1, Max = 3, Width = 25, CustomWidth = true
}); // Setting up columns
worksheetPart.Worksheet.AppendChild(columns);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet {
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Cars"
};
sheets.Append(sheet);
Data(worksheetPart); // add cars
workbookPart.Workbook.Save();
worksheetPart.Worksheet.Append(autoFilter);
worksheetPart.Worksheet.Save();
}
}
public void Filter()
{
var filter = new AutoFilter <Product>(new ProductFilter()
{
Name = "1"
});
var products = filter.Filter(DbContext.Products).ToList();
Assert.Single(products);
Assert.Equal("1", products.First().Name);
}
private static void WriteTable(WorksheetPart worksheetPart, uint tableID, object[,] values, uint nRows, uint nColumns)
{
string range = ToRange(1, nRows, 1, nColumns);
var autoFilter = new AutoFilter
{
Reference = range
};
var tableColumns = new TableColumns()
{
Count = nColumns
};
for (int c = 0; c < nColumns; c++)
{
var column = new TableColumn()
{
Id = (UInt32Value)(c + 1U),
Name = values[0, c].ToString()
};
tableColumns.Append(column);
}
var tableStyleInfo = new TableStyleInfo()
{
Name = "TableStyleMedium2",
ShowFirstColumn = true,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = true
};
var table = new Table()
{
Id = tableID,
Name = $"Table{tableID}",
DisplayName = $"Table{tableID}",
Reference = range,
TotalsRowShown = false
};
table.Append(autoFilter);
table.Append(tableColumns);
table.Append(tableStyleInfo);
//var tableParts = worksheetPart.Worksheet.AppendChild<TableParts>(new TableParts());
//tableParts.AppendChild<TablePart>(new TablePart());
var tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>($"rId{tableID}");
tableDefinitionPart.Table = table;
}
internal void FromAutoFilter(AutoFilter af)
{
this.SetAllNull();
int iStartRowIndex = 1;
int iStartColumnIndex = 1;
int iEndRowIndex = 1;
int iEndColumnIndex = 1;
string sRef = af.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iEndRowIndex;
this.EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
this.StartRowIndex = iStartRowIndex;
this.StartColumnIndex = iStartColumnIndex;
this.EndRowIndex = iStartRowIndex;
this.EndColumnIndex = iStartColumnIndex;
}
}
if (af.HasChildren)
{
SLFilterColumn fc;
using (OpenXmlReader oxr = OpenXmlReader.Create(af))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(FilterColumn))
{
fc = new SLFilterColumn();
fc.FromFilterColumn((FilterColumn)oxr.LoadCurrentElement());
this.FilterColumns.Add(fc);
}
else if (oxr.ElementType == typeof(SortState))
{
this.SortState = new SLSortState();
this.SortState.FromSortState((SortState)oxr.LoadCurrentElement());
this.HasSortState = true;
}
}
}
}
}
public void TestMultiFilter()
{
var filter = new AutoFilter <User, UserFilter>(new UserFilter {
MultiAge = new[] { 10, 6 }
});
var list = this.DbContext.Users.Filter(filter).ToList();
list.Should().HaveCount(2);
var data = this.LoggedData;
data.Should().Contain("WHERE [u].[Age] IN (@__Value_0, @__Value_1)");
}
// Given a WorkbookPart, inserts a new worksheet.
private WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName, int linesCount, WorkBookDefinition conf)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Columns columns = new Columns();
columns.Append(conf.ColumnsNames.Select((c, idx) => new Column()
{
Min = (uint)(idx + 1), Max = (uint)(idx + 1),
Width = 10, CustomWidth = true
}));
newWorksheetPart.Worksheet = new Worksheet();
newWorksheetPart.Worksheet.Append(columns);
newWorksheetPart.Worksheet.AppendChild(new SheetData());
var endColLetter = (char)('A' + conf.ColumnsNames.Length);
AutoFilter autoFilter = new AutoFilter()
{
Reference = "A1:" + (endColLetter.ToString()) + linesCount
};
newWorksheetPart.Worksheet.AppendChild(autoFilter);
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
return(newWorksheetPart);
}
// Generates content of tableDefinitionPart1.
private static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1, List <string> titres, int nbLi, int nbCol)
{
string refe = "A1:" + Ultimate.headerColumns[nbCol - 1] + nbLi;
Table table1 = new Table()
{
Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = refe, TotalsRowShown = false
};
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = refe
};
//TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)3U };
//TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Make" };
//TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Miles" };
//TableColumn tableColumn3 = new TableColumn() { Id = (UInt32Value)3U, Name = "Cost", DataFormatId = (UInt32Value)0U };
//tableColumns1.Append(tableColumn1);
//tableColumns1.Append(tableColumn2);
//tableColumns1.Append(tableColumn3);
TableColumns tableColumns1 = new TableColumns()
{
Count = (uint)titres.Count
};
int i = 1;
foreach (string t in titres)
{
TableColumn tableColumn1 = new TableColumn()
{
Id = (uint)i, Name = t
};
tableColumns1.Append(tableColumn1);
i++;
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table1.Append(autoFilter1);
//table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
public AutoSpec(AutoFilter filter)
{
Query.OrderBy(x => x.Marca).ThenByDescending(x => x.Id);
if (filter.IsPagingEnabled)
{
Query.Skip(PaginationHelper.CalculateSkip(filter))
.Take(PaginationHelper.CalculateTake(filter));
}
if (!string.IsNullOrEmpty(filter.Marca))
{
Query.Search(x => x.Marca, "%" + filter.Marca + "%");
}
}
public void TestFromAndToFilter()
{
var searchFrom = DateTimeOffset.UtcNow.AddHours(-5);
var searchTo = DateTimeOffset.UtcNow;
var filter = new AutoFilter <User, UserFilter>(new UserFilter {
CreatedAt = searchFrom, To = searchTo
});
var list = this.DbContext.Users.Filter(filter).ToList();
list.Should().HaveCount(2);
var data = this.LoggedData;
data.Should().Contain("WHERE ([u].[CreatedAt] >= @__Value_0) AND ([u].[CreatedAt] <= @__Value_1)");
}
/// <summary>
/// Generates Table Definition.
/// </summary>
/// <param name="tableDefinitionPart">Table Definition To add Table to</param>
private void GenerateTableDefinition(TableDefinitionPart tableDefinitionPart)
{
var columnEnd = ConvertToAlphabetBasedNumber(_tableColumns.Count); //This function is zero based index
var rowEnd = _rows.Count + 1; //Need to include the header
var table = new DocumentFormat.OpenXml.Spreadsheet.Table {
Id = 1U,
Name = "Table1",
DisplayName = "Table1",
Reference = $"A1:{columnEnd}{rowEnd}",
TotalsRowShown = false,
HeaderRowFormatId = 1U,
DataFormatId = 0U,
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "xr xr3"
}
};
table.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
table.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
table.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{ED5DF9B8-D8E7-4542-9E4F-8E05D7A30983}"));
AutoFilter autoFilter = new AutoFilter {
Reference = $"A1:{columnEnd}{rowEnd}"
};
TableColumns tableColumns = new TableColumns {
Count = (uint)_tableColumns.Count
};
for (var i = 0; i < tableColumns.Count; i++)
{
tableColumns.Append(new TableColumn
{
Id = (uint)(i + 1), Name = _tableColumns[i], DataFormatId = 2U
});
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo {
Name = "TableStyleMedium15", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table.Append(autoFilter);
table.Append(tableColumns);
table.Append(tableStyleInfo1);
tableDefinitionPart.Table = table;
}
public AutoFilterGroup(IStreamReader reader)
{
if (BiffRecord.GetNextRecordType(reader) == RecordType.AutoFilter)
{
this.AutoFilter = (AutoFilter)BiffRecord.ReadRecord(reader);
}
else
{
this.AutoFilter12 = (AutoFilter12)BiffRecord.ReadRecord(reader);
this.ContinueFrt12s = new List <ContinueFrt12>();
while (BiffRecord.GetNextRecordType(reader) == RecordType.ContinueFrt12)
{
this.ContinueFrt12s.Add((ContinueFrt12)BiffRecord.ReadRecord(reader));
}
}
}
private void generateTaskTableContent(TableDefinitionPart tableDefinitionPart)
{
Table table = new Table()
{
Id = (UInt32Value)4U, Name = "WorkItemsTable", DisplayName = "WorkItemsTable", Reference = "A1:J" + (reportData.tasks.value.Count + 1).ToString(), TotalsRowShown = false
};
table.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
//table1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
//table1.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
//table1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{5EFA48B8-3293-4748-AA3B-548E4EF8F199}"));
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = "A1:J" + (reportData.tasks.value.Count + 1).ToString()
};
//autoFilter1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{EDA87F4E-B12B-41C9-8B20-4850D8E9E195}"));
TableColumns tableColumns = new TableColumns()
{
Count = (uint)headers.Count
};
for (int i = 1; i <= headers.Count; i++)
{
TableColumn tableColumn = new TableColumn()
{
Id = (uint)i, Name = headers[i - 1]
};
//tableColumn.SetAttribute(new OpenXmlAttribute("xr3", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3", "{D2D20073-D29B-4500-9950-65429DC6D749}"));
Console.WriteLine("Header value: {0} ", headers[i - 1]);
Console.WriteLine("Header cell ref: {0} ", rowArry[i - 1].ToString() + "1");
tableColumns.Append(tableColumn);
}
TableStyleInfo tableStyleInfo = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table.Append(autoFilter1);
table.Append(tableColumns);
table.Append(tableStyleInfo);
tableDefinitionPart.Table = table;
}
private void DefineTable(WorksheetPart worksheetPart, LineItem[] lineItems)
{
TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>("rId" + (worksheetPart.TableDefinitionParts.Count() + 1));
int tableNo = worksheetPart.TableDefinitionParts.Count();
string reference = $"A1:{((char)(64 + _fields.Length))}{lineItems.Length}";
Table table = new Table()
{
Id = (UInt32)tableNo, Name = "Table" + tableNo, DisplayName = "Table" + tableNo, Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns = new TableColumns()
{
Count = (UInt32)_fields.Length
};
for (int i = 0; i < _fields.Length; i++)
{
tableColumns.Append(new TableColumn()
{
Id = (UInt32)(i + 1), Name = _fields[i]
});
}
table.Append(autoFilter);
table.Append(tableColumns);
tableDefinitionPart.Table = table;
TableParts tableParts = new TableParts()
{
Count = (UInt32)1
};
TablePart tablePart = new TablePart()
{
Id = "rId" + tableNo
};
tableParts.Append(tablePart);
worksheetPart.Worksheet.Append(tableParts);
}
private static void GenerateTablePartContent(TableDefinitionPart part, ReportResult reportResult, List <DataRow> rows, int columnCount)
{
int rowCount = 2;
if (rows.Count() > 0)
{
rowCount = rows.Count() + 1;
}
// int columnCount = reportResult.Metadata.ReportColumns.Values.Count()-1;
string reference = "A1:" + GetExcelColumnName(columnCount) + rowCount.ToString();
Table table1 = new Table()
{
Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns1 = new TableColumns()
{
Count = Convert.ToUInt32(columnCount + 1)
};
int i = 1;
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
tableColumns1.Append(new TableColumn()
{
Id = Convert.ToUInt32(i++), Name = column.Title.Trim()
});
}
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
table1.HeaderRowCount = 1;
part.Table = table1;
}
public IEnumerable <Auto> Get([FromQuery] AutoFilter filter)
{
autos = GetDataSet();
if (string.IsNullOrEmpty(filter.SearchText) == false)
{
autos = autos.Where(x => x.Name.IndexOf(filter.SearchText, StringComparison.CurrentCultureIgnoreCase) >= 0 ||
x.VehicleType.Equals(filter.SearchText, StringComparison.CurrentCultureIgnoreCase) == true ||
x.WMI.Equals(filter.SearchText, StringComparison.CurrentCultureIgnoreCase) == true);
}
if (filter.Country != "View All")
{
autos = autos.Where(x => x.Country == filter.Country);
}
return(autos);
}
/// <summary>
/// Generates content of tableDefinitionPart1.
/// </summary>
/// <param name="tableDefinitionPart">Table definition.</param>
private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart)
{
Table table1 = new Table() { Id = (UInt32Value)5U, Name = Constants.MetadataRangeName, DisplayName = Constants.MetadataRangeName, Reference = "A1:B44", TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:B44" };
TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)2U };
TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Name" };
TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Value" };
tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = Constants.MetadataTableStyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart.Table = table1;
}
internal AutoFilter ToAutoFilter()
{
AutoFilter af = new AutoFilter();
if (this.StartRowIndex == this.EndRowIndex && this.StartColumnIndex == this.EndColumnIndex)
{
af.Reference = SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex);
}
else
{
af.Reference = string.Format("{0}:{1}",
SLTool.ToCellReference(this.StartRowIndex, this.StartColumnIndex),
SLTool.ToCellReference(this.EndRowIndex, this.EndColumnIndex));
}
foreach (SLFilterColumn fc in this.FilterColumns)
{
af.Append(fc.ToFilterColumn());
}
if (HasSortState) af.Append(this.SortState.ToSortState());
return af;
}
public void DynamicLinq_Tests()
{
var lp = new InMemoryLinqProvider(new[]
{
new Product() {Name = "123", Id = 1}
, new Product() {Name = "234"}
});
var val = lp.GetQueryable<Product>().Where("Name = \"123\"").ToArray();
var autoFilter = new AutoFilter<Product>();
autoFilter.Filter.Add("Name", "123");
autoFilter.Filter.Add("Id", 1);
var q = autoFilter.Apply(lp.GetQueryable<Product>());
var res = q.ToArray();
}
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);
}
}
private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable,
SaveContext context)
{
context.TableId++;
var reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress;
var tableName = GetTableName(xlTable.Name, context);
var table = new Table
{
Id = context.TableId,
Name = tableName,
DisplayName = tableName,
Reference = reference
};
if (!xlTable.ShowHeaderRow)
table.HeaderRowCount = 0;
if (xlTable.ShowTotalsRow)
table.TotalsRowCount = 1;
else
table.TotalsRowShown = false;
var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()};
UInt32 columnId = 0;
foreach (var fieldName in xlTable.FieldNames.Keys)
{
columnId++;
var xlField = xlTable.Field(fieldName);
var tableColumn1 = new TableColumn
{
Id = columnId,
Name = fieldName.Replace("_x000a_", "_x005f_x000a_").Replace(Environment.NewLine, "_x000a_")
};
if (xlTable.ShowTotalsRow)
{
if (xlField.TotalsRowFunction != XLTotalsRowFunction.None)
{
tableColumn1.TotalsRowFunction = xlField.TotalsRowFunction.ToOpenXml();
if (xlField.TotalsRowFunction == XLTotalsRowFunction.Custom)
tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1);
}
if (!XLHelper.IsNullOrWhiteSpace(xlField.TotalsRowLabel))
tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel;
}
tableColumns1.AppendChild(tableColumn1);
}
var tableStyleInfo1 = new TableStyleInfo
{
ShowFirstColumn = xlTable.EmphasizeFirstColumn,
ShowLastColumn = xlTable.EmphasizeLastColumn,
ShowRowStripes = xlTable.ShowRowStripes,
ShowColumnStripes = xlTable.ShowColumnStripes
};
if (xlTable.Theme != XLTableTheme.None)
tableStyleInfo1.Name = Enum.GetName(typeof(XLTableTheme), xlTable.Theme);
if (xlTable.ShowAutoFilter)
{
var autoFilter1 = new AutoFilter();
if (xlTable.ShowTotalsRow)
{
xlTable.AutoFilter.Range = xlTable.Worksheet.Range(
xlTable.RangeAddress.FirstAddress.RowNumber, xlTable.RangeAddress.FirstAddress.ColumnNumber,
xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber);
}
else
xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress);
PopulateAutoFilter(xlTable.AutoFilter, autoFilter1);
table.AppendChild(autoFilter1);
}
table.AppendChild(tableColumns1);
table.AppendChild(tableStyleInfo1);
tableDefinitionPart.Table = table;
}
/// <summary>
/// Generates content of tableDefinitionPart1.
/// </summary>
/// <param name="tableDefinitionPart">Table definition part.</param>
/// <param name="hasFileLevelMetadata">Has file level metadata.</param>
private static void GenerateParameterTablePartContent(TableDefinitionPart tableDefinitionPart, bool hasFileLevelMetadata)
{
string reference = hasFileLevelMetadata ? "D1:I44" : "A1:F44";
Table table1 = new Table() { Id = (UInt32Value)6U, Name = Constants.ParaMetadataRangeName, DisplayName = Constants.ParaMetadataRangeName, Reference = reference, TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter() { Reference = reference };
TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)6U };
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)1U, Name = Statics.TableName });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)2U, Name = Statics.TableDescription });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)3U, Name = Statics.FieldName });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)4U, Name = Statics.FieldDescription });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)5U, Name = Statics.DataType });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)6U, Name = Statics.Units });
TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = Constants.MetadataTableStyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart.Table = table1;
}
