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);
}
}
}
