Excel to DataTables example
From WickyWiki
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
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
Dim glob_htmlName_colIdx As Integer
' 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
'Select file
openResult = OpenFileDialog()
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(r As Range) 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 = 0
glob_htmlName_colIdx = 0
glob_colIdx_id = 0
html_colSkip = 0
colIdx_status = 0
rowIdx_head = 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(rowIdx_head, colIdx).Text
'column indexes
If colName = "ID" And glob_colIdx_id <= 0 Then
glob_colIdx_id = colIdx
ElseIf InStr(colName, "naam") > 0 And glob_colIdx_name <= 0 Then
glob_colIdx_name = colIdx
glob_htmlName_colIdx = colIdx - html_colSkip
ElseIf InStr(colName, "status") > 0 And colIdx_status <= 0 Then
colIdx_status = colIdx
End If
'if not skip Column
If Left(colName, 1) <> "[" And colName <> "" Then
tHead = tHead & ins3 & "<th>" & Replace(colName, "<", "<") & "</th>" & newLine
Else
html_colSkip = html_colSkip + 1
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)
'Col B = status (colIdx_status)
'Skip row Status=x
valStatus = ws.Cells(rowIdx, colIdx_status).Text
If valStatus <> "" And valStatus <> "x" Then
tBody = tBody & ins2 & "<tr> " & newLine
For colIdx = 1 To maxColIdx
colName = ws.Cells(rowIdx_head, colIdx).Text
cellVal = ws.Cells(rowIdx, colIdx).Text
'escape
cellVal = Replace(cellVal, "<", "<")
cellVal = Replace(cellVal, ">", ">")
cellVal = Replace(cellVal, "&", "&")
'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, "<", "<") & "</td>" & newLine
End If
End If
Next colIdx
'email link column
valID = ws.Cells(rowIdx, glob_colIdx_id).Text
valNaam = ws.Cells(rowIdx, glob_colIdx_name).Text
tBody = tBody & ins3 & "<td><a target=_blank href=" & dblQoute & "mailto:mail@me.pls?subject=" & valNaam & " (Ref." & valID & ")" & "&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 = Replace(Replace(wb.FullName, ".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.ActiveWorksheet
'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
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.
<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 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
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
*/
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>
