public void Cell3()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue(1).AddToNamed("Result");
IXLCell cell = wb.Cell("Sheet1!Result");
Assert.IsNotNull(cell);
Assert.AreEqual(1, cell.GetValue<Int32>());
}
public void Then_feature_with_background_is_added_successfully()
{
var excelFeatureFormatter = Container.Resolve<ExcelFeatureFormatter>();
var feature = new Feature
{
Name = "Test Feature",
Description =
"In order to test this feature,\nAs a developer\nI want to test this feature",
};
var background = new Scenario
{
Name = "Test Background Scenario",
Description =
"In order to test this background,\nAs a developer\nI want to test this background"
};
var given = new Step { NativeKeyword = "Given", Name = "a precondition" };
background.Steps = new List<Step>(new[] { given });
feature.AddBackground(background);
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
excelFeatureFormatter.Format(worksheet, feature);
Check.That(worksheet.Cell("B4").Value).IsEqualTo(background.Name);
Check.That(worksheet.Cell("C5").Value).IsEqualTo(background.Description);
Check.That(worksheet.Cell("D6").Value).IsEqualTo(given.Name);
}
}
public void ThenStepCommentsAreAddedSuccessfully()
{
var excelStepFormatter = Container.Resolve<ExcelStepFormatter>();
var step = new Step
{
NativeKeyword = "Given",
Name = "I have some precondition",
Comments = new List<Comment>()
{
new Comment()
{
Text = "# A comment",
Type = CommentType.StepComment
}
}
};
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 5;
excelStepFormatter.Format(worksheet, step, ref row);
Check.That(worksheet.Cell("C5").Value).IsEqualTo(step.Comments.First().Text);
Check.That(worksheet.Cell("C6").Value).IsEqualTo(step.NativeKeyword);
Check.That(worksheet.Cell("D6").Value).IsEqualTo(step.Name);
}
}
public void InsideBorderTest()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
IXLRange range = ws.Range("B2:D4");
SetupBorders(range);
range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
range.Style.Border.InsideBorderColor = XLColor.Red;
IXLCell center = range.Cell(2, 2);
Assert.AreEqual(XLColor.Red, center.Style.Border.TopBorderColor);
Assert.AreEqual(XLColor.Red, center.Style.Border.BottomBorderColor);
Assert.AreEqual(XLColor.Red, center.Style.Border.LeftBorderColor);
Assert.AreEqual(XLColor.Red, center.Style.Border.RightBorderColor);
Assert.AreEqual(XLBorderStyleValues.None, range.FirstRow().Cell(1).Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstRow().Cell(2).Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.Double, range.FirstRow().Cell(3).Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.None, range.LastRow().Cell(1).Style.Border.BottomBorder);
Assert.AreEqual(XLBorderStyleValues.Thick, range.LastRow().Cell(2).Style.Border.BottomBorder);
Assert.AreEqual(XLBorderStyleValues.Double, range.LastRow().Cell(3).Style.Border.BottomBorder);
Assert.AreEqual(XLBorderStyleValues.None, range.FirstColumn().Cell(1).Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstColumn().Cell(2).Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Double, range.FirstColumn().Cell(3).Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.None, range.LastColumn().Cell(1).Style.Border.RightBorder);
Assert.AreEqual(XLBorderStyleValues.Thick, range.LastColumn().Cell(2).Style.Border.RightBorder);
Assert.AreEqual(XLBorderStyleValues.Double, range.LastColumn().Cell(3).Style.Border.RightBorder);
}
public static void Basic()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
public void Format(XLWorkbook workbook, Tree features)
{
IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0);
int startRow = 1;
this.BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features);
}
private void WriteCategoriesByMonth(XLWorkbook workbook)
{
var ws = workbook.AddWorksheet("Categorization (by month)");
var all_posts = profile.AggregatedPosts().OrderBy(p => p.Date);
var first_date = all_posts.First().Date;
var last_date = all_posts.Last().Date;
var all_categories = Category.Flatten(profile.RootCategory);
var posts_by_category = all_categories.ToDictionary(category => category, category => category.AggregatePosts().Distinct().ToList());
ws.Row(1).Cell(1).SetValue("Date");
for (int i = 0; i < all_categories.Count; i++)
{
ws.Row(1).Cell(i + 2).SetValue(all_categories[i].Name);
}
var current_date = new DateTime(first_date.Year, first_date.Month, 1);
last_date = new DateTime(last_date.Year, last_date.Month, 1).AddMonths(1);
var current_row = 2;
while (current_date != last_date)
{
ws.Row(current_row).Cell(1).SetValue(current_date.ToShortDateString());
for (int j = 0; j < all_categories.Count; j++)
{
var total = posts_by_category[all_categories[j]].Where(p => p.Date.Year == current_date.Year && p.Date.Month == current_date.Month).Sum(p => p.Value);
ws.Row(current_row).Cell(j + 2).SetValue(total);
}
current_row++;
current_date = current_date.AddMonths(1);
}
}
public void Build(Tree features)
{
if (Log.IsInfoEnabled)
{
Log.Info("Writing Excel workbook to {0}", this.configuration.OutputFolder.FullName);
}
string spreadsheetPath = this.fileSystem.Path.Combine(this.configuration.OutputFolder.FullName, "features.xlsx");
using (var workbook = new XLWorkbook())
{
foreach (var node in features)
{
var featureDirectoryTreeNode =
node as FeatureNode;
if (featureDirectoryTreeNode != null)
{
IXLWorksheet worksheet =
workbook.AddWorksheet(
this.excelSheetNameGenerator.GenerateSheetName(
workbook,
featureDirectoryTreeNode
.Feature));
this.excelFeatureFormatter.Format(
worksheet,
featureDirectoryTreeNode.Feature);
}
}
this.excelTableOfContentsFormatter.Format(workbook, features);
workbook.SaveAs(spreadsheetPath);
}
}
public void ThenTableAddedSuccessfully()
{
var excelTableFormatter = Container.Resolve<ExcelTableFormatter>();
var table = new Table();
table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
table.DataRows =
new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 6;
excelTableFormatter.Format(worksheet, table, ref row);
Check.That(worksheet.Cell("D6").Value).IsEqualTo("Var1");
Check.That(worksheet.Cell("E6").Value).IsEqualTo("Var2");
Check.That(worksheet.Cell("F6").Value).IsEqualTo("Var3");
Check.That(worksheet.Cell("G6").Value).IsEqualTo("Var4");
Check.That(worksheet.Cell("D7").Value).IsEqualTo(1.0);
Check.That(worksheet.Cell("E7").Value).IsEqualTo(2.0);
Check.That(worksheet.Cell("F7").Value).IsEqualTo(3.0);
Check.That(worksheet.Cell("G7").Value).IsEqualTo(4.0);
Check.That(worksheet.Cell("D8").Value).IsEqualTo(5.0);
Check.That(worksheet.Cell("E8").Value).IsEqualTo(6.0);
Check.That(worksheet.Cell("F8").Value).IsEqualTo(7.0);
Check.That(worksheet.Cell("G8").Value).IsEqualTo(8.0);
Check.That(row).IsEqualTo(9);
}
}
public void Create(String filePath)
{
var wb = new XLWorkbook();
var wsActiveCell = wb.AddWorksheet("Set Active Cell");
wsActiveCell.Cell("B2").SetActive();
var wsSelectRowsColumns = wb.AddWorksheet("Select Rows and Columns");
wsSelectRowsColumns.Rows("2, 4-5").Select();
wsSelectRowsColumns.Columns("2, 4-5").Select();
var wsSelectMisc = wb.AddWorksheet("Select Misc");
wsSelectMisc.Cell("B2").Select();
wsSelectMisc.Range("D2:E2").Select();
wsSelectMisc.Ranges("C3, D4:E5").Select();
wb.SaveAs(filePath);
}
public void CopyConditionalFormatsCount()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().AddConditionalFormat().WhenContains("1").Fill.SetBackgroundColor(XLColor.Blue);
IXLWorksheet ws2 = ws.CopyTo("Sheet2");
Assert.AreEqual(1, ws2.ConditionalFormats.Count());
}
public void ThenCanAddTableOfContentsWorksheetSuccessfully()
{
var excelTableOfContentsFormatter = new ExcelTableOfContentsFormatter();
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
excelTableOfContentsFormatter.Format(workbook, null);
}
}
public void CopyColumn()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Test").Style.Font.SetBold();
ws.FirstColumn().CopyTo(ws.Column(2));
Assert.IsTrue(ws.Cell("B1").Style.Font.Bold);
}
public void DefaultColorIndex64isTransparentWhite()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
XLColor color = ws.FirstCell().Style.Fill.BackgroundColor;
Assert.AreEqual(XLColorType.Indexed, color.ColorType);
Assert.AreEqual(64, color.Indexed);
Assert.AreEqual(Color.Transparent, color.Color);
}
public void Cells2()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue(1).AddToNamed("Result", XLScope.Worksheet);
IXLCells cells = wb.Cells("Sheet1!Result, ABC");
Assert.IsNotNull(cells);
Assert.AreEqual(1, cells.Count());
Assert.AreEqual(1, cells.First().GetValue<Int32>());
}
/// <summary>
/// Creates a new Excel File Writer with one worksheet (the default worksheet) in the workbook.
/// </summary>
public ExcelFileWriter( bool createDefaultWorksheet = true )
{
workbook = new XLWorkbook();
AutofitOnSave = true;
if( createDefaultWorksheet ) {
var newWorkSheet = new ExcelWorksheet( workbook.AddWorksheet( "Sheet1" ) );
namesToWorksheets.Add( newWorkSheet.Name, newWorkSheet );
DefaultWorksheet = newWorkSheet;
}
}
public void CanSaveTableCreatedFromSingleRow()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Title");
ws.Range("A1").CreateTable();
using (var ms = new MemoryStream())
wb.SaveAs(ms);
}
public void CreatingATableFromHeadersPushCellsBelow()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Title")
.CellBelow().SetValue("X");
ws.Range("A1").CreateTable();
Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty);
Assert.AreEqual(ws.Cell("A3").GetString(), "X");
}
public static void UseStylePerRange()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
// loop through cells in first col and make each one SAME colour
var range = ws.Range("A1:A10");
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#FF9"));
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
public void CopyConditionalFormatsRelative()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.Cell("A1").Value = "1";
ws.Cell("B1").Value = "1";
ws.Cell("A1").AddConditionalFormat().WhenEquals("=B1").Fill.SetBackgroundColor(XLColor.Blue);
ws.Cell("A2").Value = ws.Cell("A1");
Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B1" && v.Value.IsFormula)));
Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B2" && v.Value.IsFormula)));
}
public static void ColWidth()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
var cols = ws.Columns(1,3);
cols.Width = 44;
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
public void CanSaveTableCreatedFromEmptyDataTable()
{
var dt = new DataTable("sheet1");
dt.Columns.Add("col1", typeof (string));
dt.Columns.Add("col2", typeof (double));
var wb = new XLWorkbook();
wb.AddWorksheet(dt);
using (var ms = new MemoryStream())
wb.SaveAs(ms);
}
public void ThenCanShortenLongMultipleDuplicatedNameSuccessfully()
{
var excelSheetNameGenerator = Container.Resolve<ExcelSheetNameGenerator>();
var feature1 = new Feature {Name = "This is a really really long feature name that needs to be shortened A"};
var feature2 = new Feature {Name = "This is a really really long feature name that needs to be shortened B"};
var feature3 = new Feature {Name = "This is a really really long feature name that needs to be shortened C"};
string name1;
string name2;
string name3;
using (var wb = new XLWorkbook())
{
name1 = excelSheetNameGenerator.GenerateSheetName(wb, feature1);
wb.AddWorksheet(name1);
name2 = excelSheetNameGenerator.GenerateSheetName(wb, feature2);
wb.AddWorksheet(name2);
name3 = excelSheetNameGenerator.GenerateSheetName(wb, feature3);
}
name3.ShouldEqual("THISISAREALLYREALLYLONGFEATU(2)");
}
public void Validation_2()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell("A1").SetValue("A");
ws.Cell("B1").SetDataValidation().Custom("Sheet1!A1");
IXLWorksheet ws2 = wb.AddWorksheet("Sheet2");
ws2.Cell("A1").SetValue("B");
ws.Cell("B1").CopyTo(ws2.Cell("B1"));
Assert.AreEqual("Sheet1!A1", ws2.Cell("B1").DataValidation.Value);
}
public static void BackgroundColor()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
var subHeaders = ws.Ranges("A2:C2,A6:C6,A8:C8");
subHeaders.Style
.Fill.SetBackgroundColor(XLColor.Almond);
subHeaders.Style.Font.SetFontColor(XLColor.AliceBlue);
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
public void RowSerializer_Exports_Into_ClosedXml()
{
var config = new ExampleClassMap();
var data = TestData.GenerateExampleClasses();
using var stream = new MemoryStream();
var workbook = new ClosedXML.Excel.XLWorkbook();
var worksheet = workbook.AddWorksheet("Sheet1");
worksheet.Cell(1, 1).InsertData(new[] { config.GetHeader() });
worksheet.Cell(2, 1).InsertData(ExportSerializer.RowSerializer(data, config).Select(e => e.Data));
ResultData.VerifyExcel(worksheet);
}
private void WriteAccountsSheet(XLWorkbook workbook)
{
var accounts = profile.Accounts;
var ws = workbook.AddWorksheet("Accounts");
ws.Row(1).Cell(1).SetValue("Name");
ws.Row(1).Cell(2).SetValue("# of posts");
for (int i = 0; i < accounts.Count; i++)
{
ws.Row(i + 2).Cell(1).SetValue(accounts[i].Name);
ws.Row(i + 2).Cell(2).SetValue(accounts[i].Posts.Count);
}
}
public static void BorderStyleColor()
{
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Worksheet Name");
ws.Cell("A1").Value = Data;
var subHeaders = ws.Ranges("A2:C2,A6:C6,A8:C8");
subHeaders.Style
.Border.SetBottomBorder(XLBorderStyleValues.Double)
.Border.SetBottomBorderColor(XLColor.Aquamarine);
wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
}
private void btnFullMetal_Click(object sender, EventArgs e)
{
string strKeyWord = "毒蜥级";
string strPath = Application.StartupPath + "\\EXCEL\\" + strKeyWord + "制造方案";
Excel.XLWorkbook xFile = new Excel.XLWorkbook();
Excel.IXLWorksheet xLSheet = xFile.AddWorksheet();
int nRow = 1;
int nCol = 1;
OutputBluePrintResult(xLSheet, strKeyWord, ref nRow, ref nCol);
xFile.SaveAs(strPath + ".xlsx");
MessageBox.Show("计算完成,路径:" + strPath);
}
public void ThenTableAddedSuccessfully()
{
var excelTableFormatter = Kernel.Get<ExcelTableFormatter>();
var table = new Table();
table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
table.DataRows =
new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 6;
excelTableFormatter.Format(worksheet, table, ref row);
worksheet.Cell("D6").Value.ShouldEqual("Var1");
worksheet.Cell("E6").Value.ShouldEqual("Var2");
worksheet.Cell("F6").Value.ShouldEqual("Var3");
worksheet.Cell("G6").Value.ShouldEqual("Var4");
worksheet.Cell("D7").Value.ShouldEqual(1.0);
worksheet.Cell("E7").Value.ShouldEqual(2.0);
worksheet.Cell("F7").Value.ShouldEqual(3.0);
worksheet.Cell("G7").Value.ShouldEqual(4.0);
worksheet.Cell("D8").Value.ShouldEqual(5.0);
worksheet.Cell("E8").Value.ShouldEqual(6.0);
worksheet.Cell("F8").Value.ShouldEqual(7.0);
worksheet.Cell("G8").Value.ShouldEqual(8.0);
row.ShouldEqual(9);
}
}
public void Format(XLWorkbook workbook, GeneralTree<IDirectoryTreeNode> features)
{
IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0);
int startRow = 1;
BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features);
}
public void ThenSingleScenarioWithStepsAddedSuccessfully()
{
var excelScenarioFormatter = Container.Resolve<ExcelScenarioFormatter>();
var scenario = new Scenario
{
Name = "Test Feature",
Description =
"In order to test this feature,\nAs a developer\nI want to test this feature"
};
var given = new Step {NativeKeyword = "Given", Name = "a precondition"};
var when = new Step {NativeKeyword = "When", Name = "an event occurs"};
var then = new Step {NativeKeyword = "Then", Name = "a postcondition"};
scenario.Steps = new List<Step>(new[] {given, when, then});
using (var workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
int row = 3;
excelScenarioFormatter.Format(worksheet, scenario, ref row);
worksheet.Cell("B3").Value.ShouldEqual(scenario.Name);
worksheet.Cell("C4").Value.ShouldEqual(scenario.Description);
row.ShouldEqual(8);
}
}
