private IEnumerable <int> GetNextRowIndexesContainingDates(
IWorksheetReader worksheetReader,
IEnumerable <ExcelCellCoordinates> nextRowsCoordinatesCollection)
{
if (nextRowsCoordinatesCollection.Count() == 0)
{
return(new int[0]);
}
var worksheetIndex = nextRowsCoordinatesCollection.First().WorksheetIndex;
var datesColumnIndex = nextRowsCoordinatesCollection.First().ColumnIndex;
return(nextRowsCoordinatesCollection
.Select(coordinates => coordinates.RowIndex)
.Where(rowIndex =>
{
try
{
var nextRowCoordinates = new ExcelCellCoordinates(worksheetIndex, rowIndex, datesColumnIndex);
return worksheetReader.GetCellDateTimeAsUTC(nextRowCoordinates) != default(DateTime);
}
catch
{
return false;
}
})
.ToArray());
}
public void ReadRowAfterMoveUsingAddress()
{
WriteTestValues();
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
reader.MoveTo("B1");
List <string> row1 = reader.ReadRow();
List <string> row2 = reader.ReadRow();
Assert.That(row1, Is.EquivalentTo(new List <string> {
"B-1", "C-1"
}));
Assert.That(row2, Is.EquivalentTo(new List <string> {
"B-2", "C-2"
}));
reader.MoveTo("A1");
Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
"A-1", "B-1", "C-1"
}));
Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
"A-2", "B-2", "C-2"
}));
}
}
private IEnumerable <int> GetRowIndexesContainingDates(
IWorksheetReader worksheetReader,
ExcelCellCoordinates firstDateCellCoordinates,
int maxNumberOfEmptyRowsInBetween)
{
var firstDatesRowIndex = firstDateCellCoordinates.RowIndex;
var datesColumnIndex = firstDateCellCoordinates.ColumnIndex;
var worksheetIndex = firstDateCellCoordinates.WorksheetIndex;
var rowNumbersContainingDates = new HashSet <int>();
for (int currentRowIndex = firstDatesRowIndex; ; currentRowIndex++)
{
var currentRowCoordinates = new ExcelCellCoordinates(worksheetIndex, currentRowIndex, datesColumnIndex);
var nextRowsCoordinates = GetCellCoordinatesForNextNRows(currentRowCoordinates, n: maxNumberOfEmptyRowsInBetween);
var nextRowIndexesContainingDates = GetNextRowIndexesContainingDates(worksheetReader, nextRowsCoordinates);
if (nextRowIndexesContainingDates.Count() == 0)
{
break;
}
rowNumbersContainingDates.UnionWith(nextRowIndexesContainingDates.ToHashSet());
}
return(rowNumbersContainingDates);
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (worksheetReader == null) return;
worksheetReader.Dispose();
worksheetReader = null;
}
public void Write()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
{
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
writer.Write("One");
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B1"));
writer.MoveTo("D3");
writer.Write("New value at D3");
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("E3"));
}
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
reader.MoveTo("A1");
Assert.That(reader.Read(), Is.EqualTo("One"));
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B1"));
reader.MoveTo("D3");
Assert.That(reader.Read(), Is.EqualTo("New value at D3"));
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("E3"));
}
}
private int ValidateHeader(IWorksheetReader worksheetReader)
{
worksheetReader.MoveTo("A1");
int depth = 0;
foreach (string expected in header)
{
string address = worksheetReader.GetCurrentCell().Address;
string column = worksheetReader.Read();
if (column == string.Empty)
{
return(depth);
}
if (column != expected)
{
string message = string.Format("Invalid header at {0}: Expected: '{1}' Actual: '{2}'", address, expected, column);
throw new InvalidOperationException(message);
}
depth++;
}
if (depth == 9)
{
List <string> nextColumns = worksheetReader.ReadRow();
{
if (nextColumns.Count > 0)
{
string message = string.Format("Invalid header. No more columns expected but found {0}",
string.Join(",", nextColumns));
throw new InvalidOperationException(message);
}
}
}
return(depth);
}
public void WriteEnterpriseSiteArea()
{
Hierarchy hierarchy = new Hierarchy();
hierarchy.Enterprise = new Enterprise("My Company");
hierarchy.Enterprise.Site.Add(new Site("My Site"));
hierarchy.Enterprise.Site[0].Area.Add(new Area("My Area"));
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
HierarchyWriterCommand command = new HierarchyWriterCommand(hierarchy, spreadsheet);
command.Execute();
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
using (IWorksheetReader reader = spreadsheet.ReadWorksheet("Hierarchy"))
{
reader.MoveTo("A1");
Assert.That(reader.ReadRow(), Is.EquivalentTo(expectedHeader));
CheckRow(reader, "A2", 1, "Enterprise", string.Empty, "My Company");
CheckRow(reader, "A3", 2, "Site", string.Empty, "My Site");
CheckRow(reader, "A4", 3, "Area", string.Empty, "My Area");
}
}
}
public void Write2Rows()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
{
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
writer.WriteRow(new List <string> {
"One", "Two", "Three"
});
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A2"));
writer.WriteRow(new List <string> {
"Four", "Five", "Six"
});
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A3"));
}
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
List <string> list = reader.ReadRow();
Assert.That(list, Is.Not.Empty);
Assert.That(list, Is.EquivalentTo(new List <string> {
"One", "Two", "Three"
}));
list = reader.ReadRow();
Assert.That(list, Is.Not.Empty);
Assert.That(list, Is.EquivalentTo(new List <string> {
"Four", "Five", "Six"
}));
}
}
public void GetCurrentCellAfterMoveRowColumn()
{
WriteTestValues();
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("A1"));
Assert.That(reader.GetCurrentCell().Row, Is.EqualTo(1));
Assert.That(reader.GetCurrentCell().Column, Is.EqualTo(1));
Assert.That(reader.GetCurrentCell().Value, Is.EqualTo("A-1"));
reader.MoveTo(2, 1);
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("A2"));
Assert.That(reader.GetCurrentCell().Row, Is.EqualTo(2));
Assert.That(reader.GetCurrentCell().Column, Is.EqualTo(1));
Assert.That(reader.GetCurrentCell().Value, Is.EqualTo("A-2"));
reader.MoveTo(1, 3);
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("C1"));
Assert.That(reader.GetCurrentCell().Row, Is.EqualTo(1));
Assert.That(reader.GetCurrentCell().Column, Is.EqualTo(3));
Assert.That(reader.GetCurrentCell().Value, Is.EqualTo("C-1"));
}
}
private Item ReadFromRow(int row, IWorksheetReader reader, Item[] history)
{
Item item = null;
reader.MoveTo(row, 1);
string firstValue = reader.Read();
if (firstValue != string.Empty)
{
int depth;
int.TryParse(firstValue, out depth);
string isa95Type = reader.Read();
string isa95Class = reader.Read();
if ((depth < 1) || (depth > 9))
{
throw new InvalidOperationException("Depth must be between 1 and 9");
}
reader.MoveTo(row, depth + 3);
string name = reader.Read();
switch (isa95Type)
{
case "Enterprise":
{
item = new Enterprise(name);
break;
}
case "Site":
{
item = new Site(name);
break;
}
case "Area":
{
item = new Area(name);
break;
}
case "WorkCentre":
{
item = new WorkCentre(name);
break;
}
default:
{
throw new InvalidOperationException("Invalid ISA95 type:" + isa95Type);
}
}
history[depth] = item;
history[depth - 1].AddItem(item);
}
return(item);
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (worksheetReader == null)
{
return;
}
worksheetReader.Dispose();
worksheetReader = null;
}
private Item ReadFromRow(int row, IWorksheetReader reader, Item[] history)
{
Item item = null;
reader.MoveTo(row, 1);
string firstValue = reader.Read();
if (firstValue != string.Empty)
{
int depth;
int.TryParse(firstValue, out depth);
string isa95Type = reader.Read();
string isa95Class = reader.Read();
if ((depth < 1) || (depth > 9))
{
throw new InvalidOperationException("Depth must be between 1 and 9");
}
reader.MoveTo(row, depth + 3);
string name = reader.Read();
switch (isa95Type)
{
case "Enterprise":
{
item = new Enterprise(name);
break;
}
case "Site":
{
item = new Site(name);
break;
}
case "Area":
{
item = new Area(name);
break;
}
case "WorkCentre":
{
item = new WorkCentre(name);
break;
}
default:
{
throw new InvalidOperationException("Invalid ISA95 type:" + isa95Type);
}
}
history[depth] = item;
history[depth - 1].AddItem(item);
}
return item;
}
private static void CheckRow(IWorksheetReader reader, string address, int depth, string isa95Type, string isa95Class, string name)
{
reader.MoveTo(address);
Assert.That(reader.Read(), Is.EqualTo(depth.ToString("0")));
Assert.That(reader.Read(), Is.EqualTo(isa95Type));
Assert.That(reader.Read(), Is.EqualTo(isa95Class));
for (int i = 1; i < depth; i++)
{
Assert.That(reader.Read(), Is.EqualTo(string.Empty), "Skip level {0}", i);
}
Assert.That(reader.Read(), Is.EqualTo(name));
}
public void MoveToAddress()
{
WriteTestValues();
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
reader.MoveTo("A1");
Assert.That(reader.Read(), Is.EqualTo("A-1"));
reader.MoveTo("B2");
Assert.That(reader.Read(), Is.EqualTo("B-2"));
}
}
private void ValidateHeader(IWorksheetReader worksheetReader)
{
worksheetReader.MoveTo("A1");
foreach (string expected in header)
{
string address = worksheetReader.GetCurrentCell().Address;
string column = worksheetReader.Read();
if (column != expected)
{
string message = string.Format("Invalid header at {0}: Expected: '{1}' Actual: '{2}'", address, expected, column);
throw new InvalidOperationException(message);
}
}
}
public void IsEndOfDataEmpty()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
using (spreadsheet.WriteToWorksheet("UnitTests"))
{
}
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
using (IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests"))
{
Assert.That(reader.IsEndOfData(), Is.True);
}
}
}
public void ReadRow()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests");
List <string> list = new List <string> {
"One", "Two", "Three"
};
writer.WriteRow(list);
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests");
List <string> list = reader.ReadRow();
Assert.That(list, Is.Not.Empty);
Assert.That(list, Is.EquivalentTo(new List <string> {
"One", "Two", "Three"
}));
}
}
public void MoveToAndWrite()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
{
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
writer.MoveTo("B2");
writer.WriteRow(new List <string> {
"One", "Two", "Three"
});
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B3"));
writer.WriteRow(new List <string> {
"Four", "Five", "Six"
});
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B4"));
}
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
using (IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests"))
{
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("A1"));
Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string>()));
reader.MoveTo("B2");
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B2"));
Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
"One", "Two", "Three"
}));
Assert.That(reader.GetCurrentCell().Address, Is.EqualTo("B3"));
Assert.That(reader.ReadRow(), Is.EquivalentTo(new List <string> {
"Four", "Five", "Six"
}));
}
}
}
public void IsEndOfData()
{
WriteTestValues();
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
using (IWorksheetReader reader = spreadsheet.ReadWorksheet("UnitTests"))
{
string[] falseAddresses = new[] { "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", "D1", "D2" };
string[] trueAddresses = new [] { "A4", "A5", "B4", "B5", "C4", "C5", "D3", "D4", "D5" };
foreach (string address in falseAddresses)
{
reader.MoveTo(address);
Assert.That(reader.IsEndOfData(), Is.False, "EndOfData = false for {0}", address);
}
foreach (string address in trueAddresses)
{
reader.MoveTo(address);
Assert.That(reader.IsEndOfData(), Is.True, "EndOfData = true for {0}", address);
}
}
}
}
public void ReadAndWrite()
{
List <string> list = new List <string> {
"One", "Two", "Three"
};
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
IWorksheetWriter worksheet = spreadsheet.WriteToWorksheet("New sheet");
Assert.That(worksheet, Is.Not.Null);
worksheet.WriteRow(list);
}
List <string> result;
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
IWorksheetReader worksheet = spreadsheet.ReadWorksheet("New sheet");
Assert.That(worksheet, Is.Not.Null);
result = worksheet.ReadRow();
}
Assert.That(result, Is.EquivalentTo(list));
}
private static void CheckRow(IWorksheetReader reader, string address, int depth, string isa95Type, string isa95Class, string name)
{
reader.MoveTo(address);
Assert.That(reader.Read(), Is.EqualTo(depth.ToString("0")));
Assert.That(reader.Read(), Is.EqualTo(isa95Type));
Assert.That(reader.Read(), Is.EqualTo(isa95Class));
for (int i = 1; i < depth; i++)
{
Assert.That(reader.Read(), Is.EqualTo(string.Empty), "Skip level {0}", i);
}
Assert.That(reader.Read(), Is.EqualTo(name));
}
private Item ReadFromRow(int row, IWorksheetReader reader, Item[] history, int maxDepth)
{
Item item = null;
reader.MoveTo(row, 1);
int depth = 1;
string value = reader.Read();
while (value == string.Empty && depth <= maxDepth)
{
depth++;
value = reader.Read();
}
if (depth <= maxDepth)
{
string name = value;
switch (depth)
{
case 1:
{
item = new Enterprise(name);
break;
}
case 2:
{
item = new Site(name);
break;
}
case 3:
{
item = new Area(name);
break;
}
case 4:
{
item = new WorkCentre(name);
break;
}
default:
{
throw new InvalidOperationException("Maximum depth currently supported is 4");
}
}
history[depth] = item;
Item parent = history[depth - 1];
if (parent == null)
{
string message = string.Format("Unable to add {0} at Level {1} as there is no parent.", item,
depth);
throw new InvalidOperationException(message);
}
parent.AddItem(item);
}
else
{
reader.MoveTo(row, 1);
if (!reader.IsEndOfData())
{
string message = string.Format("Unable to read item from row {0}", row);
throw new InvalidOperationException(message);
}
}
return(item);
}
public SpendingsExcelDataParser(IWorksheetReader worksheetReader)
{
this.worksheetReader = worksheetReader;
}
private int ValidateHeader(IWorksheetReader worksheetReader)
{
worksheetReader.MoveTo("A1");
int depth = 0;
foreach (string expected in header)
{
string address = worksheetReader.GetCurrentCell().Address;
string column = worksheetReader.Read();
if (column == string.Empty)
{
return depth;
}
if (column != expected)
{
string message = string.Format("Invalid header at {0}: Expected: '{1}' Actual: '{2}'", address, expected, column);
throw new InvalidOperationException(message);
}
depth++;
}
if (depth == 9)
{
List<string> nextColumns = worksheetReader.ReadRow();
{
if (nextColumns.Count > 0)
{
string message = string.Format("Invalid header. No more columns expected but found {0}",
string.Join(",", nextColumns));
throw new InvalidOperationException(message);
}
}
}
return depth;
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelReaderCommand"/> class.
/// </summary>
/// <param name="spreadsheet">The spreadsheet.</param>
/// <param name="name">The name.</param>
protected ExcelReaderCommand(IExcelSpreadsheet spreadsheet, string name)
{
worksheetReader = spreadsheet.ReadWorksheet(name);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelReaderCommand"/> class.
/// </summary>
/// <param name="spreadsheet">The spreadsheet.</param>
/// <param name="name">The name.</param>
protected ExcelReaderCommand(IExcelSpreadsheet spreadsheet, string name)
{
worksheetReader = spreadsheet.ReadWorksheet(name);
}
private Item ReadFromRow(int row, IWorksheetReader reader, Item[] history, int maxDepth)
{
Item item = null;
reader.MoveTo(row, 1);
int depth = 1;
string value = reader.Read();
while (value == string.Empty && depth <= maxDepth)
{
depth++;
value = reader.Read();
}
if (depth <= maxDepth)
{
string name = value;
switch (depth)
{
case 1:
{
item = new Enterprise(name);
break;
}
case 2:
{
item = new Site(name);
break;
}
case 3:
{
item = new Area(name);
break;
}
case 4:
{
item = new WorkCentre(name);
break;
}
default:
{
throw new InvalidOperationException("Maximum depth currently supported is 4");
}
}
history[depth] = item;
Item parent = history[depth - 1];
if (parent == null)
{
string message = string.Format("Unable to add {0} at Level {1} as there is no parent.", item,
depth);
throw new InvalidOperationException(message);
}
parent.AddItem(item);
}
else
{
reader.MoveTo(row, 1);
if (!reader.IsEndOfData())
{
string message = string.Format("Unable to read item from row {0}", row);
throw new InvalidOperationException(message);
}
}
return item;
}
private void ValidateHeader(IWorksheetReader worksheetReader)
{
worksheetReader.MoveTo("A1");
foreach (string expected in header)
{
string address = worksheetReader.GetCurrentCell().Address;
string column = worksheetReader.Read();
if (column != expected)
{
string message = string.Format("Invalid header at {0}: Expected: '{1}' Actual: '{2}'", address, expected, column);
throw new InvalidOperationException(message);
}
}
}
