Creating a Dynamic Excel Using HTML

Kaushik Borah
10y
128.8k
0
0
100
Article

Introduction

The focus of the article is to show the developers how to generate a dynamic Excel sheet from HTML. This article includes some sample programs to show how to implement various Excel functionalities with the help of HTML. The prerequisite of these programs is, you should have Microsoft Office Excel installed in your PC. This article expects you to have the basic concepts HTML and Excel programming for better understanding.

Before starting you need to know about Namespaces/Uniform Resource Name and Meta Tag used for Microsoft Office Excel Application.

Meta Tag

HTML includes the meta element that goes inside the head element. The purpose of the meta element is to provide meta-information about the document. To generate an Excel sheet in HTML, the following meta information should be included under the Header Section:

Here is an example to generate a simple Excel sheet:

<html >

<head>

</head>

</html>

Namespace/Uniform Resource Name

In the above section we see how to generate an Excel sheet. Here we will see how to define Office objects. There is no specific HTML tag for Microsoft Office Objects, we need to use custom tags to define Office and Microsoft Excel Objects. Custom tags should have a unique Namespace or Uniform Resource Name. The following code snippet shows the required xmlns [XML Namespace] for Office and Excel:

</html>.

We will see more detailed usage of the namespace in the subsequent sections.

Custom Document Property

The Custom document property allows you to define your own property. In the following example we will see how to define Office custom properties in HTML:

<head>

<title>Document and Custom Property </title>

<!--[if gte mso 9]><xml>

<o:CustomDocumentProperties>

<o:BUSINESSGROUP dt:dt="string">CHANNELA</o:BUSINESSGROUP>

<o:BUSINESSGROUPID dt:dt="string">2</o:BUSINESSGROUPID>

</o:CustomDocumentProperties>

</xml><![endif]-->

</head>

</html>

If you look closely at the preceding code you will notice an IF block like the following:

<!--[if gte mso 9]>

<![endif]-->

This piece of code is checking the availability of Microsoft Office. It is good practice to add this piece of code before defining a property. If you save the preceding code as HTML and open as Excel then it will have the following custom property:

Figure 1:

Data Validation

Special custom tags are also available for data validation.

For example: The following tags are used to represent various properties of data validation.

<x:DataValidation>

<x:Range></x:Range>

<x:Type> </x:Type>

<x:Qualifier> </x:Qualifier>

<x:Value></x:Value>

<x:ErrorMessage> </x:ErrorMessage>

<x:ErrorTitle> </x:ErrorTitle>

</x:DataValidation>

The following table explains the preceding tags:

Tags	Descriptions
<x:Range></x:Range>	Defines the range where validation is applied.
<x:Type> </x:Type>	Tells about Validation Type like Whole, Decimal, TextLength,Custom ect
<x:Qualifier> </x:Qualifier>	Defines comparison parameters like Greater, Equal, NotEqual, Less, GreaterOrEqual, LessOrEqual ect
<x:Value></x:Value>	Defines an expression or value for validation
<x:ErrorMessage> </x:ErrorMessage>	Defines customized Error Message.
<x:ErrorTitle></x:ErrorTitle>	Title of the error message.

The following example will help you to understand the above syntax better:

<html xmlns:v="urn:schemas-microsoft-com:vml"xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns="http://www.w3.org/TR/REC-html40">

<head>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>CustomValidation</x:Name>

<x:WorksheetOptions>

<x:Selected/>

<x:DoNotDisplayGridlines/>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

<x:DataValidation>

<x:Range>$A$2</x:Range>

<x:Type>Whole</x:Type>

<x:Min>1</x:Min>

<x:Max>10</x:Max>

<x:ErrorMessage>Please enter between ! and 10</x:ErrorMessage>

<x:ErrorTitle>Test Application</x:ErrorTitle>

</x:DataValidation>

<x:DataValidation>

<x:Range>$A$3</x:Range>

<x:Type>Whole</x:Type>

<x:Qualifier>GreaterOrEqual</x:Qualifier>

<x:Value>12</x:Value>

<x:ErrorMessage>Please enter greater than equal to 12</x:ErrorMessage>

<x:ErrorTitle>Test Application</x:ErrorTitle>

</x:DataValidation>

<x:DataValidation>

<x:Range>$A$4</x:Range>

<x:Type>Whole</x:Type>

<x:Qualifier>LessOrEqual</x:Qualifier>

<x:Value>13</x:Value>

<x:ErrorMessage>Please enter less than equal to 13</x:ErrorMessage>

<x:ErrorTitle>Test Application</x:ErrorTitle>

</x:DataValidation>

<x:DataValidation>

<x:Range>$A$5</x:Range>

