You must Sign In to post a response.
  • Category: .NET

    How to export Dataset or DataTable to Excel using C#


    Are you looking for a way to export Dataset or DataTable to Excel using C# ? then read this thread to know more about it



    i want to transfer to my Dataset(ds) result or DataTable(dt) result into Excel File?(using C# in asp.net)
  • #732044
    Hi,

    To export data from Dataset/DataTable to Excel, try the following way.

    For this you need to add Excel object to your application

    Add reference of Microsoft.Office.Interop.Excel.dll in your project

    using Excel = Microsoft.Office.Interop.Excel;
    public static bool ExportDataTableToExcel(DataTable dt, string filepath)
    {

    Excel.Application oXL;
    Excel.Workbook oWB;
    Excel.Worksheet oSheet;
    Excel.Range oRange;

    try
    {
    // Start Excel and get Application object.
    oXL = new Excel.Application();

    // Set some properties
    oXL.Visible = true;
    oXL.DisplayAlerts = false;

    // Get a new workbook.
    oWB = oXL.Workbooks.Add(Missing.Value);

    // Get the Active sheet
    oSheet = (Excel.Worksheet)oWB.ActiveSheet;
    oSheet.Name = "Data";

    int rowCount = 1;
    foreach (DataRow dr in dt.Rows)
    {
    rowCount += 1;
    for (int i = 1; i < dt.Columns.Count + 1; i++)
    {
    // Add the header the first time through
    if (rowCount == 2)
    {
    oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
    }
    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
    }
    }

    // Resize the columns
    oRange = oSheet.get_Range(oSheet.Cells[1, 1],
    oSheet.Cells[rowCount, dt.Columns.Count]);
    oRange.EntireColumn.AutoFit();

    // Save the sheet and close
    oSheet = null;
    oRange = null;
    oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Excel.XlSaveAsAccessMode.xlExclusive,
    Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value);
    oWB.Close(Missing.Value, Missing.Value, Missing.Value);
    oWB = null;
    oXL.Quit();
    }
    catch
    {
    throw;
    }
    finally
    {
    // Clean up
    // NOTE: When in release mode, this does the trick
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    }

    return true;
    }

    Check these links

    http://www.gemboxsoftware.com/support/articles/import-export-dataset-xls-xlsx-ods-csv-html-net

    http://stackoverflow.com/questions/9478351/how-to-export-excel-from-dataset-or-datatable-in-c

    Thanks & Regards,
    Mahesh Durgam

  • #732050
    Hi,
    Please find the below code

    namespace ExcelEngine
    {
    public class DStoExcelEngine
    {
    public static void CreateExcelWorkbook(DataSet mySet, String destinationpath)
    {
    XmlDataDocument xmlDataDoc = new XmlDataDocument(mySet);
    XslTransform xtransform = new XslTransform();
    StreamReader myReader =new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), "Excel.xsl"));
    XmlTextReader textReader = new XmlTextReader(reader);
    xtransform.Load(textReader, null, null);
    StringWriter swriter = new StringWriter();
    xtransform.Transform(xmlDataDoc, null, swriter, null);
    StreamWriter myWriter = new StreamWriter (path + "\\Report.xls");
    myWriter.Write (sw.ToString());
    myWriter.Close ();
    }
    }
    }

    All is well.
    Thanks,
    Dharma
    Editor,Mentor,MVM
    Try and fail but don't fail and try

  • #732053
    hey,

    you can also use this code,

    var dataSet = new DataSet();

    // Load Excel file.
    var workbook = ExcelFile.Load("Workbook.xls");

    // Export all worksheets to DataSet.
    foreach (var worksheet in workbook.Worksheets)
    {
    // Extract the data from the worksheet to newly created DataTable starting at
    // first row and first column for 10 rows or until the first empty row appears.
    var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
    {
    StartRow = 0,
    StartColumn = 0,
    NumberOfRows = 10,
    ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
    });
    dataTable.TableName = worksheet.Name;

    // Add DataTable to DataSet.
    dataSet.Tables.Add(dataTable);
    }

    // Create new ExcelFile.
    var workbook2 = new ExcelFile();

    // Imports all tables from DataSet to new file.
    foreach (DataTable dataTable in dataSet.Tables)
    {
    // Add new worksheet to the file.
    var worksheet = workbook2.Worksheets.Add(dataTable.TableName);

    // Change the value of the first cell in the DataTable.
    dataTable.Rows[0][0] = "This is new file!";

    // Insert the data from DataTable to the worksheet starting at cell "A1".
    worksheet.InsertDataTable(dataTable,
    new InsertDataTableOptions("A1") { ColumnHeaders = true });
    }

    // Save the file to XLS format.
    workbook2.Save("DataSet.xls");



    refrence this,
    http://www.gemboxsoftware.com/support/articles/import-export-dataset-xls-xlsx-ods-csv-html-net

    thanks,
    ketan

  • #732054
    Hi Nirvana

    Pls check this below link

    http://www.dotnetgallery.com/lab/resource44-Export-to-excel-from-DataTable-using-CNET.aspx

  • #732071
    HI,


    You can try the following code. Here i am exporting data from datatbable. It will generate an excel.


    try
    {
    //first you have to create the datatable and bind the data here i used datatable as dt

    string Path = "D:\\ImportExcelFromDatabase\\myexcelfile_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + ".xls";
    FileInfo FI = new FileInfo(Path);
    StringWriter stringWriter = new StringWriter();
    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
    DataGrid DataGrd = new DataGrid();
    DataGrd.DataSource = dt;
    DataGrd.DataBind();

    DataGrd.RenderControl(htmlWrite);
    string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
    if (!Directory.Exists(directory))
    {
    Directory.CreateDirectory(directory);
    }

    System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
    stringWriter.ToString().Normalize();
    vw.Write(stringWriter.ToString());
    vw.Flush();
    vw.Close();
    WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());
    }
    catch (Exception ex)
    {
    //throw new Exception(ex.Message);
    }

    public static void WriteAttachment(string FileName, string FileType, string content)
    {
    HttpResponse Response = System.Web.HttpContext.Current.Response;
    Response.ClearHeaders();
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
    Response.ContentType = FileType;
    Response.Write(content);
    Response.End();

    }



    HOpe it will be useful.

    Regards,
    T.N. THEAAVARAAJ
    Senior Software Engineer,
    Microsoft Certified Technology Specialist.
    Email:devabe2005@gmail.com

  • #732077
    You can add this code snippet in your program Dataset or DataTable to Excel using C#.
    MyConnection = New System.Data.OleDb.OleDbConnection _("provider=Microsoft.Jet.OLEDB.4.0;" _Data Source='c:\example.xls'; " _Extended Properties=Excel 8.0;")
    MyCommand = New System.Data.OleDb.OleDbDataAdapter _("select * from [Sheet1$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "ExampleTable")

  • #732097
    thank you all of you for rplying me

  • #732802
    Hi,

    You can try with below code snippet.

           private void ExportDataToExcel()
    {
    int cols;
    DataTable dtMyPro=GetData("SELECT * FROM PRODUCT"); //Call data by quries
    StreamWriter swMyPro = new StreamWriter("D:/MyFile.csv");
    cols = dtMyPro.Columns.Count;
    for (int i = 0; i < cols - 1; i++)
    {
    swMyPro.Write(dtMyPro.Columns[i].ColumnName.ToString().ToUpper() + ",");
    }
    swMyPro.WriteLine();
    for (int i = 0; i < (dtMyPro.Rows.Count - 1); i++)
    {
    for (int j = 0; j < cols; j++)
    {
    if (dtMyPro.Rows[i][j].ToString() != null)
    {
    swMyPro.Write(dtMyPro.Rows[i][j].ToString()+",");
    }
    else
    {
    swMyPro.Write(",");
    }
    }
    swMyPro.WriteLine();
    }
    swMyPro.Close();
    }

    private static DataTable GetData(string qryTest)
    {
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection cnTest = new SqlConnection(strConnString))
    {
    using (SqlCommand cmdTest = new SqlCommand())
    {
    cmdTest.CommandText = qryTest;
    using (SqlDataAdapter sdaTest = new SqlDataAdapter())
    {
    cmdTest.Connection = cnTest;
    sdaTest.SelectCommand = cmdTest;
    using (DataSet dsTest = new DataSet())
    {
    DataTable dtTest = new DataTable();
    sdaTest.Fill(dtTest);
    return dtTest;
    }
    }
    }
    }
    }



    Best Regards
    Bashar

  • #739935
    How to export datagridview to excel format using c#?In this article I'm going to explain how to convert datagridview to excel format using c#. This is a requirement often in many projects as users may
    want to save some data generated in applications for future reference.
    Just drag and drop one datagridview and button control to your window application.


    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;

    namespace WindowsApplication1
    {
    public partial class Form1 : Form
    {
    System.Data.DataTable dtblSkill = new System.Data.DataTable();
    public Form1()
    {
    InitializeComponent();
    dtblSkill.Columns.Add("SkillCode", typeof(string));
    dtblSkill.Columns.Add("SkillName", typeof(string));
    AddGridViewSkillColumns();
    }
    private void AddGridViewSkillColumns()
    {
    try
    {

    DataGridViewTextBoxColumn dgvtxtSkillCode = new DataGridViewTextBoxColumn();
    dgvtxtSkillCode.Name = "SkillCode";
    dgvtxtSkillCode.DataPropertyName = "SkillCode";
    dgvtxtSkillCode.HeaderText = "Skill Code";
    dgvtxtSkillCode.Width = 120;


    DataGridViewTextBoxColumn dgvtxtSkillName = new DataGridViewTextBoxColumn();
    dgvtxtSkillName.Name = "SkillName";
    dgvtxtSkillName.HeaderText = "Skill Name";
    dgvtxtSkillName.DataPropertyName = "SkillName";
    dgvtxtSkillName.Visible = true;
    dgvtxtSkillName.Width = 160;


    dataGridView1.Columns.Add(dgvtxtSkillCode);
    dataGridView1.Columns.Add(dgvtxtSkillName);

    dataGridView1.DataSource = dtblSkill;
    }
    catch (Exception ex)
    {
    MessageBox.Show("Error Details" + ex.Message);
    }
    }

    private void button1_Click(object sender, EventArgs e)
    {
    Microsoft.Office.Interop.Excel.Application wapp;
    Microsoft.Office.Interop.Excel.Worksheet wsheet;
    Microsoft.Office.Interop.Excel.Workbook wbook;
    wapp = new Microsoft.Office.Interop.Excel.Application();
    wapp.Visible = false;
    wbook = wapp.Workbooks.Add(true);
    wsheet = (Worksheet)wbook.ActiveSheet;
    try
    {

    for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
    {
    wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;

    }

    for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
    {
    DataGridViewRow dgvrow = this.dataGridView1.Rows[i];
    for (int j = 0; j < dgvrow.Cells.Count; j++)
    {
    DataGridViewCell cellvalue = dgvrow.Cells[j];
    try
    {
    wsheet.Cells[i + 2, j + 1] = (cellvalue.Value == null) ? "" : cellvalue.Value.ToString();
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);
    }
    }
    }
    wapp.Visible = true;
    }
    catch (Exception ex)
    {
    MessageBox.Show("Error Details" + ex.Message);
    }
    }
    }
    }



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #745592
    If you are willing to pay for .NET API or try it for free for one month then i would recommend you to use Aspose.Cells for .NET API it allows you to import data from various resources to excel file. You can get this API from here:

    http://www.aspose.com/.net/excel-component.aspx

    And if you want to see their code samples for importing data to excel files then visit this page:

    http://www.aspose.com/docs/display/cellsnet/Importing%20Data%20to%20Worksheets

  • #767181
    There's a DLL library called DataTableToPDF from GusimoSoft (http://www.gusimosoft.tk) that can export a DataTable to a complete PDF report and even include a logo, title, totals, exclude columns, etc. It's very cool.

  • #767192
    Refer below link to export datatable to excel or excel to datatale:
    http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html


  • Sign In to post your comments