How to export dataGridView data Instantly to Excel on button click?

Question

I have 10k rows and 15 column in my data grid view. I want to export this data to an excel sheet o button click. I have already tried with the below code.

private void btExport_Click(object sender, EventArgs e)
    {
        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;                   
        app.Visible = true;
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;                  
        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-1 ; i++)
        {
            for(int j=0;j<dataGridView1.Columns.Count;j++)
            {
                if (dataGridView1.Rows[i].Cells[j].Value != null)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                else
                {
                    worksheet.Cells[i + 2, j + 1] = "";
                }
            }
        }
    }

This is working for me but it is taking lots of time to complete exporting process.

Is it possible to export from dataGridView (with 10k rows)to excel instantly on a button click?

Other than this, when I tried copy all dataGridview contents to clip board and then paste it to excel sheet manually, it happen almost instantly.

So is there a way to copy all dataGridView cells to clip board and paste it to excel sheet(with cell formatting) on a button click?

I have code for copy to clipboard as below, but I don't know how to paste it in to a new excel sheet by opening it.

        private void copyAllToolStripMenuItem_Click(object sender, EventArgs e)
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

Please help with an example. I am new to C#.

Solution 1

I solved this by simple copy and paste method. I don't know it is the best way to do this but,for me it works good and almost instantaneously. Here is my code.

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Thanks.

Solution 2

This is a great question and I was surprised at how difficult it was to find a clear and complete answer, most of the answers I found were either sudo-code or not 100% complete.

I was able to create a complete solution to copy and save the data from my DataGridView to an excel file based on Jake's answer so I'm posting my complete solution in the hopes that it can help other new comers to c# like myself :)

First off, you will need the Microsoft.Office.Interop.Excel reference in your project. See MSDN on how to add it.

My Code:

using Excel = Microsoft.Office.Interop.Excel;

private void btnExportToExcel_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Documents (*.xls)|*.xls";
    sfd.FileName = "Inventory_Adjustment_Export.xls";
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        // Copy DataGridView results to clipboard
        copyAlltoClipboard();

        object misValue = System.Reflection.Missing.Value;
        Excel.Application xlexcel = new Excel.Application();

        xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
        Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
        Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        // Format column D as text before pasting results, this was required for my data
        Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
        rng.NumberFormat = "@";

        // Paste clipboard results to worksheet range
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

        // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
        // Delete blank column A and select cell A1
        Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
        delRng.Delete(Type.Missing);
        xlWorkSheet.get_Range("A1").Select();

        // Save the excel file under the captured location from the SaveFileDialog
        xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlexcel.DisplayAlerts = true;
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);

        // Clear Clipboard and DataGridView selection
        Clipboard.Clear();
        dgvItems.ClearSelection();

        // Open the newly saved excel file
        if (File.Exists(sfd.FileName))
            System.Diagnostics.Process.Start(sfd.FileName);
    }
}

