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.
<TITLE>Excel to HTML</TITLE>
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 "
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
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;
Hope you will find this utility useful.