Post
Ask Question

How to Download Database to Excel Sheet in C#.NET

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Windows.Forms;  
  9. using System.IO;  
  10. using System.Data.OleDb;  
  11. using ExcelLibrary.SpreadSheet;  
  12. using Excel = Microsoft.Office.Interop.Excel;  
  13. using Microsoft.Office.Interop.Excel;  
  14. namespace ClientManagementApp   
  15. {  
  16.     public partial class FrmReport: Form   
  17.     {  
  18.         DataSet dstResults = new DataSet();  
  19.         DataView myView;  
  20.         DataSet dstConverted = new DataSet();  
  21.         DataSet dstKeyData = new DataSet();  
  22.         DataView myConvertedView;  
  23.         DataView myKeyDataView;  
  24.         DataSet dataSet = null;  
  25.         public FrmReport()  
  26.         {  
  27.             InitializeComponent();  
  28.             BindDataGrid();  
  29.         }  
  30.         private void BindDataGrid()   
  31.         {  
  32.             // DataTable dt = new DataTable();  
  33.             System.Data.DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");  
  34.             System.Data.DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");  
  35.             dataGridView1.DataSource = dtConverted;  
  36.             dataGridView2.DataSource = dtKeyData;  
  37.             // dgvResults.DataSource = dt;  
  38.         }  
  39.         public System.Data.DataTable DataGridView2DataTable(DataGridView dgv, String tblName, int minRow = 0)   
  40.         {  
  41.             System.Data.DataTable dt = new System.Data.DataTable(tblName);  
  42.             // Header columns  
  43.             foreach(DataGridViewColumn column in dgv.Columns)   
  44.             {  
  45.                 DataColumn dc = new DataColumn(column.Name.ToString());  
  46.                 dt.Columns.Add(dc);  
  47.             }  
  48.             // Data cells  
  49.             for (int i = 0; i < dgv.Rows.Count; i++)   
  50.             {  
  51.                 DataGridViewRow row = dgv.Rows[i];  
  52.                 DataRow dr = dt.NewRow();  
  53.                 for (int j = 0; j < dgv.Columns.Count; j++)   
  54.                 {  
  55.                     dr[j] = (row.Cells[j].Value == null) ? "" : row.Cells[j].Value.ToString();  
  56.                 }  
  57.                 dt.Rows.Add(dr);  
  58.             }  
  59.             // Related to the bug arround min size when using ExcelLibrary for export  
  60.             for (int i = dgv.Rows.Count; i < minRow; i++)   
  61.             {  
  62.                 DataRow dr = dt.NewRow();  
  63.                 for (int j = 0; j < dt.Columns.Count; j++)   
  64.                 {  
  65.                     dr[j] = " ";  
  66.                 }  
  67.                 dt.Rows.Add(dr);  
  68.             }  
  69.             return dt;  
  70.         }  
  71.         Excel.Application ExcelApp;  
  72.         private void Btngenerate_Click(object sender, EventArgs e)   
  73.         {  
  74.             int maxs = 1;  
  75.             FileName: int max = 0;  
  76.             progressBar1.Minimum = 0;  
  77.             ++max;  
  78.             progressBar1.Maximum = max;  
  79.             progressBar1.Visible = true;  
  80.             lbl_wait.Visible = true;  
  81.             // Create a DataSet and put both tables in it.  
  82.             System.Data.DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");  
  83.             System.Data.DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");  
  84.             dataSet = new DataSet("General");  
  85.             dataSet.Tables.Add(dtConverted);  
  86.             dataSet.Tables.Add(dtKeyData);  
  87.             try   
  88.             {  
  89.                 ExcelApp = new Excel.Application();  
  90.                 Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);  
  91.                 // Loop over DataTables in DataSet.  
  92.                 DataTableCollection collection = dataSet.Tables;  
  93.                 for (int i = collection.Count; i > 0; i--)   
  94.                 {  
  95.                     Sheets xlSheets = null;  
  96.                     Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;  
  97.                     //xlWorksheet.Name = "Mandate Summary";  
  98.                     //Create Excel Sheets  
  99.                     xlSheets = ExcelApp.Sheets;  
  100.                     xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet) xlSheets.Add(xlSheets[1],  
  101.                     Type.Missing, Type.Missing, Type.Missing);  
  102.                     System.Data.DataTable table = collection[i - 1];  
  103.                     xlWorksheet.Name = table.TableName;  
  104.                     for (int j = 1; j < table.Columns.Count + 1; j++)   
  105.                     {  
  106.                         ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;  
  107.                     }  
  108.                     // Storing Each row and column value to excel sheet  
  109.                     for (int k = 0; k < table.Rows.Count; k++)   
  110.                     {  
  111.                         for (int l = 0; l < table.Columns.Count; l++) {  
  112.                             ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString();  
  113.                         }  
  114.                     }  
  115.                     ExcelApp.Columns.AutoFit();  
  116.                 }  
  117.                 ((Microsoft.Office.Interop.Excel.Worksheet) ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();  
  118.                 ExcelApp.Visible = true;  
  119.                 //xlWorkbook.SaveAs("D:\\ClientData.xls");  
  120.                 xlWorkbook.SaveAs(@  
  121.                 "D:\ClientData-CR-DR-ALL-" + DateTime.Now.ToString("ddMMyyyy") + "-INP-" + maxs.ToString().PadLeft(6, '0') + ".xls");  
  122.                 lblreport.Text = "Report Generate Successfully";  
  123.             }   
  124.             catch (Exception ex)   
  125.             {  
  126.                 MessageBox.Show(ex.Message);  
  127.             }++maxs;  
  128.             progressBar1.Value++;  
  129.             // goto FileName;  
  130.         }  
  131.         private void FrmReport_Load(object sender, EventArgs e)   
  132.         {  
  133.             //ReadData("SELECT ID,BankName,ContactPersonName,ContactNumber,EmailId,PORecieved,PayementRecievedDate,Remark,EToken,DigitalCertificate FROM tbl_ClientDetails",  
  134.             ReadData("SELECT ID,NAME,AMT,AMC_INC,AMC,PAYMENT_RECVD,PayementRecievedDate,NARAT,VAT,S_T FROM tbl_ClientDetails",  
  135.             ref dstConverted, "Converted");  
  136.             myConvertedView = ((System.Data.DataTable) dstConverted.Tables["Converted"]).DefaultView;  
  137.             ReadData("Select ID,NAME,AMT,W_E_F,PAYMENT_RECVD_vat,DSC_2,DSC_3 from tbl_ClientDetails", ref dstKeyData, "KeyData");  
  138.             myKeyDataView = ((System.Data.DataTable) dstKeyData.Tables["KeyData"]).DefaultView;  
  139.             dataGridView1.DataSource = myConvertedView;  
  140.             dataGridView2.DataSource = myKeyDataView;  
  141.             // dataGridView1.Refresh();  
  142.         }  
  143.         public void ReadData(string strSQL, ref DataSet dstMain, string strTableName = "default")   
  144.         {  
  145.             try  
  146.             {  
  147.                 string connectionString = @  
  148.                 " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ClientMgmtDb1.accdb;";  
  149.                 // OleDbConnection cnn = conection();  
  150.                 OleDbConnection cnn = new OleDbConnection(connectionString);  
  151.                 OleDbCommand cmd = new OleDbCommand(strSQL, cnn);  
  152.                 cnn.Open();  
  153.                 OleDbDataAdapter da = new OleDbDataAdapter(cmd);  
  154.                 da.Fill(dstMain, strTableName);  
  155.                 da.Dispose();  
  156.                 cnn.Close();  
  157.             }  
  158.             catch (Exception ex)   
  159.             {  
  160.                 Console.WriteLine(ex.ToString());  
  161.             }  
  162.         }  
  163.     }  
  164. }