/**
* Add a row to the _sheet
*
* @param AddLow whether to Add the row to the low level model - false if its already there
*/
private void AddRow(HSSFRow row, bool addLow)
{
rows[row.RowNum] = row;
if (addLow)
{
_sheet.AddRow(row.RowRecord);
}
bool firstRow = rows.Count == 1;
if (row.RowNum > LastRowNum || firstRow)
{
lastrow = row.RowNum;
}
if (row.RowNum < FirstRowNum || firstRow)
{
firstrow = row.RowNum;
}
}
/// <summary>
/// Used internally to Create a high level Row object from a low level row object.
/// USed when Reading an existing file
/// </summary>
/// <param name="row">low level record to represent as a high level Row and Add to _sheet.</param>
/// <returns>HSSFRow high level representation</returns>
private HSSFRow CreateRowFromRecord(RowRecord row)
{
HSSFRow hrow = new HSSFRow(_workbook, this, row);
AddRow(hrow, false);
return hrow;
}
/// <summary>
/// Create a new row within the _sheet and return the high level representation
/// </summary>
/// <param name="rownum">The row number.</param>
/// <returns></returns>
/// @see org.apache.poi.hssf.usermodel.HSSFRow
/// @see #RemoveRow(HSSFRow)
public Zephyr.Utils.NPOI.SS.UserModel.IRow CreateRow(int rownum)
{
HSSFRow row = new HSSFRow(_workbook, this, rownum);
AddRow(row, true);
return row;
}
private void NotifyRowShifting(HSSFRow row)
{
String msg = "Row[rownum=" + row.RowNum + "] contains cell(s) included in a multi-cell array formula. " +
"You cannot change part of an array.";
foreach (ICell cell in row.Cells)
{
HSSFCell hcell = (HSSFCell)cell;
if (hcell.IsPartOfArrayFormulaGroup)
{
hcell.NotifyArrayFormulaChanging(msg);
}
}
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4)
{
continue;
}
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4)
{
continue;
}
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
Zephyr.Utils.NPOI.SS.UserModel.ISheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
if (cell.CellType == Zephyr.Utils.NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
for (int i = 21; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveExFormatRecord(
xfrs[i]
);
}
}
// Finally, update the cells to point at
// their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetEnumerator();
while (cIt.MoveNext())
{
ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
Zephyr.Utils.NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}