How to create an Excel file programmatically (Part 1)

This was a fairly easy and straight-forward job without requiring any additional APIs. Microsoft Office 2003 was pretty forgiving about Excel file extension that any auto-generated Excel file would be easily accepted without much hiccup. Nowadays, with Office 2007 and the upcoming Office 2010, we again are forced to resort to a "few" different Excel-creation techniques which yielded various pros and cons.

1. The should've-been-the-easy-way: (X)HTML+CSS+Excel Formula minus-JavaScript format saved as .xls file.
2. The should've-been-just-as-easy-way: Office 2003 XML format saved as .xls file.
3. The what-we're-now-forced-to-do: Microsoft OpenXML SDK (2.0) way creating a warning-free Excel 2007 file (SDK 2.0 is the most recent library for Office 2010).

I think I know one of the many reasons Microsoft decided to restrict the flexibility later versions of its Office suite—something to do with all those macro/VBA-infected office files or something like that. So they now go by the "locked-down-by-default" starting point. But I also think it's a crying shame that we get all sort of warning dialogs for file extension renaming and things that are already automatically recognized by Microsoft Office suite, anyway. If only there's a way to suppress the dialog from an administrative policy instead of going to each user's PC... If anything, they should make still automatically protect the end-user while still enabling programmers maintain high-productivity by adopting web document standard but without that whole pop-up dialogs everytime so that worker-bee programmers like myself aren't forced to find, re-devise yet another technique to create, and yet again re-wrap a new set of APIs on a new SDK just to do what's supposed to be a simple thing. Do I digress? Perhaps not. We'll see with the aforementioned techniques in the following illustrations.

(X)HTML && CSS || Excel Formula !JavaScript method

This technique is as simple as it could be; there really is no other additional digging required other than what any web programmer already knows. Anyone could manually write the (X)HTML-based Excel file or use his/her favorite programming language to generate the intended file. You be the judge after seeing the code in Listing 1 below.

<!--xml version="1.0" ...?--> <!--DOCTYPE html PUBLIC "... XHTML 1.0 Strict//EN"... --> <html> <head> <title>XHTML-based Excel file</title> <style>/*CSS styling directives here*/</style> </head> <body> <table> <tr> <td>This is Cell A1 in Excel</td> <td>This is Cell B1 in Excel</td> </tr> <tr> <td>123</td> <td>456</td> <td>=A2+B2</td> </tr> </table> </body> </html>

The advantages of this technique are as apparent as the advantages of following a widely-accepted and standardized document object model, especially one that would be readily usable on a web browser. In addition, this would translate well to the natural order of a spreadsheet being a two-dimensional table understood as rows and columns made up of cells. Those as what

<table>, <tr>, <td> and other related (X)HTML elements are for.

The one great disadvantage stemmed from an annoyance of the application (understandably) warning the user that the content format is different from what is understood by Excel when looking at an .xls file extension. And it is more often than not sufficient to put-off a client-centric programmer who cares about NOT scaring his customer as much as he does about programming ease and efficiency.

Will this image: be enough to make us try to think of a new, more "Excel-worthy" technique? I certainly thought so.

Up next (Part 2): Office 2003 XML format approach.

One ID to rule them all!

Having an OpenID is a cool thing. In theory, I wouldn't have needed to register for any other account ever. Alas, most ideals are just that: ideals. No, I'm not saying anything bad against OpenID; I'm only saying that even with the best intention we still frequently are unable to realize the full potential of the idea's ideals.

Take SourceForge.Net for example, it's one of sites I frequented. Sure it does take my OpenID to authenticate me when I logged into the site, but it still requires me to bind to a local SourceForge account if I want to use it to access certain things such as its CVS repositories. C'est la vie.

The OpenID provider I chose for my use is MyOpenID.com. I like MyOpenID.com because it is one of only a few (as of this writing) strong authentication providers who uses SSL certificates and Windows CardSpace/InfoCards which enables auto-authentication. This is so that while I do have a password for my OpenID account, I won't need to really memorize it or use it to sign into any sites using my OpenID account.

To demonstrate:

