public void DrawString(String str, int x, int y)
{
if (str == null || str.Equals(""))
{
return;
}
Font excelFont = font;
if (font.Name.Equals("SansSerif"))
{
excelFont = new Font("Arial", (int)(font.Size / verticalPixelsPerPoint), font.Style);
}
else
{
excelFont = new Font(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 = (HSSFShape.LINESTYLE_NONE);
HSSFRichTextString s = new HSSFRichTextString(str);
HSSFFont hssfFont = MatchFont(excelFont);
s.ApplyFont(hssfFont);
textbox.String = (s);
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("ICell comments in POI HSSF");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new HSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new HSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
HSSFComment comment2 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
comment2.SetFillColor(204, 236, 255);
HSSFRichTextString str = new HSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = hssfworkbook.CreateFont();
font.FontName = ("Arial");
font.FontHeightInPoints =10;
font.Boldweight = (short)FontBoldWeight.BOLD;
font.Color = HSSFColor.RED.index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
WriteToFile();
}
/// <summary>
/// Adds the member.
/// </summary>
/// <param name="factory">The factory.</param>
/// <param name="ticket">The ticket.</param>
/// <param name="ticketHelper">The ticket helper.</param>
/// <returns>BetListExcelBuilder object.</returns>
public static BetListExcelBuilder AddUsername(this BetListExcelBuilder factory, ITicket ticket, ITicketHelper ticketHelper)
{
IRichTextString rtfMember = new HSSFRichTextString(ticket.UserName);
factory.AddCell(rtfMember, ticketHelper.Index % 2 == 0 ? factory.OddCellStyleCenterAligned : factory.EvenCellStyleCenterAligned);
return factory;
}
public void TestClearFormatting()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
r.ClearFormatting();
Assert.AreEqual(0, r.NumFormattingRuns);
}
public virtual IRichTextString RenderRTF(ITicket ticket, ITicketHelper ticketHelper)
{
Render(ticket);
string refNo = Template.TransTime.refNo;
string transTime = string.Join(null, new string[] { CoreBetList.refno, ": ", refNo });
IRichTextString rtfTrans = new HSSFRichTextString(string.Join(null, new string[] { transTime, "\n", Template.transTime }));
return rtfTrans;
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.RED.index;
font1.IsItalic = true;
font1.Underline = (byte)FontUnderlineType.DOUBLE;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.OLIVE_GREEN.index;
font2.IsStrikeout=true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
cell1.CellStyle = style1;
ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好！");
cell2.CellStyle = style2;
//cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = hssfworkbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3=hssfworkbook.CreateFont();
font3.TypeOffset = (short)FontSuperScript.SUPER;
font3.IsItalic = true;
font3.Color = HSSFColor.BLUE.index;
font3.FontHeightInPoints=8;
richtext.ApplyFont(16, 18,font3);
cell3.SetCellValue(richtext);
WriteToFile();
}
/// <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;
this._sheet = sheet;
short xfindex = sheet.Sheet.GetXFIndexForColAt(col);
SetCellType(type, false, row, col, xfindex);
}
public void TestAlignment()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet();
HSSFPatriarch patriarch = sh1.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 6, 4)) as HSSFTextbox;
HSSFRichTextString str = new HSSFRichTextString("Hello, World");
textbox.String = (str);
textbox.HorizontalAlignment = (HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED);
textbox.VerticalAlignment = (HSSFTextbox.VERTICAL_ALIGNMENT_CENTER);
Assert.AreEqual(HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED, textbox.HorizontalAlignment);
Assert.AreEqual(HSSFTextbox.VERTICAL_ALIGNMENT_CENTER, textbox.VerticalAlignment);
}
public void TestSetDeafultTextFormat()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox1 = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 3)) as HSSFTextbox;
HSSFRichTextString rt1 = new HSSFRichTextString("Hello, World!");
Assert.AreEqual(0, rt1.NumFormattingRuns);
textbox1.String=(rt1);
HSSFRichTextString rt2 = (HSSFRichTextString)textbox1.String;
Assert.AreEqual(1, rt2.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, rt2.GetFontOfFormattingRun(0));
}
public void TestAlignment()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet();
HSSFPatriarch patriarch = sh1.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 6, 4)) as HSSFTextbox;
HSSFRichTextString str = new HSSFRichTextString("Hello, World");
textbox.String = (str);
textbox.HorizontalAlignment = HorizontalAlignment.Center;
textbox.VerticalAlignment = VerticalAlignment.Center;
Assert.AreEqual(HorizontalAlignment.Center, textbox.HorizontalAlignment);
Assert.AreEqual(VerticalAlignment.Center, textbox.VerticalAlignment);
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = HorizontalTextAlignment.Center;
record.VerticalTextAlignment = VerticalTextAlignment.Justify;
record.IsTextLocked = (true);
record.TextOrientation = TextOrientation.RotRight;
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_CENTERED);
record.VerticalTextAlignment = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_JUSTIFY);
record.IsTextLocked = (true);
record.TextOrientation = (TextObjectRecord.TEXT_ORIENTATION_ROT_RIGHT);
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
/// <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.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 TestApplyFont()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(6));
r.ApplyFont(6, 7, new HSSFFont((short)2, null));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(2, r.GetFontAtIndex(6));
r.ApplyFont(HSSFRichTextString.NO_FONT);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(2));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
r.ApplyFont(new HSSFFont((short)1, null));
Assert.AreEqual(1, r.GetFontAtIndex(0));
Assert.AreEqual(1, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(1, r.GetFontAtIndex(4));
Assert.AreEqual(1, r.GetFontAtIndex(5));
Assert.AreEqual(1, r.GetFontAtIndex(6));
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/str);
record.HorizontalTextAlignment = (/*setter*/ TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_LEFT_ALIGNED);
record.VerticalTextAlignment = (/*setter*/ TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_TOP);
record.IsTextLocked = (/*setter*/ true);
record.TextOrientation = (/*setter*/ TextObjectRecord.TEXT_ORIENTATION_NONE);
byte[] ser = record.Serialize();
Assert.AreEqual(ser.Length, simpleData.Length);
Assert.IsTrue(Arrays.Equals(simpleData, ser));
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(simpleData);
record = new TextObjectRecord(is1);
}
public void TestWrite()
{
HSSFRichTextString str = new HSSFRichTextString("Hello, World!");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/str);
record.HorizontalTextAlignment = (/*setter*/ HorizontalTextAlignment.Left);
record.VerticalTextAlignment = (/*setter*/ VerticalTextAlignment.Top);
record.IsTextLocked = (/*setter*/ true);
record.TextOrientation = (/*setter*/ TextOrientation.None);
byte[] ser = record.Serialize();
Assert.AreEqual(ser.Length, simpleData.Length);
Assert.IsTrue(Arrays.Equals(simpleData, ser));
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(simpleData);
record = new TextObjectRecord(is1);
}
public void DrawString(String str, int x, int y)
{
if (string.IsNullOrEmpty(str))
{
return;
}
// TODO-Fonts: Fallback for missing font
Font excelFont = new Font(SystemFonts.Get(font.Name.Equals("SansSerif") ? "Arial" : font.Name),
(int)(font.Size / verticalPixelsPerPoint), font.FontMetrics.Description.Style);
{
int width = (int)((TextMeasurer.Measure(str, new TextOptions(excelFont)).Width * 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;
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>
/// Renders this instance.
/// </summary>
/// <returns>IRichTextString object.</returns>
public IRichTextString Render()
{
string s = string.Empty;
foreach (var block in _blocks)
{
s += block.Text;
}
IRichTextString result = new HSSFRichTextString(s);
int textIndex = 0;
foreach (var block in _blocks)
{
IFont font = _font;
if (font == null)
{
font = block.Font;
}
else
{
if (block.Font != null)
{
font = block.Font;
}
}
if (font != null)
{
int endIndex = textIndex + block.Text.Length;
result.ApplyFont(textIndex, endIndex, font);
}
textIndex += block.Text.Length;
}
return result;
}
/// <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;
}
/// <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);
}
public void TestWriteEmpty()
{
HSSFRichTextString str = new HSSFRichTextString("");
TextObjectRecord record = new TextObjectRecord();
record.Str = (/*setter*/str);
byte[] ser = record.Serialize();
int formatDataLen = LittleEndian.GetUShort(ser, 16);
Assert.AreEqual(0, formatDataLen, "formatDataLength");
Assert.AreEqual(22, ser.Length); // just the TXO record
//read again
RecordInputStream is1 = TestcaseRecordInputStream.Create(ser);
record = new TextObjectRecord(is1);
Assert.AreEqual(0, record.Str.Length);
}
public void TestModify()
{
HSSFWorkbook wb = new HSSFWorkbook();
// create a sheet with a text box
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group1 = patriarch.CreateGroup(new
HSSFClientAnchor(0, 0, 0, 0,
(short)0, 0, (short)15, 25));
group1.SetCoordinates(0, 0, 792, 612);
HSSFTextbox textbox1 = group1.CreateTextbox(new
HSSFChildAnchor(100, 100, 300, 300));
HSSFRichTextString rt1 = new HSSFRichTextString("Hello, World!");
textbox1.String = rt1;
// Write, read back and check that our text box is there
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, patriarch.Children.Count);
group1 = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(1, group1.Children.Count);
textbox1 = (HSSFTextbox)group1.Children[(0)];
Assert.AreEqual("Hello, World!", textbox1.String.String);
// modify anchor
Assert.AreEqual(new HSSFChildAnchor(100, 100, 300, 300),
textbox1.Anchor);
HSSFChildAnchor newAnchor = new HSSFChildAnchor(200, 200, 400, 400);
textbox1.Anchor = newAnchor;
// modify text
textbox1.String = new HSSFRichTextString("Hello, World! (modified)");
// add a new text box
HSSFTextbox textbox2 = group1.CreateTextbox(new
HSSFChildAnchor(400, 400, 600, 600));
HSSFRichTextString rt2 = new HSSFRichTextString("Hello, World-2");
textbox2.String = rt2;
Assert.AreEqual(2, group1.Children.Count);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, patriarch.Children.Count);
group1 = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(2, group1.Children.Count);
textbox1 = (HSSFTextbox)group1.Children[(0)];
Assert.AreEqual("Hello, World! (modified)",
textbox1.String.String);
Assert.AreEqual(new HSSFChildAnchor(200, 200, 400, 400),
textbox1.Anchor);
textbox2 = (HSSFTextbox)group1.Children[(1)];
Assert.AreEqual("Hello, World-2", textbox2.String.String);
Assert.AreEqual(new HSSFChildAnchor(400, 400, 600, 600),
textbox2.Anchor);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
group1 = (HSSFShapeGroup)patriarch.Children[(0)];
textbox1 = (HSSFTextbox)group1.Children[(0)];
textbox2 = (HSSFTextbox)group1.Children[(1)];
HSSFTextbox textbox3 = group1.CreateTextbox(new
HSSFChildAnchor(400, 200, 600, 400));
HSSFRichTextString rt3 = new HSSFRichTextString("Hello, World-3");
textbox3.String = rt3;
}
/// <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>
/// 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(HSSFRichTextString value)
{
int row = record.Row;
int col = record.Column;
short styleIndex = record.XFIndex;
if (value == null)
{
SetCellType(CELL_TYPE_BLANK, false, row, col, styleIndex);
return;
}
if (cellType == CELL_TYPE_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 != CELL_TYPE_STRING)
{
SetCellType(CELL_TYPE_STRING, false, row, col, styleIndex);
}
int index = 0;
UnicodeString str = value.UnicodeString;
index = book.Workbook.AddSSTString(str);
((LabelSSTRecord)record).SSTIndex = index;
stringValue = value;
stringValue.SetWorkbookReferences(book.Workbook, ((LabelSSTRecord)record));
stringValue.UnicodeString = book.Workbook.GetSSTString(index);
}
public void DrawString(String str, int x, int y)
{
if (str == null || str.Equals(""))
return;
Font excelFont = font;
if (font.Name.Equals("SansSerif"))
{
excelFont = new Font("Arial", (int)(font.Size / verticalPixelsPerPoint), font.Style);
}
else
{
excelFont = new Font(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 = (HSSFShape.LINESTYLE_NONE);
HSSFRichTextString s = new HSSFRichTextString(str);
HSSFFont hssfFont = MatchFont(excelFont);
s.ApplyFont(hssfFont);
textbox.String = (s);
}
/// <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;
}
/// <summary>
/// Creates an excel comment in each cell with an associated error
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="sheet"></param>
private void HighlightErrors(ISheet excelSheet, ICOBieSheet<COBieRow> sheet)
{
//sort by row then column
var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);
// The patriarch is a container for comments on a sheet
IDrawing patr = excelSheet.CreateDrawingPatriarch();
int sheetCommnetCount = 0;
foreach (var error in errors)
{
if (error.Row > 0 && error.Column >= 0)
{
if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
//limit comments to 1000 per sheet
if (sheetCommnetCount == 999)
break;
IRow excelRow = excelSheet.GetRow(error.Row);
if (excelRow != null)
{
ICell excelCell = excelRow.GetCell(error.Column);
if (excelCell != null)
{
string description = error.ErrorDescription;
if(hasErrorLevel)
{
if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
description = "Warning: " + description;
else
description = "Error: " + description;
}
if (excelCell.CellComment == null)
{
try
{
// A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
// Create a comment 3 columns wide and 3 rows height
IClientAnchor anchor = null;
if (IsXlsx)
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
else
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
IComment comment = patr.CreateCellComment(anchor);
IRichTextString str = null;
if (IsXlsx)
{
str = new XSSFRichTextString(description);
}
else
{
str = new HSSFRichTextString(description);
}
comment.String = str;
comment.Author = "XBim";
excelCell.CellComment = comment;
_commentCount++;
sheetCommnetCount++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
if (IsXlsx)
{
((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
}
else
{
description = excelCell.CellComment.String.ToString() + " Also " + description;
excelCell.CellComment.String = new HSSFRichTextString(description);
}
}
}
}
}
}
}
/// <summary>
/// Créer une Cellule avec un Style en paramètre
/// </summary>
/// <param name="_sheet"></param>
/// <param name="_cellStyle"></param>
/// <param name="_numCol"></param>
/// <param name="_numRow"></param>
/// <param name="_text"></param>
public void createCellRichText(HSSFRow _row, HSSFCellStyle _cellStyle, int _numCol, string _text)
{
NPOI.SS.UserModel.ICell _newCell = _row.CreateCell(_numCol);
HSSFRichTextString richString = new HSSFRichTextString(_text);
_newCell.SetCellValue(richString);
_cellStyle.WrapText = true;
_newCell.CellStyle = _cellStyle;
}
public void SetText(CellPos pos, string text)
{
List<TextPosition> positions = new List<TextPosition>();
while (text.IndexOf("<b>") > -1)
{
int start = text.IndexOf("<b>");
text = text.Substring(0, start) + text.Substring(start + 3);
int end = text.IndexOf("</b>");
text = text.Substring(0, end) + text.Substring(end + 4);
positions.Add(new TextPosition { start = start, end = end });
}
HSSFRichTextString richtext = new HSSFRichTextString(text);
IFont font1 = _parent.Internal.CreateFont();
font1.Boldweight = (short)FontBoldWeight.Bold;
foreach (TextPosition position in positions)
{
richtext.ApplyFont(position.start, position.end, font1);
}
ICell cell = GetCell(pos, true);
cell.SetCellValue(richtext);
}
/// <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>
/// 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)
{
HSSFRichTextString hvalue = (HSSFRichTextString)value;
int row = record.Row;
int col = record.Column;
short styleIndex = record.XFIndex;
if (hvalue == null)
{
NotifyFormulaChanging();
SetCellType(CellType.BLANK, false, row, col, styleIndex);
return;
}
if (hvalue.Length > NPOI.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;
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);
}
/// <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;
this.sheet = sheet;
short xfindex = sheet.Sheet.GetXFIndexForColAt(col);
SetCellType(type, false, row, col, xfindex);
}
public void TestLongRecords()
{
int[] length = { 1024, 2048, 4096, 8192, 16384 }; //test against strings of different length
for (int i = 0; i < length.Length; i++)
{
StringBuilder buff = new StringBuilder(length[i]);
for (int j = 0; j < length[i]; j++)
{
buff.Append("x");
}
IRichTextString str = new HSSFRichTextString(buff.ToString());
TextObjectRecord obj = new TextObjectRecord();
obj.Str = (/*setter*/str);
byte[] data = obj.Serialize();
RecordInputStream is1 = new RecordInputStream(new MemoryStream(data));
is1.NextRecord();
TextObjectRecord record = new TextObjectRecord(is1);
str = record.Str;
Assert.AreEqual(buff.Length, str.Length);
Assert.AreEqual(buff.ToString(), str.String);
}
}
public void TestClone()
{
String text = "Hello, World";
HSSFRichTextString str = new HSSFRichTextString(text);
TextObjectRecord obj = new TextObjectRecord();
obj.Str = (/*setter*/ str);
TextObjectRecord Cloned = (TextObjectRecord)obj.Clone();
Assert.AreEqual(obj.RecordSize, Cloned.RecordSize);
Assert.AreEqual(obj.HorizontalTextAlignment, Cloned.HorizontalTextAlignment);
Assert.AreEqual(obj.Str.String, Cloned.Str.String);
//finally check that the Serialized data is the same
byte[] src = obj.Serialize();
byte[] cln = Cloned.Serialize();
Assert.IsTrue(Arrays.Equals(src, cln));
}
/// <summary>
/// Creates new Cell - Should only be called by HSSFRow. This Creates a cell
/// from scratch.
/// When the cell Is initially Created it Is Set to CELL_TYPE_BLANK. Cell types
/// can be Changed/overwritten by calling SetCellValue with the appropriate
/// type as a parameter although conversions from one type to another may be
/// prohibited.
/// </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>
public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col)
{
CheckBounds(col);
stringValue = null;
this.book = book;
this.sheet = sheet;
// Relying on the fact that by default the cellType Is Set to 0 which
// Is different to CELL_TYPE_BLANK hence the following method call correctly
// Creates a new blank cell.
short xfindex = sheet.Sheet.GetXFIndexForColAt(col);
SetCellType(CELL_TYPE_BLANK, false, row, col, xfindex);
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(4));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(6));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(7));
}
/// <summary>
/// Compares one rich text string to another.
/// </summary>
/// <param name="o">The o.</param>
/// <returns></returns>
public int CompareTo(Object o)
{
HSSFRichTextString r = (HSSFRichTextString)o;
return(str.CompareTo(r.String));
}
