Excel to HTML table conversion and code generation utility
4.00/5 (4 votes)
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.
- 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
How this works?
Simply copy the following code to an html HTMLun it.
<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>
Excel to HTML table conversion utility by Niral Soni
Absolute Path of Excel file to read
:
<INPUT TYPE="file" ID="txtFilePath" value="">
ID of the Sheet to read
:
<INPUT TYPE="text" ID="txtSheetName" value="">
Header Row Start at
:
<INPUT TYPE="text" ID="txtHeaderRowStart" value="" onblur="numberOnly(this)">
Header Column Start at
:
<INPUT TYPE="text" ID="txtHeaderColStart" value="" onblur="numberOnly(this)">
Total Header Columns Count
:
<INPUT TYPE="text" ID="txtHeaderCols" value="" onblur="numberOnly(this)">
Total Data Rows Count
:
<INPUT TYPE="text" ID="txtDataRows" value="" onblur="numberOnly(this)">
<INPUT TYPE="button" VALUE="Convert to HTML" ONCLICK="convert2HTML()">
<INPUT TYPE="button" VALUE="Toggle HTML View / Source" style="display: none;" id="btnGetSrc" ONCLICK="toggleSrc()">
</BODY>
</HTML>
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: (TypeNumber) Enter the row index from where the header starts.Header Column Start at: (TypeNumber) Enter the column index from where the header starts.Total Header Columns Count: (TypeNumber) 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: (TypeNumber) Enter the total number of the data rows of the excel sheet you want to populate in HTML table, excluding the header row.
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 -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 -
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.
var excel_sheet = excel_file.Worksheets(sheetName);
Now you need to read the desired cell values using following API -
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".