/**
* Clones all the style information from another
* ExtendedFormatRecord, onto this one. This
* will then hold all the same style options.
*
* If The source ExtendedFormatRecord comes from
* a different Workbook, you will need to sort
* out the font and format indicies yourself!
*/
public void CloneStyleFrom(ExtendedFormatRecord source)
{
field_1_font_index = source.field_1_font_index;
field_2_format_index = source.field_2_format_index;
field_3_cell_options = source.field_3_cell_options;
field_4_alignment_options = source.field_4_alignment_options;
field_5_indention_options = source.field_5_indention_options;
field_6_border_options = source.field_6_border_options;
field_7_palette_options = source.field_7_palette_options;
field_8_adtl_palette_options = source.field_8_adtl_palette_options;
field_9_fill_palette_options = source.field_9_fill_palette_options;
}
/**
* Will consider two different records with the same
* contents as Equals, as the various indexes
* that matter are embedded in the records
*/
public override bool Equals(Object obj)
{
if (this == obj)
{
return(true);
}
if (obj == null)
{
return(false);
}
if (obj is ExtendedFormatRecord)
{
ExtendedFormatRecord other = (ExtendedFormatRecord)obj;
if (field_1_font_index != other.field_1_font_index)
{
return(false);
}
if (field_2_format_index != other.field_2_format_index)
{
return(false);
}
if (field_3_cell_options != other.field_3_cell_options)
{
return(false);
}
if (field_4_alignment_options != other.field_4_alignment_options)
{
return(false);
}
if (field_5_indention_options != other.field_5_indention_options)
{
return(false);
}
if (field_6_border_options != other.field_6_border_options)
{
return(false);
}
if (field_7_palette_options != other.field_7_palette_options)
{
return(false);
}
if (field_8_adtl_palette_options != other.field_8_adtl_palette_options)
{
return(false);
}
if (field_9_fill_palette_options != other.field_9_fill_palette_options)
{
return(false);
}
return(true);
}
return(false);
}
/// <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);
}
}
}
}
/// <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, Zephyr.Utils.NPOI.HSSF.Model.InternalWorkbook workbook)
{
this.workbook = workbook;
this.index = index;
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)
{
}
/**
* Removes the given ExtendedFormatRecord record from the
* file's list. This will make all
* subsequent font indicies drop by one,
* so you'll need to update those yourself!
*/
public void RemoveExFormatRecord(ExtendedFormatRecord rec)
{
records.Remove(rec); // this updates XfPos for us
numxfs--;
}
/**
* Creates an default cell type ExtendedFormatRecord object.
* @return ExtendedFormatRecord with intial defaults (cell-type)
*/
private static ExtendedFormatRecord CreateExtendedFormat()
{
ExtendedFormatRecord retval = new ExtendedFormatRecord();
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0x0;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
retval.TopBorderPaletteIdx=HSSFColor.BLACK.index;
retval.BottomBorderPaletteIdx=HSSFColor.BLACK.index;
retval.LeftBorderPaletteIdx=HSSFColor.BLACK.index;
retval.RightBorderPaletteIdx=HSSFColor.BLACK.index;
return retval;
}
// /**
// * Creates a FormatRecord object
// * @param id the number of the format record to Create (meaning its position in
// * a file as M$ Excel would Create it.)
// * @return record containing a FormatRecord
// * @see org.apache.poi.hssf.record.FormatRecord
// * @see org.apache.poi.hssf.record.Record
// */
//protected Record CreateFormat(int id)
//{ // we'll need multiple editions for
// FormatRecord retval = new FormatRecord(); // the differnt formats
// switch (id)
// {
// case 0:
// retval.SetIndexCode((short)5);
// retval.SetFormatStringLength((byte)0x17);
// retval.SetFormatString("\"$\"#,##0_);\\(\"$\"#,##0\\)");
// break;
// case 1:
// retval.SetIndexCode((short)6);
// retval.SetFormatStringLength((byte)0x1c);
// retval.SetFormatString("\"$\"#,##0_);[Red]\\(\"$\"#,##0\\)");
// break;
// case 2:
// retval.SetIndexCode((short)7);
// retval.SetFormatStringLength((byte)0x1d);
// retval.SetFormatString("\"$\"#,##0.00_);\\(\"$\"#,##0.00\\)");
// break;
// case 3:
// retval.SetIndexCode((short)8);
// retval.SetFormatStringLength((byte)0x22);
// retval.SetFormatString(
// "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)");
// break;
// case 4:
// retval.SetIndexCode((short)0x2a);
// retval.SetFormatStringLength((byte)0x32);
// retval.SetFormatString(
// "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)");
// break;
// case 5:
// retval.SetIndexCode((short)0x29);
// retval.SetFormatStringLength((byte)0x29);
// retval.SetFormatString(
// "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)");
// break;
// case 6:
// retval.SetIndexCode((short)0x2c);
// retval.SetFormatStringLength((byte)0x3a);
// retval.SetFormatString(
// "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)");
// break;
// case 7:
// retval.SetIndexCode((short)0x2b);
// retval.SetFormatStringLength((byte)0x31);
// retval.SetFormatString(
// "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)");
// break;
// }
// return retval;
//}
/**
* Creates an ExtendedFormatRecord object
* @param id the number of the extended format record to Create (meaning its position in
* a file as MS Excel would Create it.)
*
* @return record containing an ExtendedFormatRecord
* @see org.apache.poi.hssf.record.ExtendedFormatRecord
* @see org.apache.poi.hssf.record.Record
*/
private static Record CreateExtendedFormat(int id)
{ // we'll need multiple editions
ExtendedFormatRecord retval = new ExtendedFormatRecord();
switch (id)
{
case 0:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 1:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 2:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 3:
retval.FontIndex=(short)2;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 4:
retval.FontIndex=(short)2;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 5:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 6:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 7:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 8:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 9:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 10:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 11:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 12:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 13:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 14:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff400);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
// cell records
case 15:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x0;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
// style
case 16:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0x2b;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff800);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 17:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0x29;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff800);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 18:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0x2c;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff800);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 19:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0x2a;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff800);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 20:
retval.FontIndex=(short)1;
retval.FormatIndex=(short)0x9;
retval.CellOptions=unchecked((short)0xfffffff5);
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=unchecked((short)0xfffff800);
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
// Unused from this point down
case 21:
retval.FontIndex=(short)5;
retval.FormatIndex=(short)0x0;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x800;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 22:
retval.FontIndex=(short)6;
retval.FormatIndex=(short)0x0;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x5c00;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 23:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0x31;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x5c00;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 24:
retval.FontIndex=(short)0;
retval.FormatIndex=(short)0x8;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x5c00;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
case 25:
retval.FontIndex=(short)6;
retval.FormatIndex=(short)0x8;
retval.CellOptions=(short)0x1;
retval.AlignmentOptions=(short)0x20;
retval.IndentionOptions=(short)0x5c00;
retval.BorderOptions=(short)0;
retval.PaletteOptions=(short)0;
retval.AdtlPaletteOptions=(short)0;
retval.FillPaletteOptions=(short)0x20c0;
break;
}
return retval;
}
/**
* Clones all the style information from another
* ExtendedFormatRecord, onto this one. This
* will then hold all the same style options.
*
* If The source ExtendedFormatRecord comes from
* a different Workbook, you will need to sort
* out the font and format indicies yourself!
*/
public void CloneStyleFrom(ExtendedFormatRecord source)
{
field_1_font_index = source.field_1_font_index;
field_2_format_index = source.field_2_format_index;
field_3_cell_options = source.field_3_cell_options;
field_4_alignment_options = source.field_4_alignment_options;
field_5_indention_options = source.field_5_indention_options;
field_6_border_options = source.field_6_border_options;
field_7_palette_options = source.field_7_palette_options;
field_8_adtl_palette_options = source.field_8_adtl_palette_options;
field_9_fill_palette_options = source.field_9_fill_palette_options;
}