Click here to Skip to main content
15,867,453 members
Articles / Web Development / HTML
Tip/Trick

Excel to HTML table conversion and code generation utility

Rate me:
Please Sign up or sign in to vote.
4.00/5 (4 votes)
24 Aug 2011CPOL4 min read 87.7K   8   11
Convert whole or selected data range from any excel sheet to HTML table, with source generated

Introduction   


A Simple utility to for converting Excel sheet to HTML table.

Please note that this utility works only with the Internet explorer, as it uses the ActiveX object for reading the excel file.

Why this is useful? 


There exists Excel to HTML conversion utilities which comes along with the Microsoft office suite. It does provide good facility of exporting the contents to a web page using -File | Save As | Web Page option, but the drawback is that it copies the cell contents in AS-IS format only. So, if the cell contents are HTML, it won't be parsed, but will be displayed AS-IS.

Also, if you see the source code of the file generated by exporting excel to web page using the office suite - 


  • it follows the Microsoft office specific schema namespace
  • the css (inline style as well as the class names) is neither as per required standard, nor is manageable 
  • the height/width etc are fixed calculated, is replicated for every TD tag in the html 
  • it works in its entirety - you can not export specific range of data to equivalent HTML page. 

on the other side, this utility will provide you with -

  • an ease in manipulating the column data as needed 
  • controllable and manageable css
  • option of exporting only selected range of data to equivalent HTML page 

As an example, there are certain database tools which provides the utility of exporting the result of query into an excel file. There are possibilities that the output may contain the HTML data. So, using this utility, you can directly convert the excel file or selected data range into equivalent HTML page, with your own defined css formatting !!!

How this works?  


