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