// 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; }