Exporting Dataset to Individual Excel to Sheets (Windows Application)

  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 max = 0;  
  75.             progressBar1.Minimum = 0;  
  76.             ++max;  
  77.             progressBar1.Maximum = max;  
  78.             progressBar1.Visible = true;  
  79.             lbl_wait.Visible = true;  
  80.             // Create a DataSet and put both tables in it.  
  81.             System.Data.DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");  
  82.             System.Data.DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");  
  83.             dataSet = new DataSet("General");  
  84.             dataSet.Tables.Add(dtConverted);  
  85.             dataSet.Tables.Add(dtKeyData);  
  86.             try  
  87.             {  
  88.                 ExcelApp = new Excel.Application();  
  89.                 Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);  
  90.                 // Loop over DataTables in DataSet.  
  91.                 DataTableCollection collection = dataSet.Tables;  
  92.                 for (int i = collection.Count; i > 0; i--)  
  93.                 {  
  94.                     Sheets xlSheets = null;  
  95.                     Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;  
  96.                     //xlWorksheet.Name = "Mandate Summary";  
  97.                     //Create Excel Sheets  
  98.                     xlSheets = ExcelApp.Sheets;  
  99.                     xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],  
  100.                     Type.Missing, Type.Missing, Type.Missing);  
  101.                     System.Data.DataTable table = collection[i - 1];  
  102.                     xlWorksheet.Name = table.TableName;  
  103.                     for (int j = 1; j < table.Columns.Count + 1; j++)  
  104.                     {  
  105.                         ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;  
  106.                     }  
  107.                     // Storing Each row and column value to excel sheet  
  108.                     for (int k = 0; k < table.Rows.Count; k++)  
  109.                     {  
  110.                         for (int l = 0; l < table.Columns.Count; l++)  
  111.                         {  
  112.                             ExcelApp.Cells[k + 2, l + 1] =  
  113.                             table.Rows[k].ItemArray[l].ToString();  
  114.                         }  
  115.                     }  
  116.                     ExcelApp.Columns.AutoFit();  
  117.                 }  
  118.                 ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();  
  119.                 ExcelApp.Visible = true;  
  120.                 xlWorkbook.SaveAs("D:\\abc.xls");  
  121.             }  
  122.             catch (Exception ex)  
  123.             {  
  124.                 MessageBox.Show(ex.Message);  
  125.             }  
  126.             progressBar1.Value++;  
  127.             // int max = 0;  
  128.             // progressBar1.Minimum = 0;  
  129.             // ++max;  
  130.             // progressBar1.Maximum = max;  
  131.             // progressBar1.Visible = true;  
  132.             // lbl_wait.Visible = true;  
  133.             //// DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");  
  134.             //// DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");  
  135.             // //if (dgvResults.Rows.Count > 0)  
  136.             // //{  
  137.             // try  
  138.             // {  
  139.             // // Bind Grid Data to Datatable  
  140.             // //DataTable dt = new DataTable();  
  141.             // DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");  
  142.             // DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");  
  143.             // foreach (DataGridViewColumn col in dgvResults.Columns)  
  144.             // {  
  145.             // dtConverted.Columns.Add(col.HeaderText, col.ValueType);  
  146.             // }  
  147.             // int count = 0;  
  148.             // foreach (DataGridViewRow row in dgvResults.Rows)  
  149.             // {  
  150.             // if (count < dgvResults.Rows.Count - 1)  
  151.             // {  
  152.             // dtConverted.Rows.Add();  
  153.             // dtKeyData.Rows.Add();  
  154.             // foreach (DataGridViewCell cell in row.Cells)  
  155.             // {  
  156.             // dtConverted.Rows[dtConverted.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();  
  157.             // dtKeyData.Rows[dtKeyData.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();  
  158.             // }  
  159.             // }  
  160.             // count++;  
  161.             // }  
  162.             // // Bind table data to Stream Writer to export data to respective folder  
  163.             // StreamWriter wr = new StreamWriter(@"\ClientData-CR-ALL-17022010-INP-" + max.ToString().PadLeft(6, '0') + ".xls");  
  164.             // // StreamWriter wr = new StreamWriter(@"D:\\ClientData.xls");  
  165.             // // Write Columns to excel file  
  166.             // for (int i = 0; i < dtConverted.Columns.Count; i++)  
  167.             // {  
  168.             // wr.Write(dtConverted.Columns[i].ToString().ToUpper() + "\t");  
  169.             // }  
  170.             // wr.WriteLine();  
  171.             // //write rows to excel file  
  172.             // for (int i = 0; i < (dtConverted.Rows.Count); i++)  
  173.             // {  
  174.             // for (int j = 0; j < dtConverted.Columns.Count; j++)  
  175.             // {  
  176.             // if (dtConverted.Rows[i][j] != null)  
  177.             // {  
  178.             // wr.Write(Convert.ToString(dtConverted.Rows[i][j]) + "\t");  
  179.             // }  
  180.             // else  
  181.             // {  
  182.             // wr.Write("\t");  
  183.             // }  
  184.             // }  
  185.             // wr.WriteLine();  
  186.             // }  
  187.             // wr.Close();  
  188.             // label1.Text = "Data Exported Successfully";  
  189.             // }  
  190.             // catch (Exception ex)  
  191.             // {  
  192.             // throw ex;  
  193.             // }  
  194.             // //}  
  195.             // progressBar1.Value++;  
  196.         }  
  197.         private void FrmReport_Load(object sender, EventArgs e)  
  198.         {  
  199.             ReadData("SELECT ID,BankName,ContactPersonName,ContactNumber,EmailId,PORecieved,PayementRecievedDate,Remark,EToken,DigitalCertificate FROM tbl_ClientDetails",  
  200.             ref dstConverted, "Converted");  
  201.             myConvertedView = ((System.Data.DataTable)dstConverted.Tables["Converted"]).DefaultView;  
  202.             ReadData("Select ID,BankName,InstalledDate,UserKey,ProductKey,Remark,EToken,DigitalCertificate from tbl_ClientDetails", ref dstKeyData, "KeyData");  
  203.             myKeyDataView = ((System.Data.DataTable)dstKeyData.Tables["KeyData"]).DefaultView;  
  204.             dataGridView1.DataSource = myConvertedView;  
  205.             dataGridView2.DataSource = myKeyDataView;  
  206.             // dataGridView1.Refresh();  
  207.         }  
  208.         public void ReadData(string strSQL, ref DataSet dstMain, string strTableName = "default")  
  209.         {  
  210.             try  
  211.             {  
  212.                 string connectionString = @" Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ClientMgmtDb.accdb;";  
  213.                 // OleDbConnection cnn = conection();  
  214.                 OleDbConnection cnn = new OleDbConnection(connectionString);  
  215.                 OleDbCommand cmd = new OleDbCommand(strSQL, cnn);  
  216.                 cnn.Open();  
  217.                 OleDbDataAdapter da = new OleDbDataAdapter(cmd);  
  218.                 da.Fill(dstMain, strTableName);  
  219.                 da.Dispose();  
  220.                 cnn.Close();  
  221.             }  
  222.             catch (Exception ex)  
  223.             {  
  224.                 Console.WriteLine(ex.ToString());  
  225.             }  
  226.         }  
  227.     }  
  228. }