Export DataGridView To Excel In C#

Not functional, either the poster did not test their code or posted incomplete code.

System.Runtime.InteropServices.COMException: 'Index non valide. (Exception de HRESULT : 0x8002000B (DISP_E_BADINDEX))'on( worksheet = workbook.Sheets["Sheet1"];)

Microsoft Office Excel cannot access the file 'c:\62317500'. There are several possible reasons:

Exports to exel, but error appears when saving

Data is not exported in excel file. It is showing The name 'dataGridView1' does not exist in the current context. the data where i want to export is kendo grid.

I have an error where if I change the Row to Items , I get an error on the Cells part./////// worksheet.Cells[i + 2, j + 1] = Grid.Items[i].Cells[j].Value.ToString(); it says that Grid does not contain definition fro Cells

Error message "Object reference not set to an instance of an object." on worksheet.Cells[1, i] = dg.Columns[i - 1].HeaderText;

// storing Each row and column value to excel sheet for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); }

how to export the particular data values not entire datagridview?

Error in (worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; ) as Error 2 Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel._Worksheet'. An explicit conversion exists (are you missing a cast?)

How to add a footer ie last column added by cell formatting

Https://stackoverflow.com/a/45268787/10132451

Thank you working fine.

Thanks a lot

Thanks a lot. That's so effective for me.

Getting error in below line:-// store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet;

// Copy all to clipboard dataGridViewLocationMst.SelectAll(); DataObject dataObj = dataGridViewLocationMst.GetClipboardContent(); if (dataObj != null) Clipboard.SetDataObject(dataObj); // Paste in Excel 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 Microsoft.Office.Interop.Excel.Application(); xlexcel.Visible = true; xlWorkBook = xlexcel.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

(dataGridViewLocationMst) is my datagridview object. Didn't have time to rename for this paste.

There is a much faster and more efficient way of doing this so limit the loading time to almost 0.

Good Work. Working fine for me.

can i use the same code for web forms??

getting error like Exception from HRESULT: 0x800AC472

How to change the datatype of excel column as time span at the time of exporting the data

Yes Bruce is the same thing i was did it in my project, Otherwise there is no another way to do.

pramod, I received the same error. I got whenever I would try to do anything with the worksheet while it was still loading. So to fix it, I moved the app.Visible=true; till after the for loops. However, this entire method is still really slow for me. I'm exporting 1418 rows and it takes 17 seconds

to be more explicit i want to save it where the .exe file is :) (i want the same that you can do with the data base ... if you put it in the same folder with the .exe file you don't have to give the path to it ... just the name)

any idea how can i make it save the file in the bin/debug directory ? except giving the full path... if i use "output.xls" instead of "c:\\output.xls" it saves the file to MyDocuments

Yes i got it the error actually i was trying to scroll it vertically while filling the excel sheet because i am using this code as it is ......but it take to much time to fill excel sheet i have approximately more than 1 Lakh record in datagridview

once close the solution and re open it pramod even itoo got it

getting error like Exception from HRESULT: 0x800AC472

plz any one help me?

i m getting an error when i embed it in my project worksheet = workbook.Sheets["Sheet1"];worksheet = workbook.ActiveSheet;it Generate Error Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel._Worksheet'. An explicit conversion exists (are you missing a cast?) Please help me with this problem........... but when i use this separately than it works well... .

hi....am using sql server 2005 as my back end...am trying to export excel file from gridview...it works well....but the date field is not displaying the date instead of that displays like ###### in excel sheet and phone no displays like exponential notation .....how can i solve this problem...

If i fill gridview from sql database, then how i can i modify the code. i tried in several ways. but i failed to export. only header text is visible in excel. please help me

Thanks for this post but when i use this code i got problem with these lines,worksheet = workbook.Sheets["Sheet1"];worksheet = workbook.ActiveSheet;it Generate Error Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel._Worksheet'. An explicit conversion exists (are you missing a cast?) Please help me with this problem. Thanks & Regards, Shourya V. Sengar

Thank

Hi Hiren Useful Article..Thanks for sharing :)

while i using this above code, i have got problem in the cell alignments. i have four columns in a table , one column for date and others for data. but during the excel transfer the date column is not in proper alignment .. first 12 date in right alignment and balance dates are in left alignment .. how can i make it even.