1. Go to MyOpenID.com
2. Sign in (if you already have an OpenID account)
   
3. Choose a sign-in method
   
   Or
   
4. You'll be presented with a certificate you've previously created and install in your browser
   
   Or be prompted to send your Microsoft Windows InfoCard you've previously linked to your OpenID
   
5. Click sign-in
   
6. And swweeet!! I'm in... quick & easy.

How to write clean website layout using CSS (Part 2)

The pure <div/> approach: Positions

Creating web page layout using CSS positioning really is a very simple thing to do. All we need to remember is that an XHTML element's absolute position is always relative to its container. That is the one thing we usually forget. We are so caught up in the semantic definition of the word absolute position we forget that it isn't absolute only in the context of the web page itself (<body/>).

For a similar simple markup we wrote in Listing 2 of our previous notes, we now have the following Listing 1.

Listing 1: <body id="doc"> <div id="d11" class="d1"> <div id="d11-sub1"> <div id="d11-21" class="d11-2"> </div> <div id="d11-22" class="d11-2"> </div> </div> <div id="d11-sub2"> <div id="d11-23" class="d11-2"> </div> <div id="d11-24" class="d11-2"> </div> </div> </div> <div id="d12" class="d1"> <div id="d12-21" class="d12-2"> </div> <div id="d12-22" class="d12-2"> </div> <div id="d12-23" class="d12-2"> </div> <div id="d12-24" class="d12-2"> </div> <br style="clear: both" /> </div> <br style="clear: both" /> </body>

The styling commands we needed in Listing 2 felt simpler to do and easier to understand from the perspective of having to think about how the browser layout engine will interpret the commands we write.

Listing 2: /* Section: Content specific **********************************************************************/ body, #doc { border: 5px double rgb(255,0,0); width: auto; /* Use // to give a specific instruction to IE browser only **********************************************************************/ //padding-bottom: 100px; } #d11, #d12 { height: 100%; width: 920px; border: 5px solid rgb(0,255,0); margin: 100px; /* Set margin-{left, right} to &quot;auto&quot; to center the div box because * setting text-align to &quot;center&quot; at the container level only works * with IE but not with other browsers, in addition to having the * unintended effect of having the container's text actually centered * that is if we simply just want the layouts centered, not its text. **********************************************************************/ margin-left: auto; margin-right: auto; } div.d11-2 { border: 5px solid rgb(0,0,255); margin: 50px; } #d11-sub1, #d11-sub2 { width: 900px; height: 100%; border: 1px solid red; margin: 10px; position: relative; } #d11-21, #d11-22, #d11-23, #d11-24 { width: 100px; padding: 25px; } #d11-22 { position: absolute; top: 0px; right: 0px; } #d11-24 { position: absolute; top: 0px; right: 0px; } #d12 { border: 5px solid rgb(0,150,50); } div.d12-2 { border: 5px solid rgb(0,0,255); margin: 50px; } #d12-21, #d12-22, #d12-23, #d12-24 { padding: 10px; width: 100px; float: left; } /* End Section **********************************************************************/

I'd say it looks pretty straightforward without having spent lots of hours tweaking the style attributes to get the desired results.

I was pretty happy this turned out to be easy. But then just for kicks I wanted to find out "What if I did the other way around, strictly with floats and kept all the elements' positions at its default relative value?" "Would it be just as simple?" "Would it be just as quick to do?"

The pure <div/> approach: Floats

With the same base structural template as Listing 1 above, I found that achievement the desired result using float method is considerably longer. Sure if you look at the last CSS code-block of Listing 2, you'll see that I actually cheated by using floats because well... that was actually the simplest way to order the <div/>'s in simple column formation. But then, to figure out which of the <div/>'s in square formation need to be floated the right way took quite a bit of trial and error in the order of magnitude of 5. Yes. Five. Listing 2 took me only a couple of minutes to write and get right. Listing 3 took me almost 15 minutes to do due to tinkering with the float and clear attribute combinations.

