protected internal void CreateDefaultDataColumns()
{
CT_PivotFields pivotFields;
if (pivotTableDefinition.pivotFields != null)
{
pivotFields = pivotTableDefinition.pivotFields;
}
else
{
pivotFields = pivotTableDefinition.AddNewPivotFields();
}
AreaReference sourceArea = GetPivotArea();
int firstColumn = sourceArea.FirstCell.Col;
int lastColumn = sourceArea.LastCell.Col;
CT_PivotField pivotField;
for (int i = firstColumn; i <= lastColumn; i++)
{
pivotField = pivotFields.AddNewPivotField();
pivotField.dataField = (/*setter*/ false);
pivotField.showAll = (/*setter*/ false);
}
pivotFields.count = (/*setter*/ pivotFields.SizeOfPivotFieldArray());
}
/**
* Add a column label using data from the given column and specified function
* @param columnIndex the index of the column to be used as column label.
* @param function the function to be used on the data
* The following functions exists:
* Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp
* @param valueFieldName the name of pivot table value field
*/
public void AddColumnLabel(DataConsolidateFunction function, int columnIndex, String valueFieldName)
{
AreaReference pivotArea = GetPivotArea();
int lastColIndex = pivotArea.LastCell.Col - pivotArea.FirstCell.Col;
if (columnIndex > lastColIndex && columnIndex < 0)
{
throw new IndexOutOfRangeException();
}
AddDataColumn(columnIndex, true);
AddDataField(function, columnIndex, valueFieldName);
// colfield should be Added for the second one.
if (pivotTableDefinition.dataFields.count == 2)
{
CT_ColFields colFields;
if (pivotTableDefinition.colFields != null)
{
colFields = pivotTableDefinition.colFields;
}
else
{
colFields = pivotTableDefinition.AddNewColFields();
}
colFields.AddNewField().x = (/*setter*/ -2);
colFields.count = (/*setter*/ colFields.SizeOfFieldArray());
}
}
protected AreaReference GetPivotArea()
{
IWorkbook wb = GetDataSheet().Workbook;
AreaReference pivotArea = GetPivotCacheDefinition().GetPivotArea(wb);
return(pivotArea);
}
/**
* Add a row label using data from the given column.
* @param columnIndex the index of the column to be used as row label.
*/
public void AddRowLabel(int columnIndex)
{
CheckColumnIndex(columnIndex);
AreaReference pivotArea = GetPivotArea();
int lastRowIndex = pivotArea.LastCell.Row - pivotArea.FirstCell.Row;
CT_PivotFields pivotFields = pivotTableDefinition.pivotFields;
CT_PivotField pivotField = new CT_PivotField();
CT_Items items = pivotField.AddNewItems();
pivotField.axis = (/*setter*/ ST_Axis.axisRow);
pivotField.showAll = (/*setter*/ false);
for (int i = 0; i <= lastRowIndex; i++)
{
items.AddNewItem().t = (/*setter*/ ST_ItemType.@default);
}
items.count = (/*setter*/ items.SizeOfItemArray());
pivotFields.SetPivotFieldArray(columnIndex, pivotField);
CT_RowFields rowFields;
if (pivotTableDefinition.rowFields != null)
{
rowFields = pivotTableDefinition.rowFields;
}
else
{
rowFields = pivotTableDefinition.AddNewRowFields();
}
rowFields.AddNewField().x = (/*setter*/ columnIndex);
rowFields.count = (/*setter*/ rowFields.SizeOfFieldArray());
}
private static void ConfirmWholeColumnRef(string ref1, int firstCol, int lastCol, bool firstIsAbs, bool lastIsAbs)
{
AreaReference ar = new AreaReference(ref1);
ConfirmCell(ar.FirstCell, 0, firstCol, true, firstIsAbs);
ConfirmCell(ar.LastCell, 0xFFFF, lastCol, true, lastIsAbs);
}
/**
* 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();
}
}
protected AreaReference GetPivotArea()
{
AreaReference pivotArea = new AreaReference(GetPivotCacheDefinition().
GetCTPivotCacheDefInition().cacheSource.worksheetSource.@ref);
return(pivotArea);
}
/**
* Add data field with data from the given column and specified function.
* @param function the function to be used on the data
* @param index the index of the column to be used as column label.
* The following functions exists:
* Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp
* @param valueFieldName the name of pivot table value field
*/
private void AddDataField(DataConsolidateFunction function, int columnIndex, String valueFieldName)
{
AreaReference pivotArea = GetPivotArea();
int lastColIndex = pivotArea.LastCell.Col - pivotArea.FirstCell.Col;
if (columnIndex > lastColIndex && columnIndex < 0)
{
throw new IndexOutOfRangeException();
}
CT_DataFields dataFields;
if (pivotTableDefinition.dataFields != null)
{
dataFields = pivotTableDefinition.dataFields;
}
else
{
dataFields = pivotTableDefinition.AddNewDataFields();
}
CT_DataField dataField = dataFields.AddNewDataField();
dataField.subtotal = (ST_DataConsolidateFunction)(function.Value);
ICell cell = GetDataSheet().GetRow(pivotArea.FirstCell.Row).GetCell(columnIndex);
cell.SetCellType(CellType.String);
dataField.name = (/*setter*/ valueFieldName);
dataField.fld = (uint)columnIndex;
dataFields.count = (/*setter*/ dataFields.SizeOfDataFieldArray());
}
public void TestDiscontinousReference()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls");
HSSFWorkbook wb = new HSSFWorkbook(is1);
InternalWorkbook workbook = wb.Workbook;
HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual(1, wb.NumberOfNames);
String sheetName = "Tabelle1";
String rawRefA = "$C$10:$C$14";
String rawRefB = "$C$16:$C$18";
String refA = sheetName + "!" + rawRefA;
String refB = sheetName + "!" + rawRefB;
String ref1 = refA + "," + refB;
// Check the low level record
NameRecord nr = workbook.GetNameRecord(0);
Assert.IsNotNull(nr);
Assert.AreEqual("test", nr.NameText);
Ptg[] def = nr.NameDefinition;
Assert.AreEqual(4, def.Length);
MemFuncPtg ptgA = (MemFuncPtg)def[0];
Area3DPtg ptgB = (Area3DPtg)def[1];
Area3DPtg ptgC = (Area3DPtg)def[2];
UnionPtg ptgD = (UnionPtg)def[3];
Assert.AreEqual("", ptgA.ToFormulaString());
Assert.AreEqual(refA, ptgB.ToFormulaString(eb));
Assert.AreEqual(refB, ptgC.ToFormulaString(eb));
Assert.AreEqual(",", ptgD.ToFormulaString());
Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition));
// Check the high level definition
int idx = wb.GetNameIndex("test");
Assert.AreEqual(0, idx);
NPOI.SS.UserModel.IName aNamedCell = wb.GetNameAt(idx);
// Should have 2 references
Assert.AreEqual(ref1, aNamedCell.RefersToFormula);
// Check the parsing of the reference into cells
Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula));
AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula);
Assert.AreEqual(2, arefs.Length);
Assert.AreEqual(refA, arefs[0].FormatAsString());
Assert.AreEqual(refB, arefs[1].FormatAsString());
for (int i = 0; i < arefs.Length; i++)
{
AreaReference ar = arefs[i];
ConfirmResolveCellRef(wb, ar.FirstCell);
ConfirmResolveCellRef(wb, ar.LastCell);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="RemoteAttribute"/> class.
/// </summary>
/// <param name="action">The route name.</param>
/// <param name="controller">The name of the controller.</param>
/// <param name="areaReference">
/// Find the controller in the root if <see cref="AreaReference.UseRoot"/>. Otherwise look in the current area.
/// </param>
public RemoteAttribute(string action, string controller, AreaReference areaReference)
: this(action, controller)
{
if (areaReference == AreaReference.UseRoot)
{
RouteData["area"] = null;
}
}
/*
* General
* 0
* 0.00
#,##0
#,##0.00
* "$"#,##0_);("$"#,##0)
* "$"#,##0_);[Red]("$"#,##0)
* "$"#,##0.00_);("$"#,##0.00)
* "$"#,##0.00_);[Red]("$"#,##0.00)
* 0%
* 0.00%
* 0.00E+00
# ?/?
# ??/??
# m/d/yy
# d-mmm-yy
# d-mmm
# mmm-yy
# h:mm AM/PM
# h:mm:ss AM/PM
# h:mm
# h:mm:ss
# m/d/yy h:mm
# reserved-0x17
# reserved-0x18
# reserved-0x19
# reserved-0x1A
# reserved-0x1B
# reserved-0x1C
# reserved-0x1D
# reserved-0x1E
# reserved-0x1F
# reserved-0x20
# reserved-0x21
# reserved-0x22
# reserved-0x23
# reserved-0x24
#,##0_);(#,##0)
#,##0_);[Red](#,##0)
#,##0.00_);(#,##0.00)
#,##0.00_);[Red](#,##0.00)
# _("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
# _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
# _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_
# _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
# mm:ss
# [h]:mm:ss
# mm:ss.0
##0.0E+0
# @
#
*/
public void FormatRange(int sheetIndex, int r1, int c1, int r2, int c2, string fmt)
{
CellReference a1 = new CellReference(r1, c1);
CellReference a2 = new CellReference(r2, c2);
AreaReference a = new AreaReference(a1, a2);
FormatRef(sheetIndex, a, fmt);
}
/// <summary>
/// Initializes a new instance of the <see cref="RemoteAttribute"/> class.
/// </summary>
/// <param name="action">The route name.</param>
/// <param name="controller">The name of the controller.</param>
/// <param name="areaReference">
/// Find the controller in the root if <see cref="AreaReference.UseRoot"/>. Otherwise look in the current area.
/// </param>
public RemoteAttribute(string action, string controller, AreaReference areaReference)
: this(action, controller)
{
if (areaReference == AreaReference.UseRoot)
{
RouteData["area"] = null;
}
}
public void FormatRange(int sheetIndex, string rangeRef, string fmt)
{
//for (int i = 0; i < HSSFDataFormat.NumberOfBuiltinBuiltinFormats; i++)
// {
// Console.WriteLine(HSSFDataFormat.GetBuiltinFormat((short)i));
// }
AreaReference ar = new AreaReference(rangeRef);
FormatRef(sheetIndex, ar, fmt);
}
public RemoteDoublePlusAttribute(string action, string controller, AreaReference areaReference)
: base(action, controller)
{
this._action = action;
this._controller = controller;
if (areaReference == AreaReference.UseRoot)
{
this.RouteData["area"] = null;
}
}
/**
* Verify column index (relative to first column in1 pivot area) is within the
* pivot area
*
* @param columnIndex
* @
*/
private void CheckColumnIndex(int columnIndex)
{
AreaReference pivotArea = GetPivotArea();
int size = pivotArea.LastCell.Col - pivotArea.FirstCell.Col + 1;
if (columnIndex < 0 || columnIndex >= size)
{
throw new IndexOutOfRangeException("Column Index: " + columnIndex + ", Size: " + size);
}
}
protected String FormatReferenceAsString()
{
CellReference topLeft = new CellReference(FirstRow, FirstColumn, !IsFirstRowRelative, !IsFirstColRelative);
CellReference botRight = new CellReference(LastRow, LastColumn, !IsLastRowRelative, !IsLastColRelative);
if (AreaReference.IsWholeColumnReference(topLeft, botRight))
{
return((new AreaReference(topLeft, botRight)).FormatAsString());
}
return(topLeft.FormatAsString() + ":" + botRight.FormatAsString());
}
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 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"));
}
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);
}
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;
}
}
public void TestTableFormulas()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
try
{
IFormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
XSSFSheet tableSheet = wb.GetSheet("Table") as XSSFSheet;
XSSFSheet formulaSheet = wb.GetSheet("Formulas") as XSSFSheet;
Confirm(eval, tableSheet.GetRow(5).GetCell(0), 49);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209);
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "one");
// test changing a table value, to see if the caches are properly Cleared
// Issue 59814
// this test passes before the fix for 59814
tableSheet.GetRow(1).GetCell(1).SetCellValue("ONEA");
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "ONEA");
// test Adding a row to a table, issue 59814
IRow newRow = tableSheet.GetRow(7);
if (newRow == null)
{
newRow = tableSheet.CreateRow(7);
}
newRow.CreateCell(0, CellType.Formula).CellFormula = (/*setter*/ "\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
newRow.CreateCell(1, CellType.String).SetCellValue("thirteen");
newRow.CreateCell(2, CellType.Numeric).SetCellValue(13);
// update Table
XSSFTable table = wb.GetTable("\\_Prime.1");
AreaReference newArea = new AreaReference(table.StartCellReference, new CellReference(table.EndRowIndex + 1, table.EndColIndex));
String newAreaStr = newArea.FormatAsString();
table.GetCTTable().@ref = (/*setter*/ newAreaStr);
table.GetCTTable().autoFilter.@ref = (/*setter*/ newAreaStr);
table.UpdateHeaders();
//table.UpdateReferences();
// these fail before the fix for 59814
Confirm(eval, tableSheet.GetRow(7).GetCell(0), 13 * 13);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209 + 13 * 13);
}
finally
{
wb.Close();
}
}
private void AddCodesChantier(XSSFWorkbook workbook)
{
var codesChantier = _codeChantierLogic.GetAll().ToList();
var sheet = workbook.CreateSheet("Code chantier") as XSSFSheet;
var headers = sheet.CreateRow(0);
CreateCell(headers, 0, "Code Chantier");
CreateCell(headers, 1, "Clients");
CreateCell(headers, 2, "Produit");
CreateCell(headers, 3, "Libellé");
var rowIndex = 1;
foreach (var codeChantier in codesChantier)
{
var row = sheet.CreateRow(rowIndex);
CreateCell(row, 0, codeChantier.Code);
CreateCell(row, 1, codeChantier.Client);
CreateCell(row, 2, codeChantier.Produit);
CreateCell(row, 3, codeChantier.Description);
rowIndex++;
}
var table = sheet.CreateTable();
var cttable = table.GetCTTable();
var range = new AreaReference(new CellReference(0, 0), new CellReference(rowIndex - 1, 3));
cttable.@ref = range.FormatAsString();
cttable.tableColumns = new CT_TableColumns();
cttable.tableColumns.tableColumn = new List <CT_TableColumn>();
cttable.tableColumns.count = 4;
cttable.displayName = "Chantier";
cttable.name = "Chantier";
cttable.id = 50;
cttable.tableStyleInfo = new CT_TableStyleInfo();
cttable.tableStyleInfo.name = "TableStyleMedium2";
cttable.tableStyleInfo.showColumnStripes = false;
cttable.tableStyleInfo.showRowStripes = true;
cttable.tableStyleInfo.showFirstColumn = false;
cttable.tableStyleInfo.showLastColumn = false;
cttable.totalsRowShown = false;
cttable.autoFilter = new CT_AutoFilter();
cttable.autoFilter.@ref = range.FormatAsString();
for (uint i = 0; i < 4; i++)
{
AddColumn(cttable, i);
sheet.AutoSizeColumn((int)i);
}
}
private void FormatRef(int sheetIndex, AreaReference ar, string fmt)
{
var cellRef = ar.GetAllReferencedCells();
var cellStyle = npoi_workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(fmt);
foreach (var item in cellRef)
{
var cell = GetCell(sheetIndex, item.Row, item.Col);
cell.CellStyle = cellStyle;
}
Console.WriteLine(cellRef);
}
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 Range(ISheet sheet, string reference)
{
Sheet = sheet;
Area = new AreaReference(reference);
Cells = Area.GetAllReferencedCells();
CellReference cellReference = Cells.First();
CellReference cellReference2 = Cells.Last();
FirstRow = cellReference.Row;
FirstCol = cellReference.Col;
LastRow = cellReference2.Row;
LastCol = cellReference2.Col;
RowCount = LastRow - FirstRow + 1;
ColCount = LastCol - FirstCol + 1;
}
/**
* Add column Containing data from the referenced area.
* @param columnIndex the index of the column Containing the data
* @param isDataField true if the data should be displayed in the pivot table.
*/
public void AddDataColumn(int columnIndex, bool isDataField)
{
AreaReference pivotArea = GetPivotArea();
int lastColIndex = pivotArea.LastCell.Col - pivotArea.FirstCell.Col;
if (columnIndex > lastColIndex && columnIndex < 0)
{
throw new IndexOutOfRangeException();
}
CT_PivotFields pivotFields = pivotTableDefinition.pivotFields;
CT_PivotField pivotField = new CT_PivotField();
pivotField.dataField = (/*setter*/ isDataField);
pivotField.showAll = (/*setter*/ false);
pivotFields.SetPivotFieldArray(columnIndex, pivotField);
}
public void TestSpecialSheetNames()
{
AreaReference ar;
ar = new AreaReference("'Sheet A'!A1:A1");
ConfirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
ar = new AreaReference("'Hey! Look Here!'!A1:A1");
ConfirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
ar = new AreaReference("'O''Toole'!A1:B2");
ConfirmAreaSheetName(ar, "O'Toole", "'O''Toole'!A1:B2");
ar = new AreaReference("'one:many'!A1:B2");
ConfirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
}
/*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;
}
public void TestWholeRow()
{
AreaReference oldStyle = AreaReference.GetWholeRow(SpreadsheetVersion.EXCEL97, "1", "2");
Assert.AreEqual(0, oldStyle.FirstCell.Col);
Assert.AreEqual(0, oldStyle.FirstCell.Row);
Assert.AreEqual(SpreadsheetVersion.EXCEL97.LastColumnIndex, oldStyle.LastCell.Col);
Assert.AreEqual(1, oldStyle.LastCell.Row);
AreaReference newStyle = AreaReference.GetWholeRow(SpreadsheetVersion.EXCEL2007, "1", "2");
Assert.AreEqual(0, newStyle.FirstCell.Col);
Assert.AreEqual(0, newStyle.FirstCell.Row);
Assert.AreEqual(SpreadsheetVersion.EXCEL2007.LastColumnIndex, newStyle.LastCell.Col);
Assert.AreEqual(1, newStyle.LastCell.Row);
}
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 TestAreaRef1()
{
AreaReference ar = new AreaReference("$A$1:$B$2");
Assert.IsFalse(ar.IsSingleCell, "Two cells expected");
CellReference cf = ar.FirstCell;
Assert.IsTrue(cf.Row == 0, "row is1 4");
Assert.IsTrue(cf.Col == 0, "col is1 1");
Assert.IsTrue(cf.IsRowAbsolute, "row is1 abs");
Assert.IsTrue(cf.IsColAbsolute, "col is1 abs");
Assert.IsTrue(cf.FormatAsString().Equals("$A$1"), "string is1 $A$1");
cf = ar.LastCell;
Assert.IsTrue(cf.Row == 1, "row is1 4");
Assert.IsTrue(cf.Col == 1, "col is1 1");
Assert.IsTrue(cf.IsRowAbsolute, "row is1 abs");
Assert.IsTrue(cf.IsColAbsolute, "col is1 abs");
Assert.IsTrue(cf.FormatAsString().Equals("$B$2"), "string is1 $B$2");
CellReference[] refs = ar.GetAllReferencedCells();
Assert.AreEqual(4, refs.Length);
Assert.AreEqual(0, refs[0].Row);
Assert.AreEqual(0, refs[0].Col);
Assert.IsNull(refs[0].SheetName);
Assert.AreEqual(0, refs[1].Row);
Assert.AreEqual(1, refs[1].Col);
Assert.IsNull(refs[1].SheetName);
Assert.AreEqual(1, refs[2].Row);
Assert.AreEqual(0, refs[2].Col);
Assert.IsNull(refs[2].SheetName);
Assert.AreEqual(1, refs[3].Row);
Assert.AreEqual(1, refs[3].Col);
Assert.IsNull(refs[3].SheetName);
}
public TestableRemoteAttribute(string action, string controller, AreaReference areaReference)
: base(action, controller, areaReference)
{
}
public void TestSpecialSheetNames()
{
AreaReference ar;
ar = new AreaReference("'Sheet A'!A1:A1");
ConfirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
ar = new AreaReference("'Hey! Look Here!'!A1:A1");
ConfirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
ar = new AreaReference("'O''Toole'!A1:B2");
ConfirmAreaSheetName(ar, "O'Toole", "'O''Toole'!A1:B2");
ar = new AreaReference("'one:many'!A1:B2");
ConfirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
}
private static void ConfirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText)
{
CellReference[] cells = ar.GetAllReferencedCells();
Assert.AreEqual(sheetName, cells[0].SheetName);
Assert.AreEqual(expectedFullText, ar.FormatAsString());
}
private static void ConfirmWholeColumnRef(String ref1, int firstCol, int lastCol, bool firstIsAbs, bool lastIsAbs)
{
AreaReference ar = new AreaReference(ref1);
ConfirmCell(ar.FirstCell, 0, firstCol, true, firstIsAbs);
ConfirmCell(ar.LastCell, 0xFFFF, lastCol, true, lastIsAbs);
}
public void TestReferenceWithSheet()
{
AreaReference ar;
ar = new AreaReference("Tabelle1!B5:B5");
Assert.IsTrue(ar.IsSingleCell);
TestCellReference.ConfirmCell(ar.FirstCell, "Tabelle1", 4, 1, false, false, "Tabelle1!B5");
Assert.AreEqual(1, ar.GetAllReferencedCells().Length);
ar = new AreaReference("Tabelle1!$B$5:$B$7");
Assert.IsFalse(ar.IsSingleCell);
TestCellReference.ConfirmCell(ar.FirstCell, "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
TestCellReference.ConfirmCell(ar.LastCell, "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
// And all that make it up
CellReference[] allCells = ar.GetAllReferencedCells();
Assert.AreEqual(3, allCells.Length);
TestCellReference.ConfirmCell(allCells[0], "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
TestCellReference.ConfirmCell(allCells[1], "Tabelle1", 5, 1, true, true, "Tabelle1!$B$6");
TestCellReference.ConfirmCell(allCells[2], "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
}
