/**
* 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>
/// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when
/// reading in cells from an existing sheet.
/// </summary>
/// <param name="book">Workbook record of the workbook containing this cell</param>
/// <param name="sheet">Sheet record of the sheet containing this cell</param>
/// <param name="cval">the Cell Value Record we wish to represent</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval)
{
record = cval;
cellType = DetermineType(cval);
stringValue = null;
this.book = book;
this.sheet = sheet;
switch (cellType)
{
case CellType.STRING:
stringValue = new HSSFRichTextString(book.Workbook, (LabelSSTRecord)cval);
break;
case CellType.BLANK:
break;
case CellType.FORMULA:
stringValue = new HSSFRichTextString(((FormulaRecordAggregate)cval).StringValue);
break;
}
ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(cval.XFIndex);
CellStyle = new HSSFCellStyle((short)cval.XFIndex, xf, book);
}
/// <summary>
/// Creates a cell, gives it a value, and applies a style if provided
/// </summary>
/// <param name="row">the row to Create the cell in</param>
/// <param name="column">the column index to Create the cell in</param>
/// <param name="value">The value of the cell</param>
/// <param name="style">If the style is not null, then Set</param>
/// <returns>A new HSSFCell</returns>
public static LF.Utils.NPOI.SS.UserModel.ICell CreateCell(LF.Utils.NPOI.SS.UserModel.IRow row, int column, String value, HSSFCellStyle style)
{
LF.Utils.NPOI.SS.UserModel.ICell cell = GetCell(row, column);
cell.SetCellValue(new HSSFRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return cell;
}
/// <summary>
/// Clones all the style information from another
/// HSSFCellStyle, onto this one. This
/// HSSFCellStyle will then have all the same
/// properties as the source, but the two may
/// be edited independently.
/// Any stylings on this HSSFCellStyle will be lost!
/// The source HSSFCellStyle could be from another
/// HSSFWorkbook if you like. This allows you to
/// copy styles from one HSSFWorkbook to another.
/// </summary>
/// <param name="source">The source.</param>
public void CloneStyleFrom(HSSFCellStyle source)
{
// First we need to clone the extended format
// record
format.CloneStyleFrom(source.format);
// Handle matching things if we cross workbooks
if (workbook != source.workbook)
{
// Then we need to clone the format string,
// and update the format record for this
short fmt = (short)workbook.CreateFormat(
source.GetDataFormatString()
);
this.DataFormat=(fmt);
// Finally we need to clone the font,
// and update the format record for this
FontRecord fr = workbook.CreateNewFont();
fr.CloneStyleFrom(
source.workbook.GetFontRecordAt(
source.FontIndex
)
);
HSSFFont font = new HSSFFont(
(short)workbook.GetFontIndex(fr), fr
);
this.SetFont(font);
}
}
/// <summary>
/// Get the cell style object at the given index
/// </summary>
/// <param name="idx">index within the Set of styles</param>
/// <returns>HSSFCellStyle object at the index</returns>
public LF.Utils.NPOI.SS.UserModel.ICellStyle GetCellStyleAt(short idx)
{
ExtendedFormatRecord xfr = workbook.GetExFormatAt(idx);
HSSFCellStyle style = new HSSFCellStyle(idx, xfr, this);
return style;
}
/// <summary>
/// Create a new Cell style and Add it to the workbook's style table
/// </summary>
/// <returns>the new Cell Style object</returns>
public LF.Utils.NPOI.SS.UserModel.ICellStyle CreateCellStyle()
{
if (workbook.NumExFormats == MAX_STYLES)
{
throw new InvalidOperationException("The maximum number of cell styles was exceeded. " +
"You can define up to 4000 styles in a .xls workbook");
}
ExtendedFormatRecord xfr = workbook.CreateCellXF();
short index = (short)(NumCellStyles - 1);
HSSFCellStyle style = new HSSFCellStyle(index, xfr, this);
return style;
}
