예제 #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);
            }
        }
예제 #2
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);
            }
        }
예제 #3
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);
            }
        }
예제 #4
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);
            }
        }