How to Export Datatable to Excel

$("#tableComments").DataTable({
  stripHtml: !1,
  dom: 'Bfrtip',
  buttons: ['copyHtml5', 'csvHtml5', 'pdfHtml5']
})
$('button').on('click', function(e) {
  window.open('data:application/vnd.ms-excel,' + encodeURIComponent( document.getElementById('tableComments').outerHTML));
})

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css">
<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>

<button>Export To Excel</button>
<table id="tableComments" class="display table table-info table-striped">
    <thead>
    <tr>
        <th>User</th>
        <th>Version</th>
        <th>Round</th>
        <th>Comment</th>
        <th>Screenshot #1</th>
        <th>Screenshot #2</th>
        <th>Screenshot #3</th>
        <th>Screenshot #4</th>
        <th>Status</th>
    </tr>
    </thead>
    <tbody>
    <tr>

        <td>username1</td>
        <td>versionName</td>
        <td>round</td>
        <td>comment </td>
        <td><img src="https://dummyimage.com/100x100/000/fff&text=1"/></td>
        <td>screenshot1</td>
        <td>screenshot2</td>
        <td>screenshot3</td>
        <td>approved</td>
    </tr>
    <tr>

        <td>username2</td>
        <td>versionName</td>
        <td>round</td>
        <td>comment </td>
        <td><img src="https://dummyimage.com/100x100/000/fff&text=2"/></td>
        <td>screenshot1</td>
        <td>screenshot2</td>
        <td>screenshot3</td>
        <td>approved</td>
    </tr>
    <tr>

        <td>username3</td>
        <td>versionName</td>
        <td>round</td>
        <td>comment </td>
        <td><img src="https://dummyimage.com/100x100/000/fff&text=3"/></td>
        <td>screenshot1</td>
        <td>screenshot2</td>
        <td>screenshot3</td>
        <td>approved</td>
    </tr>
    </tbody>
</table>

Export Datatable into Excel Strat from Specific Column C#

So, the most obvious approach here would be to iterate over the data table's rows and write each to the Excel sheet manually:

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;

// Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

// Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

// Write the data. Remember that Excel is 1-indexed
int rowIndex = 1;
foreach (DataRow row in table.Rows) {
    int colIndex = 6;
    foreach (DataColumn col in table.Columns) {
        oSheet.Cells[rowIndex, colIndex] = row[col];
        colIndex++;
    }

    rowIndex++;
}

// Save the Excel file
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
    false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Exit Excel
oWB.Close();
oXL.Quit();

There's probably a more elegant solution to this problem but I think this will get the job done, at least. As a reference, my Excel code was modified from this question, so you might be able to find a better match for your specific needs there.

---

Related Topics