/**
* Returns the index of the format string, used by your cell,
* or -1 if none found
*/
public int GetFormatIndex(CellValueRecordInterface cell)
{
ExtendedFormatRecord xfr = (ExtendedFormatRecord)
xfRecords[cell.XFIndex];
if (xfr == null)
{
Console.Error.WriteLine("Cell " + cell.Row + "," + cell.Column + " uses XF with index " + cell.XFIndex + ", but we don't have that");
return(-1);
}
return(xfr.FormatIndex);
}
/**
* Returns the index of the format string, used by your cell,
* or -1 if none found
*/
public int GetFormatIndex(CellValueRecordInterface cell)
{
ExtendedFormatRecord xfr = (ExtendedFormatRecord)
xfRecords[cell.XFIndex];
if (xfr == null)
{
logger.Log(POILogger.ERROR, "Cell " + cell.Row + "," + cell.Column + " uses XF with index " + cell.XFIndex + ", but we don't have that");
return(-1);
}
return(xfr.FormatIndex);
}
/**
* Process the record ourselves, but do not
* pass it on to the child Listener.
* @param record
*/
public void ProcessRecordInternally(Record record)
{
if (record is FormatRecord)
{
FormatRecord fr = (FormatRecord)record;
customFormatRecords[fr.IndexCode] = fr;
}
else if (record is ExtendedFormatRecord)
{
ExtendedFormatRecord xr = (ExtendedFormatRecord)record;
xfRecords.Add(xr);
}
}
public void TestCloneOnto()
{
ExtendedFormatRecord base1 = CreateEFR();
ExtendedFormatRecord other = new ExtendedFormatRecord();
other.CloneStyleFrom(base1);
byte[] recordBytes = other.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()
{
ExtendedFormatRecord record = CreateEFR();
Assert.AreEqual(0, record.FontIndex);
Assert.AreEqual(0, record.FormatIndex);
Assert.AreEqual(0xF5 - 256, record.CellOptions);
Assert.AreEqual(0x20, record.AlignmentOptions);
Assert.AreEqual(0, record.IndentionOptions);
Assert.AreEqual(0, record.BorderOptions);
Assert.AreEqual(0, record.PaletteOptions);
Assert.AreEqual(0, record.AdtlPaletteOptions);
Assert.AreEqual(0x20c0, record.FillPaletteOptions);
Assert.AreEqual(20 + 4, record.RecordSize);
}
public void TestRotation()
{
ExtendedFormatRecord record = CreateEFR();
Assert.AreEqual(0, record.Rotation);
record.Rotation = ((short)1);
Assert.AreEqual(1, record.Rotation);
record.Rotation = ((short)89);
Assert.AreEqual(89, record.Rotation);
record.Rotation = ((short)90);
Assert.AreEqual(90, record.Rotation);
// internally values below zero are stored differently
record.Rotation = ((short)-1);
Assert.AreEqual(255, record.Rotation);
record.Rotation = ((short)-89);
Assert.AreEqual(-77, 90 - record.Rotation);
record.Rotation = ((short)-90);
Assert.AreEqual(-76, 90 - record.Rotation);
}
/**
* Applying a user-defined style (UDS) is special. Excel does not directly reference user-defined styles, but
* instead create a 'proxy' ExtendedFormatRecord referencing the UDS as parent.
*
* The proceudre to apply a UDS is as follows:
*
* 1. search for a ExtendedFormatRecord with parentIndex == style.getIndex()
* and xfType == ExtendedFormatRecord.XF_CELL.
* 2. if not found then create a new ExtendedFormatRecord and copy all attributes from the user-defined style
* and set the parentIndex to be style.getIndex()
* 3. return the index of the ExtendedFormatRecord, this will be assigned to the parent cell record
*
* @param style the user style to apply
*
* @return the index of a ExtendedFormatRecord record that will be referenced by the cell
*/
private short ApplyUserCellStyle(HSSFCellStyle style)
{
if (style.UserStyleName == null)
{
throw new ArgumentException("Expected user-defined style");
}
InternalWorkbook iwb = book.Workbook;
short userXf = -1;
int numfmt = iwb.NumExFormats;
for (short i = 0; i < numfmt; i++)
{
ExtendedFormatRecord xf = iwb.GetExFormatAt(i);
if (xf.XFType == ExtendedFormatRecord.XF_CELL && xf.ParentIndex == style.Index)
{
userXf = i;
break;
}
}
short styleIndex;
if (userXf == -1)
{
ExtendedFormatRecord xfr = iwb.CreateCellXF();
xfr.CloneStyleFrom(iwb.GetExFormatAt(style.Index));
xfr.IndentionOptions = (short)0;
xfr.XFType = (ExtendedFormatRecord.XF_CELL);
xfr.ParentIndex = (style.Index);
styleIndex = (short)numfmt;
}
else
{
styleIndex = userXf;
}
return(styleIndex);
}
/// <summary>
/// Initializes a new instance of the <see cref="HSSFCellStyle"/> class.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="rec">The record.</param>
/// <param name="workbook">The workbook.</param>
public HSSFCellStyle(short index, ExtendedFormatRecord rec, NPOI.HSSF.Model.InternalWorkbook workbook)
{
this._workbook = workbook;
this.index = index;
this._format = rec;
}
/// <summary>
/// Initializes a new instance of the <see cref="HSSFCellStyle"/> class.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="rec">The record.</param>
/// <param name="workbook">The workbook.</param>
public HSSFCellStyle(short index, ExtendedFormatRecord rec, HSSFWorkbook workbook)
: this(index, rec, workbook.Workbook)
{
}
public void TestStore()
{
// .fontindex = 0
// .formatindex = 0
// .celloptions = fffffff5
// .Islocked = true
// .Ishidden = false
// .Recordtype= 1
// .parentidx = fff
// .alignmentoptions= 20
// .alignment = 0
// .wraptext = false
// .valignment= 2
// .justlast = 0
// .rotation = 0
// .indentionoptions= 0
// .indent = 0
// .shrinktoft= false
// .mergecells= false
// .Readngordr= 0
// .formatflag= false
// .fontflag = false
// .prntalgnmt= false
// .borderflag= false
// .paternflag= false
// .celloption= false
// .borderoptns = 0
// .lftln = 0
// .rgtln = 0
// .Topln = 0
// .btmln = 0
// .paleteoptns = 0
// .leftborder= 0
// .rghtborder= 0
// .diag = 0
// .paleteoptn2 = 0
// .Topborder = 0
// .botmborder= 0
// .adtldiag = 0
// .diaglnstyl= 0
// .Fillpattrn= 0
// .Fillpaloptn = 20c0
// .foreground= 40
// .background= 41
ExtendedFormatRecord record = new ExtendedFormatRecord();
record.FontIndex = (/*setter*/ (short)0);
record.FormatIndex = (/*setter*/ (short)0);
record.IsLocked = (/*setter*/ true);
record.IsHidden = (/*setter*/ false);
record.XFType = (/*setter*/ (short)1);
record.ParentIndex = (/*setter*/ (short)0xfff);
record.VerticalAlignment = (/*setter*/ (short)2);
record.FillForeground = (/*setter*/ (short)0x40);
record.FillBackground = (/*setter*/ (short)0x41);
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);
}
}
/// <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++)
{
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 == 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;
NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones and ones that aren't used.
/// 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[] isUsed = new bool[newPos.Length];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
isUsed[i] = false;
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;
}
// If we got a duplicate, mark the one we're keeping as used
if (earlierDuplicate != -1)
{
isUsed[earlierDuplicate] = true;
}
}
// Loop over all the cells in the file, and identify any user defined
// styles aren't actually being used (don't touch built-in ones)
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
foreach (IRow row in s)
{
foreach (ICell cellI in row)
{
HSSFCell cell = (HSSFCell)cellI;
short oldXf = cell.CellValueRecord.XFIndex;
isUsed[oldXf] = true;
}
}
}
// Mark any that aren't used as needing zapping
for (int i = 21; i < isUsed.Length; i++)
{
if (!isUsed[i])
{
// Un-used style, can be removed
zapRecords[i] = true;
newPos[i] = 0;
}
}
// 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
// removing by index, because removing by object may delete
// styles we did not intend to (the ones that _were_ duplicated and not the duplicates)
int max = newPos.Length;
int removed = 0; // to adjust index after deletion
for (int i = 21; i < max; i++)
{
if (zapRecords[i + removed])
{
workbook.Workbook.RemoveExFormatRecord(i);
i--;
max--;
removed++;
}
}
// 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())
foreach (IRow row in s)
{
//HSSFRow row = (HSSFRow)rIt.Current;
//IEnumerator cIt = row.GetEnumerator();
//while (cIt.MoveNext())
foreach (ICell cell in row)
{
//ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
Npoi.Core.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
