Categorized | excel download, excel macro and vba, javascript

HTML to Excel using Javascript and Excel to HTML using VBA

This two part article consists of:
Part A – Converting HTML to Excel using Javascript
Part B – Exporting Excel to HTML using VBA

HTML to Excel using javascript

Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document into a Excel workbook we need to carry out three steps – (a) Parse the table into its constituent rows, columns and cells (b) Use ACTIVEX to initialize a new Excel workbook and finally (c) Feed the workbook with data from step (a).

During step 1, we try to get a handle on the table in the html document using the getElementsByTagName(“table”) function. Once we have a handle on the table, we try to ascertain the number of rows (<tr>) and the number of columns that exist within that table (<tr> within a <td>). Once we know both of these, conversion form html to excel is carried out by reading each cell’s content using the javascript for loop and pushing them into the Excel worksheet (created using ACTIVEX).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<html>

<head>

<script type="text/javascript">

function write_to_excel() 

{

str="";

var mytable = document.getElementsByTagName("table")[0];

var row_Count = mytable.rows.length;

var col_Count = mytable.getElementsByTagName("tr")[0].getElementsByTagName("td").length;    

var ExcelApp = new ActiveXObject("Excel.Application");

var ExcelSheet = new ActiveXObject("Excel.Sheet");

ExcelSheet.Application.Visible = true;

    for(var i=0; i < row_count ; i++) 

    {   

        for(var j=0; j < col_Count; j++) 

        {           

            str= mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML;

            ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;

        }

    }

}

</script></script></head>

<body>

<input type="submit" value="Export to EXCEL" onclick="write_to_excel();"/>

<!-- ************************************************-->

<!--**** INSERT THE TABLE YOU WANT EXPORT HERE ****-->

<table><tr><td>First</td><td>second</td></tr></table>

<!-- *******************example given above****************-->

</body>

</html>

(note: remove the extra <script> tag from the above code just before the head tag).

As you can see, we first parse the table, then invoke ACTIVEX to create an excel object and then export our table to it.

You can download a webpage with code to export HTML to Excel here or click on the button below:

Excel to HTML using VBA

Excel can convert either a specified range or an entire workbook (including all its sheets) into an html document. The first part (converting a specified range to an html document) can be achieved by using the PublishObjects property of the workbook. Let’s look at a sample code:

1
2
3
4
5
6
7
8
9
10
11
12
13

Sub EXCEL_TO_HTML_RANGE()

Dim path As String

Dim rng As Range

path = Application.ActiveWorkbook.path & "\Book1.htm"

Set rng = Range(Cells(1, 1), Cells(10, 3))

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, path, "Sheet1", _

    rng.Address, xlHtmlStatic, "Name_Of_DIV", "Title_of_Page")

    .Publish (True)

    .AutoRepublish = False

End With

End Sub

If you open the resulting html file, you will see that the specified range has been converted into a table and has been properly enclosed with the opening and closing <table> tags. If you go back a bit, you’d remember that we talked about the same thing when we were converting html to excel. This is the key, the common denominator – that allows the data conversion to take place both ways from html to excel and back from excel to html. Also note that the PublishObjects function has the following syntax:

PublishObjects.Add(XlSourceType, FileName, Sheet, Source, HtmlType, DivID, Title)
where:

XlSourceType is the type of object that needs to be exported to HTML. The following can be specified as the source types:

FileName is the path alongwith the name of the output html file
Source is the object which needs to be converted to html. It can be a range, a chart or any of the other source types as specified in the XlSourceType parameter
HtmlType is the type of conversion method you would like to employ. The following can be specified as the html types:

DIVID is the ID of the div tag that will be used to enclose the table in the html document generated by the code.
Title is the title of the html document generated by the code.

The other part is to convert the entire workbook into an html document. This is slightly different as it uses the in-built converters in excel to achieve this goal. The code shown below can be used to convert an Excel workbook to any of the other available formats – all that needs to be done is to specify the proper file format (FileFormat property) and the extension.

1
2
3
4
5

Sub EXCEL_TO_HTML_WORKBOOK()

Dim path As String

path = Application.ActiveWorkbook.path & "\Book1.htm"

ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlHtml

End Sub

where

FileFormat is the type of output format to produce. We use the xlHtml format (.html) but the same can be used to produce other formats:

You can download a sample worksheet with VBA code for converting excel to HTML here or click on the button below:

What Do You Think ?

Comments and Trackbacks

Rafi wrote:

the code was very useful.
rafi.mohamad@gmail.com
SH95079060

August 30th, 2010 at 6:01 am
jagruti wrote:

code is not working for html page into excel sheet conversion

can u pls send the right code in my email id

December 26th, 2010 at 2:37 pm
jagruti wrote:

can u pls help me 4 my project.
the code which given above is not working
even i downloaded ur file still that file not converting html into the excel sheet
pls help

December 26th, 2010 at 2:42 pm
fabian wrote:

not working

June 8th, 2011 at 9:40 pm
Sudris wrote:

this indeed is a brilliant code.
but what would one do if the table is generated after submitting a html form and verified by a javascript?

September 1st, 2011 at 10:05 am
recher wrote:

It does not work with .cshtm

October 10th, 2011 at 9:50 pm
Webster wrote:

This solution probably only works for IE:

Tools, Internet Options, Secuirty, Custom level

Enable “Initialize and script ActiveX control notmarked as safe.”

February 15th, 2012 at 12:57 pm
Rajesh wrote:

In “HTML to Excel using javascript”, in the 1st for loop, the row_count should be row_Count.

June 6th, 2012 at 5:30 am
tonja wrote:

The code for “HTML to Excel using javascript” only pushes to one column in excel. How do you allow excel to read any of the other columns in this script?

June 6th, 2013 at 3:28 pm
sadiaelia wrote:

I would recommend you to use Aspose.Cells for Java for converting excel files to html because its a secure API and uses cloud technology and offers many features and also provides developers with alot of sample codes which they can use in their API.

July 5th, 2013 at 2:45 am