using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using ExcelLibrary.SpreadSheet;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
namespace ClientManagementApp
{
public partial class FrmReport : Form
{
DataSet dstResults = new DataSet();
DataView myView;
DataSet dstConverted = new DataSet();
DataSet dstKeyData = new DataSet();
DataView myConvertedView;
DataView myKeyDataView;
DataSet dataSet = null;
public FrmReport()
{
InitializeComponent();
BindDataGrid();
}
private void BindDataGrid()
{
// DataTable dt = new DataTable();
System.Data.DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");
System.Data.DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");
dataGridView1.DataSource = dtConverted;
dataGridView2.DataSource = dtKeyData;
// dgvResults.DataSource = dt;
}
public System.Data.DataTable DataGridView2DataTable(DataGridView dgv, String tblName, int minRow = 0)
{
System.Data.DataTable dt = new System.Data.DataTable(tblName);
// Header columns
foreach (DataGridViewColumn column in dgv.Columns)
{
DataColumn dc = new DataColumn(column.Name.ToString());
dt.Columns.Add(dc);
}
// Data cells
for (int i = 0; i < dgv.Rows.Count; i++)
{
DataGridViewRow row = dgv.Rows[i];
DataRow dr = dt.NewRow();
for (int j = 0; j < dgv.Columns.Count; j++)
{
dr[j] = (row.Cells[j].Value == null) ? "" : row.Cells[j].Value.ToString();
}
dt.Rows.Add(dr);
}
// Related to the bug arround min size when using ExcelLibrary for export
for (int i = dgv.Rows.Count; i < minRow; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
dr[j] = " ";
}
dt.Rows.Add(dr);
}
return dt;
}
Excel.Application ExcelApp;
private void Btngenerate_Click(object sender, EventArgs e)
{
int max = 0;
progressBar1.Minimum = 0;
++max;
progressBar1.Maximum = max;
progressBar1.Visible = true;
lbl_wait.Visible = true;
// Create a DataSet and put both tables in it.
System.Data.DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");
System.Data.DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");
dataSet = new DataSet("General");
dataSet.Tables.Add(dtConverted);
dataSet.Tables.Add(dtKeyData);
try
{
ExcelApp = new Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// Loop over DataTables in DataSet.
DataTableCollection collection = dataSet.Tables;
for (int i = collection.Count; i > 0; i--)
{
Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;
//xlWorksheet.Name = "Mandate Summary";
//Create Excel Sheets
xlSheets = ExcelApp.Sheets;
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
System.Data.DataTable table = collection[i - 1];
xlWorksheet.Name = table.TableName;
for (int j = 1; j < table.Columns.Count + 1; j++)
{
ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName;
}
// Storing Each row and column value to excel sheet
for (int k = 0; k < table.Rows.Count; k++)
{
for (int l = 0; l < table.Columns.Count; l++)
{
ExcelApp.Cells[k + 2, l + 1] =
table.Rows[k].ItemArray[l].ToString();
}
}
ExcelApp.Columns.AutoFit();
}
((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete();
ExcelApp.Visible = true;
xlWorkbook.SaveAs("D:\\abc.xls");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
progressBar1.Value++;
// int max = 0;
// progressBar1.Minimum = 0;
// ++max;
// progressBar1.Maximum = max;
// progressBar1.Visible = true;
// lbl_wait.Visible = true;
//// DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");
//// DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");
// //if (dgvResults.Rows.Count > 0)
// //{
// try
// {
// // Bind Grid Data to Datatable
// //DataTable dt = new DataTable();
// DataTable dtConverted = DataGridView2DataTable(dataGridView1, "Converted");
// DataTable dtKeyData = DataGridView2DataTable(dataGridView2, "KeyData");
// foreach (DataGridViewColumn col in dgvResults.Columns)
// {
// dtConverted.Columns.Add(col.HeaderText, col.ValueType);
// }
// int count = 0;
// foreach (DataGridViewRow row in dgvResults.Rows)
// {
// if (count < dgvResults.Rows.Count - 1)
// {
// dtConverted.Rows.Add();
// dtKeyData.Rows.Add();
// foreach (DataGridViewCell cell in row.Cells)
// {
// dtConverted.Rows[dtConverted.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
// dtKeyData.Rows[dtKeyData.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
// }
// }
// count++;
// }
// // Bind table data to Stream Writer to export data to respective folder
// StreamWriter wr = new StreamWriter(@"\ClientData-CR-ALL-17022010-INP-" + max.ToString().PadLeft(6, '0') + ".xls");
// // StreamWriter wr = new StreamWriter(@"D:\\ClientData.xls");
// // Write Columns to excel file
// for (int i = 0; i < dtConverted.Columns.Count; i++)
// {
// wr.Write(dtConverted.Columns[i].ToString().ToUpper() + "\t");
// }
// wr.WriteLine();
// //write rows to excel file
// for (int i = 0; i < (dtConverted.Rows.Count); i++)
// {
// for (int j = 0; j < dtConverted.Columns.Count; j++)
// {
// if (dtConverted.Rows[i][j] != null)
// {
// wr.Write(Convert.ToString(dtConverted.Rows[i][j]) + "\t");
// }
// else
// {
// wr.Write("\t");
// }
// }
// wr.WriteLine();
// }
// wr.Close();
// label1.Text = "Data Exported Successfully";
// }
// catch (Exception ex)
// {
// throw ex;
// }
// //}
// progressBar1.Value++;
}
private void FrmReport_Load(object sender, EventArgs e)
{
ReadData("SELECT ID,BankName,ContactPersonName,ContactNumber,EmailId,PORecieved,PayementRecievedDate,Remark,EToken,DigitalCertificate FROM tbl_ClientDetails",
ref dstConverted, "Converted");
myConvertedView = ((System.Data.DataTable)dstConverted.Tables["Converted"]).DefaultView;
ReadData("Select ID,BankName,InstalledDate,UserKey,ProductKey,Remark,EToken,DigitalCertificate from tbl_ClientDetails", ref dstKeyData, "KeyData");
myKeyDataView = ((System.Data.DataTable)dstKeyData.Tables["KeyData"]).DefaultView;
dataGridView1.DataSource = myConvertedView;
dataGridView2.DataSource = myKeyDataView;
// dataGridView1.Refresh();
}
public void ReadData(string strSQL, ref DataSet dstMain, string strTableName = "default")
{
try
{
string connectionString = @" Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ClientMgmtDb.accdb;";
// OleDbConnection cnn = conection();
OleDbConnection cnn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(strSQL, cnn);
cnn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dstMain, strTableName);
da.Dispose();
cnn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
Exporting Dataset to Individual Excel to Sheets (Windows Application)
This code for Exporting Dataset to Individual Excel to Sheets (Windows Application).