C# DataGridView Export to Excel

Presenting data in a tabular format is an endeavor that often arises in various scenarios. Whether you are managing complex datasets or showcasing organized information, the task of displaying data in tables is likely to be a frequent requirement. In such instances, the DataGridView control emerges as an indispensable tool within the scope of Windows Forms.

Export to Excel

The DataGridView control is thoughtfully engineered to serve as a comprehensive and all-encompassing solution for effortlessly showcasing tabular data. Its design philosophy revolves around versatility, ensuring that it can adapt to diverse use cases with utmost efficacy. This control distinguishes itself through its exceptional configurability and extensibility, providing a vast array of properties, methods, and events that empower developers to fine-tune every aspect of its appearance and behavior.

The following C# source code shows how to Export the content of a datagridview to an Excel file.

Full Source C#
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connectionString = "Data Source=.;Initial Catalog=pubs;Integrated Security=True"; string sql = "SELECT * FROM Authors"; SqlConnection connection = new SqlConnection(connectionString); SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection); DataSet ds = new DataSet(); connection.Open(); dataadapter.Fill(ds, "Authors_table"); connection.Close(); dataGridView1.DataSource = ds; dataGridView1.DataMember = "Authors_table"; } private void button2_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; Int16 i, j; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (i = 0; i <= dataGridView1.RowCount - 2; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { xlWorkSheet.Cells[i + 1, j + 1] = dataGridView1[j, i].Value.ToString(); } } xlWorkBook.SaveAs(@"c:\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } } } }

The DataGridView control bestows upon developers an unparalleled degree of control over the way data is presented and interacted with. Whether it entails modifying visual attributes such as colors and fonts, defining custom behaviors for user interactions, or implementing complex data manipulation logic, this control offers a wealth of options to tailor the tabular display to precise specifications.

Conclusion

With the DataGridView control at your disposal, the arduous task of rendering tabular data becomes a streamlined and efficient process. Its flexible nature ensures that it can effortlessly accommodate a diverse range of data types and structures. Whether you are presenting financial records, product listings, or statistical information, the DataGridView control furnishes the necessary tools to transform raw data into visually appealing and highly functional tabular representations.