<x:Type>Custom</x:Type>

<x:Qualifier>Equal</x:Qualifier>

<x:Value>AND(MOD(A5,5)=0,A5>=0)</x:Value>

<x:ErrorMessage>Please enter in multiples of Five</x:ErrorMessage>

<x:ErrorTitle>Test Application</x:ErrorTitle>

</x:DataValidation>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>9210</x:WindowHeight>

<x:WindowWidth>19035</x:WindowWidth>

<x:WindowTopX>0</x:WindowTopX>

<x:WindowTopY>75</x:WindowTopY>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

</xml><![endif]-->

</head>

Split Pane

A split pane can also be done using custom tags. The following custom tags are used to do that:

<x:SplitHorizontal></x:SplitHorizontal>

<x:SplitVertical></x:SplitVertical>

SplitHorizontal is used for horizontal split and tag specifies the number of rows of the top pane and SplitVertical is used for the vertical split and tag specifies the number of columns on the left pane. There are another two tags for configuring the split pane; they are:

<x:TopRowBottomPane></x:TopRowBottomPane>

<x:LeftColumnRightPane></x:LeftColumnRightPane>

These two proerties specify the start row of the bottom pane and the start column of the right pane. The following is a simple example that implements the preceding tags:

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns="http://www.w3.org/TR/REC-html40">

<head>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>Split</x:Name>

<x:WorksheetOptions>

<x:Selected/>

<x:DoNotDisplayGridlines/>

<x:FreezePanes/>

<x:FrozenNoSplit/>

<x:SplitHorizontal>2</x:SplitHorizontal>

<x:TopRowBottomPane>2</x:TopRowBottomPane>

<x:SplitVertical>2</x:SplitVertical>

<x:LeftColumnRightPane>2</x:LeftColumnRightPane>

<x:ActivePane>0</x:ActivePane>

<x:Panes>

<x:Pane>

<x:Number>3</x:Number>

</x:Pane>

<x:Pane>

<x:Number>1</x:Number>

</x:Pane>

<x:Pane>

<x:Number>2</x:Number>

</x:Pane>

<x:Pane>

<x:Number>0</x:Number>

<x:ActiveRow>0</x:ActiveRow>

<x:ActiveCol>1</x:ActiveCol>

</x:Pane>

</x:Panes>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>9210</x:WindowHeight>

<x:WindowWidth>19035</x:WindowWidth>

<x:WindowTopX>0</x:WindowTopX>

<x:WindowTopY>75</x:WindowTopY>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

</xml><![endif]-->

</head>

</html>

The output of the above code will be:

Figure 2:

Excel Formula and NameRange

In this section we will see how to implement an Excel formula and namerange. An Excel formula is placed inside the HTML table.

For example:

<table>

<tr>

</tr>

</table>

Similarly namerange is placed inside the anchor tag <a></a>. For example:

<table>

<tr>

</tr>

</table>

The following is an example for an Excel formula and namerange implementation:

<html xmlns:v="urn:schemas-microsoft-com:vml"xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns="http://www.w3.org/TR/REC-html40">

<head>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>Formula</x:Name>

<x:WorksheetOptions>

<x:Selected/>

<x:DoNotDisplayGridlines/>

<x:Panes>

<x:Pane>

<x:Number>3</x:Number>

<x:RangeSelection>$A$1:$B$1</x:RangeSelection>

</x:Pane>

</x:Panes>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>9210</x:WindowHeight>

<x:WindowWidth>19035</x:WindowWidth>

<x:WindowTopX>0</x:WindowTopX>

<x:WindowTopY>75</x:WindowTopY>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

</xml><![endif]-->

</head>

<body >

<table>

<tr>

</tr

</table>

</body>

</html>

Grouping

The various outline levels of grouping can be defined inside the <tr> element of HTML table. The following table row belongs to the level 1:

The following code snippet demonstrate the grouping implementation:

<head>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>Grouping</x:Name>

<x:WorksheetOptions>

<x:Selected/>

<x:DoNotDisplayGridlines/>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>9210</x:WindowHeight>

<x:WindowWidth>19035</x:WindowWidth>

<x:WindowTopX>0</x:WindowTopX>

<x:WindowTopY>75</x:WindowTopY>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

</xml><![endif]-->

</head>

<body >

<table

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</body>

</html>

The output of the above code will be like this:

Figure 3:

Conclusion

Excel has a wide range of usage, this topic would be helpful for those who want to invoke an Excel component from a web application to support Excel's inherent functionalities like formulas, grouping and such.

Up Next

Ebook Download

View all

Data Insertion in Tables

Read by 0 people

Download Now!

Learn

View all