private ICellStyle CreateStyle(byte[] color, byte[] fontColor)
{
var myColor = new XSSFColor(color);
var myFontColor = new XSSFColor(fontColor);
var style = (XSSFCellStyle)_workbook.CreateCellStyle();
var font = (XSSFFont)_workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.SetColor(myFontColor);
font.FontName = "Arial";
font.FontHeightInPoints = 10;
style.SetFont(font);
style.SetFillForegroundColor(myColor);
style.FillPattern = FillPattern.SolidForeground;
return style;
}
/**
* Set the foreground fill color represented as a {@link XSSFColor} value.
* <br/>
* <i>Note: Ensure Foreground color is Set prior to background color.</i>
* @param color the color to use
* @see #setFillBackgroundColor(NPOI.xssf.usermodel.XSSFColor) )
*/
public void SetFillForegroundColor(XSSFColor color)
{
CT_Fill ct = GetCTFill();
CT_PatternFill ptrn = ct.GetPatternFill();
if (color == null)
{
if (ptrn != null)
{
ptrn.unsetFgColor();
}
}
else
{
if (ptrn == null)
{
ptrn = ct.AddNewPatternFill();
}
ptrn.fgColor = (color.GetCTColor());
}
int idx = _stylesSource.PutFill(new XSSFCellFill(ct));
_cellXf.fillId = (uint)idx;
_cellXf.applyFill = (true);
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().RGB[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().RGB[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().RGB[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().RGB[3]);
xssfFont.Color = ((short)23);
byte[] bytes = Encoding.ASCII.GetBytes(HexDump.ToHex(0xF1F1F1));
color.rgb = (bytes);
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.RGB[2]);
CollectionAssert.AreEqual(bytes, xssfFont.GetXSSFColor().RGB);
Assert.AreEqual(0, xssfFont.Color);
}
/**
* Set the color to use for the top border as a {@link XSSFColor} value
*
* @param color the color to use
*/
public void SetTopBorderColor(XSSFColor color)
{
CT_Border ct = GetCTBorder();
if (color == null && !ct.IsSetTop())
{
return;
}
CT_BorderPr pr = ct.IsSetTop() ? ct.top : ct.AddNewTop();
if (color != null)
{
pr.color = color.GetCTColor();
}
else
{
pr.UnsetColor();
}
int idx = _stylesSource.PutBorder(new XSSFCellBorder(ct, _theme));
_cellXf.borderId = (uint)idx;
_cellXf.applyBorder = (true);
}
public void SetFillForegroundColor(XSSFColor color)
{
CT_PatternFill ptrn =
_fill.IsSetPatternFill() ? _fill.GetPatternFill() : _fill.AddNewPatternFill();
CT_Color bgColor = color.GetCTColor();
ptrn.fgColor = (bgColor);
}
public void TestRGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("50299.xlsx");
// Check the CTColor is as expected
XSSFColor rgb3 = (XSSFColor)((XSSFCellStyle)wb.GetCellStyleAt((short)25)).FillForegroundXSSFColor;
Assert.AreEqual(false, rgb3.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb3.GetCTColor().indexed);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetTint());
Assert.AreEqual(-0.34999, rgb3.GetCTColor().tint, 0.00001);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetRgb());
Assert.AreEqual(3, rgb3.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb3.Indexed);
Assert.AreEqual(-0.34999, rgb3.Tint, 0.00001);
Assert.IsFalse(rgb3.HasAlpha);
Assert.IsTrue(rgb3.HasTint);
Assert.AreEqual("FFFFFFFF", rgb3.ARGBHex);
Assert.AreEqual(3, rgb3.RGB.Length);
Assert.AreEqual(255, rgb3.RGB[0]);
Assert.AreEqual(255, rgb3.RGB[1]);
Assert.AreEqual(255, rgb3.RGB[2]);
Assert.AreEqual(4, rgb3.ARGB.Length);
Assert.AreEqual(255, rgb3.ARGB[0]);
Assert.AreEqual(255, rgb3.ARGB[1]);
Assert.AreEqual(255, rgb3.ARGB[2]);
Assert.AreEqual(255, rgb3.ARGB[3]);
// Tint doesn't have the alpha
// tint = -0.34999
// 255 * (1 + tint) = 165 truncated
// or (byte) -91 (which is 165 - 256)
Assert.AreEqual(3, rgb3.GetRgbWithTint().Length);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[0]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[1]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[2]);
// Set the colour to black, will Get translated internally
// (Excel stores 3 colour white and black wrong!)
// Set the color to black (no theme).
rgb3.SetRgb(new byte[] { 0, 0, 0 });
Assert.AreEqual("FF000000", rgb3.ARGBHex);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[2]);
// Set another, is fine
rgb3.SetRgb(new byte[] { 16, 17, 18 });
Assert.IsFalse(rgb3.HasAlpha);
Assert.AreEqual("FF101112", rgb3.ARGBHex);
Assert.AreEqual(0x10, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0x11, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0x12, rgb3.GetCTColor().GetRgb()[2]);
}
/**
* Create a Databar conditional formatting rule.
* <p>The thresholds and colour for it will be created, but will be
* empty and require configuring with
* {@link XSSFConditionalFormattingRule#getDataBarFormatting()}
* then
* {@link XSSFDataBarFormatting#getMinThreshold()}
* and
* {@link XSSFDataBarFormatting#getMaxThreshold()}
*/
public XSSFConditionalFormattingRule CreateConditionalFormattingRule(XSSFColor color)
{
XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
// Have it setup, with suitable defaults
rule.CreateDataBarFormatting(color);
// All done!
return(rule);
}
public void SetColor(XSSFColor color)
{
if (color == null)
{
this._ctFont.SetColorArray((CT_Color[])null);
}
else
{
(this._ctFont.sizeOfColorArray() == 0 ? this._ctFont.AddNewColor() : this._ctFont.GetColorArray(0)).SetRgb(color.GetRgb());
}
}
public override bool Equals(Object o)
{
if (o == null || !(o is XSSFColor))
{
return(false);
}
XSSFColor cf = (XSSFColor)o;
return(ctColor.ToString().Equals(cf.GetCTColor().ToString()));
}
private bool SameARGB(XSSFColor other)
{
if (IsRGB == other.IsRGB)
{
if (IsRGB)
{
return(Arrays.Equals(ARGB, other.ARGB));
}
return(true);
}
return(false);
}
// Helper methods for {@link #equals(Object)}
private bool SameIndexed(XSSFColor other)
{
if (IsIndexed == other.IsIndexed)
{
if (IsIndexed)
{
return(Indexed == other.Indexed);
}
return(true);
}
return(false);
}
/**
* set the color for the font in Standard Alpha Red Green Blue color value
*
* @param color - color to use
*/
public void SetColor(XSSFColor color)
{
if (color == null)
{
_ctFont.SetColorArray(null);
}
else
{
Spreadsheet.CT_Color ctColor = _ctFont.sizeOfColorArray() == 0 ? _ctFont.AddNewColor() : _ctFont.GetColorArray(0);
ctColor.SetRgb(color.RGB);
}
}
private bool SameTheme(XSSFColor other)
{
if (IsThemed == other.IsThemed)
{
if (IsThemed)
{
return(Theme == other.Theme);
}
return(true);
}
return(false);
}
private bool SameTint(XSSFColor other)
{
if (HasTint == other.HasTint)
{
if (HasTint)
{
return(Tint == other.Tint);
}
return(true);
}
return(false);
}
static ExcelCreate()
{
_scoreColor = new XSSFColor();
_scoreColor.SetRgb(new byte[] { 255, 153, 58 });
_winRateColor = new XSSFColor();
_winRateColor.SetRgb(new byte[] { 255, 0, 0 });
_bgColor1 = new XSSFColor();
_bgColor1.SetRgb(new byte[] { 112, 206, 188 });
_bgColor2 = new XSSFColor();
_bgColor2.SetRgb(new byte[] { 220, 230, 241 });
}
public void TestRGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("50299.xlsx");
// Check the CTColor is as expected
XSSFColor rgb3 = (XSSFColor)((XSSFCellStyle)wb.GetCellStyleAt((short)25)).FillForegroundXSSFColor;
Assert.AreEqual(false, rgb3.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb3.GetCTColor().indexed);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetTint());
Assert.AreEqual(-0.34999, rgb3.GetCTColor().tint, 0.00001);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetRgb());
Assert.AreEqual(3, rgb3.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb3.Indexed);
Assert.AreEqual(-0.34999, rgb3.Tint, 0.00001);
Assert.AreEqual("FFFFFFFF", rgb3.GetARGBHex());
Assert.AreEqual(3, rgb3.GetRgb().Length);
Assert.AreEqual(255, rgb3.GetRgb()[0]);
Assert.AreEqual(255, rgb3.GetRgb()[1]);
Assert.AreEqual(255, rgb3.GetRgb()[2]);
Assert.AreEqual(4, rgb3.GetARgb().Length);
Assert.AreEqual(255, rgb3.GetARgb()[0]);
Assert.AreEqual(255, rgb3.GetARgb()[1]);
Assert.AreEqual(255, rgb3.GetARgb()[2]);
Assert.AreEqual(255, rgb3.GetARgb()[3]);
// Tint doesn't have the alpha
Assert.AreEqual(3, rgb3.GetRgbWithTint().Length);
Assert.AreEqual(0, rgb3.GetRgbWithTint()[0]);
Assert.AreEqual(0, rgb3.GetRgbWithTint()[1]);
Assert.AreEqual(0, rgb3.GetRgbWithTint()[2]);
// Set the colour to black, will Get translated internally
// (Excel stores 3 colour white and black wrong!)
rgb3.SetRgb(new byte[] { 255, 255, 255 });
Assert.AreEqual("FFFFFFFF", rgb3.GetARGBHex());
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[2]);
// Set another, is fine
rgb3.SetRgb(new byte[] { 16, 17, 18 });
Assert.AreEqual("FF101112", rgb3.GetARGBHex());
Assert.AreEqual(0x10, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0x11, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0x12, rgb3.GetCTColor().GetRgb()[2]);
}
protected override void AssertColour(String hexExpected, IColor actual)
{
Assert.IsNotNull(actual, "Colour must be given");
XSSFColor colour = (XSSFColor)actual;
if (hexExpected.Length == 8)
{
Assert.AreEqual(hexExpected, colour.ARGBHex);
}
else
{
Assert.AreEqual(hexExpected, colour.ARGBHex.Substring(2));
}
}
public void TestARGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48779.xlsx");
// Check the CTColor is as expected
XSSFColor rgb4 = (XSSFColor)wb.GetCellStyleAt(1).FillForegroundColorColor;
Assert.AreEqual(false, rgb4.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb4.GetCTColor().indexed);
Assert.AreEqual(true, rgb4.GetCTColor().IsSetRgb());
Assert.AreEqual(4, rgb4.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb4.Indexed);
Assert.AreEqual(0.0, rgb4.Tint);
Assert.IsFalse(rgb4.HasTint);
Assert.IsTrue(rgb4.HasAlpha);
Assert.AreEqual("FFFF0000", rgb4.ARGBHex);
Assert.AreEqual(3, rgb4.RGB.Length);
Assert.AreEqual(255, rgb4.RGB[0]);
Assert.AreEqual(0, rgb4.RGB[1]);
Assert.AreEqual(0, rgb4.RGB[2]);
Assert.AreEqual(4, rgb4.ARGB.Length);
Assert.AreEqual(255, rgb4.ARGB[0]);
Assert.AreEqual(255, rgb4.ARGB[1]);
Assert.AreEqual(0, rgb4.ARGB[2]);
Assert.AreEqual(0, rgb4.ARGB[3]);
// Tint doesn't have the alpha
Assert.AreEqual(3, rgb4.GetRgbWithTint().Length);
Assert.AreEqual(255, rgb4.GetRgbWithTint()[0]);
Assert.AreEqual(0, rgb4.GetRgbWithTint()[1]);
Assert.AreEqual(0, rgb4.GetRgbWithTint()[2]);
// Turn on tinting, and check it behaves
// TODO These values are suspected to be wrong...
rgb4.Tint = (0.4);
Assert.IsTrue(rgb4.HasTint);
Assert.AreEqual(0.4, rgb4.Tint);
Assert.AreEqual(3, rgb4.GetRgbWithTint().Length);
Assert.AreEqual(255, rgb4.GetRgbWithTint()[0]);
Assert.AreEqual(102, rgb4.GetRgbWithTint()[1]);
Assert.AreEqual(102, rgb4.GetRgbWithTint()[2]);
}
public XSSFColor GetXSSFColor()
{
CT_Color color1 = this._ctFont.sizeOfColorArray() == 0 ? (CT_Color)null : this._ctFont.GetColorArray(0);
if (color1 == null)
{
return((XSSFColor)null);
}
XSSFColor color2 = new XSSFColor(color1);
if (this._themes != null)
{
this._themes.InheritFromThemeAsRequired(color2);
}
return(color2);
}
/**
* get the color value for the font
* References a color defined as Standard Alpha Red Green Blue color value (ARGB).
*
* @return XSSFColor - rgb color to use
*/
public XSSFColor GetXSSFColor()
{
Spreadsheet.CT_Color ctColor = _ctFont.sizeOfColorArray() == 0 ? null : _ctFont.GetColorArray(0);
if (ctColor != null)
{
XSSFColor color = new XSSFColor(ctColor);
if (_themes != null)
{
_themes.InheritFromThemeAsRequired(color);
}
return(color);
}
else
{
return(null);
}
}
public void TestIndexedColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48779.xlsx");
// Check the CTColor is as expected
XSSFColor indexed = (XSSFColor)wb.GetCellStyleAt(1).FillBackgroundColorColor;
Assert.AreEqual(true, indexed.GetCTColor().IsSetIndexed());
Assert.AreEqual(64, indexed.GetCTColor().indexed);
Assert.AreEqual(false, indexed.GetCTColor().IsSetRgb());
Assert.AreEqual(null, indexed.GetCTColor().GetRgb());
// Now check the XSSFColor
// Note - 64 is a special "auto" one with no rgb equiv
Assert.AreEqual(64, indexed.Indexed);
Assert.AreEqual(null, indexed.RGB);
Assert.AreEqual(null, indexed.GetRgbWithTint());
Assert.AreEqual(null, indexed.ARGBHex);
Assert.IsFalse(indexed.HasAlpha);
Assert.IsFalse(indexed.HasTint);
// Now Move to one with indexed rgb values
indexed.Indexed = (59);
Assert.AreEqual(true, indexed.GetCTColor().IsSetIndexed());
Assert.AreEqual(59, indexed.GetCTColor().indexed);
Assert.AreEqual(false, indexed.GetCTColor().IsSetRgb());
Assert.AreEqual(null, indexed.GetCTColor().GetRgb());
Assert.AreEqual(59, indexed.Indexed);
Assert.AreEqual("FF333300", indexed.ARGBHex);
Assert.AreEqual(3, indexed.RGB.Length);
Assert.AreEqual(0x33, indexed.RGB[0]);
Assert.AreEqual(0x33, indexed.RGB[1]);
Assert.AreEqual(0x00, indexed.RGB[2]);
Assert.AreEqual(4, indexed.ARGB.Length);
Assert.AreEqual(255, indexed.ARGB[0]);
Assert.AreEqual(0x33, indexed.ARGB[1]);
Assert.AreEqual(0x33, indexed.ARGB[2]);
Assert.AreEqual(0x00, indexed.ARGB[3]);
// You don't Get tinted indexed colours, sorry...
Assert.AreEqual(null, indexed.GetRgbWithTint());
}
public override bool Equals(Object o)
{
if (o == null || !(o is XSSFColor))
{
return(false);
}
XSSFColor other = (XSSFColor)o;
// Compare each field in ctColor.
// Cannot compare ctColor's XML string representation because equivalent
// colors may have different relation namespace URI's
return(SameARGB(other) &&
SameTheme(other) &&
SameIndexed(other) &&
SameTint(other) &&
SameAuto(other));
}
public byte[] GetRgbWithTint()
{
byte[] numArray1 = this.ctColor.GetRgb();
if (numArray1 != null)
{
if (numArray1.Length == 4)
{
byte[] numArray2 = new byte[3];
Array.Copy((Array)numArray1, 1, (Array)numArray2, 0, 3);
numArray1 = numArray2;
}
for (int index = 0; index < numArray1.Length; ++index)
{
numArray1[index] = XSSFColor.ApplyTint((int)numArray1[index] & (int)byte.MaxValue, this.ctColor.tint);
}
}
return(numArray1);
}
public void TestGetSetRightBorderColor()
{
//defaults
Assert.AreEqual(IndexedColors.Black.Index, cellStyle.RightBorderColor);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
int num = stylesTable.GetBorders().Count;
XSSFColor clr;
//setting indexed color
cellStyle.RightBorderColor = (IndexedColors.BlueGrey.Index);
Assert.AreEqual(IndexedColors.BlueGrey.Index, cellStyle.RightBorderColor);
clr = cellStyle.RightBorderXSSFColor;
Assert.IsTrue(clr.GetCTColor().IsSetIndexed());
Assert.AreEqual(IndexedColors.BlueGrey.Index, clr.Indexed);
//a new border was Added to the styles table
Assert.AreEqual(num + 1, stylesTable.GetBorders().Count);
//id of the Created border
int borderId = (int)cellStyle.GetCoreXf().borderId;
Assert.IsTrue(borderId > 0);
//check Changes in the underlying xml bean
CT_Border ctBorder = stylesTable.GetBorderAt(borderId).GetCTBorder();
Assert.AreEqual((uint)IndexedColors.BlueGrey.Index, ctBorder.right.color.indexed);
//setting XSSFColor
num = stylesTable.GetBorders().Count;
clr = new XSSFColor(Color.Cyan);
cellStyle.SetRightBorderColor(clr);
Assert.AreEqual(clr.GetCTColor().ToString(), cellStyle.RightBorderXSSFColor.GetCTColor().ToString());
byte[] rgb = cellStyle.RightBorderXSSFColor.GetRgb();
Assert.AreEqual(Color.Cyan.ToArgb(), Color.FromArgb(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF).ToArgb());
//another border was Added to the styles table
Assert.AreEqual(num, stylesTable.GetBorders().Count);
//passing null unsets the color
cellStyle.SetRightBorderColor(null);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
}
public void TestGetSetFillBackgroundColor()
{
Assert.AreEqual(IndexedColors.Automatic.Index, cellStyle.FillBackgroundColor);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
XSSFColor clr;
int num = stylesTable.GetFills().Count;
//setting indexed color
cellStyle.FillBackgroundColor = (IndexedColors.Red.Index);
Assert.AreEqual(IndexedColors.Red.Index, cellStyle.FillBackgroundColor);
clr = (XSSFColor)cellStyle.FillBackgroundColorColor;
Assert.IsTrue(clr.GetCTColor().IsSetIndexed());
Assert.AreEqual(IndexedColors.Red.Index, clr.Indexed);
//a new fill was Added to the styles table
Assert.AreEqual(num + 1, stylesTable.GetFills().Count);
//id of the Created border
int FillId = (int)cellStyle.GetCoreXf().fillId;
Assert.IsTrue(FillId > 0);
//check changes in the underlying xml bean
CT_Fill ctFill = stylesTable.GetFillAt(FillId).GetCTFill();
Assert.AreEqual((uint)IndexedColors.Red.Index, ctFill.GetPatternFill().bgColor.indexed);
//setting XSSFColor
num = stylesTable.GetFills().Count;
clr = new XSSFColor(Color.Cyan);
cellStyle.SetFillBackgroundColor(clr); // TODO this testcase assumes that cellStyle creates a new CT_Fill, but the implementation changes the existing style. - do not know whats right 8-(
Assert.AreEqual(clr.GetCTColor().ToString(), ((XSSFColor)cellStyle.FillBackgroundColorColor).GetCTColor().ToString());
byte[] rgb = ((XSSFColor)cellStyle.FillBackgroundColorColor).GetRgb();
Assert.AreEqual(Color.Cyan.ToArgb(), Color.FromArgb(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF).ToArgb());
//another border was added to the styles table
Assert.AreEqual(num + 1, stylesTable.GetFills().Count);
//passing null unsets the color
cellStyle.SetFillBackgroundColor(null);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
Assert.AreEqual(IndexedColors.Automatic.Index, cellStyle.FillBackgroundColor);
}
public void SetFillForegroundColor(XSSFColor color)
{
CT_Fill ctFill = this.GetCTFill();
CT_PatternFill ctPatternFill = ctFill.GetPatternFill();
if (color == null)
{
ctPatternFill?.unsetFgColor();
}
else
{
if (ctPatternFill == null)
{
ctPatternFill = ctFill.AddNewPatternFill();
}
ctPatternFill.fgColor = color.GetCTColor();
}
this._cellXf.fillId = (uint)this._stylesSource.PutFill(new XSSFCellFill(ctFill));
this._cellXf.applyFill = true;
}
public void SetTopBorderColor(XSSFColor color)
{
CT_Border ctBorder = this.GetCTBorder();
if (color == null && !ctBorder.IsSetTop())
{
return;
}
CT_BorderPr ctBorderPr = ctBorder.IsSetTop() ? ctBorder.top : ctBorder.AddNewTop();
if (color != null)
{
ctBorderPr.color = color.GetCTColor();
}
else
{
ctBorderPr.UnsetColor();
}
this._cellXf.borderId = (uint)this._stylesSource.PutBorder(new XSSFCellBorder(ctBorder, this._theme));
this._cellXf.applyBorder = true;
}
/**
* Set the color to use for the selected border
*
* @param side - where to apply the color defInition
* @param color - the color to use
*/
public void SetBorderColor(BorderSide side, XSSFColor color)
{
switch (side)
{
case BorderSide.BOTTOM:
SetBottomBorderColor(color);
break;
case BorderSide.RIGHT:
SetRightBorderColor(color);
break;
case BorderSide.TOP:
SetTopBorderColor(color);
break;
case BorderSide.LEFT:
SetLeftBorderColor(color);
break;
}
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().GetRgb()[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().GetRgb()[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().GetRgb()[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().GetRgb()[3]);
//Integer.toHexString(0xF1F1F1).getBytes() = [102, 49, 102, 49, 102, 49]
color.SetRgb(Encoding.ASCII.GetBytes("f1f1f1"));
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.GetRgb()[2]);
}
public XSSFDataBarFormatting CreateDataBarFormatting(XSSFColor color)
{
// Is it already there?
if (_cfRule.IsSetDataBar() && _cfRule.type == ST_CfType.dataBar)
{
return(DataBarFormatting as XSSFDataBarFormatting);
}
// Mark it as being a Data Bar
_cfRule.type = ST_CfType.dataBar;
// Ensure the right element
CT_DataBar bar = null;
if (_cfRule.IsSetDataBar())
{
bar = _cfRule.dataBar;
}
else
{
bar = _cfRule.AddNewDataBar();
}
// Set the color
bar.color = (color.GetCTColor());
// Add the default thresholds
CT_Cfvo min = bar.AddNewCfvo();
min.type = (ST_CfvoType)Enum.Parse(typeof(ST_CfvoType), RangeType.MIN.name);
CT_Cfvo max = bar.AddNewCfvo();
max.type = (ST_CfvoType)Enum.Parse(typeof(ST_CfvoType), RangeType.MAX.name);
// Wrap and return
return(new XSSFDataBarFormatting(bar));
}
/**
* Set the foreground fill color represented as a {@link XSSFColor} value.
* <br/>
* <i>Note: Ensure Foreground color is Set prior to background color.</i>
* @param color the color to use
* @see #setFillBackgroundColor(NPOI.xssf.usermodel.XSSFColor) )
*/
public void SetFillForegroundColor(XSSFColor color)
{
CT_Fill ct = GetCTFill();
CT_PatternFill ptrn = ct.GetPatternFill();
if (color == null)
{
if (ptrn != null) ptrn.unsetFgColor();
}
else
{
if (ptrn == null) ptrn = ct.AddNewPatternFill();
ptrn.fgColor =(color.GetCTColor());
}
int idx = _stylesSource.PutFill(new XSSFCellFill(ct));
_cellXf.fillId = (uint)idx;
_cellXf.applyFill = (true);
}
/**
* Set the color to use for the selected border
*
* @param side - where to apply the color defInition
* @param color - the color to use
*/
public void SetBorderColor(BorderSide side, XSSFColor color)
{
switch (side)
{
case BorderSide.BOTTOM:
SetBottomBorderColor(color);
break;
case BorderSide.RIGHT:
SetRightBorderColor(color);
break;
case BorderSide.TOP:
SetTopBorderColor(color);
break;
case BorderSide.LEFT:
SetLeftBorderColor(color);
break;
}
}
/**
* Set the color to use for the bottom border
*
* @param color the color to use, null means no color
*/
public void SetBottomBorderColor(XSSFColor color)
{
CT_Border ct = GetCTBorder();
if (color == null && !ct.IsSetBottom()) return;
CT_BorderPr pr = ct.IsSetBottom() ? ct.bottom : ct.AddNewBottom();
if (color != null) pr.SetColor(color.GetCTColor());
else pr.UnsetColor();
int idx = _stylesSource.PutBorder(new XSSFCellBorder(ct, _theme));
_cellXf.borderId = (uint)idx;
_cellXf.applyBorder = (true);
}
public void TestGetSetRightBorderColor()
{
//defaults
Assert.AreEqual(IndexedColors.Black.Index, cellStyle.RightBorderColor);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
int num = stylesTable.GetBorders().Count;
XSSFColor clr;
//setting indexed color
cellStyle.RightBorderColor = (IndexedColors.BlueGrey.Index);
Assert.AreEqual(IndexedColors.BlueGrey.Index, cellStyle.RightBorderColor);
clr = cellStyle.RightBorderXSSFColor;
Assert.IsTrue(clr.GetCTColor().IsSetIndexed());
Assert.AreEqual(IndexedColors.BlueGrey.Index, clr.Indexed);
//a new border was Added to the styles table
Assert.AreEqual(num + 1, stylesTable.GetBorders().Count);
//id of the Created border
int borderId = (int)cellStyle.GetCoreXf().borderId;
Assert.IsTrue(borderId > 0);
//check Changes in the underlying xml bean
CT_Border ctBorder = stylesTable.GetBorderAt(borderId).GetCTBorder();
Assert.AreEqual((uint)IndexedColors.BlueGrey.Index, ctBorder.right.color.indexed);
//setting XSSFColor
num = stylesTable.GetBorders().Count;
clr = new XSSFColor(Color.Cyan);
cellStyle.SetRightBorderColor(clr);
Assert.AreEqual(clr.GetCTColor().ToString(), cellStyle.RightBorderXSSFColor.GetCTColor().ToString());
byte[] rgb = cellStyle.RightBorderXSSFColor.GetRgb();
Assert.AreEqual(Color.Cyan.ToArgb(), Color.FromArgb(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF).ToArgb());
//another border was Added to the styles table
Assert.AreEqual(num, stylesTable.GetBorders().Count);
//passing null unsets the color
cellStyle.SetRightBorderColor(null);
Assert.IsNull(cellStyle.RightBorderXSSFColor);
}
public void TestGetSetFillBackgroundColor()
{
Assert.AreEqual(IndexedColors.Automatic.Index, cellStyle.FillBackgroundColor);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
XSSFColor clr;
int num = stylesTable.GetFills().Count;
//setting indexed color
cellStyle.FillBackgroundColor = (IndexedColors.Red.Index);
Assert.AreEqual(IndexedColors.Red.Index, cellStyle.FillBackgroundColor);
clr = (XSSFColor)cellStyle.FillBackgroundColorColor;
Assert.IsTrue(clr.GetCTColor().IsSetIndexed());
Assert.AreEqual(IndexedColors.Red.Index, clr.Indexed);
//a new fill was Added to the styles table
Assert.AreEqual(num + 1, stylesTable.GetFills().Count);
//id of the Created border
int FillId = (int)cellStyle.GetCoreXf().fillId;
Assert.IsTrue(FillId > 0);
//check changes in the underlying xml bean
CT_Fill ctFill = stylesTable.GetFillAt(FillId).GetCTFill();
Assert.AreEqual((uint)IndexedColors.Red.Index, ctFill.GetPatternFill().bgColor.indexed);
//setting XSSFColor
num = stylesTable.GetFills().Count;
clr = new XSSFColor(Color.Cyan);
cellStyle.SetFillBackgroundColor(clr); // TODO this testcase assumes that cellStyle creates a new CT_Fill, but the implementation changes the existing style. - do not know whats right 8-(
Assert.AreEqual(clr.GetCTColor().ToString(), ((XSSFColor)cellStyle.FillBackgroundColorColor).GetCTColor().ToString());
byte[] rgb = ((XSSFColor)cellStyle.FillBackgroundColorColor).GetRgb();
Assert.AreEqual(Color.Cyan.ToArgb(), Color.FromArgb(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF).ToArgb());
//another border was added to the styles table
Assert.AreEqual(num, stylesTable.GetFills().Count);
//passing null unsets the color
cellStyle.SetFillBackgroundColor(null);
Assert.IsNull(cellStyle.FillBackgroundColorColor);
Assert.AreEqual(IndexedColors.Automatic.Index, cellStyle.FillBackgroundColor);
}
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="xssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(XSSFWorkbook xssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
XSSFCellStyle cellstyle = xssfworkbook.CreateCellStyle() as XSSFCellStyle;
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.SetFillForegroundColor(fillForegroundColor);
}
if (fillBackgroundColor != null)
{
cellstyle.SetFillBackgroundColor(fillBackgroundColor);
}
if (font != null)
{
cellstyle.SetFont(font);
}
return cellstyle;
}
/// <summary>
/// create the colours for the COBie sheet
/// </summary>
/// <param name="colourName"></param>
/// <param name="red"></param>
/// <param name="green"></param>
/// <param name="blue"></param>
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
IColor colour = null;
if (IsXlsx)
{
byte[] rgb = new byte[3] { red, green, blue };
colour = new XSSFColor(rgb) as IColor;
}
else
{
HSSFPalette palette = ((HSSFWorkbook)ExcelWorkbook).GetCustomPalette();
colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// First 64 are system colours
//srl this code does not work with the latest version of NPOI
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 )
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
}
_colours.Add(colourName, colour);
}
private void CreateCadrePBEtMicroModule(ref int hauteur, int nombreCassette)
{
var blanc = new byte[3] { 255, 255, 255 };
var endHauteur = hauteur + 1;
var mergePB = new CellRangeAddress(hauteur, endHauteur, 20, 21);
_cadreCreator.Create(hauteur, 20, endHauteur, 21, blanc, BorderStyle.Medium, mergePB, false, true);
var cell = _sheet.GetRow(hauteur).GetCell(20);
var style = cell.CellStyle;
var myFontColor = new XSSFColor(new byte[3] { 255, 0, 0 });
var font = (XSSFFont)_workbook.CreateFont();
font.SetColor(myFontColor);
style.SetFont(font);
cell.CellStyle = style;
var nombre = (decimal)nombreCassette / 2;
cell.SetCellValue(Math.Round(nombre, MidpointRounding.AwayFromZero) + " PB");
var mergeMicroModule = new CellRangeAddress(hauteur, endHauteur, 22, 23);
_cadreCreator.Create(hauteur, 22, endHauteur, 23, blanc, BorderStyle.Medium, mergeMicroModule, false, true);
var cellMicroModule = _sheet.GetRow(hauteur).GetCell(22);
var styleMicroModule = cellMicroModule.CellStyle;
var myFontColorMicroModule = new XSSFColor(new byte[3] { 226, 107, 10 });
var fontMicroModule = (XSSFFont)_workbook.CreateFont();
fontMicroModule.SetColor(myFontColorMicroModule);
styleMicroModule.SetFont(fontMicroModule);
cellMicroModule.CellStyle = styleMicroModule;
cellMicroModule.SetCellValue(nombreCassette + " µm");
hauteur = hauteur + 2;
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontfamily">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <param name="bold">是否加粗</param>
/// <param name="typeOffset">上下标</param>
/// <returns></returns>
public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize, FontBoldWeight bold = FontBoldWeight.Normal, FontSuperScript typeOffset = FontSuperScript.None)
{
XSSFFont xfont = hssfworkbook.CreateFont() as XSSFFont;
if (string.IsNullOrEmpty(fontfamily))
{
xfont.FontName = fontfamily;
}
if (fontcolor != null)
{
xfont.SetColor(fontcolor);
}
xfont.IsItalic = false;
xfont.FontHeightInPoints = (short)fontsize;
xfont.Boldweight = (short)bold;
xfont.TypeOffset = typeOffset;
return xfont;
}
/**
* Get the color to use for the selected border
*
* @param side - where to apply the color defInition
* @return color - color to use as XSSFColor. null if color is not set
*/
public XSSFColor GetBorderColor(BorderSide side)
{
CT_BorderPr borderPr = GetBorder(side);
if (borderPr != null && borderPr.IsSetColor())
{
XSSFColor clr = new XSSFColor(borderPr.color);
if (_theme != null)
{
_theme.InheritFromThemeAsRequired(clr);
}
return clr;
}
else
{
// No border set
return null;
}
}
private bool SameAuto(XSSFColor other)
{
return(IsAuto == other.IsAuto);
}
/**
* Set the color to use for the selected border
*
* @param side - where to apply the color defInition
* @param color - the color to use
*/
public void SetBorderColor(BorderSide side, XSSFColor color)
{
CT_BorderPr borderPr = GetBorder(side, true);
if (color == null) borderPr.UnsetColor();
else
borderPr.color = color.GetCTColor();
}
/**
* get the color value for the font
* References a color defined as Standard Alpha Red Green Blue color value (ARGB).
*
* @return XSSFColor - rgb color to use
*/
public XSSFColor GetXSSFColor()
{
Spreadsheet.CT_Color ctColor = _ctFont.sizeOfColorArray() == 0 ? null : _ctFont.GetColorArray(0);
if (ctColor != null)
{
XSSFColor color = new XSSFColor(ctColor);
if (_themes != null)
{
_themes.InheritFromThemeAsRequired(color);
}
return color;
}
else
{
return null;
}
}
/**
* set the color for the font in Standard Alpha Red Green Blue color value
*
* @param color - color to use
*/
public void SetColor(XSSFColor color)
{
if (color == null) _ctFont.SetColorArray(null);
else
{
Spreadsheet.CT_Color ctColor = _ctFont.sizeOfColorArray() == 0 ? _ctFont.AddNewColor() : _ctFont.GetColorArray(0);
ctColor.SetRgb(color.GetRgb());
}
}
/**
* If the colour is based on a theme, then inherit
* information (currently just colours) from it as
* required.
*/
public void InheritFromThemeAsRequired(XSSFColor color)
{
if (color == null)
{
// Nothing for us to do
return;
}
if (!color.GetCTColor().themeSpecified)
{
// No theme Set, nothing to do
return;
}
// Get the theme colour
XSSFColor themeColor = GetThemeColor(color.Theme);
// Set the raw colour, not the adjusted one
// Do a raw Set, no adjusting at the XSSFColor layer either
color.GetCTColor().SetRgb(themeColor.GetCTColor().GetRgb());
// All done
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().GetRgb()[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().GetRgb()[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().GetRgb()[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().GetRgb()[3]);
//Integer.toHexString(0xF1F1F1).getBytes() = [102, 49, 102, 49, 102, 49]
color.SetRgb(Encoding.ASCII.GetBytes("f1f1f1"));
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.GetRgb()[2]);
}
