public void ReadArbitraryTable()
{
IList <IDictionary <string, object> > epplusResult;
IList <IDictionary <string, object> > excelDataReaderResult;
var columns = new List <KeyValuePair <string, int> >
{
new KeyValuePair <string, int>("Name", 1),
new KeyValuePair <string, int>("Value", 9),
};
using (var package = new ExcelPackage(new FileInfo(TestFilePath)))
{
package.Compatibility.IsWorksheets1Based = true;
var workbook = package.Workbook;
var reader = ExcelTableReader.ReadArbitraryTable(workbook.Worksheets[1], 7, 16, columns);
//Trace(reader);
TestReadMappedSummaryTable(reader);
epplusResult = reader.Rows.ToListOfDictionaries();
}
{
var reader = AdoTableReader.ReadArbitraryExcelTable(TestFilePath, null, 7, 16, columns);
TestReadMappedSummaryTable(reader);
excelDataReaderResult = reader.Rows.ToListOfDictionaries();
}
Assert.AreEqual(epplusResult, excelDataReaderResult);
}
ReadClusterVertexTable
(
ListObject oClusterVertexTable,
Dictionary <String, ClusterInformation> oClusterNameDictionary,
Dictionary <String, IVertex> oVertexNameDictionary,
IGraph oGraph
)
{
Debug.Assert(oClusterVertexTable != null);
Debug.Assert(oClusterNameDictionary != null);
Debug.Assert(oVertexNameDictionary != null);
Debug.Assert(oGraph != null);
AssertValid();
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oClusterVertexTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the cluster vertex information from the row.
String sClusterName, sVertexName;
if (
!oRow.TryGetNonEmptyStringFromCell(
ClusterVertexTableColumnNames.ClusterName,
out sClusterName)
||
!oRow.TryGetNonEmptyStringFromCell(
ClusterVertexTableColumnNames.VertexName, out sVertexName)
)
{
continue;
}
// Get the cluster information for the vertex and store the cluster
// information in the vertex.
ClusterInformation oClusterInformation;
IVertex oVertex;
if (
!oClusterNameDictionary.TryGetValue(sClusterName,
out oClusterInformation)
||
!oVertexNameDictionary.TryGetValue(sVertexName,
out oVertex)
)
{
continue;
}
oVertex.SetValue(ReservedMetadataKeys.PerColor,
oClusterInformation.VertexColor);
oVertex.SetValue(ReservedMetadataKeys.PerVertexShape,
oClusterInformation.VertexShape);
}
}
ReadAllColumns
(
ExcelTableReader oExcelTableReader,
ExcelTableReader.ExcelTableRow oRow,
IMetadataProvider oEdgeOrVertex,
HashSet <String> oColumnNamesToExclude
)
{
Debug.Assert(oExcelTableReader != null);
Debug.Assert(oRow != null);
Debug.Assert(oEdgeOrVertex != null);
Debug.Assert(oColumnNamesToExclude != null);
AssertValid();
foreach (String sColumnName in oExcelTableReader.ColumnNames)
{
String sValue;
if (!oColumnNamesToExclude.Contains(sColumnName) &&
oRow.TryGetNonEmptyStringFromCell(sColumnName, out sValue))
{
oEdgeOrVertex.SetValue(sColumnName, sValue);
}
}
}
public void Build(string parentSrcDir)
{
Dictionary <int, AnimLengthData> animLengths = ReadModel(parentSrcDir);
ExcelTableReader.Inst().WriteTable(xlsxPath, "_", animLengths, GetAnimLengthXlsx());
sb.AppendLine(xlsxPath);
Debug.LogWarning(sb.ToString());
}
public void ExcelTableReaderTest()
{
using (var package = new ExcelPackage(new FileInfo(TestFileWithTablesPath)))
{
var workbook = package.Workbook;
var tbSummary = workbook.Worksheets.SelectMany(sh => sh.Tables).FirstOrDefault(n => n.Name == ExcelTableNameSummary);
var tbData = workbook.Worksheets.SelectMany(sh => sh.Tables).FirstOrDefault(n => n.Name == ExcelTableNameData);
var reader = new ExcelTableReader(tbData);
Trace(new ExcelTableReader(tbSummary));
Trace(reader);
}
}
PrependGroupLabelsWithGroupNames
(
ListObject oGroupTable
)
{
Debug.Assert(oGroupTable != null);
// Use ExcelTableReader to accomplish the task with minimal code.
//
// Note that ExcelTableReader is optimized for reading, and that its
// use for writing incurs the overhead of a COM call for each written
// cell. There typically aren't many groups, though, so this is
// probably tolerable.
//
// If this turns out to be too slow, something similar to the code in
// TableColumnMapper.MapViaCopy() will need to be implemented.
ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupTable);
if (
oExcelTableReader.ColumnNames.Contains(GroupTableColumnNames.Name)
&&
oExcelTableReader.ColumnNames.Contains(GroupTableColumnNames.Label)
)
{
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
String sName;
if (oRow.TryGetNonEmptyStringFromCell(
GroupTableColumnNames.Name, out sName))
{
String sLabel;
if (oRow.TryGetNonEmptyStringFromCell(
GroupTableColumnNames.Label, out sLabel))
{
sName += ": " + sLabel;
}
oRow.GetRangeForCell(GroupTableColumnNames.Label)
.set_Value(Missing.Value, sName);
}
}
}
}
GetRowIDDictionary
(
ListObject oTable,
String sColumnName
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sColumnName));
Dictionary <Int32, Object> oRowIDDictionary =
new Dictionary <Int32, Object>();
// The code that reads the table can handle hidden rows, but not hidden
// columns. Temporarily show all hidden columns in the table.
ExcelHiddenColumns oHiddenColumns =
ExcelColumnHider.ShowHiddenColumns(oTable);
try
{
ExcelTableReader oExcelTableReader = new ExcelTableReader(oTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
Int32 iRowID;
Double dCellValue;
if (
oRow.TryGetInt32FromCell(CommonTableColumnNames.ID,
out iRowID)
&&
oRow.TryGetDoubleFromCell(sColumnName, out dCellValue)
)
{
oRowIDDictionary[iRowID] = dCellValue;
}
}
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
return(oRowIDDictionary);
}
public static IList <StringCard> GetSCardList(string excelFilePath)
{
var package = new ExcelPackage(new FileInfo(excelFilePath));
var tableReader = ExcelTableReader.ReadContiguousTableWithHeader(package.Workbook.Worksheets[1], 1);
var pocoReader = new TableMappingReader <StringCard>()
.Map(o => o.图片Id)
.Map(o => o.中文名)
.Map(o => o.效果Id)
.Map(o => o.英文名)
.Map(o => o.战力)
.Map(o => o.品质)
.Map(o => o.阵营)
.Map(o => o.站位区)
.Map(o => o.属性)
.Map(o => o.卡牌介绍)
.Map(o => o.效果)
.Map(o => o.稀有度)
.Map(o => o.排序Id);
return(pocoReader.Read(tableReader));
}
ReadTopColumn
(
ListObject oTopMetricsTable,
String sColumnHeader,
StringBuilder oTopMetrics
)
{
Debug.Assert(oTopMetricsTable != null);
Debug.Assert(!String.IsNullOrEmpty(sColumnHeader));
Debug.Assert(oTopMetrics != null);
StringBuilder oTopColumn = new StringBuilder();
Boolean bColumnIsEmpty = true;
oTopColumn.Append(sColumnHeader);
oTopColumn.Append(':');
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oTopMetricsTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
String sItemName;
if (oRow.TryGetNonEmptyStringFromCell(sColumnHeader,
out sItemName))
{
StringUtil.AppendAfterEmptyLine(oTopColumn, sItemName);
bColumnIsEmpty = false;
}
}
if (!bColumnIsEmpty)
{
StringUtil.AppendSectionSeparator(oTopMetrics);
oTopMetrics.Append(oTopColumn.ToString());
}
}
FilterColumnNames
(
ExcelTableReader oExcelTableReader,
HashSet <String> oColumnNamesToExclude
)
{
Debug.Assert(oExcelTableReader != null);
Debug.Assert(oColumnNamesToExclude != null);
AssertValid();
List <String> oFilteredColumnNames = new List <String>();
foreach (String sColumnName in oExcelTableReader.ColumnNames)
{
if (!oColumnNamesToExclude.Contains(sColumnName))
{
oFilteredColumnNames.Add(sColumnName);
}
}
return(oFilteredColumnNames.ToArray());
}
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);
}
ReadVertexTable
(
ListObject oVertexTable,
ReadWorkbookContext oReadWorkbookContext,
IGraph oGraph,
out Boolean bLayoutAndZOrderSet
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGraph != null);
AssertValid();
bLayoutAndZOrderSet = false;
if (GetTableColumnIndex(oVertexTable,
VertexTableColumnNames.VertexName, false) == NoSuchColumn)
{
// Nothing can be done without vertex names.
return;
}
Boolean bReadAllEdgeAndVertexColumns =
oReadWorkbookContext.ReadAllEdgeAndVertexColumns;
if (oReadWorkbookContext.FillIDColumns)
{
FillIDColumn(oVertexTable);
}
// Get the names of all the column pairs that are used to add custom
// menu items to the vertex context menu in the graph.
TableColumnAdder oTableColumnAdder = new TableColumnAdder();
ICollection <KeyValuePair <String, String> > aoCustomMenuItemPairNames =
oTableColumnAdder.GetColumnPairNames(oVertexTable,
VertexTableColumnNames.CustomMenuItemTextBase,
VertexTableColumnNames.CustomMenuItemActionBase);
IVertexCollection oVertices = oGraph.Vertices;
Dictionary <String, IVertex> oVertexNameDictionary =
oReadWorkbookContext.VertexNameDictionary;
Dictionary <Int32, IIdentityProvider> oEdgeRowIDDictionary =
oReadWorkbookContext.EdgeRowIDDictionary;
BooleanConverter oBooleanConverter =
oReadWorkbookContext.BooleanConverter;
VertexVisibilityConverter oVertexVisibilityConverter =
new VertexVisibilityConverter();
VertexLabelPositionConverter oVertexLabelPositionConverter =
new VertexLabelPositionConverter();
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oVertexTable);
HashSet <String> oColumnNamesToExclude = new HashSet <String>(
new String[] {
VertexTableColumnNames.VertexName
});
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the name of the vertex.
String sVertexName;
if (!oRow.TryGetNonEmptyStringFromCell(
VertexTableColumnNames.VertexName, out sVertexName))
{
continue;
}
// If the vertex was added to the graph as part of an edge,
// retrieve the vertex.
IVertex oVertex;
if (!oVertexNameDictionary.TryGetValue(sVertexName, out oVertex))
{
oVertex = null;
}
// Assume a default visibility.
Visibility eVisibility = Visibility.ShowIfInAnEdge;
String sVisibility;
if (oRow.TryGetNonEmptyStringFromCell(
CommonTableColumnNames.Visibility, out sVisibility))
{
if (!oVertexVisibilityConverter.TryWorkbookToGraph(
sVisibility, out eVisibility))
{
OnInvalidVisibility(oRow);
}
}
switch (eVisibility)
{
case Visibility.ShowIfInAnEdge:
// If the vertex is part of an edge, show it using the
// specified vertex attributes. Otherwise, skip the vertex
// row.
if (oVertex == null)
{
continue;
}
break;
case Visibility.Skip:
// Skip the vertex row and any edge rows that include the
// vertex. Do not read them into the graph.
if (oVertex != null)
{
// Remove the vertex and its incident edges from the
// graph and dictionaries.
RemoveVertex(oVertex, oReadWorkbookContext, oGraph);
}
continue;
case Visibility.Hide:
// If the vertex is part of an edge, hide it and its
// incident edges. Otherwise, skip the vertex row.
if (oVertex == null)
{
continue;
}
HideVertex(oVertex);
break;
case Visibility.Show:
// Show the vertex using the specified attributes
// regardless of whether it is part of an edge.
if (oVertex == null)
{
oVertex = CreateVertex(sVertexName, oVertices,
oVertexNameDictionary);
}
oVertex.SetValue(
ReservedMetadataKeys.VertexHasVisibilityOfShow, null);
break;
default:
Debug.Assert(false);
break;
}
Debug.Assert(oVertex != null);
// If ReadWorkbookContext.FillIDColumns is true, add the vertex to
// the vertex row ID dictionary and set the vertex's Tag to the row
// ID.
oReadWorkbookContext.AddToRowIDDictionary(oRow, oVertex, false);
if (bReadAllEdgeAndVertexColumns)
{
// All columns except the vertex name should be read and stored
// as metadata on the vertex.
ReadAllColumns(oExcelTableReader, oRow, oVertex,
oColumnNamesToExclude);
continue;
}
// Layout and z-order.
if (ReadLayoutAndZOrder(oRow, oVertex))
{
bLayoutAndZOrderSet = true;
}
// Location and Locked.
if (!oReadWorkbookContext.IgnoreVertexLocations)
{
System.Drawing.PointF oLocation;
Boolean bLocationSpecified = TryGetLocation(oRow,
VertexTableColumnNames.X, VertexTableColumnNames.Y,
oReadWorkbookContext.VertexLocationConverter,
out oLocation);
if (bLocationSpecified)
{
oVertex.Location = oLocation;
}
ReadLocked(oRow, oBooleanConverter, bLocationSpecified,
oVertex);
}
// Polar coordinates.
ReadPolarCoordinates(oRow, oVertex);
// Marked.
ReadMarked(oRow, oBooleanConverter, oVertex);
// Custom menu items.
if (aoCustomMenuItemPairNames.Count > 0)
{
ReadCustomMenuItems(oRow, aoCustomMenuItemPairNames, oVertex);
}
// Alpha.
ReadAlpha(oRow, oVertex);
// Tooltip.
ReadCellAndSetMetadata(oRow, VertexTableColumnNames.ToolTip,
oVertex, ReservedMetadataKeys.PerVertexToolTip);
// Label.
if (oReadWorkbookContext.ReadVertexLabels)
{
ReadCellAndSetMetadata(oRow, VertexTableColumnNames.Label,
oVertex, ReservedMetadataKeys.PerVertexLabel);
}
// Label fill color.
ReadColor(oRow, VertexTableColumnNames.LabelFillColor, oVertex,
ReservedMetadataKeys.PerVertexLabelFillColor,
oReadWorkbookContext.ColorConverter2);
// Label position.
ReadLabelPosition(oRow, oVertexLabelPositionConverter, oVertex);
// Radius.
Nullable <Single> oRadiusWorkbook = new Nullable <Single>();
oRadiusWorkbook = ReadRadius(oRow,
oReadWorkbookContext.VertexRadiusConverter, oVertex);
// Shape.
VertexShape eVertexShape;
if (!ReadShape(oRow, oVertex, out eVertexShape))
{
eVertexShape = oReadWorkbookContext.DefaultVertexShape;
}
// Label font size.
if (eVertexShape == VertexShape.Label && oRadiusWorkbook.HasValue)
{
// The vertex radius is used to specify font size when the
// shape is Label.
oVertex.SetValue(ReservedMetadataKeys.PerVertexLabelFontSize,
oReadWorkbookContext.VertexRadiusConverter.
WorkbookToLabelFontSize(oRadiusWorkbook.Value));
}
// Image URI.
if (eVertexShape == VertexShape.Image &&
oReadWorkbookContext.ReadVertexImages)
{
ReadImageUri(oRow, oVertex,
oReadWorkbookContext.VertexRadiusConverter,
oRadiusWorkbook.HasValue ? oRadiusWorkbook :
oReadWorkbookContext.DefaultVertexImageSize
);
}
// Color
ReadColor(oRow, VertexTableColumnNames.Color, oVertex,
ReservedMetadataKeys.PerColor,
oReadWorkbookContext.ColorConverter2);
}
if (bReadAllEdgeAndVertexColumns)
{
// Store the vertex column names on the graph.
oGraph.SetValue(ReservedMetadataKeys.AllVertexMetadataKeys,
FilterColumnNames(oExcelTableReader, oColumnNamesToExclude));
}
}
public List <Connection> CreateConnections()
{
var allTimeTableData = ExcelTableReader.ReadData(GlobalConstants.PathToExcelFile);
var allVehicles = new List <BusLine>();
var allStops = new List <Stop>();
var allConnections = new List <Connection>();
var allTrips = new List <Trip>();
foreach (var table in allTimeTableData)
{
var busLine = new BusLine();
var vehicleNumber = table.Item1.Replace("'", string.Empty);
vehicleNumber = vehicleNumber.Remove(vehicleNumber.Length - 1);
if (allVehicles.FirstOrDefault(v => v.Number == vehicleNumber) == null)
{
busLine = new BusLine()
{
Number = vehicleNumber
};
allVehicles.Add(busLine);
}
var rows = table.Item2;
foreach (var stopName in rows.Select(r => r[0]))
{
if (allStops.FirstOrDefault(s => s.Name == stopName.ToString()) == null)
{
var stop = new Stop()
{
Name = stopName.ToString()
};
allStops.Add(stop);
}
}
for (int i = 1; i < rows.Count(); i++)
{
var previousRow = rows.ToArray()[i - 1];
var currentRow = rows.ToArray()[i];
var departureStopName = previousRow[0].ToString();
var departureStop = allStops.FirstOrDefault(s => s.Name == departureStopName);
var arrivalStopName = currentRow[0].ToString();
var arrivalStop = allStops.FirstOrDefault(s => s.Name == arrivalStopName);
for (int j = 2; j < currentRow.ItemArray.Count(); j++)
{
var arrivalTime = Convert.ToDateTime(currentRow[j]);
var departureTime = Convert.ToDateTime(previousRow[j]);
//var arrivalTime = new DateTime();
//if (!String.IsNullOrEmpty(currentRow[j].ToString()))
//{
// arrivalTime = Convert.ToDateTime(currentRow[j]);
//}
//else
//{
// continue;
//}
//var nonEmptyRow = i;
//while (String.IsNullOrEmpty(rows.ToArray()[nonEmptyRow][j].ToString()))
//{
// nonEmptyRow--;
//}
//var departureTime = Convert.ToDateTime(rows.ToArray()[nonEmptyRow][j]);
var connection = new Connection();
connection.DepartureStop = departureStop;
connection.ArrivalStop = arrivalStop;
connection.DepartureTime = departureTime;
connection.ArrivalTime = arrivalTime;
connection.BusLine = busLine;
allConnections.Add(connection);
}
}
}
return(allConnections);
}
private bool Read(string fn)
{
ExcelTableReader reader = new ExcelTableReader();
if (!reader.Read(fn))
{
MessageBox.Show("Не удалось прочитать файл " + fn);
return(false);
}
int c = 0;
if (reader.Pages.Count > 1)
{
string mes = string.Format("Файл '{0}' содержит более одной закладки. Прочитать данные из первой закладки='{1}'?", Path.GetFileName(fn), reader.Pages[0].Name);
if (MessageBox.Show(mes, "", MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return(false);
}
}
c = 0;
DataTable table = reader.Pages[c].Table;
if (table.Columns.IndexOf("date") == -1)
{
MessageBox.Show("Не найдена колонка date");
return(false);
}
if (table.Columns.IndexOf("direction") == -1)
{
MessageBox.Show("Не найдена колонка direction");
return(false);
}
if (table.Columns.IndexOf("strength") == -1)
{
MessageBox.Show("Не найдена колонка strength");
return(false);
}
List <WindRecord> data = new List <WindRecord>();
List <string> errors = new List <string>();
foreach (DataRow row in table.Rows)
{
var item = new WindRecord();
if (!DateTime.TryParse(row["date"].ToString(), out DateTime dt))
{
errors.Add("invalid date:" + row["date"].ToString());
continue;
}
item.Date = dt;
if (!double.TryParse(row["direction"].ToString(), out double direction))
{
errors.Add("invalid direction:" + row["direction"].ToString());
continue;
}
item.Direction = direction;
if (!double.TryParse(row["strength"].ToString(), out double strength))
{
errors.Add("invalid strength:" + row["strength"].ToString());
continue;
}
item.Strength = strength;
data.Add(item);
}
Data = data.ToArray();
return(true);
}
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);
}
}
ReadGroupVertexTable
(
ListObject oGroupVertexTable,
ReadWorkbookContext oReadWorkbookContext,
Dictionary <String, ExcelTemplateGroupInfo> oGroupNameDictionary,
IGraph oGraph
)
{
Debug.Assert(oGroupVertexTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGroupNameDictionary != null);
Debug.Assert(oGraph != null);
AssertValid();
Dictionary <String, IVertex> oVertexNameDictionary =
oReadWorkbookContext.VertexNameDictionary;
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oGroupVertexTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the group vertex information from the row.
String sGroupName, sVertexName;
if (
!oRow.TryGetNonEmptyStringFromCell(
GroupVertexTableColumnNames.GroupName, out sGroupName)
||
!oRow.TryGetNonEmptyStringFromCell(
GroupVertexTableColumnNames.VertexName, out sVertexName)
)
{
continue;
}
// Get the group information for the vertex.
ExcelTemplateGroupInfo oExcelTemplateGroupInfo;
IVertex oVertex;
if (
!oGroupNameDictionary.TryGetValue(sGroupName,
out oExcelTemplateGroupInfo)
||
!oVertexNameDictionary.TryGetValue(sVertexName,
out oVertex)
)
{
continue;
}
// If the vertex should get its color or shape from the group, set
// the vertex's color or shape.
Boolean bReadColorFromGroup, bReadShapeFromGroup;
GetReadColorAndShapeFlags(oVertex, oExcelTemplateGroupInfo,
oReadWorkbookContext, out bReadColorFromGroup,
out bReadShapeFromGroup);
if (bReadColorFromGroup)
{
oVertex.SetValue(ReservedMetadataKeys.PerColor,
oExcelTemplateGroupInfo.VertexColor);
}
if (bReadShapeFromGroup)
{
oVertex.SetValue(ReservedMetadataKeys.PerVertexShape,
oExcelTemplateGroupInfo.VertexShape);
}
oExcelTemplateGroupInfo.Vertices.AddLast(oVertex);
}
}
ReadGroupTable
(
ListObject oGroupTable,
ReadWorkbookContext oReadWorkbookContext,
HashSet <String> oSkippedGroupNames,
HashSet <String> oHiddenGroupNames
)
{
Debug.Assert(oGroupTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oSkippedGroupNames != null);
Debug.Assert(oHiddenGroupNames != null);
AssertValid();
if (oReadWorkbookContext.FillIDColumns)
{
FillIDColumn(oGroupTable);
}
Dictionary <String, ExcelTemplateGroupInfo> oGroupNameDictionary =
new Dictionary <String, ExcelTemplateGroupInfo>();
ColorConverter2 oColorConverter2 =
oReadWorkbookContext.ColorConverter2;
GroupVisibilityConverter oGroupVisibilityConverter =
new GroupVisibilityConverter();
BooleanConverter oBooleanConverter =
oReadWorkbookContext.BooleanConverter;
ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the group information.
String sGroupName;
Color oVertexColor;
VertexShape eVertexShape;
if (
!oRow.TryGetNonEmptyStringFromCell(GroupTableColumnNames.Name,
out sGroupName)
||
!TryGetColor(oRow, GroupTableColumnNames.VertexColor,
oColorConverter2, out oVertexColor)
||
!TryGetVertexShape(oRow, GroupTableColumnNames.VertexShape,
out eVertexShape)
)
{
continue;
}
ReadVisibility(oRow, oGroupVisibilityConverter, sGroupName,
oSkippedGroupNames, oHiddenGroupNames);
Boolean bCollapsed = false;
Boolean bCollapsedCellValue;
if (
TryGetBoolean(oRow, GroupTableColumnNames.Collapsed,
oBooleanConverter, out bCollapsedCellValue)
&&
bCollapsedCellValue
)
{
bCollapsed = true;
}
String sCollapsedAttributes;
if (!oRow.TryGetNonEmptyStringFromCell(
GroupTableColumnNames.CollapsedAttributes,
out sCollapsedAttributes))
{
sCollapsedAttributes = null;
}
Int32 iRowIDAsInt32;
Nullable <Int32> iRowID = null;
if (oRow.TryGetInt32FromCell(CommonTableColumnNames.ID,
out iRowIDAsInt32))
{
iRowID = iRowIDAsInt32;
}
ExcelTemplateGroupInfo oExcelTemplateGroupInfo =
new ExcelTemplateGroupInfo(sGroupName, iRowID, oVertexColor,
eVertexShape, bCollapsed, sCollapsedAttributes);
if (oReadWorkbookContext.ReadGroupLabels)
{
String sLabel;
if (oRow.TryGetNonEmptyStringFromCell(
GroupTableColumnNames.Label, out sLabel))
{
oExcelTemplateGroupInfo.Label = sLabel;
}
}
if (!oReadWorkbookContext.IgnoreVertexLocations)
{
System.Drawing.PointF oCollapsedLocation;
if (TryGetLocation(oRow, GroupTableColumnNames.CollapsedX,
GroupTableColumnNames.CollapsedY,
oReadWorkbookContext.VertexLocationConverter,
out oCollapsedLocation))
{
oExcelTemplateGroupInfo.CollapsedLocation =
oCollapsedLocation;
}
}
try
{
oGroupNameDictionary.Add(sGroupName, oExcelTemplateGroupInfo);
}
catch (ArgumentException)
{
Range oInvalidCell = oRow.GetRangeForCell(
GroupTableColumnNames.Name);
OnWorkbookFormatError(String.Format(
"The cell {0} contains a duplicate group name. There"
+ " can't be two rows with the same group name."
,
ExcelUtil.GetRangeAddress(oInvalidCell)
),
oInvalidCell
);
}
}
return(oGroupNameDictionary);
}
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 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);
}
}
ReadGroupVertexTable
(
ListObject oGroupVertexTable,
ReadWorkbookContext oReadWorkbookContext,
Dictionary <String, GroupInformation> oGroupNameDictionary,
IGraph oGraph
)
{
Debug.Assert(oGroupVertexTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGroupNameDictionary != null);
Debug.Assert(oGraph != null);
AssertValid();
Dictionary <String, IVertex> oVertexNameDictionary =
oReadWorkbookContext.VertexNameDictionary;
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oGroupVertexTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the group vertex information from the row.
String sGroupName, sVertexName;
if (
!oRow.TryGetNonEmptyStringFromCell(
GroupVertexTableColumnNames.GroupName, out sGroupName)
||
!oRow.TryGetNonEmptyStringFromCell(
GroupVertexTableColumnNames.VertexName, out sVertexName)
)
{
continue;
}
// Get the group information for the vertex and store the group
// information in the vertex.
GroupInformation oGroupInformation;
IVertex oVertex;
if (
!oGroupNameDictionary.TryGetValue(sGroupName,
out oGroupInformation)
||
!oVertexNameDictionary.TryGetValue(sVertexName,
out oVertex)
)
{
continue;
}
oVertex.SetValue(ReservedMetadataKeys.PerColor,
oGroupInformation.VertexColor);
oVertex.SetValue(ReservedMetadataKeys.PerVertexShape,
oGroupInformation.VertexShape);
if (oReadWorkbookContext.SaveGroupVertices)
{
Debug.Assert(oGroupInformation.Vertices != null);
oGroupInformation.Vertices.Add(oVertex);
}
}
}
ReadGroupTable
(
ListObject oGroupTable,
ReadWorkbookContext oReadWorkbookContext
)
{
Debug.Assert(oGroupTable != null);
Debug.Assert(oReadWorkbookContext != null);
AssertValid();
if (oReadWorkbookContext.FillIDColumns)
{
FillIDColumn(oGroupTable);
}
Dictionary <String, GroupInformation> oGroupNameDictionary =
new Dictionary <String, GroupInformation>();
ColorConverter2 oColorConverter2 =
oReadWorkbookContext.ColorConverter2;
BooleanConverter oBooleanConverter =
oReadWorkbookContext.BooleanConverter;
ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the group information.
String sGroupName;
Color oVertexColor;
VertexShape eVertexShape;
if (
!oRow.TryGetNonEmptyStringFromCell(GroupTableColumnNames.Name,
out sGroupName)
||
!TryGetColor(oRow, GroupTableColumnNames.VertexColor,
oColorConverter2, out oVertexColor)
||
!TryGetVertexShape(oRow, GroupTableColumnNames.VertexShape,
out eVertexShape)
)
{
continue;
}
Boolean bCollapsed = false;
Boolean bCollapsedCellValue;
if (
TryGetBoolean(oRow, GroupTableColumnNames.Collapsed,
oBooleanConverter, out bCollapsedCellValue)
&&
bCollapsedCellValue
)
{
bCollapsed = true;
}
Int32 iRowIDAsInt32;
Nullable <Int32> iRowID = null;
if (oRow.TryGetInt32FromCell(CommonTableColumnNames.ID,
out iRowIDAsInt32))
{
iRowID = iRowIDAsInt32;
}
GroupInformation oGroupInformation = new GroupInformation(
sGroupName, iRowID, oVertexColor, eVertexShape, bCollapsed);
if (oReadWorkbookContext.SaveGroupVertices)
{
// ReadGroupVertexTable() will save the group's vertices in
// this LinkedList.
oGroupInformation.Vertices = new LinkedList <IVertex>();
}
try
{
oGroupNameDictionary.Add(sGroupName, oGroupInformation);
}
catch (ArgumentException)
{
Range oInvalidCell = oRow.GetRangeForCell(
GroupTableColumnNames.Name);
OnWorkbookFormatError(String.Format(
"The cell {0} contains a duplicate group name. There"
+ " can't be two rows with the same group name."
,
ExcelUtil.GetRangeAddress(oInvalidCell)
),
oInvalidCell
);
}
}
return(oGroupNameDictionary);
}
ReadClusterTable
(
ListObject oClusterTable,
ReadWorkbookContext oReadWorkbookContext
)
{
Debug.Assert(oClusterTable != null);
Debug.Assert(oReadWorkbookContext != null);
AssertValid();
Dictionary <String, ClusterInformation> oClusterNameDictionary =
new Dictionary <String, ClusterInformation>();
ColorConverter2 oColorConverter2 =
oReadWorkbookContext.ColorConverter2;
ExcelTableReader oExcelTableReader =
new ExcelTableReader(oClusterTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the cluster information.
String sClusterName;
Color oVertexColor;
VertexShape eVertexShape;
if (
!oRow.TryGetNonEmptyStringFromCell(
ClusterTableColumnNames.Name, out sClusterName)
||
!TryGetColor(oRow, ClusterTableColumnNames.VertexColor,
oColorConverter2, out oVertexColor)
||
!TryGetVertexShape(oRow, ClusterTableColumnNames.VertexShape,
out eVertexShape)
)
{
continue;
}
// Add the cluster information to the dictionary.
ClusterInformation oClusterInformation =
new ClusterInformation();
oClusterInformation.VertexColor = oVertexColor;
oClusterInformation.VertexShape = eVertexShape;
try
{
oClusterNameDictionary.Add(
sClusterName, oClusterInformation);
}
catch (ArgumentException)
{
Range oInvalidCell = oRow.GetRangeForCell(
ClusterTableColumnNames.Name);
OnWorkbookFormatError(String.Format(
"The cell {0} contains a duplicate cluster name. There"
+ " can't be two rows with the same cluster name."
,
ExcelUtil.GetRangeAddress(oInvalidCell)
),
oInvalidCell
);
}
}
return(oClusterNameDictionary);
}
public static Scenario Load(
DateTime rangeStart,
DateTime rangeEnd,
string opportunitiesPath = "Testfiles/Open_Opportunities.xlsx",
string linesPath = "Testfiles/Lines.xlsx")
{
// load opportunities
var opWorksheets = ExcelTableReader.LoadWorksheets(opportunitiesPath);
var p = new List <Project>();
foreach (var row in opWorksheets.First().Value)
{
if (new [] { "Querschnitt mm?", "Jährlicher Betrag Standartwährung", "Liefertermin Ist" }.Any(s => string.IsNullOrWhiteSpace(row[s].ToString())))
{
continue;
}
int nr = int.Parse(row["Nr."].ToString());
var descr = row["Verkaufschance Bezeichnung"].ToString();
double probability = double.NaN;
double.TryParse(row["Wahrscheinlichkeit (%)"].ToString(), out probability);
string phase = row["Verkaufsphase"].ToString();
var lt = row["Liefertermin Ist"].ToString();
var deliveryDate = DateTime.MaxValue;
if (!string.IsNullOrWhiteSpace(lt))
{
if (!DateTime.TryParse(lt, out deliveryDate))
{
DateTime.TryParseExact(lt, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out deliveryDate);
}
}
var batches = Batch.CalculateBatches(int.Parse(row["Menge (m)"].ToString()), int.Parse(row["Spannungsebene KV"].ToString()), int.Parse(row["Querschnitt mm?"].ToString()));
if (!string.IsNullOrWhiteSpace(row["Menge (m) (2)"].ToString()))
{
batches = batches.Concat(Batch.CalculateBatches(int.Parse(row["Menge (m) (2)"].ToString()), int.Parse(row["Spannungsebene KV (2)"].ToString()), int.Parse(row["Querschnitt mm? (2)"].ToString()))).ToArray();
}
var revenue = double.Parse(row["Jährlicher Betrag Standartwährung"].ToString().Replace("CHF", "").Replace(",", ""));
var margin = double.Parse(row["Profit Margin (DB1) in CHF"].ToString().Replace("CHF", "").Replace(",", ""));
if (deliveryDate.AddDays(7 * batches.Count()).AddDays(7 * 3) >= rangeStart && deliveryDate <= rangeEnd)
{
p.Add(new Opportunity(nr, descr, deliveryDate, batches, revenue, margin, probability, phase));
}
}
// load projects
var projectWorksheets = ExcelTableReader.LoadWorksheets(linesPath);
foreach (var row in projectWorksheets.First().Value)
{
int nr = int.Parse(row["Auftrag"].ToString());
var descr = row["Bezeichnung"].ToString();
var lt = row["Datum"].ToString(); //LT? TODO
var deliveryDate = string.IsNullOrWhiteSpace(lt) ? DateTime.MaxValue : DateTime.Parse(lt);
var line = int.Parse(row["Linie"].ToString().Replace("Linie", "")) == 1 ? Batch.LineCompatibility.Line1 : Batch.LineCompatibility.Line2;
var batches = Batch.CalculateBatches(double.Parse(row["Zeit"].ToString()), line);
var isInternal = new string[] { "Medium Voltage", "Medium Voltage for stock", "internal project" }.Contains(row["Remarks"].ToString());
var revenueText = row["Revenue (CHF)"].ToString();
var revenue = string.IsNullOrWhiteSpace(revenueText) || revenueText == "NA" ? 0 : double.Parse(revenueText.Replace("CHF", ""));
var marginText = row["Margin (%)"].ToString();
var margin = string.IsNullOrWhiteSpace(marginText) || marginText == "NA" ? 0 : double.Parse(marginText.Replace("%", ""));
margin *= revenue / 100;
if (deliveryDate.AddDays(7 * batches.Count()).AddDays(7 * 3) >= rangeStart && deliveryDate <= rangeEnd)
{
p.Add(new FixedProject(nr, descr, deliveryDate, batches, revenue, margin, isInternal));
}
}
// toss away all projects which have a batch in them with size 0
p = p.Where(pro => !pro.Batches.Any(b => b.UsedWorkHours == 0)).ToList();
return(new Scenario(p.ToArray()));
}
ReadEdgeTable
(
ListObject oEdgeTable,
ReadWorkbookContext oReadWorkbookContext,
IGraph oGraph
)
{
Debug.Assert(oEdgeTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGraph != null);
AssertValid();
Boolean bReadAllEdgeAndVertexColumns =
oReadWorkbookContext.ReadAllEdgeAndVertexColumns;
if (oReadWorkbookContext.FillIDColumns)
{
FillIDColumn(oEdgeTable);
}
Dictionary <String, IVertex> oVertexNameDictionary =
oReadWorkbookContext.VertexNameDictionary;
EdgeVisibilityConverter oEdgeVisibilityConverter =
new EdgeVisibilityConverter();
Boolean bGraphIsDirected =
(oGraph.Directedness == GraphDirectedness.Directed);
ExcelTableReader oExcelTableReader = new ExcelTableReader(oEdgeTable);
IVertexCollection oVertices = oGraph.Vertices;
IEdgeCollection oEdges = oGraph.Edges;
HashSet <String> oColumnNamesToExclude = new HashSet <String>(
new String[] {
EdgeTableColumnNames.Vertex1Name,
EdgeTableColumnNames.Vertex2Name
});
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
// Get the names of the edge's vertices.
String sVertex1Name, sVertex2Name;
Boolean bVertex1IsEmpty = !oRow.TryGetNonEmptyStringFromCell(
EdgeTableColumnNames.Vertex1Name, out sVertex1Name);
Boolean bVertex2IsEmpty = !oRow.TryGetNonEmptyStringFromCell(
EdgeTableColumnNames.Vertex2Name, out sVertex2Name);
if (bVertex1IsEmpty && bVertex2IsEmpty)
{
// Skip empty rows.
continue;
}
if (bVertex1IsEmpty || bVertex2IsEmpty)
{
// A half-empty row is an error.
OnHalfEmptyEdgeRow(oRow, bVertex1IsEmpty);
}
// Assume a default visibility.
Visibility eVisibility = Visibility.Show;
String sVisibility;
if (
oRow.TryGetNonEmptyStringFromCell(
CommonTableColumnNames.Visibility, out sVisibility)
&&
!oEdgeVisibilityConverter.TryWorkbookToGraph(
sVisibility, out eVisibility)
)
{
OnInvalidVisibility(oRow);
}
if (eVisibility == Visibility.Skip)
{
// Skip the edge an continue to the next edge.
continue;
}
// Create the specified vertices or retrieve them from the
// dictionary.
IVertex oVertex1 = VertexNameToVertex(
sVertex1Name, oVertices, oVertexNameDictionary);
IVertex oVertex2 = VertexNameToVertex(
sVertex2Name, oVertices, oVertexNameDictionary);
// Add an edge connecting the vertices.
IEdge oEdge = oEdges.Add(oVertex1, oVertex2, bGraphIsDirected);
// If ReadWorkbookContext.FillIDColumns is true, add the edge to
// the edge row ID dictionary and set the edge's Tag to the row ID.
oReadWorkbookContext.AddToRowIDDictionary(oRow, oEdge, true);
if (bReadAllEdgeAndVertexColumns)
{
// All columns except the vertex names should be read and
// stored as metadata on the edge.
ReadAllColumns(oExcelTableReader, oRow, oEdge,
oColumnNamesToExclude);
continue;
}
if (eVisibility == Visibility.Hide)
{
// Hide the edge and continue to the next edge.
oEdge.SetValue(ReservedMetadataKeys.Visibility,
VisibilityKeyValue.Hidden);
continue;
}
// Alpha.
Boolean bAlphaIsZero = ReadAlpha(oRow, oEdge);
if (bAlphaIsZero)
{
continue;
}
// Color.
ReadColor(oRow, EdgeTableColumnNames.Color, oEdge,
ReservedMetadataKeys.PerColor,
oReadWorkbookContext.ColorConverter2);
// Width.
ReadWidth(oRow, oReadWorkbookContext.EdgeWidthConverter, oEdge);
// Style.
ReadStyle(oRow, oReadWorkbookContext.EdgeStyleConverter, oEdge);
// Label.
if (oReadWorkbookContext.ReadEdgeLabels)
{
ReadCellAndSetMetadata(oRow, EdgeTableColumnNames.Label, oEdge,
ReservedMetadataKeys.PerEdgeLabel);
ReadColor(oRow, EdgeTableColumnNames.LabelTextColor, oEdge,
ReservedMetadataKeys.PerEdgeLabelTextColor,
oReadWorkbookContext.ColorConverter2);
ReadLabelFontSize(oRow, oReadWorkbookContext.FontSizeConverter,
oEdge);
}
// Weight.
if (oReadWorkbookContext.ReadEdgeWeights)
{
ReadEdgeWeight(oRow, oEdge);
}
}
if (bReadAllEdgeAndVertexColumns)
{
// Store the edge column names on the graph.
oGraph.SetValue(ReservedMetadataKeys.AllEdgeMetadataKeys,
FilterColumnNames(oExcelTableReader, oColumnNamesToExclude));
}
}
