/**
* 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]);
}
/**
* Find the 2D base data area for the pivot table, either from its direct reference or named table/range.
* @return AreaReference representing the current area defined by the pivot table
* @ if the ref1 attribute is not contiguous or the name attribute is not found.
*/
public AreaReference GetPivotArea(IWorkbook wb)
{
CT_WorksheetSource wsSource = ctPivotCacheDefinition.cacheSource.worksheetSource;
String ref1 = wsSource.@ref;
String name = wsSource.name;
if (ref1 == null && name == null)
{
throw new ArgumentException("Pivot cache must reference an area, named range, or table.");
}
// this is the XML format, so tell the reference that.
if (ref1 != null)
{
return(new AreaReference(ref1, SpreadsheetVersion.EXCEL2007));
}
if (name != null)
{
// named range or table?
IName range = wb.GetName(name);
if (range != null)
{
return(new AreaReference(range.RefersToFormula, SpreadsheetVersion.EXCEL2007));
}
// not a named range, check for a table.
// do this second, as tables are sheet-specific, but named ranges are not, and may not have a sheet name given.
XSSFSheet sheet = (XSSFSheet)wb.GetSheet(wsSource.sheet);
foreach (XSSFTable table in sheet.GetTables())
{
if (table.Name.Equals(name))
{ //case-sensitive?
return(new AreaReference(table.StartCellReference, table.EndCellReference));
}
}
}
throw new ArgumentException("Name '" + name + "' was not found.");
}
/**
* Creates cacheSource and workSheetSource for pivot table and sets the source reference as well assets the location of the pivot table
* @param sourceRef Source for data for pivot table - mutually exclusive with sourceName
* @param sourceName Source for data for pivot table - mutually exclusive with sourceRef
* @param position Position for pivot table in sheet
* @param sourceSheet Sheet where the source will be collected from
*/
protected internal void CreateSourceReferences(CellReference position, ISheet sourceSheet, IPivotTableReferenceConfigurator refConfig)
{
//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);
refConfig.ConfigureReference(worksheetSource);
if (worksheetSource.name == null && worksheetSource.@ref == null)
{
throw new ArgumentException("Pivot table source area reference or name must be specified.");
}
}
