public void SparseColumns() { var outPath = GetNewOutFilePath("-sparse"); var workbook = new ExcelPackage(new FileInfo(outPath)); const string sheetName = "One"; var exportConfig = new PocoThreeExportConfigurator(sheetName).Config; const int firstColumnIndex = 2; // this is index of the header row const int firstRowIndex = 3; exportConfig.LeftSheetColumnIndex = firstColumnIndex; exportConfig.TopSheetRowIndex = firstRowIndex; // no freezing panes exportConfig.FreezeColumnIndex = null; // move third column to the right Assert.IsNotEmpty(exportConfig.Columns[2].Caption, "Sheet column#2 has no caption"); var movedColumnConfig = exportConfig.GetAutoColumnConfig(exportConfig.Columns[2].Caption); Assert.IsNotNull(movedColumnConfig, "Failed to find column export config by caption"); movedColumnConfig.Index = exportConfig.Columns.Count + 2; // allow it to grow more at the end of the table movedColumnConfig.MaximumWidth = 300; var dataSetExportConfig = new DataSetExportAutoConfig(); dataSetExportConfig.AddSheet(exportConfig); var pocoList = Enumerable.Range(0, 100) .Select(i => new PocoThree(6)) .ToList(); var dataSet = new DataSetAdapter().Add(pocoList, sheetName); var exporter = new DataSetToWorkbookExporter(dataSetExportConfig) { DataSet = dataSet }; exporter.Export(workbook); workbook.Save(); TestContext.WriteLine($"Saved {outPath}."); workbook.Dispose(); workbook = new ExcelPackage(new FileInfo(outPath)); var columnReadingMap = exportConfig.Columns .Select(c => new KeyValuePair <string, int>(c.Caption, firstColumnIndex + c.Index)) .ToList(); const int startDataRowIndex = firstRowIndex + 1; var reader = new ExcelTableReader(workbook.Workbook.Worksheets["One"], startDataRowIndex, null, columnReadingMap); var readPocos = new PocoThreeReader().Read(reader); CheckEquality(pocoList, readPocos); workbook.Dispose(); if (_deleteExportedFiles) { File.Delete(outPath); } }
public void Polymorphic() { var outPath = GetNewOutFilePath("-polymorphic"); var workbook = new ExcelPackage(new FileInfo(outPath)); var dataSetExportConfig = new DataSetExportAutoConfig(); var configurator = new PocoExportConfigurator <PocoOne>("OneSheet", "One"); Expression <Func <PocoBase, int> > refId = o => o.Id; Expression <Func <PocoBase, DateTime> > refDateTime = o => o.DateTime; Expression <Func <PocoOne, IList <double?> > > refCollection = o => o.Values; Expression <Func <PocoOne, string> > refJoinedCollection = o => o.Values != null?string.Join(",", o.Values.Select(e => e.ToString())) : null; Expression <Func <PocoThree, int> > refPocoThreeInt = o => o.IntegerFromPocoThree; configurator .AddInheritedColumn(refId) .AddInheritedColumn(refDateTime) .AddColumn(refJoinedCollection, "Joined Values") .AddCollectionColumns(refCollection, 5, "value#{0}") .AddColumnPolymorphic(refPocoThreeInt); dataSetExportConfig.AddSheet(configurator.Config); dataSetExportConfig.AddSheet(new PocoThreeExportConfigurator("TwoSheet").Config); var dataSet = new DataSetAdapter(); var data1 = Enumerable.Range(0, 100) .Select(i => new PocoThree(6)) .ToList(); var data2 = Enumerable.Range(0, 1000) .Select(i => new PocoThree(9)) .ToList(); dataSet.Add(data1, "One"); dataSet.Add(data2, "TwoSheet"); var exporter = new DataSetToWorkbookExporter(dataSetExportConfig) { DataSet = dataSet }; exporter.Export(workbook); workbook.Save(); TestContext.WriteLine($"Saved {outPath}."); workbook.Dispose(); workbook = new ExcelPackage(new FileInfo(outPath)); var reader = ExcelTableReader.ReadContiguousTableWithHeader(workbook.Workbook.Worksheets["OneSheet"], 1); var readPocos = new PocoThreeReader().Read(reader); CheckEquality(data1, readPocos); workbook.Dispose(); if (_deleteExportedFiles) { File.Delete(outPath); } }
public void SimplePoco() { var outPath = GetNewOutFilePath("-simple"); var workbook = new ExcelPackage(new FileInfo(outPath)); var dataSetExportConfig = new DataSetExportAutoConfig(); const string dataTableName = "One"; const string sheetName = "OneSheet"; var configurator = new PocoExportConfigurator <PocoTwo>(sheetName, dataTableName); Expression <Func <PocoBase, int> > refId = o => o.Id; Expression <Func <PocoBase, DateTime> > refDateTime = o => o.DateTime; Expression <Func <PocoTwo, long?> > refInt = o => o.FooInt; // implicit conversion from float to double Expression <Func <PocoTwo, double?> > refFloat = o => o.FooFloat; Expression <Func <PocoTwo, string> > refString = o => o.FooString; Expression <Func <PocoTwo, long?> > refFieldInt = o => o.FieldInt; var idColumnSource = PocoColumnSourceFactory.Create(refId); var dateTimeColumnSource = PocoColumnSourceFactory.Create(refDateTime); configurator .AddColumn(idColumnSource) .AddColumn(dateTimeColumnSource) .AddColumn(refInt) .AddColumn(refFloat) .AddColumn(refString) .AddColumn(refFieldInt) // same column via reflection; duplicate caption allowed when exporting, but not when importing // as the reader would not be able to choose which column to get data from .AddColumn <int?>(nameof(PocoTwo.FieldInt), "ReflectionFieldInt") // when extracted type is unknown at compile time (type parameter is object), actual type will be resolved via reflection .AddColumn <object>(nameof(PocoTwo.FieldInt), "ReflectionFieldIntLateType"); Assert.AreEqual(typeof(int), configurator.Config.GetAutoColumnConfig("ReflectionFieldIntLateType").ColumnDataSource.DataType); dataSetExportConfig.AddSheet(configurator.Config); var dataSet = new DataSetAdapter(); var data1 = Enumerable.Range(0, 100) .Select(i => new PocoTwo(true)) .ToList(); dataSet.Add(data1, dataTableName); var exporter = new DataSetToWorkbookExporter(dataSetExportConfig) { DataSet = dataSet }; exporter.Export(workbook); workbook.Save(); TestContext.WriteLine($"Saved {outPath}."); workbook.Dispose(); workbook = new ExcelPackage(new FileInfo(outPath)); var reader = ExcelTableReader.ReadContiguousTableWithHeader(workbook.Workbook.Worksheets["OneSheet"], 1); var pocoReader = new TableMappingReader <PocoTwo>(); pocoReader .Map(o => o.Id) .Map(o => o.DateTime) .Map(o => o.FooInt) .Map(o => o.FooFloat) .Map(o => o.FooString) .Map(o => o.FieldInt); var readPocos = pocoReader.Read(reader); CheckEquality(data1, readPocos); workbook.Dispose(); if (_deleteExportedFiles) { File.Delete(outPath); } }
public void ArrayFromPoco() { var outPath = GetNewOutFilePath("-array"); var workbook = new ExcelPackage(new FileInfo(outPath)); var dataSetExportConfig = new DataSetExportAutoConfig(); Expression <Func <PocoBase, int> > refId = o => o.Id; Expression <Func <PocoBase, DateTime> > refDateTime = o => o.DateTime; Expression <Func <PocoOne, IList <double?> > > refCollection = o => o.Values; // predefined configurator reusing mappings from base class export configurators dataSetExportConfig.AddSheet(new PocoOneExportConfigurator("OneSheet", "One").Config); // adhoc configurator var configurator = new PocoExportConfigurator <PocoOne>("TwoSheet"); configurator .AddInheritedColumn(refDateTime) .AddCollectionColumns(refCollection, 10) .AddInheritedColumn(refId); dataSetExportConfig.AddSheet(configurator.Config); var dataSet = new DataSetAdapter(); var data1 = Enumerable.Range(0, 100) .Select(i => new PocoOne(6)) .ToList(); var data2 = Enumerable.Range(0, 1000) .Select(i => new PocoOne(9)) .ToList(); dataSet.Add(data1, "One"); dataSet.Add(data2, "TwoSheet"); var exporter = new DataSetToWorkbookExporter(dataSetExportConfig) { DataSet = dataSet }; exporter.Export(workbook); workbook.Save(); TestContext.WriteLine($"Saved {outPath}."); workbook.Dispose(); workbook = new ExcelPackage(new FileInfo(outPath)); var reader = ExcelTableReader.ReadContiguousTableWithHeader(workbook.Workbook.Worksheets["OneSheet"], 1); var readPocos = new PocoOneReader().Read(reader); CheckEquality(data1, readPocos); workbook.Dispose(); if (_deleteExportedFiles) { File.Delete(outPath); } }
public void OneTable() { var outPath = GetNewOutFilePath("-from-ado.net"); var workbook = new ExcelPackage(new FileInfo(outPath)); var dataSet = new DataSet(); var tableName = "LastSegments"; var dataTable = dataSet.Tables.Add(tableName); dataTable.Columns.Add("Id", typeof(long)); dataTable.Columns.Add("Description", typeof(string)); dataTable.Columns.Add("Authority", typeof(string)); dataTable.Columns.Add("MaterialLotID", typeof(string)); dataTable.Columns.Add("LotType", typeof(string)); dataTable.Columns.Add("Designation", typeof(string)); dataTable.Columns.Add("SegmentState", typeof(string)); var designations = new [] { "Production", "R&D", "Baseline" }; var segmentStates = new[] { "Commenced", "Completed", "Aborted" }; var rowCount = 1000; var random = new Random(); for (var n = 0; n < rowCount; ++n) { dataTable.Rows.Add( rowCount * 5 - n , $"Description{n}" , $"Authority{n % 3}" , $"Lot#{n}" , $"LotType{n%2}" , designations[random.Next(0, designations.Length)] , segmentStates[random.Next(0, segmentStates.Length)]); } var config = new DataSetExportAutoConfig(dataSet); var tableConfig = config.GetTableConfig(tableName); var columnConfig = tableConfig.GetAutoColumnConfig("Description"); columnConfig.MinimumWidth = 40; columnConfig.WrapText = true; tableConfig.SheetName = "Last Segments"; tableConfig.GetAutoColumnConfig("Authority").AutoFit = true; tableConfig.GetAutoColumnConfig("MaterialLotID").AutoFit = true; tableConfig.GetAutoColumnConfig("LotType").AutoFit = true; tableConfig.GetAutoColumnConfig("Designation").AutoFit = true; columnConfig = tableConfig.GetAutoColumnConfig("SegmentState"); columnConfig.Caption = "Segment State"; columnConfig.AutoFit = true; columnConfig.BackgroundColorExtractor = (d, n) => "Aborted".Equals(((DataRow)d)["SegmentState"]) ? Color.LightCoral : (Color?)null; var exporter = new DataSetToWorkbookExporter(config) { DataSet = new DataSetAdapter(dataSet) }; var start = DateTime.Now; exporter.Export(workbook); var duration = DateTime.Now - start; Console.WriteLine("Duration: {0}", duration); workbook.Save(); TestContext.WriteLine($"Saved {outPath}."); workbook.Dispose(); var readTable = AdoTableReader.GetWorksheetDataTable(outPath); Assert.AreEqual(tableConfig.Columns.Count, readTable.Columns.Count); for (var i = 0; i < tableConfig.Columns.Count; ++i) { //ExcelDataReader does not set column name, caption and contains header as first data row Assert.AreEqual(tableConfig.Columns[i].Caption, readTable.Rows[0][i]); // since header is first data row, only string type will be set correctly //Assert.AreEqual(tableConfig.Columns[i].ColumnDataSource.DataType, readTable.Columns[i].DataType); } Assert.AreEqual(rowCount, readTable.Rows.Count - 1); for (int i = 0; i < rowCount; ++i) { var savedRow = dataTable.Rows[i]; var readRow = readTable.Rows[i + 1]; for (var c = 0; c < tableConfig.Columns.Count; ++c) { Assert.AreEqual(savedRow[c], readRow[c]); } } if (_deleteExportedFiles) { File.Delete(outPath); } }