Simply copy the following code to an html HTMLun it.
JavaScript
<HTML>
	<HEAD>
		<TITLE>Excel to HTML</TITLE>
		<STYLE TYPE="text/css">body div * { font-family: Verdana; font-weight: normal; font-size: 12px; } body { background-color: #E6E6FF; } .tableContainer table { border: 1px solid #000040; } .tblHeader { font-weight: bold; text-align: center; background-color: #000040; color: white; } .oddRow, .evenRow { vertical-align: top; } .tblHeader td, .oddRow td, .evenRow td { border-left: 1px solid #000040; border-bottom: 1px solid #000040; } .lastCol { border-right: 1px solid #000040; } .oddRow { background-color: #C6C6FF; } .evenRow { background-color: #8C8CFF; }</STYLE>
		<SCRIPT LANGUAGE="JavaScript">
			<!--
			function _ge(id) { return document.getElementById(id); }
			function _nc(val) { return (val != null && val.length > 0); }

			function convert2HTML() {
				var fo = _ge('txtFilePath');
				var so = _ge('txtSheetName');
				var ho = _ge('txtHeaderRowStart');
				var co = _ge('txtHeaderColStart');
				var hco = _ge('txtHeaderCols');
				var ro = _ge('txtDataRows');

				if(!(_nc(fo.value) && _nc(so.value) && _nc(ho.value) && _nc(co.value) && _nc(hco.value) && _nc(ro.value)) ) {
					alert('All the fields are mandatory.');
					return false;
				}

				var ex;
				try {
					ex = new ActiveXObject("Excel.Application");
				}
				catch (e)
				{
					alert('Your browser does not support the Activex object.\nPlease switch to Internet Explorer.');
					return false;
				}
				var ef = ex.Workbooks.Open(fo.value,true);
				var es = ex.Worksheets(so.value);
				var rs = parseInt(ho.value,10);
				var cs = parseInt(co.value,10);
				var ce = cs + parseInt(hco.value,10) - 1;
				var re = rs + parseInt(ro.value,10);

				var oc = _ge('tableContainer');
				oc.innerHTML = '';
				var tbl = document.createElement('TABLE');
				tbl.id = 'tblExcel2Html';
				tbl.border = '0';
				tbl.cellPadding = '4';
				tbl.cellSpacing = '0';
				oc.appendChild(tbl);
				var i,j,row,col,r,c;

				for(i = rs, r = 0; i <= re; i++,r++) {
					row = tbl.insertRow(r);
					row.className = (i == rs) ? 'tblHeader' : (i % 2 == 0) ? 'evenRow' : 'oddRow';
					for(j = cs, c = 0; j <= ce; j++,c++) {
						col = row.insertCell(c);
						col.className = (j == ce) ? 'lastCol' : '';
						col.innerHTML = es.Cells(i,j).value || ' ';

					}
				}
				_ge('btnGetSrc').style.display = '';
			}

			function toggleSrc() {
				if(_ge('tableContainer').style.display == '') {
					getHTMLSrc();
				}
				else {
					back2Table();
				}
			}

			function getHTMLSrc() {
				var oc = _ge('tableContainer');
				var tx = _ge('txtOutput');
				var so = document.getElementsByTagName('style');
				var str = '<html><body>' + oc.outerHTML + so[0].outerHTML + '</body></html>';
				tx.value = str;
				oc.style.display = 'none';
				_ge('divOutput').style.display = '';
			}

			function copy2Clipboard() {
				var tx = _ge('txtOutput');
				window.clipboardData.setData("Text",tx.value);
			}

			function resetFields() {
				window.location.reload();
			}

			function back2Table() {
				_ge('divOutput').style.display = 'none';
				_ge('btnGetSrc').style.display = '';
				_ge('tableContainer').style.display = '';
			}

			function numberOnly(obj) {
				obj.value = obj.value.replace(/[^0-9]*/,'');
			}
			//-->
		</SCRIPT>
	</HEAD>
	<BODY>
	<h2>Excel to HTML table conversion utility by Niral Soni</h2>
	<div id="ExcelDetails">
		<table border="0" width="100%">
		<tr>
			<td>Absolute Path of Excel file to read</td>
			<td>:</td>
			<td><INPUT TYPE="file" ID="txtFilePath" value=""></td>
			<td>ID of the Sheet to read</td>
			<td>:</td>
			<td><INPUT TYPE="text" ID="txtSheetName" value=""></td>
		</tr>
		<tr>
			<td>Header Row Start at</td>
			<td>:</td>
			<td><INPUT TYPE="text" ID="txtHeaderRowStart" value="" onblur="numberOnly(this)"></td>
			<td>Header Column Start at</td>
			<td>:</td>
			<td><INPUT TYPE="text" ID="txtHeaderColStart" value="" onblur="numberOnly(this)"></td>
		</tr>
		<tr>
			<td>Total Header Columns Count</td>
			<td>:</td>
			<td><INPUT TYPE="text" ID="txtHeaderCols" value="" onblur="numberOnly(this)"></td>
			<td>Total Data Rows Count</td>
			<td>:</td>
			<td><INPUT TYPE="text" ID="txtDataRows" value="" onblur="numberOnly(this)"></td>
		</tr>
		<tr>
			<td colspan="6" align="CENTER"><INPUT TYPE="button" VALUE="Convert to HTML" ONCLICK="convert2HTML()"></td>
		</tr>
		</table>
		<br>
	</br></div>
	<INPUT TYPE="button" VALUE="Toggle HTML View / Source" style="display: none;" id="btnGetSrc" ONCLICK="toggleSrc()">
	<br>
	<div id="divOutput" style="display: none; height: 50%;">
		<TEXTAREA ID="txtOutput" style="width:100%; height: 100%;"></TEXTAREA>
		<br>
		<center>
			<INPUT TYPE="button" VALUE="Copy to Clipboard" ONCLICK="copy2Clipboard()">
			<INPUT TYPE="button" VALUE="Reset All" ONCLICK="resetFields()">
		</center>
	</br></div>
	<div id="tableContainer"></div>
	</BODY>
</HTML>
</br>

The required input to be provided are as described below -

  • Absolute Path of Excel file to read : Using the "Browse" button, navigate to the desired excel file. 
  • ID of the Sheet to read : Enter the name of the sheet within the excel file to read. Normally the sheet name would be "Sheet1" or "Sheet2" etc.
  • Header Row Start at :  (Type Number) Enter the row index from where the header starts. 
  • Header Column Start at : (Type Number) Enter the column index from where the header starts.
  • Total Header Columns Count : (Type Number) Starting from the header column index, enter the total number of columns of the excel sheet you want to populate in HTML table. 
  • Total Data Rows Count : (Type Number) Enter the total number of the data rows of the excel sheet you want to populate in HTML table, excluding the header row.   

Once all required values are entered, click on "Convert to HTML" button. Your browser requires ActiveX control enabled. If not, it will show you an information bar. Select "Allow block contents..." option by clicking on the information bar.

Next, Windows Security Warning popup message will be displayed. Click on "Yes" button.

The selected columns and rows from the excel sheet is now displayed as HTML table on the screen.  

You will also see a "Toggle HTML View / Source" button. Clicking on which you will see that the HTML source of the converted excel sheet is available. You can copy the data to clipboard simply by clicking on the "Copy to Clipboard" button.

Behind the Scene


Everything starts with an ActiveX control object. Thanks to Microsoft for providing such functionality. Create a reference to Excel application ActiveX object using -
JavaScript
var excel_app = new ActiveXObject("Excel.Application"); 

If you get successful in creating the object without any error, then you can proceed to the next step, which is to open the desired excel file -
JavaScript
var excel_file = excel_app.Workbooks.Open(AbsoluteFilePath, openAsReadOnly);  

Next step is to select which worksheet to read. Every worksheet within the excel file is identified by the unique name, which is available in the excel file itself. Normally it is "Sheet1", "Sheet2" etc.
JavaScript
var excel_sheet = excel_file.Worksheets(sheetName); 

Now you need to read the desired cell values using following API -
JavaScript
var cell_value = excel_sheet.Cells(rowIndex, columnIndex).value; 

In excel file, the cells are identified using the combination of Alphabets and Numbers like - A1, B1, C1 etc, with Alphabets representing the columnIndex, and Numbers representing the rowIndex. But in JavaScript, both the rowIndex and columnIndex starts with integer value "1".

Conclusion 


Hope you will find this utility useful.



License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Business Analyst Tata Consultancy Services Ltd.
India India
I started my career as Java Developer. But now more inclined towards web designing, JavaScript, HTML and CSS.I have good level of expertise on Java, Oracle and JavaScript. Designing Generic components is my main expertise.

Comments and Discussions

 
QuestionEXCEL SPREADSHEET to HTML Pin
manoj44477721-Oct-15 17:42
professionalmanoj44477721-Oct-15 17:42 
QuestionEnabling a range Pin
Member 1061009319-Feb-14 10:01
Member 1061009319-Feb-14 10:01 
Questionexcel file display as webpage along wth graph dynamically Pin
Member 103770042-Nov-13 23:02
Member 103770042-Nov-13 23:02 
GeneralMy vote of 1 Pin
viktor_khazanov27-Aug-13 2:45
viktor_khazanov27-Aug-13 2:45 
GeneralMy vote of 5 Pin
yadlapallirk20-Jun-13 5:20
yadlapallirk20-Jun-13 5:20 
GeneralMy vote of 5 Pin
eiia75-Mar-13 10:28
eiia75-Mar-13 10:28 
GeneralSuperb !! Pin
jafarmlp17-Feb-13 22:24
jafarmlp17-Feb-13 22:24 
GeneralDate field is not showing in html page Pin
Member 865840318-Feb-12 19:28
Member 865840318-Feb-12 19:28 
GeneralRe: Hi, search for the line - col.innerHTML = es.Cells(i,j).valu... Pin
Niral Soni19-Feb-12 8:14
Niral Soni19-Feb-12 8:14 
GeneralReason for my vote of 5 useful Pin
KFLee29-Aug-11 15:53
KFLee29-Aug-11 15:53 
GeneralRe: thanks Pin
Niral Soni2-Sep-11 5:04
Niral Soni2-Sep-11 5:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.