How to export databse to excel file

The presented C# program exemplifies the process of exporting database values to an Excel file. Initially, the program retrieves the data from the database and stores it in a dataset, facilitating efficient data handling. Following this step, a new Excel file is created, and the data from the dataset is written into the Excel file.

The first step involves loading the data from the Product table into a dataset, providing an organized representation of the table's contents. For further details regarding the structure and specifics of the Product table, please consult the Database Structure.

Excel file

Subsequently, a new Excel file is generated, serving as the destination for the dataset's data. This data is then seamlessly transferred to the Excel file, enabling the accurate and systematic representation of the database values.

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 1, j + 1] = data; } }
Full Source C#
using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { SqlConnection cnn ; string connectionString = null; string sql = null; string data = null; int i = 0; int j = 0; Excel.Application xlApp ; Excel.Workbook xlWorkBook ; Excel.Worksheet xlWorkSheet ; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;"; cnn = new SqlConnection(connectionString); cnn.Open(); sql = "SELECT * FROM Product"; SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn); DataSet ds = new DataSet(); dscmd.Fill(ds); for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 1, j + 1] = data; } } xlWorkBook.SaveAs("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); MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); } 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(); } } } }

Conclusion

By following this procedure, developers can effectively export database values to an Excel file, ensuring the smooth transfer and presentation of data between different platforms and applications.






Related Topics
  1. How to create Excel file in C#
  2. How to open an Excel file in C#
  3. How to read an Excel file using C#
  4. How to programmatically Add New Worksheets
  1. How to delete worksheet from an excel file
  2. How to format an Excel file using C#
  3. How to insert a picture in excel from C# App
  4. How to insert a background picture in excel
  5. How to create Excel Chart from C#
  6. How to export excel chart from C#
  7. How to excel chart in C# picturebox
  8. C# data validation input box in excel file
  9. Read and Import Excel File into DataSet or DataTable
  10. How to insert data to Excel file using OLEDB
  11. How to update data in Excel file using OLEDB
  12. How to export DataGridView to excel file