Excel to DataTables example

This example shows how to generate a DataTable (https://datatables.net) from an Excel document using a HTML template. It uses below features.

DataTables:

• Paging
• Modal detail form on row-click
• Search highlight
• Clear search button
• Column sorting
• Bootstrap 4

Excel macro:

• Row=1 is headers-row
• Clickable http links
• Clickable email link
• Skip columns with bracket-header [header] or empty
• Skip rows based on status-column with value 'x' or empty

Additional information:

• https://datatables.net
• https://datatables.net/extensions/responsive/examples/display-types/bootstrap4-modal.html

Excel Visual Basic Script (VBA)

This Excel macro generates HTML table rows from an Excel document to be inserted into the HTML template.

' global variables
Dim glob_mwb As Excel.Workbook
Dim glob_mws As Excel.Worksheet
Dim glob_inputFilename As String
Dim glob_colIdx_id As Integer
Dim glob_colIdx_name As Integer

'ranges to show selected and created files
Dim inputNameR As Excel.Range
Dim templateR As Excel.Range
Dim outputNameR As Excel.Range

' html template placeholders
Const matchHtmlTableRows = "<!--TABLE ROWS-->"
Const matchDateAndTime = "<!--DATE AND TIME-->"
Const matchFileName = "<!--FILENAME-->"
Const matchNameColumnIndex = "<!--NAME COLUMN INDEX-->"
Const matchTitle = "<!--TITLE-->"

' excel open and proces file (start here)
Sub SelectOpenAndFormat()
    Dim wb As Excel.Workbook
    Dim y As Integer
    Dim x As Integer
    Dim openResult As Integer

    'macro workbook/worksheet
    Set glob_mwb = Application.ActiveWorkbook
    Set glob_mws = Application.ActiveSheet
    
    'init glob ranges
    Set inputNameR = glob_mws.Range("B12")
    Set templateR = glob_mws.Range("B15")
    Set outputNameR = glob_mws.Range("B18")
    
    'Select file
    openResult = OpenFileDialog()
    inputNameR.Value = glob_inputFilename
    
    If openResult <> 0 Then
        Set wb = Workbooks.Open(glob_inputFilename)
    End If
    
    If Not wb Is Nothing Then
        CreateHTMLfile wb
        wb.Close
    End If
End Sub


' excel open file dialog
Function OpenFileDialog() As Integer
    Dim intChoice As Integer
    Dim strPath As String
    'select dir
    Application.FileDialog(msoFileDialogOpen).InitialFileName = Application.ActiveWorkbook.Path
    
    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    Application.FileDialog(msoFileDialogOpen).Filters.Clear
    Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel", "*.xlsx"
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
        'return filename
        glob_inputFilename = strPath
    End If
    OpenFileDialog = intChoice
End Function

'Following function converts Excel range to HTML table
Public Function ConvertRangeToHTMLrows(ws As Worksheet) As String

    newLine = Chr$(10)
    dblQoute = """"
    ins1 = Chr$(9)
    ins2 = ins1 & ins1
    ins3 = ins2 & ins1
    ins4 = ins3 & ins1
    
    'Declare variables
    glob_colIdx_name = 1
    glob_colIdx_id = 1
    
    'Find the last non-blank cell in column A(1)
    maxRowIdx = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    maxColIdx = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    'headings (rowIdx=1)
    tHead = ins2 & "<tr> " & newLine
    For colIdx = 1 To maxColIdx
        colName = ws.Cells(1, colIdx).Text
        
        'find column indexes
        If glob_colIdx_id <= 1 And InStr(1, colName, "id", vbTextCompare) > 0 Then
            glob_colIdx_id = colIdx
        ElseIf glob_colIdx_name <= 1 And (InStr(1, colName, "naam", vbTextCompare) > 0 Or InStr(1, colName, "name", vbTextCompare)) Then
            glob_colIdx_name = colIdx
        End If
        
        'if not skip Column
        If Left(colName, 1) <> "[" And colName <> "" Then
            tHead = tHead & ins3 & "<th>" & Replace(colName, "<", "&lt;") & "</th>" & newLine
        End If
    Next colIdx
    
    'email link column
    tHead = tHead & ins3 & "<th>Comments</th>" & newLine
    
    'close head
    tHead = tHead & ins2 & "</tr>" & newLine
        
    'loop over each row in the range A2..
    tBody = ""
    For rowIdx = 2 To maxRowIdx
        
        'Col A = ID (glob_colIdx_id)
        
        'Skip row ID<0
        valID = ws.Cells(rowIdx, glob_colIdx_id).Text
        valNaam = ws.Cells(rowIdx, glob_colIdx_name).Text
        If valID <> "" And valID >= 0 Then
                       
            tBody = tBody & ins2 & "<tr> " & newLine
            
            For colIdx = 1 To maxColIdx
                colName = ws.Cells(1, colIdx).Text
                cellVal = ws.Cells(rowIdx, colIdx).Text
                
                'escape
                cellVal = Replace(cellVal, "<", "&lt;")
                cellVal = Replace(cellVal, ">", "&gt;")
                cellVal = Replace(cellVal, "&", "&amp;")
                
                'skip Column
                If Left(colName, 1) <> "[" And colName <> "" Then
                    If Left(cellVal, 4) = "http" Then
                        'http link
                        tBody = tBody & ins3 & "<td><a target=_blank href=" & dblQoute & cellVal & dblQoute & ">link</a></td>" & newLine
                    Else
                        'other
                        tBody = tBody & ins3 & "<td>" & Replace(cellVal, "<", "&lt;") & "</td>" & newLine
                    End If
                End If
            Next colIdx
            
            'email link column
            tBody = tBody & ins3 & "<td><a target=_blank href=" & dblQoute & "mailto:mail@me.pls?subject=" & valNaam & " (Ref." & valID & ")" & "&amp;body=Your message here.," & dblQoute & ">mail</a></td>" & newLine
            tBody = tBody & ins2 & "</tr>" & newLine
        End If
    Next rowIdx
    
    'Return html format
    ConvertRangeToHTMLrows = "<thead>" & tHead & "</thead>" & newLine & "<tbody>" & tBody & "</tbody>" & newLine & "<tfoot>" & tHead & "</tfoot>"
End Function

Sub CreateHTMLfile(wb As Excel.Workbook)

    'outputfile (based on macro workbook location)
    outputFile = glob_mwb.Path & "\" & Replace(Replace(wb.Name, ".xlsx", ".html"), ".xls", ".html")
        
    'read template &  save to file
    nameTitle = wb.Name

    wb.Activate
    Dim ws As Worksheet
    Dim r As Range
    Set ws = wb.ActiveSheet

    templateText = templateR.Text
    
    'generate html
    If Not ws Is Nothing Then
        templateText = Replace(templateText, matchHtmlTableRows, ConvertRangeToHTMLrows(ws), vbTextCompare)
        templateText = Replace(templateText, matchDateAndTime, Format(DateTime.Now, "dd mmm yyyy, hh:mm"), vbTextCompare)
        templateText = Replace(templateText, matchFileName, wb.FullName, vbTextCompare)
        'templateText = Replace(templateText, matchNameColumnIndex, glob_htmlName_colIdx - 1, vbTextCompare)
        templateText = Replace(templateText, matchTitle, nameTitle, vbTextCompare)
        
        Open outputFile For Output As #1
        Print #1, templateText
        Close #1
        
        glob_mws.Hyperlinks.Add outputNameR, outputFile
        outputNameR.Text = outputFile
        'ThisWorkbook.FollowHyperlink (outputFile)
    End If
    
End Sub

DataTable HTML template

Below is the HTML template, if contains an example table to be removed when used with the Excel macro.

Dom elements placement

 l - length changing input control 'show N entries'
 f - filtering input 'Search'
 t - The table
 i - Table information summary 'Showing n1 to n2 of N entries'
 p - pagination control 'previous .. next'
 r - processing display element

About a bootstrap grid:

 In a bootstrap grid, columns are 100% wide. Use col-<size>-<number> classes to override. <size> can be [xs, sm, md, lg] and corresponds with the screen width that is available. <number> is [1-12] and determines how many parts-in-width of a total of 12 you want for the element.

The HTML page, you can copy and paste this into an HTML file and open it in your browser. To use this together with the macro, use it as template.

<html>
<head>
  <title><!--TITLE--></title>
  
  <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="https://johannburkard.de/resources/Johann/jquery.highlight-5.closure.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/js/bootstrap.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
  <script src="https://cdn.datatables.net/responsive/2.2.3/js/responsive.bootstrap4.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css"/>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css"/>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.2.3/css/responsive.bootstrap4.min.css"/>
  
  <style>
  div.dt-buttons {
    float: right;
    margin-left:1px;
  }
  .buttons-clear-search {
    color: gray;
    border-radius: 3px;
    border: 1px solid rgb(206, 212, 218);
    padding: 1;
    width: 31px;
    height: 31px;
  }
  </style>
  
  <style>
  div.dataTables_filter  {
    float: right;
  }
  .highlight { background-color: yellow }
  </style>

</head>

<body>

<table id="table1" class="table table-striped table-bordered table-hover nowrap" style="width:100%">

<!--TABLE ROWS-->

<thead> <tr> <th>Name</th> <th>Position</th> <th>Office</th> <th>Age</th> <th>Start date</th> <th>Extn.</th> </tr> </thead>
<tbody> 
 <tr> <td>01. Nixon</td> <td>Architect</td> <td>Edinburgh</td> <td>61</td> <td>2011/04/25</td> <td>5421</td> </tr>
 <tr> <td>02. Winters</td> <td>Accountant</td> <td>Tokyo</td> <td>63</td> <td>2011/07/25</td> <td>8422</td> </tr>
 <tr> <td>03. Cox</td> <td>Author</td> <td>San Francisco</td> <td>66</td> <td>2009/01/12</td> <td>1562</td> </tr>
 <tr> <td>04. Kelly</td> <td>Developer</td> <td>Edinburgh</td> <td>22</td> <td>2012/03/29</td> <td>6224</td> </tr>
 <tr> <td>05. Nixon</td> <td>Architect</td> <td>Edinburgh</td> <td>61</td> <td>2011/04/25</td> <td>5421</td> </tr>
 <tr> <td>06. Winters</td> <td>Accountant</td> <td>Tokyo</td> <td>63</td> <td>2011/07/25</td> <td>8422</td> </tr>
 <tr> <td>07. Cox</td> <td>Author</td> <td>San Francisco</td> <td>66</td> <td>2009/01/12</td> <td>1562</td> </tr>
 <tr> <td>08. Kelly</td> <td>Developer</td> <td>Edinburgh</td> <td>22</td> <td>2012/03/29</td> <td>6224</td> </tr>
 <tr> <td>09. Nixon</td> <td>Architect</td> <td>Edinburgh</td> <td>61</td> <td>2011/04/25</td> <td>5421</td> </tr>
 <tr> <td>10. Winters</td> <td>Accountant</td> <td>Tokyo</td> <td>63</td> <td>2011/07/25</td> <td>8422</td> </tr>
 <tr> <td>11. Cox</td> <td>Author</td> <td>San Francisco</td> <td>66</td> <td>2009/01/12</td> <td>1562</td> </tr>
 <tr> <td>12. Kelly</td> <td>Developer</td> <td>Edinburgh</td> <td>22</td> <td>2012/03/29</td> <td>6224</td> </tr>
</tbody>
<tfoot> <tr> <th>Name</th> <th>Position</th> <th>Office</th> <th>Age</th> <th>Start date</th> <th>Extn.</th> </tr> </tfoot>

</table>

<script type="text/javascript">
$.fn.dataTable.ext.buttons.custom = {
    className: 'buttons-clear-search',
    action: function ( e, dt, node, config ) {
	dt.search( '' ).draw();
	//button-text=this.text()
    }
};

$(document).ready( function () {

    var table = $('#table1').DataTable( {

        dom:
	"<'row'<'col-sm-6 col-md-6'l><'col-sm-6 col-md-6'fB>>" +
	"<'row'<'col-sm-6 col-md-5'i><'col-sm-6 col-md-7'p>>" +
	"<'row'<'col-sm-12'tr>>",
        language: {
            search: "",
            searchPlaceholder: "Search"
        },
        buttons: [
            {
                extend: 'custom',
                text: 'x'
                //className: 'buttons-clear-search',
                //key: { key: String.fromCharCode(27) } //escape key
                //action: function ( e, dt, node, config ) { dt.search( '' ).draw(); }
            }
        ],												
        responsive: {
            details: {
                type: 'column',
                target: 'tr', // row-click
                //Note: modal does not trigger when all columns are visible
                display: $.fn.dataTable.Responsive.display.modal( {
                    header: function ( row ) {
                        var data = row.data();
                        //return 'Details for '+data[<!--NAME COLUMN INDEX-->];
                        return 'Details for '+data[0]; 
                    }
                } ),
                //default renderer:
                //renderer: $.fn.dataTable.Responsive.renderer.tableAll( { tableClass: 'table' } )

                //custom renderer:
                //https://datatables.net/extensions/responsive/examples/child-rows/custom-renderer.html	    
                renderer: function ( api, rowIdx, columns ) {
                    var data = $.map( columns, function ( col, i ) {
                      return '<tr data-dt-row="'+col.rowIndex+'" data-dt-column="'+col.columnIndex+'">'+
                                '<td>'+col.title+':'+'</td> '+'<td>'+col.data+'</td>'+'</tr>';
                    } ).join('');
                    return data ?
                        $('<table class="table dtr-details" width="100%"/>').append( data )
		: false;
                }
            }
        }
    } );

    table.on( 'select', function ( e, dt, type, indexes ) {
        //if not responsive view launch responsive modal
    });

    table.on( 'draw', function () {
        var body = $( table.table().body() );
        body.removeHighlight();
        if ( table.rows( { filter: 'applied' } ).data().length ) {
           body.highlight( table.search() );
        }
    } );

} );

</script>

<p>
<i><!--FILENAME--></i><br/>
<i><!--DATE AND TIME--></i>
</p>

</body>
</html>