public void TestToFormulaString() { Area3DPtg target = new Area3DPtg("A1:B1", (short)0); String sheetName = "my sheet"; HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "Sheet1"); Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "C64"); Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book)); }
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg) { //look for the AutoFilterInfo Record NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid); AutoFilterInfoRecord info; if (record == null) { info = new AutoFilterInfoRecord(); sheet.Sheet.Records.Insert(insertPos, info); } else { info = record as AutoFilterInfoRecord; } info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1); }
private BRAIRecord CreateValuesLinkedDataRecord() { BRAIRecord r = new BRAIRecord(); r.LinkType = (BRAIRecord.LINK_TYPE_VALUES); r.ReferenceType = (BRAIRecord.REFERENCE_TYPE_WORKSHEET); r.IsCustomNumberFormat = (false); r.IndexNumberFmtRecord = ((short)0); Area3DPtg p = new Area3DPtg(0, 31, 0, 0, false, false, false, false, 0); r.FormulaOfLink = (new Ptg[] { p, }); return r; }
private void SetRepeatingRowsAndColumns( CellRangeAddress rowDef, CellRangeAddress colDef) { int sheetIndex = _workbook.GetSheetIndex(this); int maxRowIndex = SpreadsheetVersion.EXCEL97.LastRowIndex; int maxColIndex = SpreadsheetVersion.EXCEL97.LastColumnIndex; int col1 = -1; int col2 = -1; int row1 = -1; int row2 = -1; if (rowDef != null) { row1 = rowDef.FirstRow; row2 = rowDef.LastRow; if ((row1 == -1 && row2 != -1) || (row1 > row2) || (row1 < 0 || row1 > maxRowIndex) || (row2 < 0 || row2 > maxRowIndex)) { throw new ArgumentException("Invalid row range specification"); } } if (colDef != null) { col1 = colDef.FirstColumn; col2 = colDef.LastColumn; if ((col1 == -1 && col2 != -1) || (col1 > col2) || (col1 < 0 || col1 > maxColIndex) || (col2 < 0 || col2 > maxColIndex)) { throw new ArgumentException("Invalid column range specification"); } } short externSheetIndex = (short)_workbook.Workbook.CheckExternSheet(sheetIndex); bool setBoth = rowDef != null && colDef != null; bool removeAll = rowDef == null && colDef == null; HSSFName name = _workbook.GetBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex); if (removeAll) { if (name != null) { _workbook.RemoveName(name); } return; } if (name == null) { name = _workbook.CreateBuiltInName( NameRecord.BUILTIN_PRINT_TITLE, sheetIndex); } List<Ptg> ptgList = new List<Ptg>(); if (setBoth) { int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE ptgList.Add(new MemFuncPtg(exprsSize)); } if (colDef != null) { Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2, false, false, false, false, externSheetIndex); ptgList.Add(colArea); } if (rowDef != null) { Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex, false, false, false, false, externSheetIndex); ptgList.Add(rowArea); } if (setBoth) { ptgList.Add(UnionPtg.instance); } Ptg[] ptgs = ptgList.ToArray(); //ptgList.toArray(ptgs); name.SetNameDefinition(ptgs); HSSFPrintSetup printSetup = (HSSFPrintSetup)PrintSetup; printSetup.ValidSettings = (false); SetActive(true); }
/// <summary> /// Enable filtering for a range of cells /// </summary> /// <param name="range">the range of cells to filter</param> public IAutoFilter SetAutoFilter(CellRangeAddress range) { InternalWorkbook workbook = _workbook.Workbook; int sheetIndex = _workbook.GetSheetIndex(this); NameRecord name = workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1); if (name == null) { name = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1); } int firstRow = range.FirstRow; // if row was not given when constructing the range... if (firstRow == -1) { firstRow = 0; } // The built-in name must consist of a single Area3d Ptg. Area3DPtg ptg = new Area3DPtg(firstRow, range.LastRow, range.FirstColumn, range.LastColumn, false, false, false, false, sheetIndex); name.NameDefinition = (new Ptg[] { ptg }); AutoFilterInfoRecord r = new AutoFilterInfoRecord(); // the number of columns that have AutoFilter enabled. int numcols = 1 + range.LastColumn - range.FirstColumn; r.NumEntries = (short)numcols; int idx = _sheet.FindFirstRecordLocBySid(DimensionsRecord.sid); _sheet.Records.Insert(idx, r); //create a combobox control for each column HSSFPatriarch p = (HSSFPatriarch)CreateDrawingPatriarch(); for (int col = range.FirstColumn; col <= range.LastColumn; col++) { p.CreateComboBox(new HSSFClientAnchor(0, 0, 0, 0, (short)col, firstRow, (short)(col + 1), firstRow + 1)); } return new HSSFAutoFilter(this); }
/** * * @param sheetIden may be <code>null</code> * @param part1 * @param part2 may be <code>null</code> */ private ParseNode CreateAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1, SimpleRangePart part2) { int extIx; if (sheetIden == null) { extIx = Int32.MinValue; } else { String sName = sheetIden.SheetID.Name; if (sheetIden.BookName == null) { extIx = _book.GetExternalSheetIndex(sName); } else { extIx = _book.GetExternalSheetIndex(sheetIden.BookName, sName); } } Ptg ptg; if (part2 == null) { CellReference cr = part1.getCellReference(); if (sheetIden == null) { ptg = new RefPtg(cr); } else { ptg = new Ref3DPtg(cr, extIx); } } else { AreaReference areaRef = CreateAreaRef(part1, part2); if (sheetIden == null) { ptg = new AreaPtg(areaRef); } else { ptg = new Area3DPtg(areaRef, extIx); } } return new ParseNode(ptg); }
/// <summary> /// Sets the repeating rows and columns for a sheet (as found in /// File->PageSetup->Sheet). This Is function Is included in the workbook /// because it Creates/modifies name records which are stored at the /// workbook level. /// </summary> /// <param name="sheetIndex">0 based index to sheet.</param> /// <param name="startColumn">0 based start of repeating columns.</param> /// <param name="endColumn">0 based end of repeating columns.</param> /// <param name="startRow">0 based start of repeating rows.</param> /// <param name="endRow">0 based end of repeating rows.</param> /// <example> /// To set just repeating columns: /// workbook.SetRepeatingRowsAndColumns(0,0,1,-1-1); /// To set just repeating rows: /// workbook.SetRepeatingRowsAndColumns(0,-1,-1,0,4); /// To remove all repeating rows and columns for a sheet. /// workbook.SetRepeatingRowsAndColumns(0,-1,-1,-1,-1); /// </example> public void SetRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) { // Check arguments if (startColumn == -1 && endColumn != -1) throw new ArgumentException("Invalid column range specification"); if (startRow == -1 && endRow != -1) throw new ArgumentException("Invalid row range specification"); if (startColumn < -1 || startColumn >= 0xFF) throw new ArgumentException("Invalid column range specification"); if (endColumn < -1 || endColumn >= 0xFF) throw new ArgumentException("Invalid column range specification"); if (startRow < -1 || startRow > 65535) throw new ArgumentException("Invalid row range specification"); if (endRow < -1 || endRow > 65535) throw new ArgumentException("Invalid row range specification"); if (startColumn > endColumn) throw new ArgumentException("Invalid column range specification"); if (startRow > endRow) throw new ArgumentException("Invalid row range specification"); HSSFSheet sheet = (HSSFSheet)GetSheetAt(sheetIndex); int externSheetIndex = Workbook.CheckExternSheet(sheetIndex); bool settingRowAndColumn = startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1; bool removingRange = startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1; bool IsNewRecord = false; int rowColHeaderNameIndex = FindExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_PRINT_TITLE); if (removingRange) { if (rowColHeaderNameIndex >= 0) { workbook.RemoveName(rowColHeaderNameIndex); } return; } NameRecord nameRecord; if (rowColHeaderNameIndex < 0) { //does a lot of the house keeping for builtin records, like setting lengths to zero etc nameRecord = workbook.CreateBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex + 1); IsNewRecord = true; } else { nameRecord = workbook.GetNameRecord(rowColHeaderNameIndex); IsNewRecord = false; } ArrayList temp = new ArrayList(); if (settingRowAndColumn) { int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE temp.Add(new MemFuncPtg(exprsSize)); } if (startColumn >= 0) { Area3DPtg colArea = new Area3DPtg(0, MAX_ROW, startColumn, endColumn, false, false, false, false, externSheetIndex); temp.Add(colArea); } if (startRow >= 0) { Area3DPtg rowArea = new Area3DPtg(startRow, endRow, 0, MAX_COLUMN, false, false, false, false, externSheetIndex); temp.Add(rowArea); } if (settingRowAndColumn) { temp.Add(UnionPtg.instance); } Ptg[] ptgs = (Ptg[])temp.ToArray(typeof(Ptg)); nameRecord.NameDefinition = ptgs; if (IsNewRecord) { HSSFName newName = new HSSFName(this, nameRecord); names.Add(newName); } NPOI.SS.UserModel.IPrintSetup printSetup = sheet.PrintSetup; printSetup.ValidSettings = (false); sheet.IsActive = (true); }
private LinkedDataRecord CreateCategoriesLinkedDataRecord() { LinkedDataRecord r = new LinkedDataRecord(); r.LinkType = (LinkedDataRecord.LINK_TYPE_CATEGORIES); r.ReferenceType = (LinkedDataRecord.REFERENCE_TYPE_WORKSHEET); r.IsCustomNumberFormat = (false); r.IndexNumberFmtRecord = ((short)0); Area3DPtg p = new Area3DPtg(0, 31, 1, 1, false, false, false, false, 0); r.FormulaOfLink = (new Ptg[] { p, }); return r; }
private LinkedDataRecord CreateValuesLinkedDataRecord() { LinkedDataRecord r = new LinkedDataRecord(); r.SetLinkType( LinkedDataRecord.LINK_TYPE_VALUES ); r.SetReferenceType( LinkedDataRecord.REFERENCE_TYPE_WORKSHEET ); r.SetCustomNumberFormat( false ); r.SetIndexNumberFmtRecord( (short) 0 ); Area3DPtg p = new Area3DPtg(0, 31, 0, 0, false, false, false, false, 0); r.SetFormulaOfLink(new Ptg[] { p, }); return r; }
public void TestStore() { BRAIRecord record = new BRAIRecord(); record.LinkType=(BRAIRecord.LINK_TYPE_VALUES); record.ReferenceType=(BRAIRecord.REFERENCE_TYPE_WORKSHEET); record.Options=((short)0); record.IsCustomNumberFormat=(false); record.IndexNumberFmtRecord=((short)0); Area3DPtg ptg = new Area3DPtg(0, 7936, 0, 0, false, false, false, false, 0); record.FormulaOfLink = (new Ptg[] { ptg, }); byte[] recordBytes = record.Serialize(); Assert.AreEqual(recordBytes.Length - 4, data.Length); for (int i = 0; i < data.Length; i++) Assert.AreEqual(data[i], recordBytes[i + 4], "At offset " + i); }
public void TestLoad() { BRAIRecord record = new BRAIRecord(TestcaseRecordInputStream.Create((short)0x1051, data)); Assert.AreEqual(BRAIRecord.LINK_TYPE_VALUES, record.LinkType); Assert.AreEqual(BRAIRecord.REFERENCE_TYPE_WORKSHEET, record.ReferenceType); Assert.AreEqual(0, record.Options); Assert.AreEqual(false, record.IsCustomNumberFormat); Assert.AreEqual(0, record.IndexNumberFmtRecord); Area3DPtg ptgExpected = new Area3DPtg(0, 7936, 0, 0,false, false, false, false, 0); Object ptgActual = record.FormulaOfLink[0]; Assert.AreEqual(ptgExpected.ToString(), ptgActual.ToString()); Assert.AreEqual(data.Length + 4, record.RecordSize); }
public ValueEval GetArea3DEval(Area3DPtg aptg) { SheetRangeEvaluator sre = CreateExternSheetRefEvaluator(aptg.ExternSheetIndex); return new LazyAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, sre); }