public void addDataFormatsBeyondUpperLimit() { XSSFWorkbook wb = new XSSFWorkbook(); try { StylesTable styles = wb.GetStylesSource(); styles.MaxNumberOfDataFormats = (0); // Try adding a format beyond the upper limit try { styles.PutNumberFormat("\"test \"0"); Assert.Fail("Expected to raise InvalidOperationException"); } catch (InvalidOperationException e) { if (e.Message.StartsWith("The maximum number of Data Formats was exceeded.")) { // expected } else { throw e; } } } finally { wb.Close(); } }
public void ExceedNumberFormatLimit() { XSSFWorkbook wb = new XSSFWorkbook(); try { StylesTable styles = wb.GetStylesSource(); for (int i = 0; i < styles.MaxNumberOfDataFormats; i++) { wb.GetStylesSource().PutNumberFormat("\"test" + i + " \"0"); } try { wb.GetStylesSource().PutNumberFormat("\"anotherformat \"0"); } catch (InvalidOperationException e) { if (e.Message.StartsWith("The maximum number of Data Formats was exceeded.")) { //expected } else { throw e; } } } finally { wb.Close(); } }
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 doTestExisting(StylesTable st) { // Check contents Assert.IsNotNull(st.GetCTStylesheet()); Assert.AreEqual(11, st.GetXfsSize()); Assert.AreEqual(1, st.GetStyleXfsSize()); Assert.AreEqual(8, st.GetNumberFormatSize()); Assert.AreEqual(2, st.GetFonts().Count); Assert.AreEqual(2, st.GetFills().Count); Assert.AreEqual(1, st.GetBorders().Count); Assert.AreEqual("yyyy/mm/dd", st.GetNumberFormatAt(165)); Assert.AreEqual("yy/mm/dd", st.GetNumberFormatAt(167)); Assert.IsNotNull(st.GetStyleAt(0)); Assert.IsNotNull(st.GetStyleAt(1)); Assert.IsNotNull(st.GetStyleAt(2)); Assert.AreEqual(0, st.GetStyleAt(0).DataFormat); Assert.AreEqual(14, st.GetStyleAt(1).DataFormat); Assert.AreEqual(0, st.GetStyleAt(2).DataFormat); Assert.AreEqual(165, st.GetStyleAt(3).DataFormat); Assert.AreEqual("yyyy/mm/dd", st.GetStyleAt(3).GetDataFormatString()); }
public void TestPopulateExisting() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile); Assert.IsNotNull(workbook.GetStylesSource()); StylesTable st = workbook.GetStylesSource(); Assert.AreEqual(11, st.GetXfsSize()); Assert.AreEqual(1, st.GetStyleXfsSize()); Assert.AreEqual(8, 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 = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook)).GetStylesSource(); Assert.AreEqual(11, st.GetXfsSize()); Assert.AreEqual(1, st.GetStyleXfsSize()); Assert.AreEqual(10, 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 doTestExisting(StylesTable st) { // Check contents Assert.IsNotNull(st.GetCTStylesheet()); Assert.AreEqual(11, st._GetXfsSize()); Assert.AreEqual(1, st._GetStyleXfsSize()); Assert.AreEqual(8, st._GetNumberFormatSize()); Assert.AreEqual(2, st.GetFonts().Count); Assert.AreEqual(2, st.GetFills().Count); Assert.AreEqual(1, st.GetBorders().Count); Assert.AreEqual("yyyy/mm/dd", st.GetNumberFormatAt(165)); Assert.AreEqual("yy/mm/dd", st.GetNumberFormatAt(167)); Assert.IsNotNull(st.GetStyleAt(0)); Assert.IsNotNull(st.GetStyleAt(1)); Assert.IsNotNull(st.GetStyleAt(2)); Assert.AreEqual(0, st.GetStyleAt(0).DataFormat); Assert.AreEqual(14, st.GetStyleAt(1).DataFormat); Assert.AreEqual(0, st.GetStyleAt(2).DataFormat); Assert.AreEqual(165, st.GetStyleAt(3).DataFormat); Assert.AreEqual("yyyy/mm/dd", st.GetStyleAt(3).GetDataFormatString()); }
public void decreaseUpperLimitBelowCurrentNumDataFormats() { XSSFWorkbook wb = new XSSFWorkbook(); try { StylesTable styles = wb.GetStylesSource(); styles.PutNumberFormat(customDataFormat); // Try decreasing the upper limit below the current number of formats try { styles.MaxNumberOfDataFormats = (0); Assert.Fail("Expected to raise InvalidOperationException"); } catch (InvalidOperationException e) { if (e.Message.StartsWith("Cannot set the maximum number of data formats less than the current quantity.")) { // expected } else { throw e; } } } finally { wb.Close(); } }
/** * Creates a Cell Style from the supplied parts * @param cellXfId The main XF for the cell. Must be a valid 0-based index into the XF table * @param cellStyleXfId Optional, style xf. A value of <code>-1</code> means no xf. * @param stylesSource Styles Source to work off */ public XSSFCellStyle(int cellXfId, int cellStyleXfId, StylesTable stylesSource, ThemesTable theme) { _cellXfId = cellXfId; _stylesSource = stylesSource; _cellXf = stylesSource.GetCellXfAt(this._cellXfId); _cellStyleXf = cellStyleXfId == -1 ? null : stylesSource.GetCellStyleXfAt(cellStyleXfId); _theme = theme; }
/** * Creates an empty Cell Style */ public XSSFCellStyle(StylesTable stylesSource) { _stylesSource = stylesSource; // We need a new CT_Xf for the main styles // TODO decide on a style ctxf _cellXf = new CT_Xf(); _cellStyleXf = null; }
public void TestCreateNew() { StylesTable st = new StylesTable(); // Check defaults Assert.IsNotNull(st.GetCTStylesheet()); Assert.AreEqual(1, st.GetXfsSize()); Assert.AreEqual(1, st.GetStyleXfsSize()); Assert.AreEqual(0, st.GetNumberFormatSize()); }
public void TestLoadExisting() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile); Assert.IsNotNull(workbook.GetStylesSource()); StylesTable st = workbook.GetStylesSource(); doTestExisting(st); }
public void TestCreateNew() { StylesTable st = new StylesTable(); // Check defaults Assert.IsNotNull(st.GetCTStylesheet()); Assert.AreEqual(1, st._GetXfsSize()); Assert.AreEqual(1, st._GetStyleXfsSize()); Assert.AreEqual(0, st._GetNumberFormatSize()); }
private void Initialize() { this.fonts.Add(StylesTable.CreateDefaultFont()); CT_Fill[] defaultFills = StylesTable.CreateDefaultFills(); this.fills.Add(new XSSFCellFill(defaultFills[0])); this.fills.Add(new XSSFCellFill(defaultFills[1])); this.borders.Add(new XSSFCellBorder(StylesTable.CreateDefaultBorder())); this.styleXfs.Add(StylesTable.CreateDefaultXf()); CT_Xf defaultXf = StylesTable.CreateDefaultXf(); defaultXf.xfId = 0U; this.xfs.Add(defaultXf); }
public void TestLoadSaveLoad() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile); Assert.IsNotNull(workbook.GetStylesSource()); StylesTable st = workbook.GetStylesSource(); doTestExisting(st); st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook)).GetStylesSource(); doTestExisting(st); }
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 void SetUp() { stylesTable = new StylesTable(); ctStylesheet = stylesTable.GetCTStylesheet(); ctBorderA = new CT_Border(); XSSFCellBorder borderA = new XSSFCellBorder(ctBorderA); long borderId = stylesTable.PutBorder(borderA); Assert.AreEqual(1, borderId); XSSFCellBorder borderB = new XSSFCellBorder(); Assert.AreEqual(1, stylesTable.PutBorder(borderB)); ctFill = new CT_Fill(); XSSFCellFill fill = new XSSFCellFill(ctFill); long fillId = stylesTable.PutFill(fill); Assert.AreEqual(2, fillId); ctFont = new CT_Font(); XSSFFont font = new XSSFFont(ctFont); long fontId = stylesTable.PutFont(font); Assert.AreEqual(1, fontId); cellStyleXf = ctStylesheet.AddNewCellStyleXfs().AddNewXf(); cellStyleXf.borderId = 1; cellStyleXf.fillId = 1; cellStyleXf.fontId = 1; cellXfs = ctStylesheet.AddNewCellXfs(); cellXf = cellXfs.AddNewXf(); cellXf.xfId = (1); cellXf.borderId = (1); cellXf.fillId = (1); cellXf.fontId = (1); stylesTable.PutCellStyleXf(cellStyleXf); stylesTable.PutCellXf(cellXf); cellStyle = new XSSFCellStyle(1, 1, stylesTable, null); Assert.IsNotNull(stylesTable.GetFillAt(1).GetCTFill().patternFill); Assert.AreEqual(ST_PatternType.darkGray, stylesTable.GetFillAt(1).GetCTFill().patternFill.patternType); }
public void maxNumberOfDataFormats() { XSSFWorkbook wb = new XSSFWorkbook(); try { StylesTable styles = wb.GetStylesSource(); // Check default limit int n = styles.MaxNumberOfDataFormats; // https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 Assert.IsTrue(200 <= n); Assert.IsTrue(n <= 250); // Check upper limit n = int.MaxValue; styles.MaxNumberOfDataFormats = (n); Assert.AreEqual(n, styles.MaxNumberOfDataFormats); // Check negative (illegal) limits try { styles.MaxNumberOfDataFormats = (-1); Assert.Fail("Expected to get an IllegalArgumentException(\"Maximum Number of Data Formats must be greater than or equal to 0\")"); } catch (ArgumentException e) { if (e.Message.StartsWith("Maximum Number of Data Formats must be greater than or equal to 0")) { // expected } else { throw e; } } } finally { wb.Close(); } }
public void TestAddNew() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet s = wb.CreateSheet() as XSSFSheet; Assert.AreEqual(null, wb.GetTheme()); StylesTable styles = wb.GetStylesSource(); Assert.AreEqual(null, styles.GetTheme()); styles.EnsureThemesTable(); Assert.IsNotNull(styles.GetTheme()); Assert.IsNotNull(wb.GetTheme()); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; styles = wb.GetStylesSource(); Assert.IsNotNull(styles.GetTheme()); Assert.IsNotNull(wb.GetTheme()); }
/** * Verifies that this style belongs to the supplied Workbook * Styles Source. * Will throw an exception if it belongs to a different one. * This is normally called when trying to assign a style to a * cell, to ensure the cell and the style are from the same * workbook (if they're not, it won't work) * @throws ArgumentException if there's a workbook mis-match */ public void VerifyBelongsToStylesSource(StylesTable src) { if (this._stylesSource != src) { throw new ArgumentException("This Style does not belong to the supplied Workbook Stlyes Source. Are you trying to assign a style from one workbook to the cell of a differnt workbook?"); } }
/** * Create a new CT_Workbook with all values Set to default */ private void OnWorkbookCreate() { doc = new WorkbookDocument(); workbook = doc.Workbook; // don't EVER use the 1904 date system CT_WorkbookPr workbookPr = workbook.AddNewWorkbookPr(); workbookPr.date1904 = (false); CT_BookViews bvs = workbook.AddNewBookViews(); CT_BookView bv = bvs.AddNewWorkbookView(); bv.activeTab = 0; workbook.AddNewSheets(); ExtendedProperties expProps = GetProperties().ExtendedProperties; CT_ExtendedProperties ctExtendedProp= expProps.GetUnderlyingProperties(); ctExtendedProp.Application = DOCUMENT_CREATOR; ctExtendedProp.DocSecurity = 0; ctExtendedProp.DocSecuritySpecified = true; ctExtendedProp.ScaleCrop = false; ctExtendedProp.ScaleCropSpecified = true; ctExtendedProp.LinksUpToDate = false; ctExtendedProp.LinksUpToDateSpecified = true; ctExtendedProp.HyperlinksChanged = false; ctExtendedProp.HyperlinksChangedSpecified = true; ctExtendedProp.SharedDoc = false; ctExtendedProp.SharedDocSpecified = true; sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance()); stylesSource = (StylesTable)CreateRelationship(XSSFRelation.STYLES, XSSFFactory.GetInstance()); namedRanges = new List<XSSFName>(); sheets = new List<XSSFSheet>(); pivotTables = new List<XSSFPivotTable>(); }
internal override void OnDocumentRead() { try { XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream()); doc = WorkbookDocument.Parse(xmldoc, NamespaceManager); this.workbook = doc.Workbook; Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>(); Dictionary<String, ExternalLinksTable> elIdMap = new Dictionary<String, ExternalLinksTable>(); foreach (POIXMLDocumentPart p in GetRelations()) { if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p; else if (p is StylesTable) stylesSource = (StylesTable)p; else if (p is ThemesTable) theme = (ThemesTable)p; else if (p is CalculationChain) calcChain = (CalculationChain)p; else if (p is MapInfo) mapInfo = (MapInfo)p; else if (p is XSSFSheet) { shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p); } else if (p is ExternalLinksTable) { elIdMap.Add(p.GetPackageRelationship().Id, (ExternalLinksTable)p); } } bool packageReadOnly = (Package.GetPackageAccess() == PackageAccess.READ); if (stylesSource == null) { // Create Styles if it is missing if (packageReadOnly) { stylesSource = new StylesTable(); } else { stylesSource = (StylesTable)CreateRelationship(XSSFRelation.STYLES, XSSFFactory.GetInstance()); } } stylesSource.SetTheme(theme); if (sharedStringSource == null) { //Create SST if it is missing if (packageReadOnly) { sharedStringSource = new SharedStringsTable(); } else { sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance()); } } // Load individual sheets. The order of sheets is defined by the order // of CTSheet elements in the workbook sheets = new List<XSSFSheet>(shIdMap.Count); foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet) { XSSFSheet sh = null; if(shIdMap.ContainsKey(ctSheet.id)) sh = shIdMap[ctSheet.id]; if (sh == null) { logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping"); continue; } sh.sheet = ctSheet; sh.OnDocumentRead(); sheets.Add(sh); } // Load the external links tables. Their order is defined by the order // of CTExternalReference elements in the workbook externalLinks = new List<ExternalLinksTable>(elIdMap.Count); if (this.workbook.IsSetExternalReferences()) { foreach (CT_ExternalReference er in this.workbook.externalReferences.externalReference) { ExternalLinksTable el = null; if(elIdMap.ContainsKey(er.id)) el = elIdMap[(er.id)]; if (el == null) { logger.Log(POILogger.WARN, "ExternalLinksTable with r:id " + er.id + " was defined, but didn't exist in package, skipping"); continue; } externalLinks.Add(el); } } // Process the named ranges ReprocessNamedRanges(); } catch (XmlException e) { throw new POIXMLException(e); } }
public XSSFDataFormat(StylesTable stylesSource) { this.stylesSource = stylesSource; }
/** * Construct a XSSFCell. * * @param row the parent row. * @param cell the xml bean Containing information about the cell. */ public XSSFCell(XSSFRow row, CT_Cell cell) { _cell = cell; _row = row; if (cell.r != null) { _cellNum = new CellReference(cell.r).Col; } _sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource(); _stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource(); }
public void SetStylesTableReference(StylesTable stylestable) { this.styles = stylestable; }
/** * Construct a XSSFCell. * * @param row the parent row. * @param cell the xml bean Containing information about the cell. */ public XSSFCell(XSSFRow row, CT_Cell cell) { _cell = cell; _row = row; if (cell.r != null) { _cellNum = new CellReference(cell.r).Col; } else { int prevNum = row.LastCellNum; if (prevNum != -1) { _cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1; } } _sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource(); _stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource(); }
/** * Processes the given sheet */ public void ProcessSheet( SheetContentsHandler sheetContentsExtractor, StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) { DataFormatter formatter; if (locale == null) { formatter = new DataFormatter(); } else { formatter = new DataFormatter(locale); } InputSource sheetSource = new InputSource(sheetInputStream); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); try { SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.GetXMLReader(); ContentHandler handler = new XSSFSheetXMLHandler( styles, strings, sheetContentsExtractor, formatter, formulasNotResults); sheetParser.SetContentHandler(handler); sheetParser.Parse(sheetSource); } catch (ParserConfigurationException e) { throw new RuntimeException("SAX Parser appears to be broken - " + e.GetMessage()); } }
internal override void OnDocumentRead() { try { XmlDocument xmldoc = ConvertStreamToXml(GetPackagePart().GetInputStream()); doc = WorkbookDocument.Parse(xmldoc, NamespaceManager); this.workbook = doc.Workbook; Dictionary<String, XSSFSheet> shIdMap = new Dictionary<String, XSSFSheet>(); foreach (POIXMLDocumentPart p in GetRelations()) { if (p is SharedStringsTable) sharedStringSource = (SharedStringsTable)p; else if (p is StylesTable) stylesSource = (StylesTable)p; else if (p is ThemesTable) theme = (ThemesTable)p; else if (p is CalculationChain) calcChain = (CalculationChain)p; else if (p is MapInfo) mapInfo = (MapInfo)p; else if (p is XSSFSheet) { shIdMap.Add(p.GetPackageRelationship().Id,(XSSFSheet)p); } } if (null != stylesSource) { stylesSource.SetTheme(theme); } if (sharedStringSource == null) { //Create SST if it is missing sharedStringSource = (SharedStringsTable)CreateRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.GetInstance()); } // Load individual sheets. The order of sheets is defined by the order of CT_Sheet elements in the workbook sheets = new List<XSSFSheet>(shIdMap.Count); foreach (CT_Sheet ctSheet in this.workbook.sheets.sheet) { XSSFSheet sh = shIdMap[ctSheet.id]; if (sh == null) { logger.Log(POILogger.WARN, "Sheet with name " + ctSheet.name + " and r:id " + ctSheet.id + " was defined, but didn't exist in package, skipping"); continue; } sh.sheet = ctSheet; sh.OnDocumentRead(); sheets.Add(sh); } // Process the named ranges namedRanges = new List<XSSFName>(); if (workbook.IsSetDefinedNames()) { foreach (CT_DefinedName ctName in workbook.definedNames.definedName) { namedRanges.Add(new XSSFName(ctName, this)); } } } catch (XmlException e) { throw new POIXMLException(e); } }
///** // * Perform a registration of ourselves // * to the style table // */ public long RegisterTo(StylesTable styles) { this._themes = styles.GetTheme(); short idx = (short)styles.PutFont(this, true); this._index = idx; return idx; }
public void SetStylesTableReference(StylesTable stylestable) { this.styles = stylestable; if (st.sizeOfRArray() > 0) { foreach (CT_RElt r in st.r) { CT_RPrElt pr = r.rPr; if (pr != null && pr.sizeOfRFontArray() > 0) { String fontName = pr.GetRFontArray(0).val; if (fontName.StartsWith("#")) { int idx = int.Parse(fontName.Substring(1)); XSSFFont font = styles.GetFontAt(idx); pr.rFont = null; SetRunAttributes(font.GetCTFont(), pr); } } } } }
public void TestApplyFontWithStyles() { XSSFRichTextString rt = new XSSFRichTextString("Apache POI"); StylesTable tbl = new StylesTable(); rt.SetStylesTableReference(tbl); try { rt.ApplyFont(0, 10, (short)1); Assert.Fail("Fails without styles in the table"); } catch (ArgumentOutOfRangeException e) { // expected } tbl.PutFont(new XSSFFont()); rt.ApplyFont(0, 10, (short)1); rt.ApplyFont((short)1); }