private static void FillRow(IXLRow row1)
{
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
}
public void CopyingColumns()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
column1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
column1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
column1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
column1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
column1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(1, 2).Value = column1;
ws.Cell(1, 3).Value = column1.Column(1, 7);
IXLColumn column2 = ws.Column(2);
Assert.AreEqual(XLColor.Red, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen),
column2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column2.Cell(7).Style.Fill.BackgroundColor);
IXLColumn column3 = ws.Column(3);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen),
column3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column3.Cell(7).Style.Fill.BackgroundColor);
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
FillRow(row1);
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
Assert.AreEqual(3, ws.ConditionalFormats.Count());
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula));
}
public void Create(String filePath)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Using Colors");
Int32 ro = 0;
// From Known color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Red;
ws.Cell(ro, 2).Value = "XLColor.Red";
// From Color not so known
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Byzantine;
ws.Cell(ro, 2).Value = "XLColor.Byzantine";
ro++;
// FromArgb(Int32 argb) using Hex notation
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0xFF00FF);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0xFF00FF)";
// FromArgb(Int32 argb) using an integer (you need to convert the hex value to an int)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(16711935);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(16711935)";
// FromArgb(Int32 r, Int32 g, Int32 b)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(255, 0, 255)";
// FromArgb(Int32 a, Int32 r, Int32 g, Int32 b)
// Note: Excel ignores the alpha value
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0, 255, 0, 255)";
ro++;
// FromColor(Color color)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromColor(Color.Red);
ws.Cell(ro, 2).Value = "XLColor.FromColor(Color.Red)";
ro++;
// FromHtml(String htmlColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#FF996515");
ws.Cell(ro, 2).Value = "XLColor.FromHtml(\"#FF996515\")";
ro++;
// FromIndex(Int32 indexedColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(25);
ws.Cell(ro, 2).Value = "XLColor.FromIndex(25)";
ro++;
// FromKnownColor(KnownColor knownColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromKnownColor(KnownColor.Plum);
ws.Cell(ro, 2).Value = "XLColor.FromKnownColor(KnownColor.Plum)";
ro++;
// FromName(String colorName)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromName("PowderBlue");
ws.Cell(ro, 2).Value = "XLColor.FromName(\"PowderBlue\")";
ro++;
// From Theme color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1)";
// From Theme color with tint
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1, 0.5)";
ws.Columns().AdjustToContents();
wb.SaveAs(filePath);
}