Listing 3: /* Section: Content specific **********************************************************************/ body, #doc { border: 5px double rgb(255,0,0); //padding-bottom: 0; } #d11, #d12 { width: 920px; border: 5px solid rgb(0,255,0); margin: 100px; /* Set margin-{left, right} to &quot;auto&quot; to center the div box because * setting text-align to &quot;center&quot; at the container level only works * with IE but not with other browsers, in addition to having the * unintended effect of having the container's text actually centered * that is if we simply just want the layouts centered, not its text. **********************************************************************/ margin-left: auto; margin-right: auto; } div.d11-2, div.d12-2 { border: 5px solid rgb(0,0,255); margin: 50px; } #d11-21, #d11-22, #d11-23, #d11-24 { width: 100px; padding: 25px; } #d11-sub1, #d11-sub2 { border: 1px solid red; margin: 10px; //padding-bottom: 50px; } #d11-21, #d11-23 { float: left; clear: left; } #d11-22, #d11-24 { float: right; clear: right; } #d11-23 {clear: left;} #d12 { border: 5px solid rgb(0,150,50); } #d12-21, #d12-22, #d12-23, #d12-24 { padding: 10px; width: 100px; float: left; } #d12-21 {clear: left;} #d12-24 {clear: right;} /* End Section **********************************************************************/

How to write clean website layout using CSS (Part 1)

There are three well-known techniques in creating a website layout using CSS:

1. The <table/> approach.
2. The <table/> + <div/> hybrid approach.
3. The pure <div/> approach.
   1. The positioning approach of absolutists v. relativists; and,
   2. The clearing float (or is it the floating clear? <smile/>).

There are others of course; very advanced CSS masters like to further enhance the cleanliness of their XHTML code without using a single <div/> or <span/>.

As I jumped into refresher exercises, I wondered: "How easily would it be for me to do this (ref: screen)?" Or, "Is it even possible to easily do it for the four popular browsers today?"

---

Warning: At the time of writing, the following materials were only proofed on:

• Internet Explorer 7.0.6001.18000
• Firefox 3.0.6
• Safari 3.2.2 (525.28.1)
• Chrome 1.0.154.48

This article merely documents these variations and choices. We do not advocate one approach versus another as these choices are left to each of our individual circumstances and reasons. Although <grin/> I would soon give up doing the first approach.

The base skeletal structure for each of the tested approaches is the following Listing 1(with slight differences):

