public static void ExportToExcel(DataSet dataSet, string fileName) { //export a DataSet to Excel DialogResult retry = DialogResult.Retry; while (retry == DialogResult.Retry) { try { using (ExcelWriter writer = new ExcelWriter(fileName)) { writer.WriteStartDocument(); foreach (DataTable data in dataSet.Tables) { string wsName = data.TableName; // Write the worksheet contents writer.WriteStartWorksheet(wsName); //Write header row writer.WriteStartRow(); foreach (DataColumn col in data.Columns) { writer.WriteExcelUnstyledCell(col.Caption); } writer.WriteEndRow(); //write data foreach (DataRow row in data.Rows) { writer.WriteStartRow(); foreach (object o in row.ItemArray) { writer.WriteExcelAutoStyledCell(o); } writer.WriteEndRow(); } writer.WriteEndWorksheet(); } // Close up the document writer.WriteEndDocument(); writer.Close(); retry = DialogResult.Cancel; } } catch (Exception myException) { retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk); } } }
public void NullReferenceTest() { var records = new List <A> { new A { Id = "1", } }; using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(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 LargeFileTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(stream)) { // Write out 66K rows for (var i = 0; i < 66000; i++) { excel.WriteCell(i, 0, i.ToString()); } excel.Close(); // Now read it back stream.Position = 0; using (var book = new C1XLBook()) { book.Load(stream, FileFormat.OpenXml); var sheet = book.Sheets[0]; // Verify 66K rows for (var i = 0; i < 66000; i++) { Assert.AreEqual(i.ToString(), sheet[i, 0].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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <SameNameMultipleTimesClassMap>(); 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("ColumnName", sheet.Cell(1, 1).Value); Assert.AreEqual("ColumnName", sheet.Cell(1, 2).Value); Assert.AreEqual("ColumnName", sheet.Cell(1, 3).Value); // Check the first record Assert.AreEqual("1", sheet.Cell(2, 1).Value); Assert.AreEqual("2", sheet.Cell(2, 2).Value); Assert.AreEqual("3", sheet.Cell(2, 3).Value); } } } }
public void NullReferenceTest() { var records = new List <A> { new A { Id = "1", } }; using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <AMap>(); 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("AId", sheet[0, 0].Value); Assert.AreEqual("BId", sheet[0, 1].Value); Assert.AreEqual("CId", sheet[0, 2].Value); Assert.AreEqual("DId", sheet[0, 3].Value); // Check the first record Assert.AreEqual("1", sheet[1, 0].Value); Assert.AreEqual(null, sheet[1, 1].Value); Assert.AreEqual(null, sheet[1, 2].Value); Assert.AreEqual(null, sheet[1, 3].Value); } } } }
public void LargeFileTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(stream)) { // Write out 66K rows for (var i = 0; i < 66000; i++) { excel.WriteCell(i, 0, i.ToString()); } excel.Close(); // Now read it back stream.Position = 0; using (var book = new XLWorkbook(stream)) { var sheet = book.Worksheets.Worksheet(1); // Verify 66K rows for (var i = 0; i < 66000; i++) { Assert.AreEqual(i.ToString(), sheet.Cell(i + 1, 1).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 ExcelWriter(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 ExcelWriter(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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <AMap>(); 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("AId", sheet[0, 0].Value); Assert.AreEqual("BId", sheet[0, 1].Value); Assert.AreEqual("CId", sheet[0, 2].Value); Assert.AreEqual("DId", sheet[0, 3].Value); // Check the first record Assert.AreEqual("a1", sheet[1, 0].Value); Assert.AreEqual("b1", sheet[1, 1].Value); Assert.AreEqual("c1", sheet[1, 2].Value); Assert.AreEqual("d1", sheet[1, 3].Value); // Check the second record Assert.AreEqual("a2", sheet[2, 0].Value); Assert.AreEqual("b2", sheet[2, 1].Value); Assert.AreEqual("c2", sheet[2, 2].Value); Assert.AreEqual("d2", sheet[2, 3].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 ExcelWriter(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 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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <TestRecordMap>(); excel.WriteRecords(records); excel.ChangeSheet(2); excel.WriteRecords(records, false); 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, false); } } } }
/// <summary> /// Show Dialog to select suites and make xlsx file with them. /// </summary> /// <param name="fileName"></param> public void GenerateExcelFile(string fileName) { SuiteExportChoseForm exportDialog = new SuiteExportChoseForm(this.suiteTreeView.Nodes); if (exportDialog.ShowDialog() == DialogResult.OK) { List <TestSuite> suites = this.MakeSuiteList(exportDialog.Nodes); ExcelWriter file = new ExcelWriter(fileName); foreach (TestSuite suite in suites) { file.GenerateSuiteSheet(suite); } file.Save(); file.Close(); } }
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 ExcelWriter(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 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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <MultipleNamesClassMap>(); 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("int1", sheet.Cell(1, 1).Value); Assert.AreEqual("string1", sheet.Cell(1, 2).Value); // Check the first record Assert.AreEqual((double)1, sheet.Cell(2, 1).Value); Assert.AreEqual("one", sheet.Cell(2, 2).Value); // Check the second record Assert.AreEqual((double)2, sheet.Cell(3, 1).Value); Assert.AreEqual("two", sheet.Cell(3, 2).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 ExcelWriter(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 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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <TestRecordNoIndexesMap>(); 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("Int Column", sheet.Cell(1, 1).Value); Assert.AreEqual("StringColumn", sheet.Cell(1, 2).Value); Assert.AreEqual("FirstColumn", sheet.Cell(1, 3).Value); Assert.AreEqual("TypeConvertedColumn", sheet.Cell(1, 4).Value); // Check the first record Assert.AreEqual((double)1, sheet.Cell(2, 1).Value); Assert.AreEqual("string column", sheet.Cell(2, 2).Value); Assert.AreEqual("first column", sheet.Cell(2, 3).Value); Assert.AreEqual("test", sheet.Cell(2, 4).Value); } } } }
public void WriteNoGetterTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(stream)) { var list = new List <TestPrivateGet> { new TestPrivateGet { ID = 1, Name = "one" } }; excel.WriteRecords(list); excel.Close(); stream.Position = 0; using (var book = new XLWorkbook(stream)) { var sheet = book.Worksheets.Worksheet(1); Assert.AreEqual("ID", sheet.Cell(1, 1).Value); Assert.AreEqual("", sheet.Cell(1, 2).Value); Assert.AreEqual((double)1, sheet.Cell(2, 1).Value); Assert.AreEqual("", sheet.Cell(2, 2).Value); } } } }
public void WriteCellTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(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.Parse("2017-10-16 03:05 PM"); 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 New Roman"); excel.WriteCell(0, 4, date); excel.WriteCell(0, 5, date, dateFormat: "M/d/yyyy h:mm:ss tt"); excel.WriteCell(0, 6, date, dateFormat: "M/d/yyyy"); excel.WriteCell(0, 7, date, dateFormat: "dddd, MMMM d, yyyy", fontStyle: FontStyle.Bold); excel.WriteCell(0, 8, (byte)1); excel.WriteCell(0, 9, (short)2); excel.WriteCell(0, 10, 3); excel.WriteCell(0, 11, "=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, "$#,##0.00;($#,##0.00)"); excel.WriteCell(1, 4, (decimal) - 123.456, "$#,##0.00;($#,##0.00)"); excel.WriteCell(1, 5, guid); excel.WriteCell(1, 6, true); excel.WriteCell(1, 7, false); excel.WriteCell(1, 8, (string)null); excel.WriteCell(1, 9, new TimeSpan(1, 2, 3)); excel.WriteCell(1, 10, "=2*3"); // 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 XLWorkbook(stream)) { var sheet = book.Worksheets.Worksheet(1); // Verify row and column styles Assert.AreEqual(true, sheet.Row(2).Style.Font.Bold); Assert.AreEqual(16.0, sheet.Row(2).Style.Font.FontSize); Assert.AreEqual(true, sheet.Column(8).Style.Font.Italic); Assert.AreEqual(24.0, sheet.Column(8).Style.Font.FontSize); // Verify the overridden row and column sizes Assert.AreEqual(600, sheet.Row(2).Height); // TODO: This is not working. Have to figure out why ... //Assert.AreEqual(495, sheet.Column(2).Width); //Assert.AreEqual(700, sheet.Column(12).Width); // Check some automatically sized column widths Assert.AreEqual(23.71, sheet.Column(3).Width); Assert.AreEqual(11.209999999999999, sheet.Column(4).Width); // Verify first row Assert.AreEqual("one", sheet.Cell(1, 1).Value); Assert.AreEqual("", sheet.Cell(1, 1).Style.NumberFormat.Format); Assert.AreEqual("", sheet.Cell(1, 1).Style.DateFormat.Format); Assert.AreEqual("one, two", sheet.Cell(1, 2).Value); Assert.AreEqual(true, sheet.Cell(1, 2).Style.Font.Bold); Assert.AreEqual("one \"two\" three", sheet.Cell(1, 3).Value); Assert.AreEqual(18.0, sheet.Cell(1, 3).Style.Font.FontSize); Assert.AreEqual(" one ", sheet.Cell(1, 4).Value); Assert.AreEqual("Times New Roman", sheet.Cell(1, 4).Style.Font.FontName); Assert.AreEqual(date, sheet.Cell(1, 5).Value); Assert.AreEqual("10/16/2017 15:05", sheet.Cell(1, 5).GetFormattedString()); Assert.AreEqual("", sheet.Cell(1, 5).Style.DateFormat.Format); Assert.AreEqual(date, sheet.Cell(1, 6).Value); Assert.AreEqual("10/16/2017 3:05:00 PM", sheet.Cell(1, 6).GetFormattedString()); Assert.AreEqual("M/d/yyyy h:mm:ss tt", sheet.Cell(1, 6).Style.DateFormat.Format); Assert.AreEqual(date, sheet.Cell(1, 7).Value); Assert.AreEqual("10/16/2017", sheet.Cell(1, 7).GetFormattedString()); Assert.AreEqual("M/d/yyyy", sheet.Cell(1, 7).Style.DateFormat.Format); Assert.AreEqual(date, sheet.Cell(1, 8).Value); Assert.AreEqual("Monday, October 16, 2017", sheet.Cell(1, 8).GetFormattedString()); Assert.AreEqual("dddd, MMMM d, yyyy", sheet.Cell(1, 8).Style.DateFormat.Format); Assert.AreEqual(true, sheet.Cell(1, 8).Style.Font.Bold); Assert.AreEqual((double)1, sheet.Cell(1, 9).Value); Assert.AreEqual("1", sheet.Cell(1, 9).GetFormattedString()); Assert.AreEqual((double)2, sheet.Cell(1, 10).Value); Assert.AreEqual("2", sheet.Cell(1, 10).GetFormattedString()); Assert.AreEqual((double)3, sheet.Cell(1, 11).Value); Assert.AreEqual("3", sheet.Cell(1, 11).GetFormattedString()); Assert.AreEqual("1+2", sheet.Cell(1, 12).FormulaA1); Assert.AreEqual((double)3, sheet.Cell(1, 12).Value); Assert.AreEqual("3", sheet.Cell(1, 12).GetFormattedString()); // Verify second row Assert.AreEqual((double)4, sheet.Cell(2, 1).Value); Assert.AreEqual("4", sheet.Cell(2, 1).GetFormattedString()); Assert.AreEqual("", sheet.Cell(2, 1).Style.NumberFormat.Format); Assert.AreEqual((double)5, sheet.Cell(2, 2).Value); Assert.AreEqual("5", sheet.Cell(2, 2).GetFormattedString()); Assert.AreEqual((double)6, sheet.Cell(2, 3).Value); Assert.AreEqual("6", sheet.Cell(2, 3).GetFormattedString()); Assert.AreEqual(123.456, sheet.Cell(2, 4).Value); Assert.AreEqual("$123.46", sheet.Cell(2, 4).GetFormattedString()); Assert.AreEqual("$#,##0.00;($#,##0.00)", sheet.Cell(2, 4).Style.NumberFormat.Format); Assert.AreEqual(-123.456, sheet.Cell(2, 5).Value); Assert.AreEqual("($123.46)", sheet.Cell(2, 5).GetFormattedString()); Assert.AreEqual("$#,##0.00;($#,##0.00)", sheet.Cell(2, 5).Style.NumberFormat.Format); Assert.AreEqual("bfb9c599-bc9e-4f97-ae59-25f2ca09cfdf", sheet.Cell(2, 6).Value); Assert.AreEqual("bfb9c599-bc9e-4f97-ae59-25f2ca09cfdf", sheet.Cell(2, 6).GetFormattedString()); Assert.AreEqual("true", sheet.Cell(2, 7).Value); Assert.AreEqual("true", sheet.Cell(2, 7).GetFormattedString()); Assert.AreEqual("false", sheet.Cell(2, 8).Value); Assert.AreEqual("false", sheet.Cell(2, 8).GetFormattedString()); Assert.AreEqual("", sheet.Cell(2, 9).Value); Assert.AreEqual("01:02:03", sheet.Cell(2, 10).Value); Assert.AreEqual("01:02:03", sheet.Cell(2, 10).GetFormattedString()); Assert.AreEqual("", sheet.Cell(2, 10).Style.NumberFormat.Format); Assert.AreEqual("2*3", sheet.Cell(2, 11).FormulaA1); Assert.AreEqual((double)6, sheet.Cell(2, 11).Value); Assert.AreEqual("6", sheet.Cell(2, 11).GetFormattedString()); // Verify third sheet Assert.AreEqual(3, book.Worksheets.Count); sheet = book.Worksheets.Worksheet(3); Assert.AreEqual("third sheet", sheet.Cell(1, 1).Value); } } } }
public void WriteCellTest() { using (var stream = new MemoryStream()) { using (var excel = new ExcelWriter(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, horizontalAlign: ExcelAlignHorizontal.Right, verticalAlign: ExcelAlignVertical.Center); 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); // Check some automatically sizes column widths Assert.AreEqual(2655, sheet.Columns[2].Width); Assert.AreEqual(5541, sheet.Columns[4].Width); // 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); // 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(XLAlignHorzEnum.Right, sheet[0, 6].Style.AlignHorz); Assert.AreEqual(XLAlignVertEnum.Center, sheet[0, 6].Style.AlignVert); 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); } } } }
internal int Export(string filePath, string startDtts, string endDtts) { exportedSubModel = 0; if (modelExportStartedEvent != null) { var ModelStartedArgs = new ModelExportStartedEventHandlerArgs(); modelExportStartedEvent(this, ModelStartedArgs); } bool hasData = false; using (ExcelWriter writer = new ExcelWriter(filePath)) { writer.WriteStartDocument(); // write a main chart DataTable rawData = GetRawData(startDtts, endDtts, this.ChartID); rawData.TableName = this.ChartID; RemoveSuperflousColumns(rawData); if (rawData.Rows.Count > 0) { hasData = true; writer.WriteSheet("Main-" + ChartID, rawData); exportedSubModel++; } rawData.Dispose(); if (subModelExportCompletedEvent != null) { var subArgs = new SubModelExportCompletedEventHandlerArgs(); subArgs.SubModelRawID = ChartID; subModelExportCompletedEvent(this, subArgs); } // write sub charts foreach (SPCModel sub in this.SubModels) { rawData = GetRawData(startDtts, endDtts, sub.ChartID); rawData.TableName = sub.ChartID; RemoveSuperflousColumns(rawData); if (rawData.Rows.Count > 0) { hasData = true; writer.WriteSheet(sub.ChartID, rawData); exportedSubModel++; } rawData.Dispose(); if (subModelExportCompletedEvent != null) { var subArgs = new SubModelExportCompletedEventHandlerArgs(); subArgs.SubModelRawID = sub.ChartID; subModelExportCompletedEvent(this, subArgs); } } writer.WriteEndDocument(); writer.Close(); } var modelArgs = new ModelExportCompletedEventHandlerArgs(); modelArgs.SPCModelName = this.SPCModelName; modelArgs.exportedModelNum = exportedSubModel; modelArgs.filePath = filePath; if (hasData == false) { System.IO.File.Delete(filePath); modelArgs.filePath = string.Empty; } if (modelExportCompletedEvent != null) { modelExportCompletedEvent(this, modelArgs); } return(exportedSubModel); }
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 ExcelWriter(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); } } } }
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 ExcelWriter(stream)) { excel.Configuration.RegisterClassMap <PersonMap>(); 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("FirstName", sheet.Cell(1, 1).Value); Assert.AreEqual("LastName", sheet.Cell(1, 2).Value); Assert.AreEqual("HomeStreet", sheet.Cell(1, 3).Value); Assert.AreEqual("HomeCity", sheet.Cell(1, 4).Value); Assert.AreEqual("HomeState", sheet.Cell(1, 5).Value); Assert.AreEqual("HomeZip", sheet.Cell(1, 6).Value); Assert.AreEqual("HomeID", sheet.Cell(1, 7).Value); Assert.AreEqual("WorkStreet", sheet.Cell(1, 8).Value); Assert.AreEqual("WorkCity", sheet.Cell(1, 9).Value); Assert.AreEqual("WorkState", sheet.Cell(1, 10).Value); Assert.AreEqual("WorkZip", sheet.Cell(1, 11).Value); Assert.AreEqual("WorkID", sheet.Cell(1, 12).Value); Assert.AreEqual("NullStreet", sheet.Cell(1, 13).Value); Assert.AreEqual("NullCity", sheet.Cell(1, 14).Value); Assert.AreEqual("NullState", sheet.Cell(1, 15).Value); Assert.AreEqual("NullZip", sheet.Cell(1, 16).Value); Assert.AreEqual("NullID", sheet.Cell(1, 17).Value); // Check the record Assert.AreEqual("First Name", sheet.Cell(2, 1).Value); Assert.AreEqual("Last Name", sheet.Cell(2, 2).Value); Assert.AreEqual("Home Street", sheet.Cell(2, 3).Value); Assert.AreEqual("Home City", sheet.Cell(2, 4).Value); Assert.AreEqual("Home State", sheet.Cell(2, 5).Value); Assert.AreEqual("Home Zip", sheet.Cell(2, 6).Value); Assert.AreEqual(2.0, sheet.Cell(2, 7).Value); Assert.AreEqual("Work Street", sheet.Cell(2, 8).Value); Assert.AreEqual("Work City", sheet.Cell(2, 9).Value); Assert.AreEqual("Work State", sheet.Cell(2, 10).Value); Assert.AreEqual("Work Zip", sheet.Cell(2, 11).Value); Assert.AreEqual(3.0, sheet.Cell(2, 12).Value); Assert.AreEqual("", sheet.Cell(2, 13).Value); Assert.AreEqual("", sheet.Cell(2, 14).Value); Assert.AreEqual("", sheet.Cell(2, 15).Value); Assert.AreEqual("", sheet.Cell(2, 16).Value); Assert.AreEqual(0.0, sheet.Cell(2, 17).Value); } } } }