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:

export-html-to-excel

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:
excel-to-html-xlsourcetype

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:

excel-to-html-xlhtmltype
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:
excel-to-html-fileformat

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

excel-to-html


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Rafi wrote:

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

  2. jagruti wrote:

    code is not working for html page into excel sheet conversion

    can u pls send the right code in my email id

  3. 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

  4. fabian wrote:

  5. 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?

  6. recher wrote:

    It does not work with .cshtm

  7. Webster wrote:

    This solution probably only works for IE:

    Tools, Internet Options, Secuirty, Custom level

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

  8. Rajesh wrote:

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

  9. 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?

  10. 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.

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel