public void TestToFormulaString()
{
Area3DPtg target = new Area3DPtg("A1:B1", (short)0);
String sheetName = "my sheet";
HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName);
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb);
Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "Sheet1");
Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book));
wb.SetSheetName(0, "C64");
Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book));
}
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg)
{
//look for the AutoFilterInfo Record
NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
AutoFilterInfoRecord info;
if (record == null)
{
info = new AutoFilterInfoRecord();
sheet.Sheet.Records.Insert(insertPos, info);
}
else
{
info = record as AutoFilterInfoRecord;
}
info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1);
}
private BRAIRecord CreateValuesLinkedDataRecord()
{
BRAIRecord r = new BRAIRecord();
r.LinkType = (BRAIRecord.LINK_TYPE_VALUES);
r.ReferenceType = (BRAIRecord.REFERENCE_TYPE_WORKSHEET);
r.IsCustomNumberFormat = (false);
r.IndexNumberFmtRecord = ((short)0);
Area3DPtg p = new Area3DPtg(0, 31, 0, 0,
false, false, false, false, 0);
r.FormulaOfLink = (new Ptg[] { p, });
return r;
}
private void SetRepeatingRowsAndColumns(
CellRangeAddress rowDef, CellRangeAddress colDef)
{
int sheetIndex = _workbook.GetSheetIndex(this);
int maxRowIndex = SpreadsheetVersion.EXCEL97.LastRowIndex;
int maxColIndex = SpreadsheetVersion.EXCEL97.LastColumnIndex;
int col1 = -1;
int col2 = -1;
int row1 = -1;
int row2 = -1;
if (rowDef != null)
{
row1 = rowDef.FirstRow;
row2 = rowDef.LastRow;
if ((row1 == -1 && row2 != -1) || (row1 > row2)
|| (row1 < 0 || row1 > maxRowIndex)
|| (row2 < 0 || row2 > maxRowIndex))
{
throw new ArgumentException("Invalid row range specification");
}
}
if (colDef != null)
{
col1 = colDef.FirstColumn;
col2 = colDef.LastColumn;
if ((col1 == -1 && col2 != -1) || (col1 > col2)
|| (col1 < 0 || col1 > maxColIndex)
|| (col2 < 0 || col2 > maxColIndex))
{
throw new ArgumentException("Invalid column range specification");
}
}
short externSheetIndex =
(short)_workbook.Workbook.CheckExternSheet(sheetIndex);
bool setBoth = rowDef != null && colDef != null;
bool removeAll = rowDef == null && colDef == null;
HSSFName name = _workbook.GetBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
if (removeAll)
{
if (name != null)
{
_workbook.RemoveName(name);
}
return;
}
if (name == null)
{
name = _workbook.CreateBuiltInName(
NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
}
List<Ptg> ptgList = new List<Ptg>();
if (setBoth)
{
int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
ptgList.Add(new MemFuncPtg(exprsSize));
}
if (colDef != null)
{
Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2,
false, false, false, false, externSheetIndex);
ptgList.Add(colArea);
}
if (rowDef != null)
{
Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex,
false, false, false, false, externSheetIndex);
ptgList.Add(rowArea);
}
if (setBoth)
{
ptgList.Add(UnionPtg.instance);
}
Ptg[] ptgs = ptgList.ToArray();
//ptgList.toArray(ptgs);
name.SetNameDefinition(ptgs);
HSSFPrintSetup printSetup = (HSSFPrintSetup)PrintSetup;
printSetup.ValidSettings = (false);
SetActive(true);
}
/// <summary>
/// Enable filtering for a range of cells
/// </summary>
/// <param name="range">the range of cells to filter</param>
public IAutoFilter SetAutoFilter(CellRangeAddress range)
{
InternalWorkbook workbook = _workbook.Workbook;
int sheetIndex = _workbook.GetSheetIndex(this);
NameRecord name = workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
if (name == null)
{
name = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1);
}
int firstRow = range.FirstRow;
// if row was not given when constructing the range...
if (firstRow == -1)
{
firstRow = 0;
}
// The built-in name must consist of a single Area3d Ptg.
Area3DPtg ptg = new Area3DPtg(firstRow, range.LastRow,
range.FirstColumn, range.LastColumn,
false, false, false, false, sheetIndex);
name.NameDefinition = (new Ptg[] { ptg });
AutoFilterInfoRecord r = new AutoFilterInfoRecord();
// the number of columns that have AutoFilter enabled.
int numcols = 1 + range.LastColumn - range.FirstColumn;
r.NumEntries = (short)numcols;
int idx = _sheet.FindFirstRecordLocBySid(DimensionsRecord.sid);
_sheet.Records.Insert(idx, r);
//create a combobox control for each column
HSSFPatriarch p = (HSSFPatriarch)CreateDrawingPatriarch();
for (int col = range.FirstColumn; col <= range.LastColumn; col++)
{
p.CreateComboBox(new HSSFClientAnchor(0, 0, 0, 0,
(short)col, firstRow, (short)(col + 1), firstRow + 1));
}
return new HSSFAutoFilter(this);
}
/**
*
* @param sheetIden may be <code>null</code>
* @param part1
* @param part2 may be <code>null</code>
*/
private ParseNode CreateAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
SimpleRangePart part2)
{
int extIx;
if (sheetIden == null)
{
extIx = Int32.MinValue;
}
else
{
String sName = sheetIden.SheetID.Name;
if (sheetIden.BookName == null)
{
extIx = _book.GetExternalSheetIndex(sName);
}
else
{
extIx = _book.GetExternalSheetIndex(sheetIden.BookName, sName);
}
}
Ptg ptg;
if (part2 == null)
{
CellReference cr = part1.getCellReference();
if (sheetIden == null)
{
ptg = new RefPtg(cr);
}
else
{
ptg = new Ref3DPtg(cr, extIx);
}
}
else
{
AreaReference areaRef = CreateAreaRef(part1, part2);
if (sheetIden == null)
{
ptg = new AreaPtg(areaRef);
}
else
{
ptg = new Area3DPtg(areaRef, extIx);
}
}
return new ParseNode(ptg);
}
/// <summary>
/// Sets the repeating rows and columns for a sheet (as found in
/// File->PageSetup->Sheet). This Is function Is included in the workbook
/// because it Creates/modifies name records which are stored at the
/// workbook level.
/// </summary>
/// <param name="sheetIndex">0 based index to sheet.</param>
/// <param name="startColumn">0 based start of repeating columns.</param>
/// <param name="endColumn">0 based end of repeating columns.</param>
/// <param name="startRow">0 based start of repeating rows.</param>
/// <param name="endRow">0 based end of repeating rows.</param>
/// <example>
/// To set just repeating columns:
/// workbook.SetRepeatingRowsAndColumns(0,0,1,-1-1);
/// To set just repeating rows:
/// workbook.SetRepeatingRowsAndColumns(0,-1,-1,0,4);
/// To remove all repeating rows and columns for a sheet.
/// workbook.SetRepeatingRowsAndColumns(0,-1,-1,-1,-1);
/// </example>
public void SetRepeatingRowsAndColumns(int sheetIndex,
int startColumn, int endColumn,
int startRow, int endRow)
{
// Check arguments
if (startColumn == -1 && endColumn != -1) throw new ArgumentException("Invalid column range specification");
if (startRow == -1 && endRow != -1) throw new ArgumentException("Invalid row range specification");
if (startColumn < -1 || startColumn >= 0xFF) throw new ArgumentException("Invalid column range specification");
if (endColumn < -1 || endColumn >= 0xFF) throw new ArgumentException("Invalid column range specification");
if (startRow < -1 || startRow > 65535) throw new ArgumentException("Invalid row range specification");
if (endRow < -1 || endRow > 65535) throw new ArgumentException("Invalid row range specification");
if (startColumn > endColumn) throw new ArgumentException("Invalid column range specification");
if (startRow > endRow) throw new ArgumentException("Invalid row range specification");
HSSFSheet sheet = (HSSFSheet)GetSheetAt(sheetIndex);
int externSheetIndex = Workbook.CheckExternSheet(sheetIndex);
bool settingRowAndColumn =
startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
bool removingRange =
startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;
bool IsNewRecord = false;
int rowColHeaderNameIndex = FindExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_PRINT_TITLE);
if (removingRange)
{
if (rowColHeaderNameIndex >= 0)
{
workbook.RemoveName(rowColHeaderNameIndex);
}
return;
}
NameRecord nameRecord;
if (rowColHeaderNameIndex < 0)
{
//does a lot of the house keeping for builtin records, like setting lengths to zero etc
nameRecord = workbook.CreateBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex + 1);
IsNewRecord = true;
}
else
{
nameRecord = workbook.GetNameRecord(rowColHeaderNameIndex);
IsNewRecord = false;
}
ArrayList temp = new ArrayList();
if (settingRowAndColumn)
{
int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
temp.Add(new MemFuncPtg(exprsSize));
}
if (startColumn >= 0)
{
Area3DPtg colArea = new Area3DPtg(0, MAX_ROW, startColumn, endColumn,
false, false, false, false, externSheetIndex);
temp.Add(colArea);
}
if (startRow >= 0)
{
Area3DPtg rowArea = new Area3DPtg(startRow, endRow, 0, MAX_COLUMN,
false, false, false, false, externSheetIndex);
temp.Add(rowArea);
}
if (settingRowAndColumn)
{
temp.Add(UnionPtg.instance);
}
Ptg[] ptgs = (Ptg[])temp.ToArray(typeof(Ptg));
nameRecord.NameDefinition = ptgs;
if (IsNewRecord)
{
HSSFName newName = new HSSFName(this, nameRecord);
names.Add(newName);
}
NPOI.SS.UserModel.IPrintSetup printSetup = sheet.PrintSetup;
printSetup.ValidSettings = (false);
sheet.IsActive = (true);
}
private LinkedDataRecord CreateCategoriesLinkedDataRecord()
{
LinkedDataRecord r = new LinkedDataRecord();
r.LinkType = (LinkedDataRecord.LINK_TYPE_CATEGORIES);
r.ReferenceType = (LinkedDataRecord.REFERENCE_TYPE_WORKSHEET);
r.IsCustomNumberFormat = (false);
r.IndexNumberFmtRecord = ((short)0);
Area3DPtg p = new Area3DPtg(0, 31, 1, 1,
false, false, false, false, 0);
r.FormulaOfLink = (new Ptg[] { p, });
return r;
}
private LinkedDataRecord CreateValuesLinkedDataRecord()
{
LinkedDataRecord r = new LinkedDataRecord();
r.SetLinkType( LinkedDataRecord.LINK_TYPE_VALUES );
r.SetReferenceType( LinkedDataRecord.REFERENCE_TYPE_WORKSHEET );
r.SetCustomNumberFormat( false );
r.SetIndexNumberFmtRecord( (short) 0 );
Area3DPtg p = new Area3DPtg(0, 31, 0, 0,
false, false, false, false, 0);
r.SetFormulaOfLink(new Ptg[] { p, });
return r;
}
public void TestStore()
{
BRAIRecord record = new BRAIRecord();
record.LinkType=(BRAIRecord.LINK_TYPE_VALUES);
record.ReferenceType=(BRAIRecord.REFERENCE_TYPE_WORKSHEET);
record.Options=((short)0);
record.IsCustomNumberFormat=(false);
record.IndexNumberFmtRecord=((short)0);
Area3DPtg ptg = new Area3DPtg(0, 7936, 0, 0,
false, false, false, false, 0);
record.FormulaOfLink = (new Ptg[] { ptg, });
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length - 4, data.Length);
for (int i = 0; i < data.Length; i++)
Assert.AreEqual(data[i], recordBytes[i + 4], "At offset " + i);
}
public void TestLoad()
{
BRAIRecord record = new BRAIRecord(TestcaseRecordInputStream.Create((short)0x1051, data));
Assert.AreEqual(BRAIRecord.LINK_TYPE_VALUES, record.LinkType);
Assert.AreEqual(BRAIRecord.REFERENCE_TYPE_WORKSHEET, record.ReferenceType);
Assert.AreEqual(0, record.Options);
Assert.AreEqual(false, record.IsCustomNumberFormat);
Assert.AreEqual(0, record.IndexNumberFmtRecord);
Area3DPtg ptgExpected = new Area3DPtg(0, 7936, 0, 0,false, false, false, false, 0);
Object ptgActual = record.FormulaOfLink[0];
Assert.AreEqual(ptgExpected.ToString(), ptgActual.ToString());
Assert.AreEqual(data.Length + 4, record.RecordSize);
}
public ValueEval GetArea3DEval(Area3DPtg aptg)
{
SheetRangeEvaluator sre = CreateExternSheetRefEvaluator(aptg.ExternSheetIndex);
return new LazyAreaEval(aptg.FirstRow, aptg.FirstColumn,
aptg.LastRow, aptg.LastColumn, sre);
}
