public void ReadAdHocTableExcelDataReader()
{
ITableReader reader = AdoTableReader.ReadContiguousExcelTableWithHeader(XlsTestFilePath, null, 17);
//Trace(reader);
var xlsResult = reader.Rows.ToListOfDictionaries();
reader = AdoTableReader.ReadContiguousExcelTableWithHeader(TestFilePath, null, 17);
//Trace(reader);
var xlsxResult = reader.Rows.ToListOfDictionaries();
Assert.AreEqual(xlsResult, xlsxResult);
Assert.AreEqual(1895, xlsResult.Count);
}
public void ReadAdHocTableXlsx()
{
IList <IDictionary <string, object> > epplusResult;
using (var package = new ExcelPackage(new FileInfo(TestFilePath)))
{
package.Compatibility.IsWorksheets1Based = true;
var workbook = package.Workbook;
var epplusReader = ExcelTableReader.ReadContiguousTableWithHeader(workbook.Worksheets[1], 17);
//Trace(reader);
epplusResult = epplusReader.Rows.ToListOfDictionaries();
}
var reader = AdoTableReader.ReadContiguousExcelTableWithHeader(TestFilePath, null, 17);
//Trace(reader);
var excelDataReaderResult = reader.Rows.ToListOfDictionaries();
Assert.AreEqual(1895, excelDataReaderResult.Count);
Assert.AreEqual(epplusResult.Count, excelDataReaderResult.Count);
Assert.AreEqual(epplusResult, excelDataReaderResult);
}
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);
}
}
