public void ReadEnterpriseSiteArea()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(header);
WriteRow(writer, "A2", 1, "Enterprise", "", "My Enterprise");
WriteRow(writer, "A3", 2, "Site", "", "My Site");
WriteRow(writer, "A4", 3, "Area", "", "My Area");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
command.Execute();
}
Assert.That(hierarchy, Is.Not.Null);
Assert.That(hierarchy.GetCount(), Is.EqualTo(3));
Assert.That(hierarchy.Enterprise, Is.Not.Null);
Assert.That(hierarchy.Enterprise.Name, Is.EqualTo("My Enterprise"));
Assert.That(hierarchy.Enterprise.Site, Is.Not.Empty);
Assert.That(hierarchy.Enterprise.Site.Count, Is.EqualTo(1));
Assert.That(hierarchy.Enterprise.Site[0].Name, Is.EqualTo("My Site"));
Assert.That(hierarchy.Enterprise.Site[0].Area, Is.Not.Empty);
Assert.That(hierarchy.Enterprise.Site[0].Area[0].Name, Is.EqualTo("My Area"));
Assert.That(hierarchy.Enterprise.Site[0].Area[0].Area1, Is.Empty);
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (worksheetWriter == null) return;
worksheetWriter.Dispose();
worksheetWriter = null;
}
public void Read2SitesWithAreas()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(header);
WriteRow(writer, "A2", 1, "Enterprise", "", "My Enterprise");
WriteRow(writer, "A3", 2, "Site", "", "Site 1");
WriteRow(writer, "A4", 3, "Area", "", "Area A");
WriteRow(writer, "A5", 2, "Site", "", "Site 2");
WriteRow(writer, "A6", 3, "Area", "", "Area B");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
command.Execute();
}
Assert.That(hierarchy, Is.Not.Null);
Assert.That(hierarchy.GetCount(), Is.EqualTo(5));
Assert.That(hierarchy.Enterprise.FullName, Is.EqualTo("My Enterprise"));
Assert.That(hierarchy.Enterprise.Site[0].FullName, Is.EqualTo("My Enterprise.Site 1"));
Assert.That(hierarchy.Enterprise.Site[0].Area[0].FullName, Is.EqualTo("My Enterprise.Site 1.Area A"));
Assert.That(hierarchy.Enterprise.Site[1].FullName, Is.EqualTo("My Enterprise.Site 2"));
Assert.That(hierarchy.Enterprise.Site[1].Area[0].FullName, Is.EqualTo("My Enterprise.Site 2.Area B"));
}
}
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"));
}
}
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 AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
if (FileContentStream == null)
{
throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
}
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
if (ss == null)
{
throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
}
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
if (worksheetPart != null)
{
worksheetWriter.CreateErrorRows(worksheetPart);
worksheetPart.Worksheet.Save();
}
}
}
public void AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
if (FileContentStream == null)
{
throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
}
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
if (ss == null)
{
throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
}
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
if (worksheetPart != null)
{
worksheetWriter.CreateErrorRows(worksheetPart);
worksheetPart.Worksheet.Save();
}
}
}
private static void WriteRow(IWorksheetWriter writer, string address, params string[] values)
{
writer.MoveTo(address);
foreach (string value in values)
{
writer.Write(value);
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (worksheetWriter == null)
{
return;
}
worksheetWriter.Dispose();
worksheetWriter = null;
}
private static void WriteRow(IWorksheetWriter writer, string address, int depth, string isa95Type, string isa95Class, string name)
{
writer.MoveTo(address);
writer.Write(depth.ToString("0"));
writer.Write(isa95Type);
writer.Write(isa95Class);
for (int i = 1; i < depth; i++)
{
writer.Write("");
}
writer.Write(name);
}
private void WriteHeaderRow(IWorksheetWriter worksheetWriter)
{
foreach (string headerColumn in _headerColumns)
{
worksheetWriter
.SetBackgroundColor(Color.WhiteSmoke)
.Write(headerColumn)
.MoveRight();
}
worksheetWriter
.NewLine();
}
public void Dispose()
{
if (writer != null)
{
writer.Dispose();
writer = null;
}
if (spreadsheet != null)
{
spreadsheet.Dispose();
spreadsheet = null;
}
}
public void Dispose()
{
if (writer != null)
{
writer.Dispose();
writer = null;
}
if (spreadsheet != null)
{
spreadsheet.Dispose();
spreadsheet = null;
}
}
public void GetCurrentCell()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
using (IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests"))
{
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("A1"));
writer.MoveTo("B2");
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("B2"));
writer.MoveTo(10, 20);
Assert.That(writer.GetCurrentCell().Address, Is.EqualTo("T10"));
}
}
}
private void WriteTestValues()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
IWorksheetWriter writer = spreadsheet.WriteToWorksheet("UnitTests");
writer.WriteRow(new List <string> {
"A-1", "B-1", "C-1"
});
writer.WriteRow(new List <string> {
"A-2", "B-2", "C-2"
});
writer.WriteRow(new List <string> {
"A-3", "B-3", "C-3"
});
}
}
public void ReadInvalidDepth()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(header);
WriteRow(writer, "A2", 10, "Enterprise", "", "My Enterprise");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
Assert.Throws <InvalidOperationException>(command.Execute);
}
}
}
public ExcelFileGenerator(ExcelColumnFactory factory,
IWorksheetWriter worksheetWriter,
ISharedStringsWriter sharedStringsWriter,
IStylesWriter stylesWriter,
Stream templateStream,
CompressionStrategy compressionStrategy,
CompressionLevel compressionLevel,
Func <IEnumerable <PropertyInfo>, IEnumerable <PropertyInfo> > propertiesFilter)
{
_factory = factory;
_worksheetWriter = worksheetWriter;
_sharedStringsWriter = sharedStringsWriter;
_stylesWriter = stylesWriter;
_templateStream = templateStream;
_compressionStrategy = compressionStrategy;
_compressionLevel = compressionLevel;
_propertiesFilter = propertiesFilter;
}
public void ReadInvalidDepth()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(header);
WriteRow(writer, "A2", "", "", "", "My Enterprise");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
Assert.That(ex.Message, Is.StringContaining("My Enterprise").And.StringContaining("Level 4"));
}
}
}
public void ReadOnlyWorksheet()
{
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.CreateNew(Filename))
{
Assert.That(spreadsheet.IsReadOnly, Is.False);
IWorksheetWriter worksheet = spreadsheet.WriteToWorksheet("New sheet");
worksheet.WriteRow(new List <string> {
"One", "Two", "Three"
});
Assert.That(worksheet, Is.Not.Null);
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Assert.That(spreadsheet.IsReadOnly, Is.True);
Assert.Throws <InvalidOperationException>(() => spreadsheet.WriteToWorksheet("Should fail"));
}
}
public void HeaderWithIncorrectNames()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(new List <string> {
"Column 1", "Column 2", "Column 3", "Column 4"
});
WriteRow(writer, "A2", "My Enterprise");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
Assert.That(ex.Message, Is.StringContaining("Column 1").And.StringContaining("Level 1"));
}
}
}
public void InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
byte[] byteArray = File.ReadAllBytes(templatePath);
MemoryStream stream = new MemoryStream(byteArray);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
if (worksheetPart != null)
{
worksheetWriter.CreateHeader(worksheetPart);
}
}
FileContentStream = stream;
}
public void ReadEmpty()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(header);
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
command.Execute();
}
Assert.That(hierarchy, Is.Not.Null);
Assert.That(hierarchy.Enterprise, Is.Null);
Assert.That(hierarchy.GetCount(), Is.EqualTo(0));
}
}
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 InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
byte[] byteArray = File.ReadAllBytes(templatePath);
MemoryStream stream = new MemoryStream(byteArray);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
if (worksheetPart != null)
{
worksheetWriter.CreateHeader(worksheetPart);
}
}
FileContentStream = stream;
}
public void Level2HeaderWithGreaterDepth()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(new List <string> {
"Level 1", "Level 2"
});
WriteRow(writer, "A2", "My Enterprise");
WriteRow(writer, "A3", "", "My Site");
WriteRow(writer, "A4", "", "", "My Area");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
Exception ex = Assert.Throws <InvalidOperationException>(command.Execute);
Assert.That(ex.Message, Is.StringContaining("row 4"));
}
}
}
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 Level2HeaderEnterpriseOnly()
{
using (IWorksheetWriter writer = SetupWorksheet("Hierarchy"))
{
writer.WriteRow(new List <string> {
"Level 1", "Level 2"
});
WriteRow(writer, "A2", "My Enterprise");
}
using (IExcelSpreadsheet spreadsheet = ExcelSpreadsheet.OpenReadOnly(Filename))
{
Hierarchy hierarchy = Hierarchy.Empty();
using (ICommand command = CreateCommand(spreadsheet, hierarchy))
{
command.Execute();
}
Assert.That(hierarchy, Is.Not.Null);
Assert.That(hierarchy.Enterprise, Is.Not.Null);
Assert.That(hierarchy.Enterprise.Name, Is.EqualTo("My Enterprise"));
Assert.That(hierarchy.GetCount(), Is.EqualTo(1));
}
}
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));
}
public Worksheet(string filename, string name)
{
spreadsheet = ExcelSpreadsheet.CreateNew(filename);
writer = spreadsheet.WriteToWorksheet(name);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelWriterCommand"/> class.
/// </summary>
/// <param name="spreadsheet">The spreadsheet.</param>
/// <param name="name">The name.</param>
protected ExcelWriterCommand(IExcelSpreadsheet spreadsheet, string name)
{
worksheetWriter = spreadsheet.WriteToWorksheet(name);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelWriterCommand"/> class.
/// </summary>
/// <param name="spreadsheet">The spreadsheet.</param>
/// <param name="name">The name.</param>
protected ExcelWriterCommand(IExcelSpreadsheet spreadsheet, string name)
{
worksheetWriter = spreadsheet.WriteToWorksheet(name);
}
private static void WriteRow(IWorksheetWriter writer, string address, int depth, string isa95Type, string isa95Class, string name)
{
writer.MoveTo(address);
writer.Write(depth.ToString("0"));
writer.Write(isa95Type);
writer.Write(isa95Class);
for (int i = 1; i < depth; i++)
{
writer.Write("");
}
writer.Write(name);
}
private static void WriteRow(IWorksheetWriter writer, string address, params string[] values)
{
writer.MoveTo(address);
foreach (string value in values)
{
writer.Write(value);
}
}
public Worksheet(string filename, string name)
{
spreadsheet = ExcelSpreadsheet.CreateNew(filename);
writer = spreadsheet.WriteToWorksheet(name);
}