Export DataGridView to Excel without CheckBox using C# and VB.Net in Windows Application

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Sep 30, 2019 08:17 AM
Forum: Windows Basics
Answer: 1
Views: 3943
Sample Code: Download

I have displayed data in the datagridview with the check box

now I want to export it into excel. how to export it in excel using closed XML library

my code is following

CheckBox headerCheckBox = new CheckBox();
private void DisplayData()
{
    dGV.AllowUserToAddRows = false;
    dGV.Columns.Clear();
    con = new SqlDbConnect();
    con.SqlQuery(@"select distinct se.MarksID,SessionName,ExamType,ClassName,SectionName,SubjectName,se.AdmissionNo,sr.SName,sr.RollNo,Max,Mark from tblSetMarks as se
        inner join tblSession as ses on se.SessionID=ses.SessionID
        inner join tblSetExam as st on se.SetExamID=st.SetExamID
        inner join tblAssignSubjects as si on se.SubjectID=si.ASID
        inner join tblDefSubject as des on si.SubjectID=des.SubjectID
        inner join tblStdReg as sr on se.AdmissionNo= sr.AdmissionNo
        inner join tblDefClass as dc on sr.ClassID=dc.ClassID
        inner join tblDefSection as ds on sr.SectionID= ds.SectionID
        where se.SessionID=@SesId and se.SetExamID=@EId and se.ClassID=@CId and se.SectionID=@SId and se.SubjectID=@SbId order by AdmissionNo asc;");
    con.Cmd.Parameters.Add(new SqlParameter("@SesId", this.cmbSession.SelectedValue.ToString()));
    con.Cmd.Parameters.Add(new SqlParameter("@EId", this.cmbExam.SelectedValue.ToString()));
    con.Cmd.Parameters.Add(new SqlParameter("@CId", this.cmbClass.SelectedValue.ToString()));
    con.Cmd.Parameters.Add(new SqlParameter("@SId", this.cmbSection.SelectedValue.ToString()));
    con.Cmd.Parameters.Add(new SqlParameter("@SbId", this.cmbSubjects.SelectedValue.ToString()));
    paging.SelectCommand = con.Cmd;
    sBuilder = new SqlCommandBuilder(paging);
    ds = new DataSet();
    paging.Fill(ds, "tblSetMarks");
    sTable = ds.Tables["tblSetMarks"];
    dGV.DataSource = ds.Tables["tblSetMarks"].DefaultView;
    //dGV.ReadOnly = true;

    Point headerCellLocation = this.dGV.GetCellDisplayRectangle(0, -1, true).Location;
    headerCheckBox.Location = new Point(headerCellLocation.X + 8, headerCellLocation.Y + 1);
    headerCheckBox.BackColor = System.Drawing.Color.White;
    headerCheckBox.Size = new Size(18, 18);

    headerCheckBox.Click += new EventHandler(HeaderCheckBox_Clicked);
    dGV.Controls.Add(headerCheckBox);

    DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
    checkBoxColumn.HeaderText = "";
    checkBoxColumn.Width = 30;
    checkBoxColumn.Name = "checkBoxColumn";
    dGV.Columns.Insert(0, checkBoxColumn);

    dGV.Columns[1].Width = 80;
    dGV.Columns[2].Width = 100;
    dGV.Columns[3].Width = 100;
    dGV.Columns[4].Width = 100;
    dGV.Columns[5].Width = 100;
    dGV.Columns[6].Width = 200;
    dGV.Columns[7].Width = 100;
    dGV.Columns[8].Width = 200;
    dGV.Columns[9].Width = 80;
    dGV.Columns[10].Width = 80;
    dGV.Columns[11].Width = 80;
    dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
    dGV.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
    dGV.RowsDefaultCellStyle.BackColor = System.Drawing.Color.GhostWhite;
    dGV.AlternatingRowsDefaultCellStyle.BackColor = System.Drawing.Color.GhostWhite;
    dGV.CellBorderStyle = DataGridViewCellBorderStyle.Single;
    dGV.DefaultCellStyle.SelectionBackColor = System.Drawing.Color.ForestGreen;
    dGV.DefaultCellStyle.SelectionForeColor = System.Drawing.Color.White;
    dGV.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
    dGV.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
    dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
    dGV.AllowUserToResizeColumns = true;
}
private void HeaderCheckBox_Clicked(object sender, EventArgs e)
{
    dGV.EndEdit();
    foreach (DataGridViewRow row in dGV.Rows)
    {
        DataGridViewCheckBoxCell checkBox = (row.Cells["checkBoxColumn"] as DataGridViewCheckBoxCell);
        checkBox.Value = headerCheckBox.Checked;
    }
}

private void DataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.RowIndex >= 0 && e.ColumnIndex == 0)
    {
        bool isChecked = true;
        foreach (DataGridViewRow row in dGV.Rows)
        {
            if (Convert.ToBoolean(row.Cells["checkBoxColumn"].EditedFormattedValue) == false)
            {
                isChecked = false;
                break;
            }
        }
        headerCheckBox.Checked = isChecked;
    }
}

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 4

