/// <summary>
/// Create an HSSFSheet from an existing sheet in the HSSFWorkbook.
/// </summary>
/// <param name="sheetIndex">the sheet index</param>
/// <returns>HSSFSheet representing the Cloned sheet.</returns>
public ISheet CloneSheet(int sheetIndex)
{
ValidateSheetIndex(sheetIndex);
HSSFSheet srcSheet = (HSSFSheet)_sheets[sheetIndex];
String srcName = workbook.GetSheetName(sheetIndex);
ISheet clonedSheet = srcSheet.CloneSheet(this);
clonedSheet.IsSelected = (false);
clonedSheet.IsActive = (false);
String name = GetUniqueSheetName(srcName);
int newSheetIndex = _sheets.Count;
_sheets.Add((HSSFSheet)clonedSheet);
workbook.SetSheetName(newSheetIndex, name);
// Check this sheet has an autofilter, (which has a built-in NameRecord at workbook level)
int filterDbNameIndex = FindExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_FILTER_DB);
if (filterDbNameIndex != -1)
{
NameRecord newNameRecord = workbook.CloneFilter(filterDbNameIndex, newSheetIndex);
HSSFName newName = new HSSFName(this, newNameRecord);
names.Add(newName);
}
// TODO - maybe same logic required for other/all built-in name records
workbook.CloneDrawings(((HSSFSheet)clonedSheet).Sheet);
return clonedSheet;
}
/// <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);
}
/// <summary>
/// Creates a new named range and Add it to the model
/// </summary>
/// <returns>named range high level</returns>
public NPOI.SS.UserModel.IName CreateName()
{
NameRecord nameRecord = workbook.CreateName();
HSSFName newName = new HSSFName(this, nameRecord);
names.Add(newName);
return newName;
}
/**
* given a POI POIFSFileSystem object, and a specific directory
* within it, read in its Workbook and populate the high and
* low level models. If you're reading in a workbook...start here.
*
* @param directory the POI filesystem directory to process from
* @param preserveNodes whether to preseve other nodes, such as
* macros. This takes more memory, so only say yes if you
* need to. If set, will store all of the POIFSFileSystem
* in memory
* @see org.apache.poi.poifs.filesystem.POIFSFileSystem
* @exception IOException if the stream cannot be read
*/
public HSSFWorkbook(DirectoryNode directory, bool preserveNodes):base(directory)
{
String workbookName = GetWorkbookDirEntryName(directory);
this.preserveNodes = preserveNodes;
// If we're not preserving nodes, don't track the
// POIFS any more
if (!preserveNodes)
{
this.directory = null;
}
_sheets = new List<HSSFSheet>(INITIAL_CAPACITY);
names = new List<HSSFName>(INITIAL_CAPACITY);
// Grab the data from the workbook stream, however
// it happens to be spelled.
Stream stream = directory.CreatePOIFSDocumentReader(workbookName);
List<Record> records = RecordFactory.CreateRecords(stream);
workbook = InternalWorkbook.CreateWorkbook(records);
SetPropertiesFromWorkbook(workbook);
int recOffset = workbook.NumRecords;
// Convert all LabelRecord records to LabelSSTRecord
ConvertLabelRecords(records, recOffset);
RecordStream rs = new RecordStream(records, recOffset);
while (rs.HasNext())
{
InternalSheet sheet = InternalSheet.CreateSheet(rs);
_sheets.Add(new HSSFSheet(this, sheet));
}
for (int i = 0; i < workbook.NumNames; ++i)
{
NameRecord nameRecord = workbook.GetNameRecord(i);
HSSFName name = new HSSFName(this, workbook.GetNameRecord(i), workbook.GetNameCommentRecord(nameRecord));
names.Add(name);
}
}
//
/// <summary>
/// As #removeName(String) is not necessarily unique (name + sheet index is unique),
/// this method is more accurate.
/// </summary>
/// <param name="name">the name to remove.</param>
public void RemoveName(HSSFName name)
{
int index = GetNameIndex(name);
RemoveName(index);
}
//
/// <summary>
/// As GetNameIndex(String) is not necessarily unique
/// (name + sheet index is unique), this method is more accurate.
/// </summary>
/// <param name="name">the name whose index in the list of names of this workbook should be looked up.</param>
/// <returns>an index value >= 0 if the name was found; -1, if the name was not found</returns>
public int GetNameIndex(HSSFName name)
{
for (int k = 0; k < names.Count; k++)
{
if (name == names[(k)])
{
return k;
}
}
return -1;
}
internal HSSFName CreateBuiltInName(byte builtinCode, int sheetIndex)
{
NameRecord nameRecord =
workbook.CreateBuiltInName(builtinCode, sheetIndex + 1);
HSSFName newName = new HSSFName(this, nameRecord, null);
names.Add(newName);
return newName;
}
/// <summary>
/// Create an HSSFSheet from an existing sheet in the HSSFWorkbook.
/// </summary>
/// <param name="sheetIndex">the sheet index</param>
/// <returns>HSSFSheet representing the Cloned sheet.</returns>
public HSSFSheet CloneSheet(int sheetIndex)
{
ValidateSheetIndex(sheetIndex);
HSSFSheet srcSheet = (HSSFSheet)_sheets[sheetIndex];
String srcName = workbook.GetSheetName(sheetIndex);
HSSFSheet clonedSheet = srcSheet.CloneSheet(this);
clonedSheet.IsSelected = (false);
clonedSheet.IsActive = (false);
String name = GetUniqueSheetName(srcName);
int newSheetIndex = _sheets.Count;
_sheets.Add(clonedSheet);
workbook.SetSheetName(newSheetIndex, name);
// Check this sheet has an autofilter, (which has a built-in NameRecord at workbook level)
int filterDbNameIndex = FindExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_FILTER_DB);
if (filterDbNameIndex >= 0)
{
NameRecord origNameRecord = workbook.GetNameRecord(filterDbNameIndex);
// copy original formula but adjust 3D refs to the new external sheet index
int newExtSheetIx = workbook.CheckExternSheet(newSheetIndex);
Ptg[] ptgs = origNameRecord.NameDefinition;
for (int i = 0; i < ptgs.Length; i++)
{
Ptg ptg = ptgs[i];
ptg = ptg.Copy();
if (ptg is Area3DPtg)
{
Area3DPtg a3p = (Area3DPtg)ptg;
a3p.ExternSheetIndex = newExtSheetIx;
}
else if (ptg is Ref3DPtg)
{
Ref3DPtg r3p = (Ref3DPtg)ptg;
r3p.ExternSheetIndex = (newExtSheetIx);
}
ptgs[i] = ptg;
}
NameRecord newNameRecord = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, newSheetIndex + 1);
newNameRecord.NameDefinition = (ptgs);
newNameRecord.IsHiddenName = true;
HSSFName newName = new HSSFName(this, newNameRecord);
names.Add(newName);
workbook.CloneDrawings(clonedSheet.Sheet);
}
return clonedSheet;
}
/// <summary>
/// Creates a new named range and Add it to the model
/// </summary>
/// <returns>named range high level</returns>
public HSSFName CreateName()
{
NameRecord nameRecord = workbook.CreateName();
HSSFName newName = new HSSFName(this, nameRecord);
names.Add(newName);
return newName;
}
