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