private NPOI.SS.UserModel.ICell GetCell(string rangeName)
{
var range = Book.GetNameAt(Book.GetNameIndex(ExcelNameEscape(rangeName)));
var areaRef = new AreaReference(range.RefersToFormula);
CellReference[] crefs = areaRef.GetAllReferencedCells();
CellReference cref = crefs[0];
return Book.GetSheet(cref.SheetName)
.GetRow(cref.Row)
.GetCell(cref.Col);
}
protected AreaPtgBase(AreaReference ar)
{
CellReference firstCell = ar.FirstCell;
CellReference lastCell = ar.LastCell;
FirstRow = (firstCell.Row);
FirstColumn = (firstCell.Col == -1 ? 0 : (int)firstCell.Col);
LastRow = (lastCell.Row);
LastColumn = (lastCell.Col == -1 ? 0xFF : (int)lastCell.Col);
IsFirstColRelative = (!firstCell.IsColAbsolute);
IsLastColRelative = (!lastCell.IsColAbsolute);
IsFirstRowRelative = (!firstCell.IsRowAbsolute);
IsLastRowRelative = (!lastCell.IsRowAbsolute);
}
protected AreaPtgBase(String arearef)
{
AreaReference ar = new AreaReference(arearef);
CellReference firstCell = ar.FirstCell;
CellReference lastCell = ar.LastCell;
FirstRow = firstCell.Row;
FirstColumn = firstCell.Col;
LastRow = lastCell.Row;
LastColumn = lastCell.Col;
IsFirstColRelative = !firstCell.IsColAbsolute;
IsLastColRelative = !lastCell.IsColAbsolute;
IsFirstRowRelative = !firstCell.IsRowAbsolute;
IsLastRowRelative = !lastCell.IsRowAbsolute;
}
public Area3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, AreaReference arearef)
: base(arearef)
{
this.externalWorkbookNumber = externalWorkbookNumber;
this.firstSheetName = sheetName.SheetId.Name;
if (sheetName is SheetRangeIdentifier)
{
this.lastSheetName = ((SheetRangeIdentifier)sheetName).LastSheetIdentifier.Name;
}
else
{
this.lastSheetName = null;
}
}
private static void Confirm(String refA, String refB, String expectedAreaRef)
{
ValueEval[] args = {
CreateRefEval(refA),
CreateRefEval(refB),
};
AreaReference ar = new AreaReference(expectedAreaRef);
ValueEval result = EvalInstances.Range.Evaluate(args, 0, (short)0);
Assert.IsTrue(result is AreaEval);
AreaEval ae = (AreaEval)result;
Assert.AreEqual(ar.FirstCell.Row, ae.FirstRow);
Assert.AreEqual(ar.LastCell.Row, ae.LastRow);
Assert.AreEqual(ar.FirstCell.Col, ae.FirstColumn);
Assert.AreEqual(ar.LastCell.Col, ae.LastColumn);
}
public void SetUp()
{
IWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
IRow row1 = sheet.CreateRow(0);
// Create a cell and Put a value in it.
ICell cell = row1.CreateCell(0);
cell.SetCellValue("Names");
ICell cell2 = row1.CreateCell(1);
cell2.SetCellValue("#");
ICell cell7 = row1.CreateCell(2);
cell7.SetCellValue("Data");
ICell cell10 = row1.CreateCell(3);
cell10.SetCellValue("Value");
IRow row2 = sheet.CreateRow(1);
ICell cell3 = row2.CreateCell(0);
cell3.SetCellValue("Jan");
ICell cell4 = row2.CreateCell(1);
cell4.SetCellValue(10);
ICell cell8 = row2.CreateCell(2);
cell8.SetCellValue("Apa");
ICell cell11 = row1.CreateCell(3);
cell11.SetCellValue(11.11);
IRow row3 = sheet.CreateRow(2);
ICell cell5 = row3.CreateCell(0);
cell5.SetCellValue("Ben");
ICell cell6 = row3.CreateCell(1);
cell6.SetCellValue(9);
ICell cell9 = row3.CreateCell(2);
cell9.SetCellValue("Bepa");
ICell cell12 = row1.CreateCell(3);
cell12.SetCellValue(12.12);
AreaReference source = new AreaReference("A1:C2");
pivotTable = sheet.CreatePivotTable(source, new CellReference("H5"));
}
/**
* Generates a cache field for each column in the reference area for the pivot table.
* @param sheet The sheet where the data i collected from
*/
protected internal void CreateCacheFields(ISheet sheet)
{
//Get values for start row, start and end column
AreaReference ar = new AreaReference(ctPivotCacheDefinition.cacheSource.worksheetSource.@ref);
CellReference firstCell = ar.FirstCell;
CellReference lastCell = ar.LastCell;
int columnStart = firstCell.Col;
int columnEnd = lastCell.Col;
IRow row = sheet.GetRow(firstCell.Row);
CT_CacheFields cFields;
if (ctPivotCacheDefinition.cacheFields != null)
{
cFields = ctPivotCacheDefinition.cacheFields;
}
else
{
cFields = ctPivotCacheDefinition.AddNewCacheFields();
}
//For each column, create a cache field and give it en empty sharedItems
for (int i = columnStart; i <= columnEnd; i++)
{
CT_CacheField cf = cFields.AddNewCacheField();
if (i == columnEnd)
{
cFields.count = (/*setter*/cFields.SizeOfCacheFieldArray());
}
//General number format
cf.numFmtId = (/*setter*/0);
ICell cell = row.GetCell(i);
cell.SetCellType(CellType.String);
cf.name = (/*setter*/row.GetCell(i).StringCellValue);
cf.AddNewSharedItems();
}
}
public Ptg Get3DReferencePtg(AreaReference area, SheetIdentifier sheet)
{
if (sheet._bookName != null)
{
int bookIndex = ResolveBookIndex(sheet._bookName);
return new Area3DPxg(bookIndex, sheet, area);
}
else
{
return new Area3DPxg(sheet, area);
}
}
public void SetPivotData(XSSFWorkbook wb)
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IRow row1 = sheet.CreateRow(0);
// Create a cell and Put a value in it.
ICell cell = row1.CreateCell(0);
cell.SetCellValue("Names");
ICell cell2 = row1.CreateCell(1);
cell2.SetCellValue("#");
ICell cell7 = row1.CreateCell(2);
cell7.SetCellValue("Data");
IRow row2 = sheet.CreateRow(1);
ICell cell3 = row2.CreateCell(0);
cell3.SetCellValue("Jan");
ICell cell4 = row2.CreateCell(1);
cell4.SetCellValue(10);
ICell cell8 = row2.CreateCell(2);
cell8.SetCellValue("Apa");
IRow row3 = sheet.CreateRow(2);
ICell cell5 = row3.CreateCell(0);
cell5.SetCellValue("Ben");
ICell cell6 = row3.CreateCell(1);
cell6.SetCellValue(9);
ICell cell9 = row3.CreateCell(2);
cell9.SetCellValue("Bepa");
AreaReference source = new AreaReference("A1:B2");
sheet.CreatePivotTable(source, new CellReference("H5"));
}
public Ptg Get3DReferencePtg(AreaReference areaRef, SheetIdentifier sheet)
{
int extIx = GetSheetExtIx(sheet);
return new Area3DPtg(areaRef, extIx);
}
public Area3DPtg(AreaReference arearef, int externIdx):base(arearef)
{
ExternSheetIndex=(externIdx);
}
protected Area2DPtgBase(AreaReference ar):base(ar)
{
}
public void TestNamedCell_1()
{
// Setup for this Testcase
String sheetName = "Test Named Cell";
String cellName = "named_cell";
String cellValue = "TEST Value";
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet(sheetName);
ICreationHelper factory = wb.GetCreationHelper();
sheet.CreateRow(0).CreateCell(0).SetCellValue(factory.CreateRichTextString(cellValue));
// create named range for a single cell using areareference
IName namedCell = wb.CreateName();
namedCell.NameName = (cellName);
String reference = "'" + sheetName + "'" + "!A1:A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cellName);
IName aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
AreaReference aref = new AreaReference(aNamedCell.RefersToFormula);
Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'");
CellReference cref = aref.FirstCell;
Assert.IsNotNull(cref);
ISheet s = wb.GetSheet(cref.SheetName);
Assert.IsNotNull(s);
IRow r = sheet.GetRow(cref.Row);
ICell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference");
}
protected AreaReference GetPivotArea()
{
AreaReference pivotArea = new AreaReference(GetPivotCacheDefinition().
GetCTPivotCacheDefInition().cacheSource.worksheetSource.@ref);
return pivotArea;
}
public void Test51963()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("51963.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual("Abc,1", sheet.SheetName);
XSSFName name = wb.GetName("Intekon.ProdCodes") as XSSFName;
Assert.AreEqual("'Abc,1'!$A$1:$A$2", name.RefersToFormula);
AreaReference ref1 = new AreaReference(name.RefersToFormula);
Assert.AreEqual(0, ref1.FirstCell.Row);
Assert.AreEqual(0, ref1.FirstCell.Col);
Assert.AreEqual(1, ref1.LastCell.Row);
Assert.AreEqual(0, ref1.LastCell.Col);
}
public Area3DPxg(SheetIdentifier sheetName, AreaReference arearef)
: this(-1, sheetName, arearef)
{
}
public AreaPtg(AreaReference areaRef):base(areaRef)
{
}
/**
* Creates cacheSource and workSheetSource for pivot table and Sets the source reference as well assets the location of the pivot table
* @param source Source for data for pivot table
* @param position Position for pivot table in sheet
* @param sourceSheet Sheet where the source will be collected from
*/
protected internal void CreateSourceReferences(AreaReference source, CellReference position, ISheet sourceSheet)
{
//Get cell one to the right and one down from position, add both to AreaReference and Set pivot table location.
AreaReference destination = new AreaReference(position, new CellReference(position.Row + 1, position.Col + 1));
CT_Location location;
if (pivotTableDefinition.location == null)
{
location = pivotTableDefinition.AddNewLocation();
location.firstDataCol = (/*setter*/1);
location.firstDataRow = (/*setter*/1);
location.firstHeaderRow = (/*setter*/1);
}
else
{
location = pivotTableDefinition.location;
}
location.@ref = (/*setter*/destination.FormatAsString());
pivotTableDefinition.location = (/*setter*/location);
//Set source for the pivot table
CT_PivotCacheDefinition cacheDef = GetPivotCacheDefinition().GetCTPivotCacheDefInition();
CT_CacheSource cacheSource = cacheDef.AddNewCacheSource();
cacheSource.type = (/*setter*/ST_SourceType.worksheet);
CT_WorksheetSource worksheetSource = cacheSource.AddNewWorksheetSource();
worksheetSource.sheet = (/*setter*/sourceSheet.SheetName);
SetDataSheet(sourceSheet);
String[] firstCell = source.FirstCell.CellRefParts;
String[] lastCell = source.LastCell.CellRefParts;
worksheetSource.@ref = (/*setter*/firstCell[2] + firstCell[1] + ':' + lastCell[2] + lastCell[1]);
}
/*public String Area{
RangeAddress ra = new RangeAddress( FirstColumn,FirstRow + 1, LastColumn, LastRow + 1);
String result = ra.GetAddress();
return result;
}*/
public void SetArea(String ref1)
{
AreaReference ar = new AreaReference(ref1);
CellReference frstCell = ar.FirstCell;
CellReference lastCell = ar.LastCell;
FirstRow=(short)frstCell.Row;
FirstColumn=frstCell.Col;
LastRow=(short)lastCell.Row;
LastColumn=lastCell.Col;
IsFirstColRelative=!frstCell.IsColAbsolute;
IsLastColRelative=!lastCell.IsColAbsolute;
IsFirstRowRelative=!frstCell.IsRowAbsolute;
IsLastRowRelative=!lastCell.IsRowAbsolute;
}
