Text boxes

xlsgen > overview > Text boxes

Text boxes are a convenient method to annotate spreadsheets. There are two types of text boxes.

Floating text boxes. Part of the grid.
Chart text boxes. Part of a chart. Moves with the chart. Explained here.

Text boxes in xlsgen

Text boxes support a number of features :

regular label or rich label
hyperlink
auto-sizing the text box around the content
formula to govern the content of the text box
alternative text
formatting style
picture extraction (PNG file format)

Text boxes can also be :

enumerated
introspected
deleted
rendered (while rendering a sheet)

Adding text boxes

Text boxes are exposed using the IXlsTextBox interface. One particular detail is how text box are positioned in the grid.

Below is a drawing explaining how it works :

Positioning a text box in the Excel grid

Text boxes can be positioned in the grid either using this anchor model (NewTextBox), or a pixel coordinate system (NewTextBoxInPixels).

Java code


XlsWorkbook wbk = engine.New( "textbox.xls");

XlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );

// text box using a formula and a hyperlink
XlsTextBox textbox1 = wksht.NewTextBox(2,3, 5,6, 0,200, 80,300);
textbox1.putFormula("=Sheet1!$B$2");
textbox1.putAlternativeText("floating text box alternative text");
XlsHyperlink hlink = wksht.NewHyperlink();
hlink.putCellAnchor("Sheet1", 5, 1);
hlink.putCaption("some caption");
textbox1.putHyperlink(hlink);

// text box using a formatting style
XlsTextBox textbox2 = wksht.NewTextBox(6,3, 9,6, 0,200, 80,300);
textbox2.putLabel("abcd");
XlsStyle style = wksht.NewStyle();
style.getAlignment().putHorizontal(xlsgen.halign_center);
style.getAlignment().putVertical(xlsgen.valign_bottom);
textbox2.getPatterns().getBorders().putType(xlsgen.chartbordertype_none);
textbox2.getPatterns().getArea().getPattern().putBackgroundColor(0x0000FF88);
textbox2.putStyle(style);

wbk.Close();

VB code


IXlsWorkbook wbk = engine.New( "textbox.xls")

IXlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" )

' text box using a formula and a hyperlink
IXlsTextBox textbox1 = wksht.NewTextBox(2,3, 5,6, 0,200, 80,300)
textbox1.Formula = "=Sheet1!$B$2"
textbox1.AlternativeText = "floating text box alternative text"
IXlsHyperlink hlink = wksht.NewHyperlink
hlink.CellAnchor("Sheet1", 5, 1)
hlink.Caption = "some caption"
textbox1.Hyperlink = hlink

' text box using a formatting style
IXlsTextBox textbox2 = wksht.NewTextBox(6,3, 9,6, 0,200, 80,300)
textbox2.Label = "abcd"
IXlsStyle style = wksht.NewStyle
style.Alignment.Horizontal = enumHorizontalAlignment.halign_center
style.Alignment.Vertical = enumVerticalAlignment.valign_bottom
textbox2.Patterns.Borders.Type = enumChartBorder.chartbordertype_none
textbox2.Patterns.Area.Pattern.BackgroundColor = &H0000FF88
textbox2.Style = style

wbk.Close

C# code


IXlsWorkbook wbk = engine.New( "textbox.xls");

IXlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );

// text box using a formula and a hyperlink
IXlsTextBox textbox1 = wksht.NewTextBox(2,3, 5,6, 0,200, 80,300);
textbox1.Formula = "=Sheet1!$B$2";
textbox1.AlternativeText = "floating text box alternative text";
IXlsHyperlink hlink = wksht.NewHyperlink();
hlink.CellAnchor("Sheet1", 5, 1);
hlink.Caption = "some caption";
textbox1.Hyperlink = hlink;

// text box using a formatting style
IXlsTextBox textbox2 = wksht.NewTextBox(6,3, 9,6, 0,200, 80,300);
textbox2.Label = "abcd";
IXlsStyle style = wksht.NewStyle();
style.Alignment.Horizontal = enumHorizontalAlignment.halign_center;
style.Alignment.Vertical = enumVerticalAlignment.valign_bottom;
textbox2.Patterns.Borders.Type = enumChartBorder.chartbordertype_none;
textbox2.Patterns.Area.Pattern.BackgroundColor = 0x0000FF88;
textbox2.Style = style;

wbk.Close();

C/C++ code


xlsgen::IXlsWorkbookPtr wbk = engine->New( L"textbox.xls");

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"Sheet1" );

// text box using a formula and a hyperlink
xlsgen::IXlsTextBoxPtr textbox1 = wksht->NewTextBox(2,3, 5,6, 0,200, 80,300);
textbox1->Formula = L"=Sheet1!$B$2";
textbox1->AlternativeText = L"floating text box alternative text";
xlsgen::IXlsHyperlinkPtr hlink = wksht->NewHyperlink();
hlink->CellAnchor(L"Sheet1", 5, 1);
hlink->Caption = L"some caption";
textbox1->Hyperlink = hlink;

// text box using a formatting style
xlsgen::IXlsTextBoxPtr textbox2 = wksht->NewTextBox(6,3, 9,6, 0,200, 80,300);
textbox2->Label = L"abcd";
xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Alignment->Horizontal = xlsgen::halign_center;
style->Alignment->Vertical = xlsgen::valign_bottom;
textbox2->Patterns->Borders->Type = xlsgen::chartbordertype_none;
textbox2->Patterns->Area->Pattern->BackgroundColor = 0x0000FF88;
textbox2->Style = style;

wbk->Close();

Managing text boxes

xlsgen exposes the collection of text boxes in each worksheet. Text boxes can be enumerated by index. And then each text box can be introspected and even deleted.

accessing text boxes : worksheet.TextBoxes. The IXlsTextBoxes interface is documented.
accessing one text box : worksheet.TextBoxes.Item(i)
introspecting a property of a text box : worksheet.TextBoxes.Item(i).Label
deleting a text box : worksheet.TextBoxes.Item(i).Delete()