public void Test_47701() { byte[] data = HexRead.ReadFromString( "15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " + "00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " + "00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " + "00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " + "00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " + "10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " + "00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " + "00, 00, 08, 00, 00, 00"); RecordInputStream in1 = TestcaseRecordInputStream.Create(ObjRecord.sid, data); // check read OK ObjRecord record = new ObjRecord(in1); Assert.AreEqual(3, record.SubRecords.Count); SubRecord sr = record.SubRecords[(2)]; Assert.IsTrue(sr is LbsDataSubRecord); LbsDataSubRecord lbs = (LbsDataSubRecord)sr; Assert.AreEqual(4, lbs.NumberOfItems); Assert.IsTrue(lbs.Formula is AreaPtg); AreaPtg ptg = (AreaPtg)lbs.Formula; CellRangeAddress range = new CellRangeAddress( ptg.FirstRow, ptg.LastRow, ptg.FirstColumn, ptg.LastColumn); Assert.AreEqual("H10:H13", range.FormatAsString()); // check that it re-Serializes to the same data byte[] ser = record.Serialize(); TestcaseRecordInputStream.ConfirmRecordEncoding(ObjRecord.sid, data, ser); }
/// <summary> /// Formats data marker using canonical format, for example /// 'SheetName!$A$1:$A$5'. /// </summary> /// <returns>formatted data marker</returns> public String FormatAsString() { String sheetName = (sheet == null) ? (null) : (sheet.SheetName); if (range == null) { return(null); } else { return(range.FormatAsString(sheetName, true)); } }
public void TestHSSFSetArrayFormula_SingleCell() { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells; Assert.AreEqual(1, cells.Length); // sheet.SetArrayFormula Creates rows and cells for the designated range Assert.IsNotNull(sheet.GetRow(2)); ICell cell = sheet.GetRow(2).GetCell(2); Assert.IsNotNull(cell); Assert.IsTrue(cell.IsPartOfArrayFormulaGroup); //retrieve the range and check it is the same Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString()); FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord); Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString()); Assert.IsTrue(agg.IsPartOfArrayFormula); }
public void TestShiftWithMergedRegions() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue(1.1); row.CreateCell(1).SetCellValue(2.2); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2); Assert.AreEqual( region.FormatAsString(),"A1:C1"); sheet.AddMergedRegion(region); sheet.ShiftRows(0, 1, 2); region = sheet.GetMergedRegion(0); Assert.AreEqual( region.FormatAsString(),"A3:C3"); }
internal void SetCellArrayFormula(String formula, CellRangeAddress range) { SetFormula(formula, FormulaType.Array); CT_CellFormula cellFormula = _cell.f; cellFormula.t = (ST_CellFormulaType.array); cellFormula.@ref = (range.FormatAsString()); }
public IAutoFilter SetAutoFilter(CellRangeAddress range) { CT_AutoFilter af = worksheet.autoFilter; if (af == null) af = worksheet.AddNewAutoFilter(); CellRangeAddress norm = new CellRangeAddress(range.FirstRow, range.LastRow, range.FirstColumn, range.LastColumn); String ref1 = norm.FormatAsString(); af.@ref = (ref1); XSSFWorkbook wb = (XSSFWorkbook)Workbook; int sheetIndex = Workbook.GetSheetIndex(this); XSSFName name = wb.GetBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); if (name == null) { name = wb.CreateBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); name.GetCTName().hidden = true; CellReference r1 = new CellReference(SheetName, range.FirstRow, range.FirstColumn, true, true); CellReference r2 = new CellReference(null, range.LastRow, range.LastColumn, true, true); String fmla = r1.FormatAsString() + ":" + r2.FormatAsString(); name.RefersToFormula = fmla; } return new XSSFAutoFilter(this); }
private void ValidateArrayFormulas(CellRangeAddress region) { int firstRow = region.FirstRow; int firstColumn = region.FirstColumn; int lastRow = region.LastRow; int lastColumn = region.LastColumn; for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { IRow row = GetRow(rowIn); if (row == null) continue; ICell cell = row.GetCell(colIn); if (cell == null) continue; if (cell.IsPartOfArrayFormulaGroup) { CellRangeAddress arrayRange = cell.ArrayFormulaRange; if (arrayRange.NumberOfCells > 1 && (arrayRange.IsInRange(region.FirstRow, region.FirstColumn) || arrayRange.IsInRange(region.FirstRow, region.FirstColumn))) { String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " + "You cannot merge cells of an array."; throw new InvalidOperationException(msg); } } } } }
/** * Adds a merged region of cells (hence those cells form one). * * @param region (rowfrom/colfrom-rowto/colto) to merge * @return index of this region */ public int AddMergedRegion(CellRangeAddress region) { region.Validate(SpreadsheetVersion.EXCEL2007); // throw InvalidOperationException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet ValidateArrayFormulas(region); CT_MergeCells ctMergeCells = worksheet.IsSetMergeCells() ? worksheet.mergeCells : worksheet.AddNewMergeCells(); CT_MergeCell ctMergeCell = ctMergeCells.AddNewMergeCell(); ctMergeCell.@ref = (region.FormatAsString()); return ctMergeCells.sizeOfMergeCellArray(); }
public void TestFormatAsString() { CellRangeAddress ref1 = new CellRangeAddress(1, 2, 3, 4); Assert.AreEqual("D2:E3", ref1.FormatAsString()); Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString()); Assert.AreEqual("sheet1!$D$2:$E$3", ref1.FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!D2:E3", ref1.FormatAsString("sheet1", false)); Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false)); Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false)); Assert.AreEqual("D2:E3", ref1.FormatAsString(null, false)); Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, false)); Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, false)).FormatAsString(null, false)); Assert.AreEqual("$D$2:$E$3", ref1.FormatAsString(null, true)); Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true)); Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true)); ref1 = new CellRangeAddress(-1, -1, 3, 4); Assert.AreEqual("D:E", ref1.FormatAsString()); Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true)); Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true)); Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true)); Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false)); Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false)); ref1 = new CellRangeAddress(1, 2, -1, -1); Assert.AreEqual("2:3", ref1.FormatAsString()); Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true)); Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true)); Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true)); Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false)); Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false)); ref1 = new CellRangeAddress(1, 1, 2, 2); Assert.AreEqual("C2", ref1.FormatAsString()); Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true)); Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true)); Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true)); Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true)); Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false)); Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false)); // is this a valid Address? ref1 = new CellRangeAddress(-1, -1, -1, -1); Assert.AreEqual(":", ref1.FormatAsString()); }
public void TestSetArrayFormula_SingleCell() { ICell[] cells; IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); ICell cell = sheet.CreateRow(0).CreateCell(0); Assert.IsFalse(cell.IsPartOfArrayFormulaGroup); try { cell.GetArrayFormulaRange(/*getter*/); Assert.Fail("expected exception"); } catch (InvalidOperationException e) { Assert.AreEqual("Cell A1 is not part of an array formula.", e.Message); } // row 3 does not yet exist Assert.IsNull(sheet.GetRow(2)); CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells; Assert.AreEqual(1, cells.Length); // sheet.SetArrayFormula Creates rows and cells for the designated range Assert.IsNotNull(sheet.GetRow(2)); cell = sheet.GetRow(2).GetCell(2); Assert.IsNotNull(cell); Assert.IsTrue(cell.IsPartOfArrayFormulaGroup); //retrieve the range and check it is the same Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString()); //check the formula Assert.AreEqual("SUM(C11:C12*D11:D12)", cell.CellFormula); }
public void TestRemoveArrayFormula() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); Assert.AreEqual("C4:C6", range.FormatAsString()); ICellRange<ICell> cr = sheet.SetArrayFormula("SUM(A1:A3*B1:B3)", range); Assert.AreEqual(3, cr.Size); // remove the formula cells in C4:C6 ICellRange<ICell> dcells = sheet.RemoveArrayFormula(cr.TopLeftCell); // RemoveArrayFormula should return the same cells as SetArrayFormula Assert.IsTrue(Arrays.Equals(cr.FlattenedCells, dcells.FlattenedCells)); foreach (ICell acell in cr) { Assert.IsFalse(acell.IsPartOfArrayFormulaGroup); Assert.AreEqual(CellType.BLANK, acell.CellType); } // cells C4:C6 are not included in array formula, // invocation of sheet.RemoveArrayFormula on any of them throws ArgumentException foreach (ICell acell in cr) { try { sheet.RemoveArrayFormula(acell); Assert.Fail("expected exception"); } catch (ArgumentException e) { String ref1 = new CellReference(acell).FormatAsString(); Assert.AreEqual("Cell " + ref1 + " is not part of an array formula.", e.Message); } } }