here is the better way... Public Sub user_autosave_game_all() Dim lsz_exportallpath As String = My.Computer.FileSystem.SpecialDirectories.Desktop Grd_Table.ExportToXls(lsz_exportallpath & "\ xls111.xls") System.Diagnostics.Process.Start(lsz_exportallpath & "\ xls111.xls") frm_main.bb_start.Enabled = True End Sub email: [email protected]

How can we save the data to the same excel worksheet and ignore duplicates in the excel worksheet?

one problem here is that in the above program where is the "saveFileExcel" declared Contents added by Duong on Dec 02, 2010 please give some clarity

it worked!... how about cell size and merging of cells...???

it worked!... how about cell size and merging of cells...???

it worked!... how about cell size and merging of cell...???

After adding reference to COM object as shown above........include using Excel = Microsoft.Office.Interop.Excel; and then just copy and paste this code....it surely works....i too tried Microsoft.Office.Interop.Excel.Application wapp; Microsoft.Office.Interop.Excel.Worksheet wsheet; Microsoft.Office.Interop.Excel.Workbook wbook; wapp = new Microsoft.Office.Interop.Excel.Application(); wapp.Visible = false; wbook = wapp.Workbooks.Add(true); wsheet = (Excel.Worksheet)wbook.ActiveSheet; try { //int iX; //int iY; int i; for (i = 0; i < this.dataGridView1.Columns.Count; i++) { wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText; //System.Drawing.FontStyle.Bold; } wsheet.get_Range(wsheet.Cells[1, 1], wsheet.Cells[1, i]).Font.Bold = true; for (i = 0; i < this.dataGridView1.Rows.Count; i++) { DataGridViewRow row = this.dataGridView1.Rows[i]; for (int j = 0; j < row.Cells.Count; j++) { DataGridViewCell cell = row.Cells[j]; try { wsheet.Cells[i + 2, j + 1] = (cell.Value == null) ? "" : cell.Value.ToString(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } wapp.Visible = true; } catch (Exception ex1) { MessageBox.Show(ex1.Message); }

I m beginer in this field, So i got some eror related to this article. That was name space error. Please help me to solve this error .Thanks in advance. my code is using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using Excel = Microsoft.Office.Interop.Excel; namespace datagridview { public partial class Form1 : Form { int cntr = 0; OleDbConnection con =new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=E:\\datagridview\\db1.mdb"); public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'db1DataSet.tokendetails' table. You can move, or remove it, as needed. this.tokendetailsTableAdapter.Fill(this.db1DataSet.tokendetails); } private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) { if (cntr % 2 == 0) dataGridView1.Sort(dataGridView1.Columns[e.ColumnIndex], ListSortDirection.Ascending); else dataGridView1.Sort(dataGridView1.Columns[e.ColumnIndex], ListSortDirection.Descending); cntr++; } private void button4_Click(object sender, EventArgs e) { // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program //Funny app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet try { //Fixed:(Microsoft.Office.Interop.Excel.Worksheet) worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; // changing the name of active sheet worksheet.Name = "Exported from Ketoan"; // storing header part in Excel for (int i = 1; i < dataGridView1 .Columns .Count + 1; i++) { worksheet.Cells[1, i] = dataGridView1 .Columns [i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < dataGridView1 .Rows .Count - 1; i++) { for (int j = 0; j < dataGridView1 .Columns .Count; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1 .Rows [i].Cells[j].Value.ToString(); } } // save the application string fileName = String.Empty; //SaveFileDialog saveFileDialog1 = new SaveFileDialog(); SaveFileDialog saveExcel = new saveExcel(); // SaveFileDialog saveFileExcel = new saveFileExcel(); saveExcel .Filter = "Excel files | *.xls|All files(*.*)|*.*"; saveExcel.FilterIndex = 2; saveExcel .RestoreDirectory = true; //saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*"; //saveFileExcel.FilterIndex = 2; //saveFileExcel.RestoreDirectory = true; if (saveExcel .ShowDialog() == DialogResult.OK) { fileName = saveExcel .FileName; //Fixed-old code :11 para->add 1:Type.Missing workbook.SaveAs("export", 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); } else return; // Exit from the application //app.Quit(); } catch (System.Exception ex) { } finally { app.Quit(); workbook = null; app = null; } } } }

nice article, it helps me alot

I am a beginner in this field so i got a few error related to this article . so please cleare my doubt . when i use this code like worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; i got Error Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel._Worksheet'. An explicit conversion exists (are you missing a cast?) when i use save command the error like it takes 11 argument. my big doubt is i used sql datatatable to select query from table then i got Error 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel.DataTable' how i remove this error . its very urgent