Export Datatable to Excel File

Export DataTable to Excel File

use this code...

    dt = city.GetAllCity();//your datatable
    string attachment = "attachment; filename=city.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-excel";
    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }
    Response.Write("\n");
    int i;
    foreach (DataRow dr in dt.Rows)
    {
        tab = "";
        for (i = 0; i < dt.Columns.Count; i++)
        {
            Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }
        Response.Write("\n");
    }
    Response.End();

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.

How to export dataTable as Excel format?

Use this approach

oTableTools: {
  sSwfPath: "copy_csv_xls_pdf.swf",
  aButtons: [
    { sExtends: "xls",
      mColumns: 'visible',
      sFileName: 'export.xls',
      sToolTip: 'Save current table as XLS'
    }
  ]
}

The important things is sExtends: "xls" and sFileName to ensure a proper filename attached to the downloaded file.

But as other people mention in comments, you should really consider using the buttons plugin, here is a demo using that -> https://jsfiddle.net/zm825k01/

Rename excel file when exporting a datatable in Jquery

A simple workaround can be based on adding a mousedown event to the button.

$(document).on('mousedown', '#exportButton', function(e) {
    swal("Enter the filename :", {
        content: "input",
    }).then(function (value) {
        if (value.trim().length > 0)
            $('#exportButton').data('filename', value).trigger('click');
    });
})
var table = $('#example').DataTable({
    "dom": 'Btri',
    "searching": false,
    "paging": false,
    "info": false,
    buttons: [{
        extend: 'excelHtml5',
        className: 'btn btn-primary btn-sm',
        text: 'Export',
        autoFilter: true,
        attr: {id: 'exportButton'},
        sheetName: 'data',
        title: '',
        filename: function ( ) {
            return $('#exportButton').data('filename');
        }
    }]
});

<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.bootstrap4.min.css" rel="stylesheet">

<script src="https://code.jquery.com/jquery-3.5.1.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.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.colVis.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sweetalert/2.1.2/sweetalert.min.js"></script>

<table id="example" class="table table-bordered table-hover nowrap" style="width:100%">
    <thead>
    <tr>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Age</th>
        <th>Start date</th>
        <th>Salary</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>Tiger Nixon</td>
        <td>System Architect</td>
        <td>Edinburgh</td>
        <td>61</td>
        <td>2011/04/25</td>
        <td>$320,800</td>
    </tr>
    <tr>
        <td>Garrett Winters</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>63</td>
        <td>2011/07/25</td>
        <td>$170,750</td>
    </tr>
    </tbody>
</table>

How can I Export a datatable to an excel sheet with user-defined row no of the excel sheet from c#?

I tried this one I got the answer. For those who downvoted my question, there is a possible to do the way. First you should think the route and work

private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
private static Microsoft.Office.Interop.Excel.Application oXL;
public static void ReadExistingExcel()
{
   string path = @"C:\Tool\Reports1.xls";
   oXL = new Microsoft.Office.Interop.Excel.Application();
   oXL.Visible = true;
   oXL.DisplayAlerts = false;
   mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
   //Get all the sheets in the workbook
  mWorkSheets = mWorkBook.Worksheets;
   //Get the allready exists sheet
   mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
   Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;
   int colCount = range.Columns.Count;
   int rowCount= range.Rows.Count;
   for (int index = 1; index < 15; index++)
   {
      mWSheet1.Cells[rowCount + index, 1] = rowCount +index;
      mWSheet1.Cells[rowCount + index, 2] = "New Item"+index;
   }
   mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
   Missing.Value, Missing.Value, Missing.Value,    Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
   Missing.Value, Missing.Value, Missing.Value,
   Missing.Value, Missing.Value);
   mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
   mWSheet1 = null;
   mWorkBook = null;
   oXL.Quit();
   GC.WaitForPendingFinalizers();
   GC.Collect();
   GC.WaitForPendingFinalizers();
   GC.Collect();
} 

Export data table to Excel sheet

Since you haven't mentioned if excel is installed, i recommend EPPlus to create the excel file. It has a convenient method LoadFromDataTable:

using (var pck = new ExcelPackage())
{
    var ws = pck.Workbook.Worksheets.Add("Worksheet-Name");
    ws.Cells["A1"].LoadFromDataTable(dataTable1, true, OfficeOpenXml.Table.TableStyles.Medium1);
    using(var fileStream = File.Create(path))
        pck.SaveAs(fileStream);
}

Edit i've only just seen that you have tagged export-to-csv.

var lines = dataTable1.AsEnumerable()
    .Select(r => string.Join(",", r.ItemArray));
string csv = string.Join(Environment.NewLine, lines);

File.AppendAllText(path, csv);

---

Related Topics