Click here to Skip to main content
15,219,951 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi, I am new here .Also new to .net . When i am exporting data from a DataGridview to Excel using Button Click event ,it's very slow & taking much time.In future i will store mote than 3000 record . so please suggest me for fatser way. I m using windows form.

What I have tried:

{
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;
            worksheet.Name = "AssetAllocatedtoEmployee";

            for (int i = 1; i < dataGridView1.Columns.Count+1; i++)
            
            {
                worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            {

                for (int i =    0; i < dataGridView1.Rows.Count; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }

                var saveFileDialog = new SaveFileDialog();
                saveFileDialog.FileName = "AssetAllocatedtoEmployee";
                saveFileDialog.Filter = "Excel files(2007)|*.xlsx";
                if (saveFileDialog.ShowDialog()==DialogResult.OK)
                {
                    workbook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    
                }
Posted
Updated 27-May-20 2:39am
Comments
Maciej Los 24-May-20 2:57am
   
Does you DataGridView component is bind with datasource, bindingsource?
Where from data come?
Satyabrata Samantaray 24-May-20 3:40am
   
string query = "";
if (comboBox1.SelectedIndex == 10)
{
MessageBox.Show("Select the criteria to search");
}
if (comboBox1.SelectedIndex == 0)
{
query = "select * from tblAssetMaster where Branch_EmpID like '%" + textBox5.Text.Trim() + "%'";
}
else if (comboBox1.SelectedIndex == 1)
{
query = "select * from tblAssetMaster where SerialNumber like '%" + textBox5.Text.Trim() + "%'";
}
else if (comboBox1.SelectedIndex == 2)
{
query = "select * from tblAssetMaster where UINNumber like '%" + textBox5.Text.Trim() + "%'";
}
//UINNumber
// serialNumber
con.Open();
SqlCommand cmd6 = new SqlCommand(query, con);

SqlDataReader sqlReader6 = cmd6.ExecuteReader();

if (sqlReader6.HasRows)

{
DataTable dt = new DataTable();

DataTable dt1 = new DataTable();

dt.Load(sqlReader6);

dt1 = dt.DefaultView.ToTable(true);

dgv1.DataSource = dt1;

foreach (DataGridViewBand band in dgv1.Columns)
{
band.ReadOnly = true;
}

}
con.Close();
CHill60 27-May-20 8:13am
   
Not a solution to your problem but your SQL statements are vulnerable to SQL Injection attack - never concatenate strings to create sql statements. See SQL injection - Wikipedia[^]
SQL Injection Prevention · OWASP Cheat Sheet Series[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Couple of things you could try..

Assuming it's the cell by cell copy that is taking the time you could drop the
.ToString()
in
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
Or,
You could export it to a CSV first then SaveAs a .xlsx (check the formatting when you do this) - e.g. see this CodeProject article Exporting DataGridview To Excel[^].

Not a method I would advocate but some people have some success using Copy/Paste e.g. see c# - How to export dataGridView data Instantly to Excel on button click?[^] Please take care if you go down that route - make sure you are really pasting where you think you are pasting!

Another solution may be to drop the Interop and use Open XML instead - Welcome to the Open XML SDK 2.5 for Office | Microsoft Docs[^]

Or,

Why not drop the intermediate datagridview altogether and export the data from SQL directly to Excel - e.g. Export a complete database to an Excel file[^] (sorry it's in VB.NET but there are free conversion utilities on the internet)

Edit - if you want to compare timings have a look at this CP article How To: Measure execution time in C#[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100