Reply
ANSWER
User: pandeyism
Replied: on Oct 01, 2019 04:06 AM
Report

Hi smile,

Refer below sample.

Namespaces

using System.IO;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Reflection
Imports ClosedXML.Excel
Imports System.Data.SqlClient

Code

private void Form1_Load(object sender, EventArgs e)
{
    this.BindDataGridView();
    DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
    checkBoxColumn.HeaderText = "";
    checkBoxColumn.Width = 30;
    checkBoxColumn.Name = "checkBoxColumn";
    dataGridView1.Columns.Insert(0, checkBoxColumn);
}

private void BindDataGridView()
{
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM CustomerTest", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }
    }
}

private void btnExportExcel_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    for (int i = 1; i < dataGridView1.Columns.Count; i++)
    {
        dt.Columns.Add(dataGridView1.Columns[i].HeaderText, dataGridView1.Columns[i].ValueType);
    }
    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        dt.Rows.Add();
        for (int j = 1; j < dataGridView1.Rows[i].Cells.Count; j++)
        {
            dt.Rows[dt.Rows.Count - 1][dataGridView1.Rows[i].Cells[j - 1].ColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
        }
    }

    string folderPath = "C:\\Excel\\";
    if (!Directory.Exists(folderPath))
    {
        Directory.CreateDirectory(folderPath);
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt, "Customers");
        wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
    }
}

VB.Net

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    Me.BindDataGridView()
    Dim checkBoxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()
    checkBoxColumn.HeaderText = ""
    checkBoxColumn.Width = 30
    checkBoxColumn.Name = "checkBoxColumn"
    dataGridView1.Columns.Insert(0, checkBoxColumn)
End Sub

Private Sub BindDataGridView()
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("SELECT * FROM CustomerTest", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub

Private Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportExcel.Click
    Dim dt As DataTable = New DataTable()
    For i As Integer = 1 To dataGridView1.Columns.Count - 1
        dt.Columns.Add(dataGridView1.Columns(i).HeaderText, dataGridView1.Columns(i).ValueType)
    Next
    For i As Integer = 0 To dataGridView1.Rows.Count - 1
        dt.Rows.Add()
        For j As Integer = 1 To dataGridView1.Rows(i).Cells.Count - 1
            dt.Rows(dt.Rows.Count - 1)(dataGridView1.Rows(i).Cells(j - 1).ColumnIndex) = dataGridView1.Rows(i).Cells(j).Value.ToString()
        Next
    Next
    Dim folderPath As String = "C:\Excel\"
    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If
    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dt, "Customers")
        wb.SaveAs(folderPath & "DataGridViewExport.xlsx")
    End Using
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Export DataGridView to Excel without CheckBox using C# and VB.Net in Windows Application

Company

Explore

Follow us

Contact Us