How to export dataGridView data Instantly to Excel on button click?
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#.
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.
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();
}
}
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);
}