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