public void GetCellStyleAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
try
{
short i = 0;
//get default style
ICellStyle cellStyleAt = workbook.GetCellStyleAt(i);
Assert.IsNotNull(cellStyleAt);
//get custom style
StylesTable styleSource = workbook.GetStylesSource();
XSSFCellStyle customStyle = new XSSFCellStyle(styleSource);
XSSFFont font = new XSSFFont();
font.FontName = ("Verdana");
customStyle.SetFont(font);
int x = styleSource.PutStyle(customStyle);
cellStyleAt = workbook.GetCellStyleAt((short)x);
Assert.IsNotNull(cellStyleAt);
}
finally
{
}
}
public void Bug49702()
{
// First try with a new file
XSSFWorkbook wb = new XSSFWorkbook();
// Should have one style
Assert.AreEqual(1, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
try
{
wb.GetCellStyleAt((short)1);
Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Add another one
ICellStyle cs = wb.CreateCellStyle();
cs.DataFormat = ((short)11);
// Re-check
Assert.AreEqual(2, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
wb.GetCellStyleAt((short)1);
try
{
wb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Save and reload
XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(2, nwb.NumCellStyles);
nwb.GetCellStyleAt((short)0);
nwb.GetCellStyleAt((short)1);
try
{
nwb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Now with an existing file
wb = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");
Assert.AreEqual(3, wb.NumCellStyles);
wb.GetCellStyleAt((short)0);
wb.GetCellStyleAt((short)1);
wb.GetCellStyleAt((short)2);
try
{
wb.GetCellStyleAt((short)3);
Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
}
public void Test58084()
{
IWorkbook reference = XSSFTestDataSamples.OpenSampleWorkbook("template.xlsx");
IWorkbook target = new XSSFWorkbook();
copyStyles(reference, target);
Assert.AreEqual(reference.NumCellStyles, target.NumCellStyles);
ISheet sheet = target.CreateSheet();
IRow row = sheet.CreateRow(0);
int col = 0;
for (short i = 1; i < target.NumCellStyles; i++)
{
ICell cell = row.CreateCell(col++);
cell.SetCellValue("Coucou" + i);
cell.CellStyle = (target.GetCellStyleAt(i));
}
/*OutputStream out = new FileOutputStream("C:\\temp\\58084.xlsx");
* target.write(out);
* out.close();*/
IWorkbook copy = XSSFTestDataSamples.WriteOutAndReadBack(target);
// previously this Assert.Failed because the border-element was not copied over
var xxx = copy.GetCellStyleAt((short)1).BorderBottom;
copy.Close();
target.Close();
reference.Close();
}
public NpoiExcelCreator()
{
try
{
logger = Logger.getInstance;
consoleLogger = ConsoleLogger.getInstance;
//iworkbook = new HSSFWorkbook();
workbook = new XSSFWorkbook();
XSSFFont ffont = (XSSFFont)workbook.CreateFont();
ffont.FontHeight = fontSize * fontSize;
ffont.SetColor(new XSSFColor(fontRgb));
ffont.FontName = fontStyle;
defaultCellStyle = (XSSFCellStyle)workbook.GetCellStyleAt(0);
}
catch (Exception ex)
{
logger.logException(ex);
}
//defaultCellStyle.SetFillForegroundColor(new XSSFColor(cellRgb));
//defaultCellStyle.FillPattern = FillPattern.SolidForeground;
//defaultCellStyle.SetFont(ffont);
//defaultCellStyle.BorderBottom = BorderStyle.Thin;
//defaultCellStyle.BorderTop = BorderStyle.Thin;
//defaultCellStyle.BorderLeft = BorderStyle.Thin;
//defaultCellStyle.BorderRight= BorderStyle.Thin;
}
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]);
}
public NpoiExcelReadWrite(string ExcelDocument)
{
try
{
file = new FileStream(ExcelDocument, FileMode.Open, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(file);
file.Close();
defaultCellStyle = (XSSFCellStyle)workbook.GetCellStyleAt(0);
}
catch (Exception ex)
{
}
}
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 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());
}
private string styleName(ICellStyle style)
{
if (style == null)
{
if (isXSSFWorkbook)
{
style = _work.GetCellStyleAt((short)0);
}
else
{
style = wb.GetCellStyleAt((short)0);
}
}
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("style_{0}", style.Index));
return(sb.ToString());
}
public void TestGetFillForegroundColor()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable styles = wb.GetStylesSource();
Assert.AreEqual(1, wb.NumCellStyles);
Assert.AreEqual(2, styles.GetFills().Count);
XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPattern.SolidForeground);
Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
public ICellStyle GetCellStyleAt(short idx)
{
return(XssfWorkbook.GetCellStyleAt(idx));
}