Example #1
0
        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);
            }
        }
Example #2
0
        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);
            }
        }
Example #3
0
        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);
            }
        }
Example #4
0
        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);
            }
        }
Example #5
0
        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);
            }
        }