Search

Friday, January 8, 2016
0 comments

Export DataGridView to Excel using OpenXML and ClosedXML

10:08 PMFriday, January 8, 2016
In this article, I will explain you, How to export data of DataGridView to Excel file. In this article, I will use OpenXML and ClosedXml libraries for export data. We can also export data of DataGridView using Foreach loop by fetching each row of it. To do this task, first of all Download two assemblies i.e :

  1. Open XML SDK 2.0 For Microsoft Office 
  2. Closed XML

Now, Add windows form into your project. Add a DataGridView and single button on it.

using System;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;

namespace Export_DataTable_Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.BindData();
        }

        private void BindData()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[3] { new DataColumn("StudentId", typeof(int)),
            new DataColumn("StudentName", typeof(string)),
            new DataColumn("StudentCity",typeof(string)) });
            dt.Rows.Add(1, "Jacob lefore", "US");
            dt.Rows.Add(2, "Ammey smith", "UK");
            dt.Rows.Add(3, "Bill Smith", "Fr");
            dt.Rows.Add(4, "Robert", "Ru");
            this.dataGridView1.DataSource = dt;
        }

        private void ExportExcel_Click(object sender, EventArgs e)
        {
     
            DataTable dt = new DataTable();

 
            foreach (DataGridViewColumn column in dataGridView1.Columns)
            {
                dt.Columns.Add(column.HeaderText, column.ValueType);
            }

   
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                dt.Rows.Add();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                }
            }

            //Exporting to Excel
            string folderPath = "C:\\my\\";
            if (!Directory.Exists(folderPath))
            {
                Directory.CreateDirectory(folderPath);
            }
            using (XLWorkbook xlfile = new XLWorkbook())
            {
               xlfile.Worksheets.Add(dt, "students");
               xlfile.SaveAs(folderPath + "data.xlsx");
            }
        }
    }
}

Code Generates the following output

Export DataGridView to Excel using OpenXML and ClosedXML

Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top