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