Listing 1: <!--xml version="1.0" encoding="UTF-8"?--> <!--DOCTYPE html PUBLIC "-//W3C/DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml1-transitional.dtd"--> <html> <head> <title>DIV</title> <style type="text/css" media="all"> /* Section: Initialization **********************************************************************/ * { margin: 0px auto; padding: 0px; outline: 0 border: 0 none; font-family: cambria, georgia, "Times New Roman", times, serif; font-size: 11pt; font-style: inherit; font-weight: inherit; vertical-align: baseline; text-decoration: none; /* Set min-height to "100%" and overflow to "auto" in order to set * the div box to stretch to the height of its children elements. This * setting bears no effect whatsoever on IE. **********************************************************************/ min-height: 100%; /* Use // to give a specific instruction to IE browser only **********************************************************************/ } } body { padding: 10px; } h1, h2, h3, h4, h5, h6, p, pre, blockquote, form, ul, ol, dl { margin: 20px 0; } ul, ol, dl {list-style: none;} table, tr, td { border-spacing: 0; } a img, :link img, :visited img, { border: 0 none; vertical-align: bottom; } code, .code{ padding: 10px; padding-left: 15px; font-family: consolas, monaco, "Courier New", courier, monospace; font-size: 9pt; color: rgb(100,100,100); white-space: pre; text-align: left; } /* End Section </style> </head> <body> ... </body> </html>

---

The <table/> approach

If we search the Internet for arguments against using <table/> tags for layout purposes, we will find them in abundance. They are all valid arguments and we should heed them. I simply could not bring myself to nest <table/> tags like it's 1999.

The <table/> + <div/> approach

For the following simple layout in Listing 2:

Listing 2: <body> <table> <tr> <td id="t11-21"> <div id="d11-21" class="d11-2"> </div> </td> <td id="t11-22"> <div id="d11-22" class="d11-2"> </div> </td> </tr> <tr> <td id="t11-23"> <div id="d11-23" class="d11-2"> </div> </td> <td id="t11-24"> <div id="d11-24" class="d11-2"> </div> </td> </tr> </table> <table> <tr> <td id="t12-21"> <div id="d12-21" class="d12-2"> </div> </td> <td id="t12-22"> <div id="d12-22" class="d12-2"> </div> </td> <td id="t12-23"> <div id="d12-23" class="d12-2"> </div> </td> <td id="t12-24"> <div id="d12-24" class="d12-2"> </div> </td> </tr> </table> </body>

We still had the following styling code in Listing 3

Listing 3: /* Section: Content specific **********************************************************************/ body, #doc { width: 100%; border: 5px double rgb(255,0,0); //padding-bottom: 100px; } table { width: 948px; border: 5px solid rgb(0,255,0); margin: 100px; /* Set margin-{left, right} to &quot;auto&quot; to center the div box because * setting text-align to &quot;center&quot; at the container level only works * with IE but not with other browsers, in addition to having the * unintended effect of having the container's text actually centered * that is if we simply just want the layouts centered, not its text. **********************************************************************/ margin-left: auto; margin-right: auto; table-layout: fixed; } /* TR styling doesn't work on IE ************************************************************************/ tr { width: 928px; border: 5px solid red; display: block; } td { vertical-align: top; } #t11-21, #t11-23 {width: 100%;} #t11-22, #t11-24 {text-align: right;} #t11-21, #t11-22, #t11-23, #t11-24 { //width: 50%; border: 1px solid blue; } #t12-21, #t12-22, #t12-23, #t12-24 { width: 25%; text-align: center; border: 1px solid blue; } div.d11-2, div.d12-2 { border: 5px solid rgb(0,0,255); margin: 50px; } #d11-21, #d11-22, #d11-23, #d11-24 , #d12-21, #d12-22, #d12-23, #d12-24 { text-align: left; width: 100px; padding: 25px; overflow: visible; } #d12-21, #d12-22, #d12-23, #d12-24 { padding: 10px; //margin-left: 0px; //margin-right: 0px; } /* End Section ************************************************************************/

Yes, this approach yields a slightly cleaner code result than only the <table/> based approach. But it isn't without its own problems. There's that <tr/> rendering issue in IE. And of course, I found that the CSS implementation differences between the various browsers render this approach virtually indefensible, as in saying: "If we have to resort to using various CSS tricks/hacks anyway, why not simply do a Table-less Layout with its pros and cons already weighed by the web design community at large, especially when we already have ample samples written by masters such as those from A LIST apart and Zen Garden—two sources of my favorites reading materials where I found a wealth of knowledge shared to us all common netizens."

Need I say more?

Up next: The pure <div/> approach (Part 2).

How to create a new Blogger template (Part 2)

Blogger's Layout Data Tags

As of this writing, a Blogger's template document requires at least a skin declaration, usually placed in the document header as seen in Listing 1 below.Listing 1: <b:skin><!--[CDATA[ /* Blogger only allows exactly one (1) <b:skin/--> element be defined * in the template document. * Although this is essentially where we place our global CSS code, * we can still declare style blocks or inline style attributes as * usual just like the example below. */ .globalStyleClass { definitions; } ]]></b:skin> <style type="text/css"> .standardstyleclass { definitions; } </style>

For details on why tag is required read this Blogger Help article—that is, if you want to integrate your customized template to Blogger's WYSIWYG layout editor.

That's pretty much it. The rest, we can just drop widget definitions following this Blogger Widget article and regular X/HTML web-design exercises.

The following is the result of this new Blogger template exercise.

Before:

To what we're seeing now.

How to create a new Blogger template (Part 1)

1. Start with a Blogger's basic default template and dissect it.
2. Understand basic X/HTML coding.
3. Understand Blogger's Layouts Data Tags
4. Know where to find help for customizing Blogger's template.

Dissecting a Blogger's basic default template

Leave the first two lines alone.

Listing 1:
<?xml version="1.0" ...?>
<!DOCTYPE html PUBLIC "... XHTML 1.0 Strict//EN"... >

