xlsgen > overview > Html cells

Adding formatting to cells is quite tedious as it involves learning an object model and every single of the 30+ properties of a cell's formatting takes a line of code to add.

Of course, you can cut the time spent by simply doing formattings by hand in Excel, and then use the automatic source code generator which is part of xlsgen, as it spits out the corresponding source code in the programming language of your choice.

That is all fine, but the fact remains there is still something new and specific to learn, even though formatting cells is not rocket science and thus should follow some formatting standard.

Can you guess a programming language which deals with this kind of stuff?

HTML, of course!

So what if xlsgen exposed the ability to read and write cells with just Html markup ? Would not that make it possible for web developers to use xlsgen without having to learn much?.

Html strings is a feature that lets you read and write numbers, strings, dates and formulas using Html markup. It's a very concise and convenient model. Here are a couple examples.

Java code
worksheet.getCell(4,1).putHtmlLabel("<b>bold<i> and<br>italic</i> text</b>stop");

worksheet.getCell(7,1).putHtmlLabel("<font orientation=32 color=\"00FF00\">text</font>);

worksheet.getCell(12,1).putHtmlLabel("<font style=\"background-color : #FFCCAA;fill-pattern:darkHorizontal\">text</font>);

worksheet.getCell(2,2).putHtmlFloat("<font style=\"border-width:dashed;border-color: #FFCCAA\">-3.14159265361</font>);

worksheet.getCell(3,2).putHtmlDate("<font style=\"border-bottom-width:double;border-bottom-color: black\" format=\"dddd/mm/yyyy\" autofit=true>11 Jan 2007</font>);

worksheet.getCell(4,2).putHtmlNumber("<font format=\"# ##0.000\" style=\"background-color:DarkSalmon\">1</font>);

worksheet.getCell(6,2).putHtmlFormula("<font style=\"background-color:GreenYellow\">=SUM(B4:B5)</font>");
VB code
worksheet.Cell(4,1).HtmlLabel = "<b>bold<i> and<br>italic</i> text</b>stop";

worksheet.Cell(7,1).HtmlLabel = "<font orientation=32 color=""00FF00"">text</font>";

worksheet.Cell(12,1).HtmlLabel= "<font style=""background-color : #FFCCAA;fill-pattern:darkHorizontal"">text</font>";

worksheet.Cell(2,2).HtmlFloat = "<font style=""border-width:dashed;border-color: #FFCCAA"">-3.14159265361</font>";

worksheet.Cell(3,2).HtmlDate = "<font style=""border-bottom-width:double;border-bottom-color: black"" format=""dddd/mm/yyyy"" autofit=true>11 Jan 2007</font>";

worksheet.Cell(4,2).HtmlNumber = "<font format=""# ##0.000"" style=""background-color:DarkSalmon"">1</font>";

worksheet.Cell(6,2).HtmlFormula = "<font style=""background-color:GreenYellow"">=SUM(B4:B5)</font>";
C# code
worksheet.get_Cell(4,1).HtmlLabel = "<b>bold<i> and<br>italic</i> text</b>stop";

worksheet.get_Cell(7,1).HtmlLabel = "<font orientation=32 color=\"00FF00\">text</font>";

worksheet.get_Cell(12,1).HtmlLabel= "<font style=\"background-color : #FFCCAA;fill-pattern:darkHorizontal\">text</font>";

worksheet.get_Cell(2,2).HtmlFloat = "<font style=\"border-width:dashed;border-color: #FFCCAA\">-3.14159265361</font>";

worksheet.get_Cell(3,2).HtmlDate = "<font style=\"border-bottom-width:double;border-bottom-color: black\" format=\"dddd/mm/yyyy\" autofit=true>11 Jan 2007</font>";

worksheet.get_Cell(4,2).HtmlNumber = "<font format=\"# ##0.000\" style=\"background-color:DarkSalmon\">1</font>";

worksheet.get_Cell(6,2).HtmlFormula = "<font style=\"background-color:GreenYellow\">=SUM(B4:B5)</font>";
C++ code
worksheet->Cell[4][1]->HtmlLabel = L"<b>bold<i> and<br>italic</i> text</b>stop";

worksheet->Cell[7][1]->HtmlLabel = L"<font orientation=32 color=\"00FF00\">text</font>";

worksheet->Cell[12][1]->HtmlLabel= L"<font style=\"background-color : #FFCCAA;fill-pattern:darkHorizontal\">text</font>";

worksheet->Cell[2][2]->HtmlFloat = L"<font style=\"border-width:dashed;border-color: #FFCCAA\">-3.14159265361</font>";

worksheet->Cell[3][2]->HtmlDate = L"<font style=\"border-bottom-width:double;border-bottom-color: black\" format=\"dddd/mm/yyyy\" autofit=true>11 Jan 2007</font>";

worksheet->Cell[4][2]->HtmlNumber = L"<font format=\"# ##0.000\" style=\"background-color:DarkSalmon\">1</font>";

worksheet->Cell[6][2]->HtmlFormula = L"<font style=\"background-color:GreenYellow\">=SUM(B4:B5)</font>";

Here is an example of screen capture of cells resulting from statements like this :


Html cells

 

Programming

Read/write methods are available from the IXlsCell interface. The IXlsCell interface is available from the IXlsWorksheet interface.

Methods include reading and writing litterals such as numbers, floats, dates and strings.

There are also methods to read and write formulas. It's a convenient way to read or create formulas complete with their associated formatting. Of course, just like regular formula creation, the formula language is English by default, and you can set a different formula language. See formulas for more info.

If you are not sure about the Html markup, take a look at the reference sheet below, also you can always use the source code generator which creates source code that, depends on your choice, uses the object model interfaces, or plain Html.

 

Reference

TagAttributeAttribute value
B  
I  
U  
STRIKE  
SUP  
SUB  
H1  
H2  
H3  
H4  
H5  
FONTcolor24-bit RGB color such as #FF0000, or any the known html color names (red, black, ...)
FONTsizefont height in pixels
FONTnamefont name
FONTorientationAny orientation angle in degrees, or "vertical" for a text with vertical layout.
FONTindentinteger value.
FONTshrinktofittrue or false
FONTalignhorizontal alignment
general
left
center
right
justify
centerselection
FONTvalignvertical alignment
general
top
center
bottom
justify
FONTrtlright-to-left boolean : true or false
FONTformatstring representing a number format
FONTwidthwidth of cell in pixels
FONTheightheight of cell in pixels
FONTautofittrue to autofit the column
FONTlockedtrue or false
FONThiddentrue or false
FONTunderlinenone
single
double
singleaccounting
doubleaccounting
Any tagstylebackground-color: color
Any tagstyleforeground-color: color
Any tagstylefill-pattern:
none
solid
darkGray
mediumGray
lightGray
gray125
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis
Any tagstylefill-gradient:
gradientstyle_horizontal0
gradientstyle_horizontal1
gradientstyle_horizontal2
gradientstyle_horizontal3
gradientstyle_verticall0
gradientstyle_verticall1
gradientstyle_verticall2
gradientstyle_verticall3
gradientstyle_diagonalUp0
gradientstyle_diagonalUp1
gradientstyle_diagonalUp2
gradientstyle_diagonalUp3
gradientstyle_diagonalDown0
gradientstyle_diagonalDown1
gradientstyle_diagonalDown2
gradientstyle_diagonalDown3
gradientstyle_fromCorner0
gradientstyle_fromCorner1
gradientstyle_fromCorner2
gradientstyle_fromCorner3
gradientstyle_fromCenter0
gradientstyle_fromCenter1
Any tagstylefill-gradient-color1: first color of the gradient fill
Any tagstylefill-gradient-color2: second color of the gradient fill
Any tagstyleborder-color: color of all 4 borders
Any tagstyleborder-width: style of all 4 borders
Any tagstyleborder-top-color: color
Any tagstyleborder-top-width:
none
thin
medium
dashed
dotted
thick
double
hair
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
Any tagstyleborder-bottom-color: color
Any tagstyleborder-bottom-width: see border-top-width
Any tagstyleborder-left-color: color
Any tagstyleborder-left-width: see border-top-width
Any tagstyleborder-right-color: color
Any tagstyleborder-right-width: see border-top-width
Any tagstyleborder-diagonalup-color: color
Any tagstyleborder-diagonalup-width: see border-top-width
Any tagstyleborder-diagonaldown-color: color
Any tagstyleborder-diagonaldown-width: see border-top-width
Any tagformatnumber format (can be used with numbers and dates)
Any tagwraptrue or false
BR line break

 

xlsgen documentation. © ARsT Design all rights reserved.