private void copyAlltoClipboard()
{
    dgvItems.SelectAll();
    DataObject dataObj = dgvItems.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

Solution 3

I did not intend to steal @Jake and @Cornelius's answer, so i tried editing it. but it was rejected. Anyways, the only improvement I have to point out is about avoiding extra blank column in excel after paste. Adding one line dataGridView1.RowHeadersVisible = false; hides so called "Row Header" which appears on the left most part of DataGridView, and so it is not selected and copied to clipboard when you do dataGridView1.SelectAll();

private void copyAlltoClipboard()
    {
        //to remove the first blank column from datagridview
        dataGridView1.RowHeadersVisible = false;
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

Solution 4

using Excel = Microsoft.Office.Interop.Excel;


private void btnExportExcel_Click(object sender, EventArgs e)
{
    try
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Visible = true;
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
        int StartCol = 1;
        int StartRow = 1;
        int j = 0, i = 0;

        //Write Headers
        for (j = 0; j < dgvSource.Columns.Count; j++)
        {
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
            myRange.Value2 = dgvSource.Columns[j].HeaderText;
        }

        StartRow++;

        //Write datagridview content
        for (i = 0; i < dgvSource.Rows.Count; i++)
        {
            for (j = 0; j < dgvSource.Columns.Count; j++)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                    myRange.Value2 = dgvSource[j, i].Value == null ? "" : dgvSource[j, i].Value;
                }
                catch
                {
                    ;
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Solution 5

Interop is slow and has other issues, using the the clipboard seems non extensible. Here are two other ways to do this

1. Work with Excel 2007+ files directly instead of working with Excel, it'll be much (much) faster. You can use OpenXML (http://openxmldeveloper.org/) which is Microsoft's SDK. The best way to learn OpenXML is to download the Productivity tool (http://www.microsoft.com/en-us/download/details.aspx?id=5124), ittakes an existing file and generates the code required to create it. Another, perhaps simpler, option is to use ClosedXML (http://closedxml.codeplex.com/). It seems a lot easier to use (look at the example http://closedxml.codeplex.com/wikipage?title=Showcase&referringTitle=Home), but I have no experience with it. I'm sure there are other libraries that wrap work with Excel.

2. Work with excel via OLEDB. This allows you to work with Excel as if it's a dababase. See http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB or Performance of OLEDB to read Excel for examples and more details.

I'd start with ClosedXML.

View more solutions

Comments

[Vadim]

If you think your solution best answers your question, you should accept it.

[Vishwanath Dalvi]

This doesn't copy the headers which is the most important to have.

[mack]

I was able to grab the header text by setting the clipboard copy mode to select headers also: ` DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText DataGridView1.MultiSelect = True DataGridView1.SelectAll()`

[Gurunadh]

@Jake, ya it is working really great and added Mack step also to copy the header row.

[1teamsah]

How can I export these to an exist Excel file's specific rows? Specific row can be determined by this: Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[15, 1]; How can I append data to an exist Excel file?

[Cornelius]

@MeysamTolouee I needed to know the same thing, I figured out how to save my results, I posted my code below.

[KSdev]

This works for me and many other people I created the Application for, but for one user it creates an image in Excel. It is not located in a Cell although it looks like it is in A1. I cannot seem to figure out anything wrong with the code. Anyone run into this issue?

[Gusstavv Gil]

// For some reason column A is always blank in the worksheet. As @Rahul mentioned below, the first blank column can be avoided in the clipboard copy by adding dataGridView1.RowHeadersVisible = false; (this hides the Row Header column). You can reshow it at the end of the clipboard copy if needed.

[Рахул Маквана]

So I am using this code, and on some machine in production it does paste an image. It looks like bitmap representation of the data being pasted by "PasteSpecial()" function. No idea how to resolve this.

[Ștefan Blaga]

How do I copy also the format and the backcolor cell from the dataGridView? The code above copy only the data, without the format of the cell (wrap cell, merge cell, backcolor cell, font )

[solujic]

Good code, but I would add it's crucial to add these 2 lines: workbook.SaveAs(ExcelFileSavePath); to save it and excel.Quit() to kill the process otherwise it's going to have that read-only message when opening Excel file

[S.I.J]

@Cornelius How do I make the copy/export process task continue in the background or make the copying asynchronous

[BrianMichaels]

Thanks. This worked for me! I up-voted this one. I had trouble in Visual Studio 2017 community edition finding the Mcrosoft.Office.Interop.Excel but if you add the Microsoft Excel 15.0 Object Library under COM in the reference manager it will add the assembly to your project.

[AlirezaK]

It worked for me. Thanks. It removes the blank rows after every row of my DataGridView.

[Pratik Bhavsar]

(for less than 65.536 rows with titles) - What does this mean? @Caveman

[Morcilla de Arroz]

@PratikBhavsar I mean that Excel only admits 65.536 rows for a sheet

[Pratik Bhavsar]

Thanks, anyway I figured exporting to CSV is handier since no additional DLLs are required.

[Nadjib Bendaoud]

Hi, your code worked like a charm, however, i would like to paste some texte in excel before the datagridview; like a title, is there a way to do that ?

[WATYF]

Don't use the clipboard. It is unreliable with larger datasets and will not always paste in all of the data.