C# .NET - Export datatable to excel sheet - Asked By Kannabiran . on 30-Sep-14 10:23 AM

I have an excel file with 5 sheet in the application folder, in that i have to export each 5 datatable data to each sheet. i am using visual studio 2010.

Robbe Morris replied to Kannabiran . on 30-Sep-14 10:26 AM
Ideally, you'd use something like spreadsheetgear.com just in case your end user doesn't have the right version of Microsoft Office installed.  Assuming you can't afford that, this link will get you started working with Microsoft Office COM in your C# .NET console/desktop/windows services app.

Working with Microsoft Office in an ASP.NET application is not recommended.  It isn't stable in a multi-user environment.

You can use some of the keyword classes and methods to refine your google searches for working with spreadsheets.

http://www.nullskull.com/articles/create_macro_at_runtime_in_dotnet.asp
alex smith replied to Kannabiran . on 09-Oct-14 09:35 PM
hello,

According to your description, this article gives a solution if you don't mind using third-party toolkit to export datatable to Excel, try to figure out whether it is working for your situation or not -
http://www.e-iceblue.com/Tutorials/Spire.DataExport/Spire.DataExport-Program-Guide/How-to-Export-Datatable-to-Excel-through-DataGridView.html
najna abdulla replied to Kannabiran . on 27-Nov-14 12:14 AM
first you can download EEPLUS library,through Nu Get.EPPlus is a really good library to help you generate Excel spreadsheets together with C#!
 After that include header files in Controller,In which we are going to define the method for download to excel sheet
like as showing in below
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.Data;
using System.IO;
  public void DownloadToXml()
        {
namespace Suite.Controllers
{
   DataSet dataToExcel = budget.DocsDS;  //budget.DocsDS its a dataset(set of datatables),so here acces your datatable

  string excelSheetName = "BudgetDocument";
            string fileName = "BudgetDocs";
            string currentDirectorypath = Server.MapPath("~/Reports");
            string finalFileNameWithPath = string.Empty;
            using (var pck = new ExcelPackage())
                fileName = string.Format("{0}_{1}", fileName, DateTime.Now.ToString("dd-MM-yyyy"));
            finalFileNameWithPath = string.Format("{0}\\{1}.xlsx", currentDirectorypath, fileName);

            if(System.IO.File.Exists(Server.MapPath("~/Reports/" + fileName + ".xlsx")))
                System.IO.File.Delete(Server.MapPath("~/Reports/" + fileName + ".xlsx"));


            var newFile = new FileInfo(finalFileNameWithPath);

            //Step 1 : Create object of ExcelPackage class and pass file path to constructor.
            using (var package = new ExcelPackage(newFile))
            {
                //Step 2 : Add a new worksheet to ExcelPackage object and give a suitable name
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelSheetName);
                
                //Step 3 : Start loading datatable form A1 cell of worksheet.
                worksheet.Cells["A1"].LoadFromDataTable(dataToExcel.Tables[0], true, TableStyles.Light1);

                //Step 4 : (Optional) Set the file properties like title, author and subject
                package.Workbook.Properties.Title = @"This code is part of tutorials available at http://bytesofcode.hubpages.com";
                package.Workbook.Properties.Author = "Bytes Of Code";
                package.Workbook.Properties.Subject = @"Register here for more http://hubpages.com/_bytes/user/new/";

                //Step 5 : Save all changes to ExcelPackage object which will create Excel 2007 file.
                package.Save();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.TransmitFile(Server.MapPath("~/Reports/" + fileName + ".xlsx"));
                Response.End();
            }
        }

}