After the first two lines of code, a Blogger's template is essentially just a regular X/HTML document (according to the specified DOCTYPE in code line #2).

Side notes: Personally, I use XHTML 1.0 Transition for reasons we'll experience when we exercise the coding style I wrote in here: How to write clean X/HTML document. For this reason, in the remaining content of this study assumes and follows the transitional document type declaration.

Basic X/HTML coding

An X/HTML document essentially consists of simple structural tags as seen in Listing 2 below.

Listing 2: <html> <head> <style></style> <script></script> </head> <body> </body> </html>

Despite all of the content we see when view an X/HTML document on the web, that's really all there is to it. A Blogger's template is no exception. The some differences we see in the basic template we've downloaded usually has to do with what Blogger labels "Layout Data Tags" or, for those used to using Blogger's WYSIWYG layout editing tool, "Widgets" (regardless of whether or not they're displayed on the resulting browser screen).

In Blogger's document template case, there are extra namespaces declared as attributes of the <html> tag as shown in Listing 3 below.

Listing 3: <html xmlns="http://www.w3.org/1999/xhtml" xmlns:b="http://www.google.com/2005/gml/b" xmlns:data="http://www.google.com/2005/gml/data" xmlns:expr="http://www.google.com/2005/gml/expr" expr:dir="data:blog.languageDirection"> ... </html>

We should do the same.

How to write clean X/HTML document

---

Warning: XMP tag is an officially deprecated tag as of HTML specification 3.02. So follow this advise at your own risk.

---

Use <xmp/> tag instead of the using many <br/> tags to pre-format ourmultiline texts inside a <p> tag. We should use <xmp/> tag for plain text and demonstrated code listings instead of using escaped stuff (entities) such as &lt; and &gt; Escaping characters are such tedious chores and we are all better off with using <xmp/> tag. If we want to have the HTML content of our tags still interpreted as HTML content then we choose to use the <pre/> tag instead of <xmp/> tag (ref: Listing 1) Listing 1: <pre> <b><u>HI THERE!</u></b> XMP is sweet. I don't know why such as useful tag is deprecated. </pre> Listing 1 will still be interpreted as HTML content and render as the following: (notice how the rendered text includes the invisible 'tab' characters). Let's compare it to what we would have to do in Listing 2 below if we don't use <xmp/> Listing 2: &nbsp;&nbsp;&nbsp;&nbsp;<b><u>HI THERE!</u></b><br/> &nbsp;&nbsp;&nbsp;&nbsp;XMP is sweet.<br/> &nbsp;&nbsp;&nbsp;&nbsp;I don't know why such as useful tag is deprecated.<br/> Which codeblock is easier to work with? Just for writing:

	HI THERE!
	XMP is sweet.
	I don't know why such as useful tag is deprecated.

We could alternatively use CSS to control our content like the following: Listing 3: <p style="white-space: pre;"> My multi line content inside a paragraph &lt;p/&gt; tag </p> to render the following:

My multi line content inside a paragraph <p/> tag

Although the sample in Listing 3 takes the white-space character and carriage-return/line-feed character, it does not take the tab character and other invisible characters that may be inside our text content-not to mention more typing compared to using <pre/> tag. If it is still chosen, IE will fault us (if we are reading this using IE, we will see the above example above as a single-line, instead of what it should be). Using the <xmp/> tag and/or <pre/> tag helps us reduce the mess that other developers would see when they view the source of our program. What mess am I talking about? Think about this: How often do we use &nbsp; like the above example? Yep, that's what I'm talking about :) So what if we want to stylize an <xmp/> content?

All we have to do is use a CSS class as we would in any other case:
For example: we could do the following:
Listing 4:
<xmp style="color: blue; font-weight: bold;">Content</xmp>

Such that the result renders as the following: Content We should be careful and test the use of <xmp/> tag carefully since this tag has been officially deprecated as of HTML standard 3.02 and is replaced by the <pre/> tag (see: WARNING at the top of this page), despite the fact that the <pre/> tag is available, it doesn't completely offer all the simple escaping facilities offered by the <xmp/> tag.