how to Export data Grid View values into Excel using C#

Question

hello friends I have very big problem in front of me now i want to know that how can export data grid view values in Excel using C#.. if you have any idea or any demo then please send me As Soon As Possible.... Thanks in Advance

Suthish Nair · Answer

refer some different ways from sqlteam.. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Sam Hobbs · Answer

There are at least two ways to export data to Excel. You could use ADO and make a worksheet a table that you write to or you could interface with Excel directly in the manner that Gomathi Palaniswamy shows. I really think that Gomathi Palaniswamy's sample code is an excellent start. Try this. Try creating a spreadsheet in Excel manually that looks like the one you need to create in code. Then figure out what you need to do to do the equivalent using code. You say  only insert the value not Column name . I think you are not familiar with Excel enough; if you were familiar with Excel then you would understand how to interface with it in code.

Naeem Khan · Answer

thank you so much for reply me friends.. well i agree with sam and i would like to import data table from mysql to Excel... so do you have any idea what is the query from export the data table from MySql into Excel... and i know what is the Query for that but the problem with that when i m Exporting the Data into Excel.. its only insert the value not Column name so i dont know where is the Problem with this code.. i m sending you coding please check it now.. and reply me ASAP... i want to import full table with column name... // code of my sql SELECT * INTO OUTFILE 'C:\my_excel_table.csv' FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '
' FROM mytableName; Thanks in Advance

Mahesh Chand · Answer

I agree with Sam. Before we spend our time answering questions you must clearly explain the problem.

Sam Hobbs · Answer

I think you are still not getting something important. When asking questions you need to be specific and you need to be prepared to do some work yourself. If it is true that we don't understand then you need to spend more time explaining what you are trying to say. I really think however that we do understand and instead of trying to understand you are insisting that we don't understand. I suggest that you don't export from the DataGridView; export from the data that you read from MySQL. You are likely to insist that I don't understand but you are trying to do things the beginner way and I am tgrying to tell you the way to do it the way that experienced programmers would.

Naeem Khan · Answer

hello friends I think still you are not getting my problem........... i want to export data grid view data into Excel through button event.... my data is coming from MySql and now i just want to Export into Excel file... so now hope u understand what i want... Thanks In Advance

Sam Hobbs · Answer

Instead of exporting from a control such as a DataGridView think in terms of getting data from MySQL using a DataGridView or other control as the UI to update that data then writing that data out. Theare are more than one way to do all that; one possibility would be to use ADO. You can use .Net ADO to do all of it.

Mahesh Chand · Answer

I guess you do not want to use GridView to export data. In that case you need to create Excel sheet in your code and fill data one by one from your DataSet/DataTable object. Here are some articles: Generating Excel Reports using ASP.NET and C# by Sushmita Kumari on Jan 07 2006 Creating an Excel Spreadsheet Programmatically by G Gnana Arun Ganesh on Jan 09 2006 Writing Dataset to Excel in ASP.NET by Krishnan LN on Feb 28 2006 Play with the Excel Object Library using .NET by Sushmita Kumari on Mar 02 2006 More articles here ...

Naeem Khan · Answer

hello friend Thank you so much for Reply but i dont want that i want only that i have data which is coming from data base using MySql in Data Grid View.. now i just want to Export this data into Excel.. when i click on button.... so please make me code for that .. if it possible for you.... Thanks in Advance

Raghavendra U · Answer

hi try this code GridView1.DataSource = Ds; GridView1.DataBind(); Response.Clear(); Response.AddHeader(content-disposition attachment;filename=Report.xls); Response.Charset = ; Response.ContentType = application/vnd.xls; StringWriter StringWriter = new System.IO.StringWriter(); HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter); GridView1.RenderControl(HtmlTextWriter); Response.Write(StringWriter.ToString()); Response.End();

Gomathi Palaniswamy · Answer

Hi Friend Use below coding for your sample private void std_SelectedIndexChanged(object sender EventArgs e) { if (std.Text == Feebback) { { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; try { //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = true; //Get a new workbook. oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; DataTable dt = new DataTable(); string[] fields ={ totalname}; dt = opr.getrecords(tblFeedback fields); ta = dt.Rows.Count; //Add table headers going cell by cell. for (int k = 0; k { for (int i = 1; i { for (int j = 1; j { if (count { string sub = dt.Rows[k][name].ToString(); oSheet.Cells[i j] = sub.ToString(); string tot = dt.Rows[k][total].ToString(); oSheet.Cells[i + 1 j] = tot.ToString(); } count++; k++; toty = j.ToString(); } } } DisplayQuarterlySales(oSheet); } catch (Exception theException) { } } } } private void DisplayQuarterlySales(Excel._Worksheet oWS) { Excel._Workbook oWB; Excel.Series oSeries; Excel.Range oResizeRange; Excel._Chart oChart; totx = range[Convert.ToInt32(toty)] + 2; string frange =A2+:+ +totx+; //Add a Chart for the selected data. oWB = (Excel._Workbook)oWS.Parent; oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value Missing.Value Missing.Value Missing.Value); //Use the ChartWizard to create a new chart from the selected data. oResizeRange = oWS.get_Range(frange.ToString() Missing.Value).get_Resize( Missing.Valueta); oChart.ChartWizard(oResizeRange Excel.XlChartType.xl3DColumn Missing.Value Excel.XlRowCol.xlColumns Missing.Value Missing.Value Missing.Value Missing.Value Missing.Value Missing.Value Missing.Value); oSeries = (Excel.Series)oChart.SeriesCollection(1); oSeries.XValues = oWS.get_Range(A1 D1); for (int iRet = 1; iRet { oSeries = (Excel.Series)oChart.SeriesCollection(iRet); String seriesName; seriesName = =\Q; seriesName = String.Concat(seriesName iRet); seriesName = String.Concat(seriesName \); oSeries.Name = seriesName; } oChart.Location(Excel.XlChartLocation.xlLocationAsObject oWS.Name); //Move the chart so as not to cover your data. oResizeRange = (Excel.Range)oWS.Rows.get_Item(10 Missing.Value); oWS.Shapes.Item(Chart 1).Top = (float)(double)oResizeRange.Top; oResizeRange = (Excel.Range)oWS.Columns.get_Item(2 Missing.Value); oWS.Shapes.Item(Chart 1).Left = (float)(double)oResizeRange.Left; } header file using Excel = Microsoft.Office.Interop.Excel; With Regards Gomathi.P