| public GetStylesSource ( ) : NPOI.XSSF.Model.StylesTable | ||
| return | NPOI.XSSF.Model.StylesTable | |
public void TestLoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.AreEqual("dd/mm/yyyy", workbook.GetSheetAt(0).GetRow(1).GetCell(0).RichStringCellValue.String);
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
// Write out, and check
// Load up again, check all still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
Assert.AreEqual("dd/mm/yyyy", wb2.GetSheetAt(0).GetRow(1).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yyyy/mm/dd", wb2.GetSheetAt(0).GetRow(2).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yyyy-mm-dd", wb2.GetSheetAt(0).GetRow(3).GetCell(0).RichStringCellValue.String);
Assert.AreEqual("yy/mm/dd", wb2.GetSheetAt(0).GetRow(4).GetCell(0).RichStringCellValue.String);
Assert.IsNotNull(wb2.GetSharedStringSource());
Assert.IsNotNull(wb2.GetStylesSource());
}
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook parent = (XSSFWorkbook)this.GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(parent.GetStylesSource());
CT_TextParagraph ctTextParagraph = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun ctRegularTextRun = ctTextParagraph.AddNewR();
CT_TextCharacterProperties characterProperties = ctRegularTextRun.AddNewRPr();
characterProperties.lang = "en-US";
characterProperties.sz = 1100;
ctRegularTextRun.t = str.String;
}
else
{
for (int index = 0; index < str.GetCTRst().sizeOfRArray(); ++index)
{
CT_RElt rarray = str.GetCTRst().GetRArray(index);
CT_RPrElt pr = rarray.rPr ?? rarray.AddNewRPr();
CT_RegularTextRun ctRegularTextRun = ctTextParagraph.AddNewR();
CT_TextCharacterProperties rPr = ctRegularTextRun.AddNewRPr();
rPr.lang = "en-US";
XSSFSimpleShape.ApplyAttributes(pr, rPr);
ctRegularTextRun.t = rarray.t;
}
}
this.ctShape.txBody.SetPArray(new CT_TextParagraph[1]
{
ctTextParagraph
});
}
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
{
}
}
/**
* Creates a new XSSFRichTextString for you.
*/
public IRichTextString CreateRichTextString(String text)
{
XSSFRichTextString rt = new XSSFRichTextString(text);
rt.SetStylesTableReference(workbook.GetStylesSource());
return(rt);
}
public void TestBug52348()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(0, st.GetStyleXfsSize());
XSSFCellStyle style = workbook.CreateCellStyle() as XSSFCellStyle; // no exception at this point
Assert.IsNull(style.GetStyleXf());
}
public void TestBug55650()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("52348.xlsx");
StylesTable st = workbook.GetStylesSource();
Assert.AreEqual(0, st.StyleXfsSize);
// no exception at this point
XSSFCellStyle style = workbook.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle as XSSFCellStyle;
Assert.IsNull(style.GetStyleXf());
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(workbook));
}
public void TestStyles()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
StylesTable ss = workbook.GetStylesSource();
Assert.IsNotNull(ss);
StylesTable st = ss;
// Has 8 number formats
Assert.AreEqual(8, st._GetNumberFormatSize());
// Has 2 fonts
Assert.AreEqual(2, st.GetFonts().Count);
// Has 2 Fills
Assert.AreEqual(2, st.GetFills().Count);
// Has 1 border
Assert.AreEqual(1, st.GetBorders().Count);
// Add two more styles
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 8,
st.PutNumberFormat("testFORMAT"));
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 8,
st.PutNumberFormat("testFORMAT"));
Assert.AreEqual(StylesTable.FIRST_CUSTOM_STYLE_ID + 9,
st.PutNumberFormat("testFORMAT2"));
Assert.AreEqual(10, st._GetNumberFormatSize());
// Save, load back in again, and check
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ss = workbook.GetStylesSource();
Assert.IsNotNull(ss);
Assert.AreEqual(10, st._GetNumberFormatSize());
Assert.AreEqual(2, st.GetFonts().Count);
Assert.AreEqual(2, st.GetFills().Count);
Assert.AreEqual(1, st.GetBorders().Count);
}
public void TestExisting()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
// And check a few low level bits too
OPCPackage pkg = OPCPackage.Open(HSSFTestDataSamples.OpenSampleFileStream("Formatting.xlsx"));
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared, styles and themes
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(6, wbPart.Relationships.Size);
}
public void TestCreateSaveLoad()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st._GetXfsSize());
Assert.AreEqual(1, st._GetStyleXfsSize());
Assert.AreEqual(0, st._GetNumberFormatSize());
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st._GetXfsSize());
Assert.AreEqual(1, st._GetStyleXfsSize());
Assert.AreEqual(0, st._GetNumberFormatSize());
}
public override void Test49928()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("49928.xlsx");
doTest49928Core(wb);
// an attempt to register an existing format returns its index
int poundFmtIdx = wb.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle.DataFormat;
Assert.AreEqual(poundFmtIdx, wb.GetStylesSource().PutNumberFormat(poundFmt));
// now create a custom format with Pound (\u00a3)
IDataFormat dataFormat = wb.CreateDataFormat();
short customFmtIdx = dataFormat.GetFormat("\u00a3##.00[Yellow]");
Assert.IsTrue(customFmtIdx > BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
Assert.AreEqual("\u00a3##.00[Yellow]", dataFormat.GetFormat(customFmtIdx));
}
public void TestGetFontAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
StylesTable styleSource = workbook.GetStylesSource();
short i = 0;
//get default font
IFont fontAt = workbook.GetFontAt(i);
Assert.IsNotNull(fontAt);
//get customized font
XSSFFont customFont = new XSSFFont();
customFont.IsItalic = (true);
int x = styleSource.PutFont(customFont);
fontAt = workbook.GetFontAt((short)x);
Assert.IsNotNull(fontAt);
}
/**
* Set a single paragraph of text on the shape. Note this will replace all existing paragraphs Created on the shape.
* @param str rich text string representing the paragraph text
*/
public void SetText(XSSFRichTextString str)
{
XSSFWorkbook wb = (XSSFWorkbook)GetDrawing().GetParent().GetParent();
str.SetStylesTableReference(wb.GetStylesSource());
CT_TextParagraph p = new CT_TextParagraph();
if (str.NumFormattingRuns == 0)
{
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
rPr.sz = (/*setter*/ 1100);
r.t = (/*setter*/ str.String);
}
else
{
for (int i = 0; i < str.GetCTRst().SizeOfRArray(); i++)
{
CT_RElt lt = str.GetCTRst().GetRArray(i);
CT_RPrElt ltPr = lt.rPr;
if (ltPr == null)
{
ltPr = lt.AddNewRPr();
}
CT_RegularTextRun r = p.AddNewR();
CT_TextCharacterProperties rPr = r.AddNewRPr();
rPr.lang = (/*setter*/ "en-US");
ApplyAttributes(ltPr, rPr);
r.t = (/*setter*/ lt.t);
}
}
ClearText();
ctShape.txBody.SetPArray(new CT_TextParagraph[] { p });
_paragraphs.Add(new XSSFTextParagraph(ctShape.txBody.GetPArray(0), ctShape));
}
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(FillPatternType.NoFill, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SolidForeground);
Assert.AreEqual(FillPatternType.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 = (FillPatternType.SolidForeground);
Assert.AreEqual(FillPatternType.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 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(FillPatternType.NO_FILL, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
}
/**
* Repeatedly writing the same file which has styles
* TODO Currently failing
*/
public void DISABLEDtest49940()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("styles.xlsx");
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual(10, wb.GetStylesSource().GetNumCellStyles());
MemoryStream b1 = new MemoryStream();
MemoryStream b2 = new MemoryStream();
MemoryStream b3 = new MemoryStream();
wb.Write(b1);
wb.Write(b2);
wb.Write(b3);
foreach (byte[] data in new byte[][] {
b1.ToArray(), b2.ToArray(), b3.ToArray()
})
{
MemoryStream bais = new MemoryStream(data);
wb = new XSSFWorkbook(bais);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual(10, wb.GetStylesSource().GetNumCellStyles());
}
}
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 void TestSaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
}
public void TestPopulateNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
StylesTable st = wb.GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(1, st._GetXfsSize());
Assert.AreEqual(1, st._GetStyleXfsSize());
Assert.AreEqual(0, st._GetNumberFormatSize());
int nf1 = st.PutNumberFormat("yyyy-mm-dd");
int nf2 = st.PutNumberFormat("yyyy-mm-DD");
Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));
st.PutStyle(new XSSFCellStyle(st));
// Save and re-load
st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();
Assert.IsNotNull(st.GetCTStylesheet());
Assert.AreEqual(2, st._GetXfsSize());
Assert.AreEqual(1, st._GetStyleXfsSize());
Assert.AreEqual(2, st._GetNumberFormatSize());
Assert.AreEqual("yyyy-mm-dd", st.GetNumberFormatAt(nf1));
Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));
Assert.AreEqual(nf2, st.PutNumberFormat("yyyy-mm-DD"));
}
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(FillPatternType.NO_FILL, defaultStyle.FillPattern);
XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();
customStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, customStyle.FillPattern);
Assert.AreEqual(3, styles.GetFills().Count);
customStyle.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, customStyle.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
for (int i = 0; i < 3; i++)
{
XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();
style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, style.FillPattern);
Assert.AreEqual(4, styles.GetFills().Count);
style.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, style.FillForegroundColor);
Assert.AreEqual(4, styles.GetFills().Count);
}
}
public void TestSetDefaultColumnStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
StylesTable stylesTable = workbook.GetStylesSource();
XSSFFont font = new XSSFFont();
font.FontName = ("Cambria");
stylesTable.PutFont(font);
CT_Xf cellStyleXf = new CT_Xf();
cellStyleXf.fontId = (1);
cellStyleXf.fillId= 0;
cellStyleXf.borderId= 0;
cellStyleXf.numFmtId= 0;
stylesTable.PutCellStyleXf(cellStyleXf);
CT_Xf cellXf = new CT_Xf();
cellXf.xfId = (1);
stylesTable.PutCellXf(cellXf);
XSSFCellStyle cellStyle = new XSSFCellStyle(1, 1, stylesTable, null);
Assert.AreEqual(1, cellStyle.FontIndex);
sheet.SetDefaultColumnStyle(3, cellStyle);
Assert.AreEqual(1u, ctWorksheet.GetColsArray(0).GetColArray(0).style);
}
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 TestSaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
}
public void TestGetCellStyleAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
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);
}
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 void TestGetFontAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
StylesTable styleSource = workbook.GetStylesSource();
short i = 0;
//get default font
IFont fontAt = workbook.GetFontAt(i);
Assert.IsNotNull(fontAt);
//get customized font
XSSFFont customFont = new XSSFFont();
customFont.IsItalic = (true);
int x = styleSource.PutFont(customFont);
fontAt = workbook.GetFontAt((short)x);
Assert.IsNotNull(fontAt);
}
