//[TestMethod]
public void TestMethod1()
{
var workbook = new Workbook();
CreateStyles(workbook);
var worksheet = workbook.AddSheet("Sheet 1");
CreateSheet(worksheet);
foreach (var row in worksheet.Rows)
{
var aCell = row.GetCell("a");
aCell.Style = "style1";
var bCell = row.GetCell("b");
bCell.Style = "style2";
var fCell = row.GetCell("f");
fCell.DataType = DataType.Number;
var gCell = row.GetCell("g");
//gCell.DataType = DataType.DateTime;
}
var outerXml = workbook.OuterXml;
// Assertions
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
File.WriteAllText(path + @"\file1.xml", outerXml);
}
public void Workbook_AddWorksheet_ByName()
{
// Arrange
var book = new Workbook();
// Act
book.AddSheet("name");
// Assert
Assert.AreEqual(1, book.Sheets.Count);
}
public void Workbook_ClearWorksheets()
{
// Arrange
var sheet = new Worksheet("name");
var book = new Workbook();
book.AddSheet(sheet);
// Act
book.ClearSheets();
// Assert
Assert.AreEqual(0, book.Sheets.Count);
}
public void Workbook_RemoveWorksheet_ByName_WrongName_ReturnsFalse()
{
// Arrange
var sheet = new Worksheet("name");
var book = new Workbook();
book.AddSheet(sheet);
// Act
var value = book.RemoveSheet("wrongname");
// Assert
Assert.IsFalse(value);
Assert.AreEqual(1, book.Sheets.Count);
}
public void Workbook_RemoveWorksheet_ByName()
{
// Arrange
var sheet = new Worksheet("name");
var book = new Workbook();
book.AddSheet(sheet);
// Act
var value = book.RemoveSheet("name");
// Assert
Assert.IsTrue(value);
Assert.AreEqual(0, book.Sheets.Count);
}
public void Test_CanGenerate()
{
var testData = new List <TestClass> {
new TestClass {
name = "a", id = 1, amount = 12.20M
},
new TestClass {
name = "b", id = 2, amount = 2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
},
new TestClass {
name = "c", id = 5, amount = 10.2M
}
};
var excel = new Workbook(ExcelType.XSSF);
var book1 = excel.AddSheet <TestClass>("testSheet")
.IsFreezeTitle(false);
book1.AddColumn("固定值列")
.Value("hhh")
.HasCellType(CellType.String);
book1.AddColumn("id列")
.Value(i => i.id)
.HasFormat("0");
book1.AddColumn("计算列")
.Value(i => $"name: {i.name}, amount: {i.amount}")
.HasWidth(20);
book1.AddColumn("金额列")
.Value(i => i.amount);
book1.AddColumn("名称列")
.Value(i => i.name)
.HasIndex(1);
book1.Fill(testData);
var d = excel.Build();
using (Stream file = new FileStream(Path.Combine(Environment.CurrentDirectory, "test.xlsx"), FileMode.Create, FileAccess.Write)) {
excel.Build().Write(file);
}
}
private Worksheet FillWorksheet(DataTable data)
{
var workbook = new Workbook();
var sheet = workbook.AddSheet();
sheet.Name = "Channel delivery times";
sheet.Columns = new List <Column>();
var row = new Row()
{
Index = 0
};
int columnIndex = 0;
int[] colWidths = { 90, 200, 200, 80, 80, 150, 150, 150, 150, 150 };
string[] colAlign = { "right", "left", "left", "right", "right", "right", "right", "right", "right" };
foreach (DataColumn dataColumn in data.Columns)
{
sheet.Columns.Add(new Column()
{
Width = columnIndex < colWidths.Length ? colWidths[columnIndex] : 80
});
string cellValue = dataColumn.ColumnName.Replace("_", " ");
var cell = new Cell()
{
Index = columnIndex, Value = cellValue, Bold = true
};
cell.Background = "#CCEE00";
row.AddCell(cell);
columnIndex++;
}
sheet.AddRow(row);
int rowIndex = 1;
foreach (DataRow dataRow in data.Rows)
{
row = new Row()
{
Index = rowIndex++
};
columnIndex = 0;
foreach (DataColumn dataColumn in data.Columns)
{
var cell = new Cell()
{
TextAlign = columnIndex < colAlign.Length ? colAlign[columnIndex] : "left",
Index = columnIndex++
};
Type tp = dataRow[dataColumn.ColumnName].GetType();
if (tp.Name == "Int32")
{
cell.Value = (int)dataRow[dataColumn.ColumnName];
}
else if (tp.Name == "DateTime")
{
cell.Value = (DateTime)dataRow[dataColumn.ColumnName];
cell.Format = "DateTime";
}
else
{
cell.Value = dataRow[dataColumn.ColumnName].ToString();
}
row.AddCell(cell);
}
sheet.AddRow(row);
}
return(sheet);
}
public void TestMethod1()
{
var workbook = new Workbook();
var worksheet1 = workbook.AddSheet("sheet 1");
for (int row = 1; row < 10; row++)
{
for (int col = 0; col < 5; col++)
{
var cell = worksheet1.GetCell(row, col);
cell.Value = string.Format("{0}:{1}", row + 1, col + 1);
}
}
var worksheet2 = workbook.AddSheet("sheet 2");
var style1 = workbook.AddStyle("style1");
style1.AlignmentStyle.Horizontal = HorizontalAlignment.Center;
style1.AlignmentStyle.ShrinkToFit = true;
style1.Borders.Add("#000000", BorderLineStyle.Continuous);
style1.FontStyle.Bold = true;
style1.FontStyle.Family = FontFamily.Modern;
style1.FontStyle.FontName = "Arial";
style1.FontStyle.Size = 14;
style1.FontStyle.Shadow = true;
style1.FontStyle.Italic = true;
style1.FontStyle.Color = "#ff0000";
style1.InteriorStyle.Pattern = InteriorStylePattern.Solid;
style1.InteriorStyle.Color = "#e0e0e0";
var cellA1 = worksheet2["a1"];
cellA1.Value = "value a1";
cellA1.Style = "style1";
var cellA2 = worksheet2["a2"];
cellA2.Value = "value a2";
cellA2.Style = "style1";
var cellA3 = worksheet2["a3"];
cellA3.Value = "value a3";
cellA3.Style = "style1";
var cellB1 = worksheet2["b1"];
cellB1.Value = "value b1";
cellB1.Style = "style1";
var cellB2 = worksheet2["b2"];
cellB2.Value = "value b2";
cellB2.Style = "style1";
var cellB3 = worksheet2["b3"];
cellB3.Value = "value b3";
cellB3.Style = "style1";
// Assertions
var outerXml = workbook.OuterXml;
var path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
File.WriteAllText(path + @"\file2.xml", outerXml);
}
