/// <summary>
/// Creates new Cell - Should only be called by HSSFRow. This Creates a cell
/// from scratch.
/// </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="row">the row of this cell</param>
/// <param name="col">the column for this cell</param>
/// <param name="type">CellType.Numeric, CellType.String, CellType.Formula, CellType.Blank,
/// CellType.Boolean, CellType.Error</param>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col,
CellType type)
{
CheckBounds(col);
cellType = CellType.Unknown; // Force 'SetCellType' to Create a first Record
stringValue = null;
this.book = book;
_sheet = sheet;
short xfindex = sheet.Sheet.GetXFIndexForColAt(col);
SetCellType(type, false, row, col, xfindex);
}
/// <summary>
/// Set a string value for the cell. Please note that if you are using
/// full 16 bit Unicode you should call SetEncoding() first.
/// </summary>
/// <param name="value">value to Set the cell to. For formulas we'll Set the formula
/// string, for String cells we'll Set its value. For other types we will
/// Change the cell to a string cell and Set its value.
/// If value is null then we will Change the cell to a Blank cell.</param>
public void SetCellValue(IRichTextString value)
{
int row = _record.Row;
int col = _record.Column;
short styleIndex = _record.XFIndex;
if (value == null)
{
NotifyFormulaChanging();
SetCellType(CellType.Blank, false, row, col, styleIndex);
return;
}
if (value.Length > Npoi.Core.SS.SpreadsheetVersion.EXCEL97.MaxTextLength)
{
throw new ArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
if (cellType == CellType.Formula)
{
// Set the 'pre-Evaluated result' for the formula
// note - formulas do not preserve text formatting.
FormulaRecordAggregate fr = (FormulaRecordAggregate)_record;
fr.SetCachedStringResult(value.String);
// Update our local cache to the un-formatted version
stringValue = new HSSFRichTextString(value.String);
return;
}
if (cellType != CellType.String)
{
SetCellType(CellType.String, false, row, col, styleIndex);
}
int index = 0;
HSSFRichTextString hvalue = (HSSFRichTextString)value;
UnicodeString str = hvalue.UnicodeString;
index = book.Workbook.AddSSTString(str);
((LabelSSTRecord)_record).SSTIndex = index;
stringValue = hvalue;
stringValue.SetWorkbookReferences(book.Workbook, ((LabelSSTRecord)_record));
stringValue.UnicodeString = book.Workbook.GetSSTString(index);
}
public void DrawString(String str, int x, int y)
{
if (string.IsNullOrEmpty(str))
{
return;
}
using (Font excelFont = new Font(font.Name.Equals("SansSerif") ? "Arial" : font.Name, (int)(font.Size / verticalPixelsPerPoint), font.Style))
{
FontDetails d = StaticFontMetrics.GetFontDetails(excelFont);
int width = (int)((d.GetStringWidth(str) * 8) + 12);
int height = (int)((font.Size / verticalPixelsPerPoint) + 6) * 2;
y -= Convert.ToInt32((font.Size / verticalPixelsPerPoint) + 2 * verticalPixelsPerPoint); // we want to Draw the shape from the top-left
HSSFTextbox textbox = escherGroup.CreateTextbox(new HSSFChildAnchor(x, y, x + width, y + height));
textbox.IsNoFill = (true);
textbox.LineStyle = LineStyle.None;
HSSFRichTextString s = new HSSFRichTextString(str);
HSSFFont hssfFont = MatchFont(excelFont);
s.ApplyFont(hssfFont);
textbox.String = (s);
}
}
/// <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;
_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>
/// 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.Core.SS.UserModel.ISheet s = 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;
if (cell.CellType == Npoi.Core.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>
/// Set a string value for the cell. Please note that if you are using
/// full 16 bit Unicode you should call SetEncoding() first.
/// </summary>
/// <param name="value">value to Set the cell to. For formulas we'll Set the formula
/// string, for String cells we'll Set its value. For other types we will
/// Change the cell to a string cell and Set its value.
/// If value is null then we will Change the cell to a Blank cell.</param>
public void SetCellValue(string value)
{
HSSFRichTextString str = new HSSFRichTextString(value);
SetCellValue(str);
}
/// <summary>
/// Sets the cell type. The SetValue flag indicates whether to bother about
/// trying to preserve the current value in the new record if one is Created.
/// The SetCellValue method will call this method with false in SetValue
/// since it will overWrite the cell value later
/// </summary>
/// <param name="cellType">Type of the cell.</param>
/// <param name="setValue">if set to <c>true</c> [set value].</param>
/// <param name="row">The row.</param>
/// <param name="col">The col.</param>
/// <param name="styleIndex">Index of the style.</param>
private void SetCellType(CellType cellType, bool setValue, int row, int col, short styleIndex)
{
if (cellType > CellType.Error)
{
throw new Exception("I have no idea what type that Is!");
}
switch (cellType)
{
case CellType.Formula:
FormulaRecordAggregate frec = null;
if (cellType != this.cellType)
{
frec = _sheet.Sheet.RowsAggregate.CreateFormula(row, col);
}
else
{
frec = (FormulaRecordAggregate)_record;
}
frec.Column = col;
if (setValue)
{
frec.FormulaRecord.Value = NumericCellValue;
}
frec.XFIndex = styleIndex;
frec.Row = row;
_record = frec;
break;
case CellType.Numeric:
NumberRecord nrec = null;
if (cellType != this.cellType)
{
nrec = new NumberRecord();
}
else
{
nrec = (NumberRecord)_record;
}
nrec.Column = col;
if (setValue)
{
nrec.Value = NumericCellValue;
}
nrec.XFIndex = styleIndex;
nrec.Row = row;
_record = nrec;
break;
case CellType.String:
LabelSSTRecord lrec = null;
if (cellType != this.cellType)
{
lrec = new LabelSSTRecord();
}
else
{
lrec = (LabelSSTRecord)_record;
}
lrec.Column = col;
lrec.Row = row;
lrec.XFIndex = styleIndex;
if (setValue)
{
string str = ConvertCellValueToString();
int sstIndex = book.Workbook.AddSSTString(new UnicodeString(str));
lrec.SSTIndex = (sstIndex);
UnicodeString us = book.Workbook.GetSSTString(sstIndex);
stringValue = new HSSFRichTextString();
stringValue.UnicodeString = us;
}
_record = lrec;
break;
case CellType.Blank:
BlankRecord brec = null;
if (cellType != this.cellType)
{
brec = new BlankRecord();
}
else
{
brec = (BlankRecord)_record;
}
brec.Column = col;
// During construction the cellStyle may be null for a Blank cell.
brec.XFIndex = styleIndex;
brec.Row = row;
_record = brec;
break;
case CellType.Boolean:
BoolErrRecord boolRec = null;
if (cellType != this.cellType)
{
boolRec = new BoolErrRecord();
}
else
{
boolRec = (BoolErrRecord)_record;
}
boolRec.Column = col;
if (setValue)
{
boolRec.SetValue(ConvertCellValueToBoolean());
}
boolRec.XFIndex = styleIndex;
boolRec.Row = row;
_record = boolRec;
break;
case CellType.Error:
BoolErrRecord errRec = null;
if (cellType != this.cellType)
{
errRec = new BoolErrRecord();
}
else
{
errRec = (BoolErrRecord)_record;
}
errRec.Column = col;
if (setValue)
{
errRec.SetValue((byte)HSSFErrorConstants.ERROR_VALUE);
}
errRec.XFIndex = styleIndex;
errRec.Row = row;
_record = errRec;
break;
}
if (cellType != this.cellType &&
this.cellType != CellType.Unknown) // Special Value to indicate an Uninitialized Cell
{
_sheet.Sheet.ReplaceValueRecord(_record);
}
this.cellType = cellType;
}
