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 WriteCellTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriterC1(stream)) { // Set up our row and column formats first excel.SetRowFormat(1, fontStyle: FontStyle.Bold, fontSize: 16); excel.SetColumnFormat(7, fontStyle: FontStyle.Italic, fontSize: 24); var date = DateTime.Today; var guid = new Guid("bfb9c599-bc9e-4f97-ae59-25f2ca09cfdf"); excel.WriteCell(0, 0, "one"); excel.WriteCell(0, 1, "one, two", fontStyle: FontStyle.Bold); excel.WriteCell(0, 2, "one \"two\" three", fontSize: 18); excel.WriteCell(0, 3, " one ", fontName: "Times"); excel.WriteCell(0, 4, date); excel.WriteCell(0, 5, date, null, "d"); excel.WriteCell(0, 6, date, null, "D", FontStyle.Bold); excel.WriteCell(0, 7, (byte)1); excel.WriteCell(0, 8, (short)2); excel.WriteCell(0, 9, 3); excel.WriteCell(0, 10, "=1+2"); excel.WriteCell(1, 0, (long)4); excel.WriteCell(1, 1, (float)5); excel.WriteCell(1, 2, (double)6); excel.WriteCell(1, 3, (decimal)123.456, "C"); excel.WriteCell(1, 4, guid); excel.WriteCell(1, 5, true); excel.WriteCell(1, 6, false); excel.WriteCell(1, 7, (string)null); excel.WriteCell(1, 8, new TimeSpan(1, 2, 3)); excel.WriteCell(1, 9, new TimeSpan(1, 2, 3), "g"); excel.WriteCell(1, 10, "=2*3"); // Auto size the columns excel.AdjustColumnsToContent(0, 10000); // Override some column and row sizes excel.SetRowHeight(1, 600); excel.SetColumnWidth(1, 500); excel.SetColumnWidth(11, 700); // Change to third sheet and write a cell excel.ChangeSheet(2); excel.WriteCell(0, 0, "third sheet"); excel.Close(); stream.Position = 0; using (var book = new C1XLBook()) { book.Load(stream, FileFormat.OpenXml); var sheet = book.Sheets[0]; // Verify row and column styles Assert.AreEqual(FontStyle.Bold, sheet.Rows[1].Style.Font.Style); Assert.AreEqual(16.0, sheet.Rows[1].Style.Font.SizeInPoints); Assert.AreEqual(FontStyle.Italic, sheet.Columns[7].Style.Font.Style); Assert.AreEqual(24.0, sheet.Columns[7].Style.Font.SizeInPoints); // Verify the overridden row and column sizes Assert.AreEqual(600, sheet.Rows[1].Height); Assert.AreEqual(495, sheet.Columns[1].Width); Assert.AreEqual(700, sheet.Columns[11].Width); // Check some automatically sizes column widths Assert.AreEqual(2655, sheet.Columns[2].Width); Assert.AreEqual(2347, sheet.Columns[4].Width); // Verify first row Assert.AreEqual("one", sheet[0, 0].Value); Assert.AreEqual("", sheet[0, 0].Style.Format); Assert.AreEqual("one, two", sheet[0, 1].Value); Assert.AreEqual(FontStyle.Bold, sheet[0, 1].Style.Font.Style); Assert.AreEqual("one \"two\" three", sheet[0, 2].Value); Assert.AreEqual(18.0, sheet[0, 2].Style.Font.SizeInPoints); Assert.AreEqual(" one ", sheet[0, 3].Value); Assert.AreEqual("Times New Roman", sheet[0, 3].Style.Font.Name); Assert.AreEqual(date, sheet[0, 4].Value); Assert.AreEqual(@"m\/D\/YYYY\ H:mm:ss\ AM/PM", sheet[0, 4].Style.Format); Assert.AreEqual(date, sheet[0, 5].Value); Assert.AreEqual(@"m\/D\/YYYY", sheet[0, 5].Style.Format); Assert.AreEqual(date, sheet[0, 6].Value); Assert.AreEqual(@"DDDD,\ mmmm\ D,\ YYYY", sheet[0, 6].Style.Format); Assert.AreEqual(FontStyle.Bold, sheet[0, 6].Style.Font.Style); Assert.AreEqual((double)1, sheet[0, 7].Value); Assert.AreEqual((double)2, sheet[0, 8].Value); Assert.AreEqual((double)3, sheet[0, 9].Value); Assert.AreEqual("=1+2", sheet[0, 10].Formula); Assert.AreEqual(null, sheet[0, 10].Value); // Verify second row Assert.AreEqual((double)4, sheet[1, 0].Value); Assert.AreEqual("", sheet[1, 0].Style.Format); Assert.AreEqual((double)5, sheet[1, 1].Value); Assert.AreEqual((double)6, sheet[1, 2].Value); Assert.AreEqual(123.456, sheet[1, 3].Value); Assert.AreEqual(@"$#,##0.00;($#,##0.00)", sheet[1, 3].Style.Format); Assert.AreEqual("bfb9c599-bc9e-4f97-ae59-25f2ca09cfdf", sheet[1, 4].Value); Assert.AreEqual("true", sheet[1, 5].Value); Assert.AreEqual("false", sheet[1, 6].Value); Assert.AreEqual(null, sheet[1, 7].Value); Assert.AreEqual("01:02:03", sheet[1, 8].Value); Assert.AreEqual("", sheet[1, 8].Style.Format); Assert.AreEqual("01:02:03", sheet[1, 9].Value); Assert.AreEqual("", sheet[1, 9].Style.Format); Assert.AreEqual("=2*3", sheet[1, 10].Formula); Assert.AreEqual(null, sheet[1, 10].Value); // Verify third sheet Assert.AreEqual(3, book.Sheets.Count); sheet = book.Sheets[2]; Assert.AreEqual("third sheet", sheet[0, 0].Value); } } } }