public void WriteRecordsTest()
{
var date = DateTime.Today;
var yesterday = DateTime.Today.AddDays(-1);
var records = new List <TestRecord> {
new TestRecord {
IntColumn = 1,
StringColumn = "string column",
IgnoredColumn = "ignored column",
FirstColumn = "first column",
TypeConvertedColumn = "written as test",
BoolColumn = true,
DoubleColumn = 12.34,
DateTimeColumn = date,
NullStringColumn = null,
FormulaColumn = "=1+2",
},
new TestRecord {
IntColumn = 2,
StringColumn = "string column 2",
IgnoredColumn = "ignored column 2",
FirstColumn = "first column 2",
TypeConvertedColumn = "written as test",
BoolColumn = false,
DoubleColumn = 43.21,
DateTimeColumn = yesterday,
NullStringColumn = null,
FormulaColumn = "not a formula",
},
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <TestRecordMap>();
excel.WriteRecords(records);
excel.ChangeSheet(2);
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
CheckRecords(sheet, date, yesterday);
Assert.AreEqual(3, book.Sheets.Count);
sheet = book.Sheets[2];
CheckRecords(sheet, date, yesterday);
}
}
}
}
public void NullReferenceTest()
{
var records = new List <A> {
new A {
Id = "1",
}
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <AMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new XLWorkbook(stream)) {
var sheet = book.Worksheets.Worksheet(1);
// Check the header row
Assert.AreEqual("AId", sheet.Cell(1, 1).Value);
Assert.AreEqual("BId", sheet.Cell(1, 2).Value);
Assert.AreEqual("CId", sheet.Cell(1, 3).Value);
Assert.AreEqual("DId", sheet.Cell(1, 4).Value);
// Check the first record
Assert.AreEqual("1", sheet.Cell(2, 1).Value);
Assert.AreEqual("", sheet.Cell(2, 2).Value);
Assert.AreEqual("", sheet.Cell(2, 3).Value);
Assert.AreEqual("", sheet.Cell(2, 4).Value);
}
}
}
}
public void SameNameMultipleTimesTest()
{
var records = new List <SameNameMultipleTimesClass> {
new SameNameMultipleTimesClass {
Name1 = "1",
Name2 = "2",
Name3 = "3"
}
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <SameNameMultipleTimesClassMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
// Check the header row
Assert.AreEqual("ColumnName", sheet[0, 0].Value);
Assert.AreEqual("ColumnName", sheet[0, 1].Value);
Assert.AreEqual("ColumnName", sheet[0, 2].Value);
// Check the first record
Assert.AreEqual("1", sheet[1, 0].Value);
Assert.AreEqual("2", sheet[1, 1].Value);
Assert.AreEqual("3", sheet[1, 2].Value);
}
}
}
}
public void WriteNoGetterTest()
{
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
var list = new List <TestPrivateGet> {
new TestPrivateGet {
ID = 1,
Name = "one"
}
};
excel.WriteRecords(list);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
Assert.AreEqual("ID", sheet[0, 0].Value);
Assert.AreEqual(null, sheet[0, 1].Value);
Assert.AreEqual((double)1, sheet[1, 0].Value);
Assert.AreEqual(null, sheet[1, 1].Value);
}
}
}
}
public void NestedReferencesTest()
{
var records = new List <A>();
for (var i = 0; i < 2; i++)
{
var row = i + 1;
records.Add(
new A {
Id = "a" + row,
B = new B {
Id = "b" + row,
C = new C {
Id = "c" + row,
D = new D {
Id = "d" + row
}
}
}
});
}
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <AMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new XLWorkbook(stream)) {
var sheet = book.Worksheets.Worksheet(1);
// Check the header row
Assert.AreEqual("AId", sheet.Cell(1, 1).Value);
Assert.AreEqual("BId", sheet.Cell(1, 2).Value);
Assert.AreEqual("CId", sheet.Cell(1, 3).Value);
Assert.AreEqual("DId", sheet.Cell(1, 4).Value);
// Check the first record
Assert.AreEqual("a1", sheet.Cell(2, 1).Value);
Assert.AreEqual("b1", sheet.Cell(2, 2).Value);
Assert.AreEqual("c1", sheet.Cell(2, 3).Value);
Assert.AreEqual("d1", sheet.Cell(2, 4).Value);
// Check the second record
Assert.AreEqual("a2", sheet.Cell(3, 1).Value);
Assert.AreEqual("b2", sheet.Cell(3, 2).Value);
Assert.AreEqual("c2", sheet.Cell(3, 3).Value);
Assert.AreEqual("d2", sheet.Cell(3, 4).Value);
}
}
}
}
public void WriteMultipleNamesTest()
{
var records = new List <MultipleNamesClass> {
new MultipleNamesClass {
IntColumn = 1,
StringColumn = "one"
},
new MultipleNamesClass {
IntColumn = 2,
StringColumn = "two"
}
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <MultipleNamesClassMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
// Check the header row
Assert.AreEqual("int1", sheet[0, 0].Value);
Assert.AreEqual("string1", sheet[0, 1].Value);
// Check the first record
Assert.AreEqual((double)1, sheet[1, 0].Value);
Assert.AreEqual("one", sheet[1, 1].Value);
// Check the second record
Assert.AreEqual((double)2, sheet[2, 0].Value);
Assert.AreEqual("two", sheet[2, 1].Value);
}
}
}
}
public void WriteRecordsNoIndexesTest()
{
var records = new List <TestRecordNoIndexes> {
new TestRecordNoIndexes {
IntColumn = 1,
StringColumn = "string column",
IgnoredColumn = "ignored column",
FirstColumn = "first column",
TypeConvertedColumn = "written as test",
},
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <TestRecordNoIndexesMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
// Check the header row
Assert.AreEqual("Int Column", sheet[0, 0].Value);
Assert.AreEqual("StringColumn", sheet[0, 1].Value);
Assert.AreEqual("FirstColumn", sheet[0, 2].Value);
Assert.AreEqual("TypeConvertedColumn", sheet[0, 3].Value);
// Check the first record
Assert.AreEqual((double)1, sheet[1, 0].Value);
Assert.AreEqual("string column", sheet[1, 1].Value);
Assert.AreEqual("first column", sheet[1, 2].Value);
Assert.AreEqual("test", sheet[1, 3].Value);
}
}
}
}
public void WriteRecordsWithReferencesTest()
{
var records = new List <Person> {
new Person {
FirstName = "First Name",
LastName = "Last Name",
HomeAddress = new Address {
Street = "Home Street",
City = "Home City",
State = "Home State",
Zip = "Home Zip",
ID = 2,
},
WorkAddress = new Address {
Street = "Work Street",
City = "Work City",
State = "Work State",
Zip = "Work Zip",
ID = 3,
},
NullAddress = null,
},
};
using (var stream = new MemoryStream()) {
using (var excel = new ExcelWriterC1(stream)) {
excel.Configuration.RegisterClassMap <PersonMap>();
excel.WriteRecords(records);
excel.Close();
stream.Position = 0;
using (var book = new C1XLBook()) {
book.Load(stream, FileFormat.OpenXml);
var sheet = book.Sheets[0];
// Check the header row
Assert.AreEqual("FirstName", sheet[0, 0].Value);
Assert.AreEqual("LastName", sheet[0, 1].Value);
Assert.AreEqual("HomeStreet", sheet[0, 2].Value);
Assert.AreEqual("HomeCity", sheet[0, 3].Value);
Assert.AreEqual("HomeState", sheet[0, 4].Value);
Assert.AreEqual("HomeZip", sheet[0, 5].Value);
Assert.AreEqual("HomeID", sheet[0, 6].Value);
Assert.AreEqual("WorkStreet", sheet[0, 7].Value);
Assert.AreEqual("WorkCity", sheet[0, 8].Value);
Assert.AreEqual("WorkState", sheet[0, 9].Value);
Assert.AreEqual("WorkZip", sheet[0, 10].Value);
Assert.AreEqual("WorkID", sheet[0, 11].Value);
Assert.AreEqual("NullStreet", sheet[0, 12].Value);
Assert.AreEqual("NullCity", sheet[0, 13].Value);
Assert.AreEqual("NullState", sheet[0, 14].Value);
Assert.AreEqual("NullZip", sheet[0, 15].Value);
Assert.AreEqual("NullID", sheet[0, 16].Value);
// Check the record
Assert.AreEqual("First Name", sheet[1, 0].Value);
Assert.AreEqual("Last Name", sheet[1, 1].Value);
Assert.AreEqual("Home Street", sheet[1, 2].Value);
Assert.AreEqual("Home City", sheet[1, 3].Value);
Assert.AreEqual("Home State", sheet[1, 4].Value);
Assert.AreEqual("Home Zip", sheet[1, 5].Value);
Assert.AreEqual(2.0, sheet[1, 6].Value);
Assert.AreEqual("Work Street", sheet[1, 7].Value);
Assert.AreEqual("Work City", sheet[1, 8].Value);
Assert.AreEqual("Work State", sheet[1, 9].Value);
Assert.AreEqual("Work Zip", sheet[1, 10].Value);
Assert.AreEqual(3.0, sheet[1, 11].Value);
Assert.AreEqual(null, sheet[1, 12].Value);
Assert.AreEqual(null, sheet[1, 13].Value);
Assert.AreEqual(null, sheet[1, 14].Value);
Assert.AreEqual(null, sheet[1, 15].Value);
Assert.AreEqual(0.0, sheet[1, 16].Value);
}
}
}
}
