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.Threading;
using System.Data.OleDb;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
//using Lateral8.Articles.OpenXML;
using System.IO;
using System.Collections;
//using RKLib.ExportData;
//using Microsoft.Office.Interop.Excel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Reconciler
{
public partial class ExcelComparison : Form
{
//duplicate datatables
System.Data.DataTable dtduplicatesinexcel1 = null;
System.Data.DataTable dtduplicatesinexcel2 = null;
//Global Decalrations
//class decalrations
Logging logError = new Logging();
//controls decalrations
OpenFileDialog openExcel1;
OpenFileDialog openExcel2;
//Variables
DataSet dsExcelData;
public ExcelComparison()
{
InitializeComponent();
}
private void btnBrowseExcel1_Click(object sender, EventArgs e)
{
try
{
openExcel1 = new OpenFileDialog();
// openExcel1.Filter = "Excel Files(*.xls)|*.xls;|(*.xlsx)|*.xlsx";
openExcel1.Filter = "Excel Files(*.xls)|*.xls;";
if (openExcel1.ShowDialog() == DialogResult.OK)
{
txtExcelPath1.Text = openExcel1.FileName;
//this.Cursor = Cursors.WaitCursor;
////tEXcel1 = new Thread(ProcessExcel);
////tEXcel2.Start();
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnBrowseExcel2_Click(object sender, EventArgs e)
{
try
{
openExcel2 = new OpenFileDialog();
// openExcel2.Filter = "Excel Files(*.xls)|*.xls;|(*.xlsx)|*.xlsx";
openExcel2.Filter = "Excel Files(*.xls)|*.xls;";
if (openExcel2.ShowDialog() == DialogResult.OK)
{
txtExcelPath2.Text = openExcel2.FileName;
//this.Cursor = Cursors.WaitCursor;
//tEXcel2 = new Thread(ProcessExcel);
//tEXcel2.Start();
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
if (txtExcelPath1.Text != string.Empty)
{
if (txtExcelPath2.Text != string.Empty)
{
dsExcelData = new DataSet();
dsExcelData = ImportExcelXLS(txtExcelPath1.Text, txtExcelPath2.Text, true);
if (dsExcelData != null && dsExcelData.Tables[0].Rows.Count != 0 && dsExcelData.Tables[1].Rows.Count != 0)
{
MessageBox.Show("Import is done sucessfully", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
groupBox2.Enabled = true;
//chkExcel1.Enabled = true;
chkExcel1.Text = openExcel1.SafeFileName;
//chkExcel2.Enabled = true;
chkExcel2.Text = openExcel2.SafeFileName;
//lstExcel1Columns.Enabled = true;
lstExcel1Columns.Items.Clear();
for (int i = 0; i < dsExcelData.Tables[0].Columns.Count; i++)
{
if (dsExcelData.Tables[0].Columns[i].ColumnName != " ")
{
lstExcel1Columns.Items.Add(dsExcelData.Tables[0].Columns[i].ColumnName);
}
}
lstExcel2Columns.Items.Clear();
for (int i = 0; i < dsExcelData.Tables[1].Columns.Count; i++)
{
if (dsExcelData.Tables[1].Columns[i].ColumnName != " ")
{
lstExcel2Columns.Items.Add(dsExcelData.Tables[1].Columns[i].ColumnName);
}
}
}
else
{
MessageBox.Show("Error while importing", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Please select Excel2", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Please select Excel1", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public void ProcessExcel()
//{
// try
// {
// dsExcelData = new DataSet();
// dsExcelData = ImportExcelXLS(txtExcelPath1.Text, txtExcelPath2.Text, true);
// if (dsExcelData != null && dsExcelData.Tables[0].Rows.Count != 0 && dsExcelData.Tables[1].Rows.Count != 0)
// {
// for (int i = 0; i < dsExcelData.Tables[0].Columns.Count - 1; i++)
// {
// lstExcel1Columns.Items.Add(dsExcelData.Tables[0].Columns[i].ColumnName);
// }
// for (int i = 0; i < dsExcelData.Tables[1].Columns.Count - 1; i++)
// {
// lstExcel2Columns.Items.Add(dsExcelData.Tables[0].Columns[i].ColumnName);
// }
// }
// }
// catch (Exception ex)
// {
// logError.Logflatfile_Ex(ex);
// MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// }
//}
private DataSet ImportExcelXLS(string FileName1, string FileName2, bool hasHeaders)
{
int TableCounter = 0;
try
{
// SetControlPropertyValue(lblImportStatus, "Text", "Getting Data...");
string HDR = hasHeaders ? "Yes" : "No";
int dot1 = FileName1.LastIndexOf('.');
int dot2 = FileName2.LastIndexOf('.');
string strConn1;
string strConn2;
if (FileName1.Remove(0, dot1) == ".xls")
{
strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName1 + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
// strConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName1 + ";Extended Properties=Excel 12.0;";
}
else
{
strConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName1 + ";Extended Properties=Excel 12.0;";
}
if (FileName2.Remove(0, dot2) == ".xls")
{
strConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName2 + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
// strConn2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName2 + ";Extended Properties=Excel 12.0;";
}
else
{
strConn2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName2 + ";Extended Properties=Excel 12.0;";
}
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn1))
{
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
string sheet = row["TABLE_NAME"].ToString();
if (!(sheet.Contains("HTML")))
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "] ", conn);
cmd.CommandType = CommandType.Text;
System.Data.DataTable outputTable = new System.Data.DataTable(sheet+TableCounter++);
new OleDbDataAdapter(cmd).Fill(outputTable);
if (outputTable.Rows.Count != 0)
{
for (int i = 0; i <= outputTable.Rows.Count - 1; i++)
{
string valuesarr = string.Empty;
List<object> lst = new List<object>(outputTable.Rows[i].ItemArray);
foreach (object s in lst)
{
valuesarr += s.ToString();
}
if (valuesarr == string.Empty)
{
outputTable.Rows[i].Delete();
}
}
outputTable.AcceptChanges();
if (outputTable.Rows.Count != 0)
{
output.Tables.Add(outputTable);
}
}
//if (outputTable.Rows.Count != 1)
//{
//}
//else
//{
// string val = outputTable.Rows[0][0].ToString(); // Where Fieldname is the name of fields from your database that you want to get
// if (val != string.Empty)
// {
// output.Tables.Add(outputTable);
// }
//}
}
//output.Merge(outputTable);
// outputTable.Rows.Cast<DataRow>().Where(Drow => !Drow.ItemArray.All(field => field is System.DBNull).CopyToDataTable();
//break;
}
}
using (OleDbConnection conn = new OleDbConnection(strConn2))
{
//TableCounter = 0;
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
string sheet = row["TABLE_NAME"].ToString();
if (!(sheet.Contains("HTML")))
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "] ", conn);
cmd.CommandType = CommandType.Text;
System.Data.DataTable outputTable = new System.Data.DataTable(sheet +TableCounter++);
new OleDbDataAdapter(cmd).Fill(outputTable);
if (outputTable.Rows.Count != 0)
{
for (int i = 0; i <= outputTable.Rows.Count - 1; i++)
{
string valuesarr = string.Empty;
List<object> lst = new List<object>(outputTable.Rows[i].ItemArray);
foreach (object s in lst)
{
valuesarr += s.ToString();
}
if (valuesarr == string.Empty)
{
outputTable.Rows[i].Delete();
}
}
outputTable.AcceptChanges();
if (outputTable.Rows.Count != 0)
{
output.Tables.Add(outputTable);
}
}
}
//output.Merge(outputTable);
// outputTable.Rows.Cast<DataRow>().Where(Drow => !Drow.ItemArray.All(field => field is System.DBNull).CopyToDataTable();
//break;
}
}
return output;
}
catch (Exception ee)
{
// SetControlPropertyValue(lblImportStatus, "Text", "");
// SetControlPropertyValue(lblWorking, "Visible", false);
logError.Logflatfile_Ex(ee);
MessageBox.Show("Error While Reading Excel", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw ee;
// s1.Abort();
}
}
private void MoveItemUp()
{
try
{
if (chkExcel1.Checked == true)
{
if (lstExcel1Columns.SelectedItems.Count > 0)
{
object selected = lstExcel1Columns.SelectedItem;
int indx = lstExcel1Columns.Items.IndexOf(selected);
int totl = lstExcel1Columns.Items.Count;
if (indx == 0)
{
lstExcel1Columns.Items.Remove(selected);
lstExcel1Columns.Items.Insert(totl - 1, selected);
lstExcel1Columns.SetSelected(totl - 1, true);
}
else
{
lstExcel1Columns.Items.Remove(selected);
lstExcel1Columns.Items.Insert(indx - 1, selected);
lstExcel1Columns.SetSelected(indx - 1, true);
}
}
if (lstExcel2Columns.SelectedItems.Count > 0)
{
MessageBox.Show("Please select second file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
if (lstExcel2Columns.SelectedItems.Count > 0)
{
object selected = lstExcel2Columns.SelectedItem;
int indx = lstExcel2Columns.Items.IndexOf(selected);
int totl = lstExcel2Columns.Items.Count;
if (indx == 0)
{
lstExcel2Columns.Items.Remove(selected);
lstExcel2Columns.Items.Insert(totl - 1, selected);
lstExcel2Columns.SetSelected(totl - 1, true);
}
else
{
lstExcel2Columns.Items.Remove(selected);
lstExcel2Columns.Items.Insert(indx - 1, selected);
lstExcel2Columns.SetSelected(indx - 1, true);
}
}
if (lstExcel1Columns.SelectedItems.Count > 0)
{
MessageBox.Show("Please select first file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void MoveItemDown()
{
try
{
if (chkExcel1.Checked == true)
{
if (lstExcel1Columns.SelectedItems.Count > 0)
{
object selected = lstExcel1Columns.SelectedItem;
int indx = lstExcel1Columns.Items.IndexOf(selected);
int totl = lstExcel1Columns.Items.Count;
if (indx == totl - 1)
{
lstExcel1Columns.Items.Remove(selected);
lstExcel1Columns.Items.Insert(0, selected);
lstExcel1Columns.SetSelected(0, true);
}
else
{
lstExcel1Columns.Items.Remove(selected);
lstExcel1Columns.Items.Insert(indx + 1, selected);
lstExcel1Columns.SetSelected(indx + 1, true);
}
}
}
else
{
if (lstExcel2Columns.SelectedItems.Count > 0)
{
object selected = lstExcel2Columns.SelectedItem;
int indx = lstExcel2Columns.Items.IndexOf(selected);
int totl = lstExcel2Columns.Items.Count;
if (indx == totl - 1)
{
lstExcel2Columns.Items.Remove(selected);
lstExcel2Columns.Items.Insert(0, selected);
lstExcel2Columns.SetSelected(0, true);
}
else
{
lstExcel2Columns.Items.Remove(selected);
lstExcel2Columns.Items.Insert(indx + 1, selected);
lstExcel2Columns.SetSelected(indx + 1, true);
}
}
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RemoveItem()
{
try
{
if (chkExcel1.Checked == true)
{
for (int i = lstExcel1Columns.SelectedIndices.Count - 1; i >= 0; i--)
{
lstExcel1Columns.Items.RemoveAt(lstExcel1Columns.SelectedIndices[i]);
}
if (lstExcel2Columns.SelectedItems.Count > 0)
{
MessageBox.Show("Please select second file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
for (int i = lstExcel2Columns.SelectedIndices.Count - 1; i >= 0; i--)
{
lstExcel2Columns.Items.RemoveAt(lstExcel2Columns.SelectedIndices[i]);
}
if (lstExcel1Columns.SelectedItems.Count > 0)
{
MessageBox.Show("Please select first file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnUP_Click(object sender, EventArgs e)
{
try
{
if (chkExcel1.Checked == true || chkExcel2.Checked == true)
{
MoveItemUp();
}
else
{
MessageBox.Show("Please select a file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnDown_Click(object sender, EventArgs e)
{
try
{
if (chkExcel1.Checked == true || chkExcel2.Checked == true)
{
MoveItemDown();
}
else
{
MessageBox.Show("Please select a file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
if (chkExcel1.Checked == true || chkExcel2.Checked == true)
{
RemoveItem();
}
else
{
MessageBox.Show("Please select a file", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void chkExcel1_CheckedChanged(object sender, EventArgs e)
{
try
{
if (chkExcel2.Checked == true)
{
chkExcel2.Checked = false;
chkExcel2.Checked = true;
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void chkExcel2_CheckedChanged(object sender, EventArgs e)
{
try
{
if (chkExcel1.Checked == true)
{
chkExcel1.Checked = false;
chkExcel1.Checked = true;
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public void duplicaterows(System.Data.DataTable dataTable)
//{
// List<string> l = new List<string>();
// Int32 c = 0;
// for (Int32 r0 = 0; r0 < dataTable.Rows.Count; r0++)
// {
// for (Int32 r1 = r0 + 1; r1 < dataTable.Rows.Count; r1++)
// {
// Boolean rowsEqual = true;
// for (c = 0; c < dataTable.Columns.Count; c++)
// {
// if (!Object.Equals(dataTable.Rows[r0][c], dataTable.Rows[r1][c]))
// {
// rowsEqual = false;
// break;
// }
// }
// if (rowsEqual)
// {
// lstExcel1Columns.Items.Add(dataTable.Rows[r0][dataTable.Columns[c].ColumnName].ToString());
// // .Add(dataTable.Rows[r0].ToString());
// // Console.WriteLine(String.Format("Row {0} is a duplicate of row {1}.", r0, r1));
// }
// }
// }
//}
//public System.Data.DataTable RemoveDuplicateRows(System.Data.DataTable dTable, List<string> colName)
//{
// Hashtable hTable = new Hashtable();
// ArrayList duplicateList = new ArrayList();
// //Add list of all the unique item value to hashtable, which stores combination of key, value pair.
// //And add duplicate item value in arraylist.
// foreach (string colName1 in colName)
// {
// foreach (DataRow drow in dTable.Rows)
// {
// if (hTable.Equals(colName1 == "" ? drow[0] : drow[colName1]))
// duplicateList.Add(drow);
// else
// hTable.Add(colName1 == "" ? drow[0] : drow[colName1], string.Empty);
// }
// }
// //Removing a list of duplicate items from datatable.
// //foreach (DataRow dRow in duplicateList)
// // dTable.Rows.Remove(dRow);
// //Datatable which contains unique records will be return as output.
// return dTable;
//}
//public void dup(System.Data.DataTable dt)
//{
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// for (int count = 0; count < dt.Rows.Count; count++)
// {
// for (int index = dt.Rows.Count - 1; index > count; index--)
// {
// if (dt.Rows[count][dt.Columns[i].ColumnName] == dt.Rows[index][dt.Columns[i].ColumnName])
// {
// //dt.Rows.RemoveAt(index);
// lstExcel1Columns.Items.Add(dt.Rows[count][dt.Columns[i].ColumnName].ToString());
// }
// }
// }
// }
//}
private void btnCompare_Click(object sender, EventArgs e)
{
//int excel1columnscount = 0;
//int excel2columnscount = 0;
try
{
if (lstExcel1Columns.Items.Count != 0 && lstExcel2Columns.Items.Count != 0)
{
if (lstExcel1Columns.Items.Count == lstExcel2Columns.Items.Count)
{
//if (samecolumns == lstExcel1Columns.Items.Count)
//{
System.Data.DataTable dtlst1 = null;
//new System.Data.DataTable("dtlst1");
System.Data.DataTable dtlst2 = null;
// new System.Data.DataTable("dtlst2");
if (dsExcelData.Tables.Contains("dtlst1"))
{
dsExcelData.Tables.Remove("dtlst1");
dtlst1 = new System.Data.DataTable("dtlst1");
}
else
{
dtlst1 = new System.Data.DataTable("dtlst1");
}
if (dsExcelData.Tables.Contains("dtlst2"))
{
dsExcelData.Tables.Remove("dtlst2");
dtlst2 = new System.Data.DataTable("dtlst2");
}
else
{
dtlst2 = new System.Data.DataTable("dtlst2");
}
if (dsExcelData.Tables.Contains("dtduplicatesinexcel1"))
{
dsExcelData.Tables.Remove("dtduplicatesinexcel1");
dtduplicatesinexcel1 = new System.Data.DataTable("dtduplicatesinexcel1");
}
else
{
dtduplicatesinexcel1 = new System.Data.DataTable("dtduplicatesinexcel1");
}
if (dsExcelData.Tables.Contains("dtduplicatesinexcel2"))
{
dsExcelData.Tables.Remove("dtduplicatesinexcel2");
dtduplicatesinexcel2 = new System.Data.DataTable("dtduplicatesinexcel2");
}
else
{
dtduplicatesinexcel2 = new System.Data.DataTable("dtduplicatesinexcel2");
}
DataSet ds = new DataSet();
// DataColumn[] dc1;
List<string> firstColumns1 = new List<string>();
for (int i = 0; i < lstExcel1Columns.Items.Count; i++)
{
dtlst1.Columns.Add(lstExcel1Columns.Items[i].ToString());
firstColumns1.Add(lstExcel1Columns.Items[i].ToString());
dtduplicatesinexcel1.Columns.Add(lstExcel1Columns.Items[i].ToString());
}
foreach (DataRow dr in dsExcelData.Tables[0].Rows)
{
DataRow dr1 = dtlst1.NewRow();
for (int i = 0; i < dtlst1.Columns.Count; i++)
{
//dr1[i] = dr[i];
dr1[lstExcel1Columns.Items[i].ToString()] = dr[dsExcelData.Tables[0].Columns[lstExcel1Columns.Items[i].ToString()]];
}
dtlst1.Rows.Add(dr1);
}
dsExcelData.Tables.Add(dtlst1);
//for (int i = 0; i < firstColumns1.Length; i++)
//{
// firstColumns1[i] = dsExcelData.Tables["dtlst1"].Columns[lstExcel1Columns.Items[i].ToString()];
//}
//}
//if (dsExcelData.Tables[1].Columns.Count != lstExcel2Columns.Items.Count)
//{
List<string> secondColumns1 = new List<string>();
for (int i = 0; i < lstExcel2Columns.Items.Count; i++)
{
dtlst2.Columns.Add(lstExcel2Columns.Items[i].ToString());
secondColumns1.Add(lstExcel2Columns.Items[i].ToString());
dtduplicatesinexcel2.Columns.Add(lstExcel2Columns.Items[i].ToString());
}
foreach (DataRow dr in dsExcelData.Tables[1].Rows)
{
DataRow dr2 = dtlst2.NewRow();
for (int i = 0; i < dtlst2.Columns.Count; i++)
{
// dr2[i] = dr[i];
dr2[lstExcel2Columns.Items[i].ToString()] = dr[dsExcelData.Tables[1].Columns[lstExcel2Columns.Items[i].ToString()]];
}
dtlst2.Rows.Add(dr2);
}
dsExcelData.Tables.Add(dtlst2);
// dup(dsExcelData.Tables["dtlst1"]);
System.Data.DataTable dt1 = RemoveDuplicates1(dsExcelData.Tables["dtlst1"].Copy(), firstColumns1);
System.Data.DataTable dt2 = RemoveDuplicates1(dsExcelData.Tables["dtlst2"].Copy(), secondColumns1);
dsExcelData.Tables.Add(dtduplicatesinexcel1);
dsExcelData.Tables.Add(dtduplicatesinexcel2);
//DataRow[] duplicaterowsinexcel1 = FindDups(dsExcelData.Tables["dtlst1"], 0, firstColumns1);
// DataRow[] duplicaterowsinexcel2 = FindDups(dsExcelData.Tables["dtlst2"], 0, secondColumns);
//}
// RemoveDuplicateRows(dsExcelData.Tables["dtlst1"], ls1);
// duplicaterows(dsExcelData.Tables["dtlst1"]);
ds = getDifferentRecords(dsExcelData.Tables["dtlst1"], dsExcelData.Tables["dtlst2"]);
if (ds != null)
{
MessageBox.Show("Comparison is done sucessfully", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
groupBox3.Enabled = true;
rdoMatched1.Text = "Matched rows in " + chkExcel1.Text;
rdoMatched2.Text = "Matched rows in " + chkExcel2.Text;
rdoExtrarows1.Text = "Different rows in " + chkExcel1.Text;
rdoExtrarows2.Text = "Different rows in " + chkExcel2.Text;
rdoDuplicateRowsExcel1.Text = "Duplicate rows in " + chkExcel1.Text;
rdoDuplicateRowsExcel2.Text = "Duplicate rows in " + chkExcel2.Text;
if (ds != null && ds.Tables["ResultDataTableCommon1"].Rows.Count != 0)
{
lblMatchedCount1.Text = ds.Tables["ResultDataTableCommon1"].Rows.Count.ToString();
}
else
{
lblMatchedCount1.Text = "0".ToString();
}
if (ds != null && ds.Tables["ResultDataTableCommon2"].Rows.Count != 0)
{
lblMatchedCount2.Text = ds.Tables["ResultDataTableCommon2"].Rows.Count.ToString();
}
else
{
lblMatchedCount2.Text = "0".ToString();
}
if (ds != null && ds.Tables["ExtraRowsinExcel1"].Rows.Count != 0)
{
lblExtraRowsinExcel11.Text = ds.Tables["ExtraRowsinExcel1"].Rows.Count.ToString();
}
else
{
lblExtraRowsinExcel11.Text = "0".ToString();
}
if (ds != null && ds.Tables["ExtraRowsinExcel2"].Rows.Count != 0)
{
lblExtraRowsinExcel22.Text = ds.Tables["ExtraRowsinExcel2"].Rows.Count.ToString();
}
else
{
lblExtraRowsinExcel22.Text = "0".ToString();
}
if (dsExcelData != null && dsExcelData.Tables["dtduplicatesinexcel1"].Rows.Count != 0)
{
lblDuplicateRowsInexcel1count.Text = dsExcelData.Tables["dtduplicatesinexcel1"].Rows.Count.ToString();
}
else
{
lblDuplicateRowsInexcel1count.Text = "0".ToString();
}
if (dsExcelData != null && dsExcelData.Tables["dtduplicatesinexcel2"].Rows.Count != 0)
{
lblDuplicateRowsInexcel2count.Text = dsExcelData.Tables["dtduplicatesinexcel2"].Rows.Count.ToString();
}
else
{
lblDuplicateRowsInexcel2count.Text = "0".ToString();
}
}
else
{
MessageBox.Show("Error while comparing", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Comparison is not possible between different columns count", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("No Columns to compare", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
////if (dt.Rows.Count == 0)
//// MessageBox.Show("Equal");
////else
//// MessageBox.Show("Not Equal");
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//static void CompareRows(DataTable table1, DataTable table2)
//{
// foreach (DataRow row1 in table1.Rows)
// {
// foreach (DataRow row2 in table2.Rows)
// {
// var array1 = row1.ItemArray;
// var array2 = row2.ItemArray;
// if (array1.SequenceEqual(array2))
// {
// Console.WriteLine("Equal: {0} {1}", row1["Drug"], row2["Drug"]);
// }
// else
// {
// Console.WriteLine("Not equal: {0} {1}", row1["Drug"], row2["Drug"]);
// }
// }
// }
//}
#region Compare two DataTables and return a DataTable with DifferentRecords
//public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
//{
// //Create Empty Table
// DataTable ResultDataTable = new DataTable("ResultDataTable");
// //use a Dataset to make use of a DataRelation object
// using (DataSet ds = new DataSet())
// {
// //Add tables
// ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });
// //Get Columns for DataRelation
// DataColumn[] firstColumns = new DataColumn[lstExcel1Columns.Items.Count];
// for (int i = 0; i < firstColumns.Length; i++)
// {
// firstColumns[i] = ds.Tables[0].Columns[lstExcel1Columns.Items[i].ToString()];
// }
// DataColumn[] secondColumns = new DataColumn[lstExcel2Columns.Items.Count];
// for (int i = 0; i < secondColumns.Length; i++)
// {
// secondColumns[i] = ds.Tables[1].Columns[lstExcel2Columns.Items[i].ToString()];
// }
// //Create DataRelation
// DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
// ds.Relations.Add(r1);
// DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
// ds.Relations.Add(r2);
// //Create columns for return table
// for (int i = 0; i < firstColumns.Count(); i++)
// {
// ResultDataTable.Columns.Add(firstColumns[i].ToString());
// }
// //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
// ResultDataTable.BeginLoadData();
// foreach (DataRow parentrow in ds.Tables[0].Rows)
// {
// DataRow[] childrows = parentrow.GetChildRows(r1);
// if (childrows == null || childrows.Length == 0)
// ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
// }
// //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
// foreach (DataRow parentrow in ds.Tables[1].Rows)
// {
// DataRow[] childrows = parentrow.GetChildRows(r2);
// if (childrows == null || childrows.Length == 0)
// ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
// }
// ResultDataTable.EndLoadData();
// }
// dsExcelData.Tables.Add(ResultDataTable);
// return ResultDataTable;
//}
public DataSet getDifferentRecords(System.Data.DataTable FirstDataTable, System.Data.DataTable SecondDataTable)
{
//Create Empty Table
try
{
System.Data.DataTable ResultDataTableCommon1 = null;
System.Data.DataTable ResultDataTableCommon2 = null;
System.Data.DataTable ExtraRowsinExcel1 = null;
System.Data.DataTable ExtraRowsinExcel2 = null;
if (dsExcelData.Tables.Contains("ResultDataTableCommon1"))
{
dsExcelData.Tables.Remove("ResultDataTableCommon1");
ResultDataTableCommon1 = new System.Data.DataTable("ResultDataTableCommon1");
}
else
{
ResultDataTableCommon1 = new System.Data.DataTable("ResultDataTableCommon1");
}
if (dsExcelData.Tables.Contains("ResultDataTableCommon2"))
{
dsExcelData.Tables.Remove("ResultDataTableCommon2");
ResultDataTableCommon2 = new System.Data.DataTable("ResultDataTableCommon2");
}
else
{
ResultDataTableCommon2 = new System.Data.DataTable("ResultDataTableCommon2");
}
if (dsExcelData.Tables.Contains("ExtraRowsinExcel1"))
{
dsExcelData.Tables.Remove("ExtraRowsinExcel1");
ExtraRowsinExcel1 = new System.Data.DataTable("ExtraRowsinExcel1");
}
else
{
ExtraRowsinExcel1 = new System.Data.DataTable("ExtraRowsinExcel1");
}
if (dsExcelData.Tables.Contains("ExtraRowsinExcel2"))
{
dsExcelData.Tables.Remove("ExtraRowsinExcel2");
ExtraRowsinExcel2 = new System.Data.DataTable("ExtraRowsinExcel2");
}
else
{
ExtraRowsinExcel2 = new System.Data.DataTable("ExtraRowsinExcel2");
}
//use a Dataset to make use of a DataRelation object
using (DataSet ds = new DataSet())
{
//Add tables
ds.Tables.AddRange(new System.Data.DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });
//Get Columns for DataRelation
DataColumn[] firstColumns = new DataColumn[lstExcel1Columns.Items.Count];
for (int i = 0; i < firstColumns.Length; i++)
{
firstColumns[i] = ds.Tables[0].Columns[lstExcel1Columns.Items[i].ToString()];
}
DataColumn[] secondColumns = new DataColumn[lstExcel2Columns.Items.Count];
for (int i = 0; i < secondColumns.Length; i++)
{
secondColumns[i] = ds.Tables[1].Columns[lstExcel2Columns.Items[i].ToString()];
}
//Create DataRelation
DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
ds.Relations.Add(r1);
DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
ds.Relations.Add(r2);
//DataRelation r3=new DataRelation(string.Empty,
//Create columns for return table
for (int i = 0; i < firstColumns.Count(); i++)
{
// ResultDataTableDiff.Columns.Add(firstColumns[i].ToString());
ResultDataTableCommon1.Columns.Add(firstColumns[i].ToString());
}
for (int i = 0; i < secondColumns.Count(); i++)
{
// ResultDataTableDiff.Columns.Add(firstColumns[i].ToString());
ResultDataTableCommon2.Columns.Add(secondColumns[i].ToString());
}
for (int i = 0; i < firstColumns.Count(); i++)
{
ExtraRowsinExcel1.Columns.Add(firstColumns[i].ToString());
}
for (int i = 0; i < secondColumns.Count(); i++)
{
ExtraRowsinExcel2.Columns.Add(secondColumns[i].ToString());
}
ResultDataTableCommon2.BeginLoadData();
ExtraRowsinExcel1.BeginLoadData();
ExtraRowsinExcel2.BeginLoadData();
//Common data in both tables
ResultDataTableCommon1.BeginLoadData();
//If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
foreach (DataRow parentrow in ds.Tables[0].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r1);
if (childrows == null || childrows.Length == 0)
{
//ResultDataTableDiff.LoadDataRow(parentrow.ItemArray, true);
ExtraRowsinExcel1.LoadDataRow(parentrow.ItemArray, true);
}
else
{
ResultDataTableCommon1.LoadDataRow(parentrow.ItemArray, true);
}
}
//If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
//foreach (DataRow parentrow in ds.Tables[0].Rows)
//{
// foreach (DataRow childrelated in parentrow.GetChildRows(r1))
// {
// //DataRow[] childrows = childrelated.Table;
// if (childrelated == null)
// {
// //ResultDataTableDiff.LoadDataRow(parentrow.ItemArray, true);
// ExtraRowsinExcel1.LoadDataRow(parentrow.ItemArray, true);
// }
// else
// {
// ResultDataTableCommon1.LoadDataRow(parentrow.ItemArray, true);
// }
// }
//}
//foreach (DataRow parentrow in ds.Tables[0].Rows)
//{
// foreach (DataRow childrelated in parentrow.GetChildRows(r1))
// {
// //DataRow[] childrows = childrelated.Table;
// if (childrelated == null)
// {
// //ResultDataTableDiff.LoadDataRow(parentrow.ItemArray, true);
// ExtraRowsinExcel1.LoadDataRow(parentrow.ItemArray, true);
// }
// else
// {
// ResultDataTableCommon1.LoadDataRow(parentrow.ItemArray, true);
// }
// }
//}
//If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
foreach (DataRow parentrow in ds.Tables[1].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r2);
if (childrows == null || childrows.Length == 0)
{
//ResultDataTableDiff.LoadDataRow(parentrow.ItemArray, true);
ExtraRowsinExcel2.LoadDataRow(parentrow.ItemArray, true);
}
else
{
ResultDataTableCommon2.LoadDataRow(parentrow.ItemArray, true);
}
}
ResultDataTableCommon2.EndLoadData();
ResultDataTableCommon1.EndLoadData();
ExtraRowsinExcel1.EndLoadData();
ExtraRowsinExcel2.EndLoadData();
}
dsExcelData.Tables.Add(ResultDataTableCommon1);
dsExcelData.Tables.Add(ResultDataTableCommon2);
dsExcelData.Tables.Add(ExtraRowsinExcel1);
dsExcelData.Tables.Add(ExtraRowsinExcel2);
return dsExcelData;
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
private System.Data.DataTable RemoveDuplicates1(System.Data.DataTable tbl1, List<String> keyColumns1)
{
try
{
if (tbl1.TableName == "dtlst1")
{
int rowNdx = 0;
//dtduplicatesinexcel1.BeginLoadData();
while (rowNdx < tbl1.Rows.Count - 1)
{
DataRow[] dups = FindDups1(tbl1, rowNdx, keyColumns1);
if (dups.Length > 0)
{
//for (int i = 0; i < dups.Length; i++)
//{
// dtduplicatesinexcel1.ImportRow(dups[i]);
//}
// dtduplicatesinexcel1.LoadDataRow(dups[i].ItemArray, true);
foreach (DataRow dup in dups)
{
dtduplicatesinexcel1.ImportRow(dup);
tbl1.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
// dtduplicatesinexcel1.EndLoadData();
}
else if (tbl1.TableName == "dtlst2")
{
int rowNdx = 0;
//dtduplicatesinexcel1.BeginLoadData();
while (rowNdx < tbl1.Rows.Count - 1)
{
DataRow[] dups = FindDups1(tbl1, rowNdx, keyColumns1);
if (dups.Length > 0)
{
//for (int i = 0; i < dups.Length; i++)
//{
// dtduplicatesinexcel1.ImportRow(dups[i]);
//}
// dtduplicatesinexcel1.LoadDataRow(dups[i].ItemArray, true);
foreach (DataRow dup in dups)
{
dtduplicatesinexcel2.ImportRow(dup);
tbl1.Rows.Remove(dup);
}
}
else
{
rowNdx++;
}
}
}
return tbl1;
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
//dsExcelData.Tables.Add(dtduplicatesinexcel1);
}
//private System.Data.DataTable RemoveDuplicates2(System.Data.DataTable tbl2, List<String> keyColumns2)
//{
// int rowNdx = 0;
// try
// {
// //dtduplicatesinexcel1.BeginLoadData();
// while (rowNdx < tbl2.Rows.Count - 1)
// {
// DataRow[] dups = FindDups2(tbl2, rowNdx, keyColumns2);
// if (dups.Length > 0)
// {
// //for (int i = 0; i < dups.Length; i++)
// //{
// // dtduplicatesinexcel1.ImportRow(dups[i]);
// //}
// // dtduplicatesinexcel1.LoadDataRow(dups[i].ItemArray, true);
// foreach (DataRow dup in dups)
// {
// dtduplicatesinexcel2.ImportRow(dup);
// tbl2.Rows.Remove(dup);
// }
// }
// else
// {
// rowNdx++;
// }
// }
// // dtduplicatesinexcel1.EndLoadData();
// }
// catch (Exception ex)
// {
// }
// dsExcelData.Tables.Add(dtduplicatesinexcel2);
// return tbl2;
//}
private DataRow[] FindDups1(System.Data.DataTable tbl1, int sourceNdx1, List<string> keyColumns1)
{
ArrayList retVal = new ArrayList();
try
{
DataRow sourceRow = tbl1.Rows[sourceNdx1];
for (int i = sourceNdx1 + 1; i < tbl1.Rows.Count; i++)
{
DataRow targetRow = tbl1.Rows[i];
if (IsDup1(sourceRow, targetRow, keyColumns1))
{
if (retVal.Count.Equals(0))
retVal.Add(sourceRow);
retVal.Add(targetRow);
}
}
return (DataRow[])retVal.ToArray(typeof(DataRow));
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
//private DataRow[] FindDups2(System.Data.DataTable tbl2, int sourceNdx2, List<string> keyColumns2)
//{
// ArrayList retVal = new ArrayList();
// try
// {
// DataRow sourceRow = tbl2.Rows[sourceNdx2];
// for (int i = sourceNdx2 + 1; i < tbl2.Rows.Count; i++)
// {
// DataRow targetRow = tbl2.Rows[i];
// if (IsDup2(sourceRow, targetRow, keyColumns2))
// {
// if (retVal.Count.Equals(0))
// retVal.Add(sourceRow);
// retVal.Add(targetRow);
// }
// }
// }
// catch (Exception ex)
// {
// }
// return (DataRow[])retVal.ToArray(typeof(DataRow));
//}
private bool IsDup1(DataRow sourceRow1, DataRow targetRow1, List<String> keyColumns1)
{
bool retVal = true;
try
{
foreach (String column in keyColumns1)
{
if (sourceRow1.Table.Columns.Contains(column))
{
retVal = retVal && sourceRow1[column].Equals(targetRow1[column]);
if (!retVal) break;
}
}
return retVal;
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
//private bool IsDup2(DataRow sourceRow2, DataRow targetRow2, List<String> keyColumns2)
//{
// bool retVal = true;
// try
// {
// foreach (String column in keyColumns2)
// {
// if (sourceRow2.Table.Columns.Contains(column))
// {
// retVal = retVal && sourceRow2[column].Equals(targetRow2[column]);
// if (!retVal) break;
// }
// }
// }
// catch (Exception ex)
// {
// }
// return retVal;
//}
//private DataRow[] FindDups(System.Data.DataTable tbl, int sourceNdx, DataColumn[] keyColumns)
//{
// ArrayList retVal = new ArrayList();
// DataRow sourceRow = tbl.Rows[sourceNdx];
// for (int i = sourceNdx + 1; i < tbl.Rows.Count; i++)
// {
// DataRow targetRow = tbl.Rows[i];
// if (IsDup(sourceRow, targetRow, keyColumns))
// {
// retVal.Add(targetRow);
// }
// }
// return (DataRow[])retVal.ToArray(typeof(DataRow));
//}
//private bool IsDup(DataRow sourceRow, DataRow targetRow, DataColumn[] keyColumns)
//{
// bool retVal = true;
// foreach (DataColumn column in keyColumns)
// {
// retVal = retVal && sourceRow[column].Equals(targetRow[column]);
// if (!retVal) break;
// }
// return retVal;
//}
#endregion
//private void exportDataTableToExcel(DataTable dt, string filePath)
//{
// // Excel file Path
// string myFile = filePath;
// //System.Data.DataRow dr = default(System.Data.DataRow);
// int colIndex = 0;
// int rowIndex = 0;
// // Open the file and write the headers
// StreamWriter fs = new StreamWriter(myFile, false);
// fs.WriteLine("<? xml version=\"1.0\"?>");
// fs.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
// fs.WriteLine("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
// // Create the styles for the worksheet
// fs.WriteLine(" <ss:Styles>");
// // Style for the column headers
// fs.WriteLine(" <ss:Style ss:ID=\"1\">");
// fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
// fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
// fs.WriteLine(" <ss:Interior ss:Color=\"#254117\" ss:Pattern=\"Solid\"/>");
// fs.WriteLine(" </ss:Style>");
// // Style for the column information
// fs.WriteLine(" <ss:Style ss:ID=\"2\">");
// fs.WriteLine(" <ss:Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
// fs.WriteLine(" </ss:Style>");
// // Style for the column headers
// fs.WriteLine(" <ss:Style ss:ID=\"3\">");
// fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
// fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
// fs.WriteLine(" <ss:Interior ss:Color=\"#736AFF\" ss:Pattern=\"Solid\"/>");
// fs.WriteLine(" </ss:Style>");
// fs.WriteLine(" </ss:Styles>");
// // Write the worksheet contents
// fs.WriteLine("<ss:Worksheet ss:Name=\"Sheet1\">");
// fs.WriteLine(" <ss:Table>");
// fs.WriteLine(" <ss:Row>");
// foreach (DataColumn dc in dt.Columns)
// {
// fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"1\">" + "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", dc.ColumnName));
// }
// fs.WriteLine(" </ss:Row>");
// object cellText = null;
// // Write contents for each cell
// foreach (DataRow dr in dt.Rows)
// {
// rowIndex = rowIndex + 1;
// colIndex = 0;
// fs.WriteLine(" <ss:Row>");
// foreach (DataColumn dc in dt.Columns)
// {
// cellText = dr[dc];
// // Check for null cell and change it to empty to avoid error
// if (cellText == null) cellText = "";
// fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"2\">" +
// "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", cellText));
// colIndex = colIndex + 1;
// }
// fs.WriteLine(" </ss:Row>");
// }
// fs.WriteLine(" <ss:Row>");
// fs.WriteLine(" </ss:Row>");
// // Close up the document
// fs.WriteLine(" </ss:Table>");
// fs.WriteLine("</ss:Worksheet>");
// fs.WriteLine("</ss:Workbook>");
// fs.Close();
//public void a(System.Data.DataTable dt) //}
//{
// Excel.Application oXL;
// Excel.Workbook oWB;
// Excel.Worksheet oSheet;
// Excel.Range oRange;
// // Start Excel and get Application object.
// oXL = new Excel.Application();
// // Set some properties
// oXL.Visible = true;
// oXL.DisplayAlerts = false;
// // Get a new workbook.
// oWB = oXL.Workbooks.Add(Missing.Value);
// // Get the active sheet
// oSheet = (Excel.Worksheet)oWB.ActiveSheet;
// oSheet.Name = "Customers";
// // Process the DataTable
// // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
// System.Data.DataTable dt1 = dsExcelData.Tables[4];
// int rowCount = 1;
// foreach (DataRow dr in dt1.Rows)
// {
// rowCount += 1;
// for (int i = 1; i < dt1.Columns.Count + 1; i++)
// {
// // Add the header the first time through
// if (rowCount == 2)
// {
// oSheet.Cells[1, i] = dt1.Columns[i - 1].ColumnName;
// }
// oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
// }
// }
// // Resize the columns
// oRange = oSheet.get_Range(oSheet.Cells[1, 1],
// oSheet.Cells[rowCount, dt1.Columns.Count]);
// oRange.EntireColumn.AutoFit();
// // Save the sheet and close
// oSheet = null;
// oRange = null;
// oWB.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
// oWB.Close(Missing.Value, Missing.Value, Missing.Value);
// oWB = null;
// oXL.Quit();
// // Clean up
// // NOTE: When in release mode, this does the trick
// GC.WaitForPendingFinalizers();
// GC.Collect();
// GC.WaitForPendingFinalizers();
// GC.Collect();
//}
//declare a variable to hold the CurrentCulture
//System.Globalization.CultureInfo oldCI;
////get the old CurrenCulture and set the new, en-US
//void SetNewCurrentCulture()
//{
// oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//}
////reset Current Culture back to the originale
//void ResetCurrentCulture()
//{
// System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
//}
//public void CreateExcelSheet(System.Data.DataTable dtCopy)
//{
// SetNewCurrentCulture();
// // string newFilePath = Server.MapPath("ExcelFile/OfficeErrorList.xlsx");
// ApplicationClass objExcel = null;
// Workbooks objBooks = null;
// _Workbook objBook = null;
// Sheets objSheets = null;
// _Worksheet objSheet = null;
// Range objRange = null;
// int row = 1, col = 1;
// try
// {
// // System.Data.DataTable dtCustmer = GetAllCustomers();
// //System.Data.DataTable dtCustmer = Dt.Clone();
// objExcel = new ApplicationClass();
// objBooks = objExcel.Workbooks;
// objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
// //Print column heading in the excel sheet
// int j = col;
// foreach (DataColumn column in dsExcelData.Tables[4].Columns)
// {
// objSheets = objBook.Worksheets;
// objSheet = (_Worksheet)objSheets.get_Item(1);
// objRange = (Range)objSheet.Cells[row, j];
// objRange.Value2 = column.ColumnName;
// // objRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
// //objRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Maroon);
// j++;
// }
// row++;
// int count = dsExcelData.Tables[4].Columns.Count;
// foreach (DataRow dataRow in dsExcelData.Tables[4].Rows)
// {
// int k = col;
// for (int i = 0; i < count; i++)
// {
// objRange = (Range)objSheet.Cells[row, k];
// objRange.Value2 = dataRow[i].ToString();
// k++;
// }
// row++;
// }
// //Save Excel document
// objSheet.Name = "Sample Sheet";
// object objOpt = Missing.Value;
// objBook.SaveAs("c:\\test.xsl", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
// objBook.Close(null, null, null);
// //objBook.SaveAs("c:\\test.xsl", objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
// //s objBook.Close(false, objOpt, objOpt);
// }
// catch (Exception ex)
// {
// ////pnlLoading.Visible = false;
// //xlWorkBook.Close(false, misValue, misValue);
// //xlApp.Quit();
// //releaseObject(xlWorkSheet);
// //releaseObject(xlWorkBook);
// //releaseObject(xlApp);
// //MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// //return;
// }
//}
//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();
// }
//}
//public static void ExcelExport(System.Data.DataTable data, String fileName)
//{
// //export a DataTable to Excel
// DialogResult retry = DialogResult.Retry;
// while (retry == DialogResult.Retry)
// {
// try
// {
// using (ExcelWriter writer = new ExcelWriter(fileName))
// {
// writer.WriteStartDocument();
// // Write the worksheet contents
// writer.WriteStartWorksheet("Sheet1");
// //Write header row
// writer.WriteStartRow();
// foreach (DataColumn col in data.Columns)
// writer.WriteExcelUnstyledCell(col.Caption);
// writer.WriteEndRow();
// //write data
// foreach (DataRow row in data.Rows)
// {
// writer.WriteStartRow();
// foreach (object o in row.ItemArray)
// {
// writer.WriteExcelAutoStyledCell(o);
// }
// writer.WriteEndRow();
// }
// // Close up the document
// writer.WriteEndWorksheet();
// writer.WriteEndDocument();
// writer.Close();
// //if (openAfter)
// // OpenFile(fileName);
// //retry = DialogResult.Cancel;
// }
// }
// catch (Exception myException)
// {
// retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
// }
// }
//}
private void Export1(string fileName, System.Data.DataTable dt)
{
try
{
//string FileName = fileName;
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename=\"" + FileName + "\""));
// HttpContext.Current.Response.ContentType = "application/vnd.xls";
//TitleText = fileName + " From " + ddlMonth1.SelectedItem + "-" + ddlYear1.Text + " To " + ddlMonth2.SelectedItem + "-" + ddlYear2.Text + "";
using (StreamWriter sw = new StreamWriter(fileName))
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
System.Web.UI.WebControls.Table table = new System.Web.UI.WebControls.Table();
//TableRow trHeading = new TableRow();
//TableCell tdCaption = new TableCell();
//tdCaption.RowSpan = 2;
//tdCaption.ColumnSpan = 2 * dt.Columns.Count;
//tdCaption.Font.Bold = true;
//tdCaption.Font.Size = 16;
//tdCaption.HorizontalAlign = HorizontalAlign.Center;
//tdCaption.VerticalAlign = VerticalAlign.Middle;
//System.Web.UI.WebControls.Label lblText = new System.Web.UI.WebControls.Label();
// lblText.Text = "<h3>" + TitleText + "</h3>";
//tdCaption.Controls.Add(lblText);
//tdCaption.Attributes.Add("style", "color:blue");
// tdCaption.BackColor = System.Drawing.Color.Gray;
// tdCaption.ForeColor = System.Drawing.Color.White;
// trHeading.Cells.Add(tdCaption);
//trHeading.BorderWidth = 0;
//table.Rows.Add(trHeading);
//TableRow trspace = new TableRow();
//TableCell tdspace = new TableCell();
//tdspace.HorizontalAlign = HorizontalAlign.Center;
//tdspace.VerticalAlign = VerticalAlign.Middle;
//table.Rows.Add(trspace);
// include the gridline settings
// table.GridLines = GridLines.Both;
// add the header row to the table
TableRow headerRow = new TableRow();
for (int i = 0; i < dt.Columns.Count; i++)
{
TableHeaderCell headerCell = new TableHeaderCell();
//headerCell.ColumnSpan = 2;
headerCell.Font.Bold = true;
headerCell.Font.Size = 12;
headerCell.HorizontalAlign = HorizontalAlign.Center;
headerCell.VerticalAlign = VerticalAlign.Middle;
headerCell.Text = dt.Columns[i].ColumnName;
headerRow.Cells.Add(headerCell);
}
table.Rows.Add(headerRow);
// add each of the data rows to the table
foreach (DataRow dr in dt.Rows)
{
TableRow tr = new TableRow();
for (int i = 0; i < dt.Columns.Count; i++)
{
TableCell td = new TableCell();
td.HorizontalAlign = HorizontalAlign.Center;
td.VerticalAlign = VerticalAlign.Middle;
//td.ColumnSpan = 2;
td.Text = Convert.ToString(dr[i]);
tr.Cells.Add(td);
}
table.Rows.Add(tr);
}
// render the table into the htmlwriter
table.RenderControl(htw);
sw.Close();
// render the htmlwriter into the response
// HttpContext.Current.Response.Write(sw.ToString());
// HttpContext.Current.Response.End();
}
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public void ExportToExcel(System.Data.DataTable dtexport ,string filename)
//{
// ApplicationClass ExcelApp = new ApplicationClass();
// ExcelApp.Application.Workbooks.Add(Type.Missing);
// object misValue = System.Reflection.Missing.Value;
// for (int i = 1; i < dtexport.Columns.Count + 1; i++)
// {
// ExcelApp.Cells[1, i] = dtexport.Columns[i - 1].ColumnName;
// }
// for (int i = 0; i < dtexport.Rows.Count; i++)
// {
// for (int j = 0; j < dtexport.Columns.Count; j++)
// {
// ExcelApp.Cells[i + 2, j + 1] = dtexport.Rows[i][j].ToString();
// }
// }
// ExcelApp.ActiveWorkbook.SaveCopyAs(filename);
// // SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// //SaveCopyAs(filename);
// //.SaveAs(filename);
// ExcelApp.ActiveWorkbook.Saved = true;
// ExcelApp.Quit();
//}
//public void write()
//{
// StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");
// try
// {
// for (int i = 0; i < dsExcelData.Tables[0].Columns.Count; i++)
// {
// wr.Write(dsExcelData.Tables[0].Columns[i].ToString().ToUpper() + "\t");
// }
// wr.WriteLine();
// //write rows to excel file
// for (int i = 0; i < (dsExcelData.Tables[0].Rows.Count); i++)
// {
// for (int j = 0; j < dsExcelData.Tables[0].Columns.Count; j++)
// {
// if (dsExcelData.Tables[0].Rows[i][j] != null)
// {
// wr.Write(Convert.ToString(dsExcelData.Tables[0].Rows[i][j]) + "\t");
// }
// else
// {
// wr.Write("\t");
// }
// }
// //go to next line
// wr.WriteLine();
// }
// //close file
// wr.Close();
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
//private void convertToExcel(DataSet dsBook)
//{
// try
// {
// int rows = dsBook.Tables[4].Rows.Count + 1;
// int cols = dsBook.Tables[4].Columns.Count;
// string ExcelFileName = "D:\\abcd.xls";
// if (File.Exists(ExcelFileName))
// {
// File.Delete(ExcelFileName);
// }
// StreamWriter writer = new StreamWriter(ExcelFileName, false);
// writer.WriteLine("");
// writer.WriteLine("");
// writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
// writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
// writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
// writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
// writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
// writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
// writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
// writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
// writer.WriteLine(" <Company>51aspx.com</Company>");
// writer.WriteLine(" <Version>11.6408</Version>");
// writer.WriteLine(" </DocumentProperties>");
// writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
// writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
// writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
// writer.WriteLine(" <WindowTopX>480</WindowTopX>");
// writer.WriteLine(" <WindowTopY>15</WindowTopY>");
// writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
// writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
// writer.WriteLine(" </ExcelWorkbook>");
// writer.WriteLine(" <Styles>");
// writer.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
// writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>");
// writer.WriteLine(" <Borders/>");
// writer.WriteLine(" <Font/>");
// writer.WriteLine(" <Interior/>");
// writer.WriteLine(" <Protection/>");
// writer.WriteLine(" </Style>");
// writer.WriteLine(" <Style ss:ID=\"s21\">");
// writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
// writer.WriteLine(" </Style>");
// writer.WriteLine(" </Styles>");
// writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
// writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
// writer.WriteLine(" x:FullRows=\"1\">");
// //generate title
// writer.WriteLine("<Row>");
// foreach (DataColumn eachCloumn in dsBook.Tables[4].Columns)
// {
// writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
// writer.Write(eachCloumn.ColumnName.ToString());
// writer.WriteLine("</Data></Cell>");
// }
// writer.WriteLine("</Row>");
// //generate data
// foreach (DataRow eachRow in dsBook.Tables[4].Rows)
// {
// writer.WriteLine("<Row>");
// for (int currentRow = 0; currentRow != cols; currentRow++)
// {
// writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
// writer.Write(eachRow[currentRow].ToString());
// writer.WriteLine("</Data></Cell>");
// }
// writer.WriteLine("</Row>");
// }
// writer.WriteLine(" </Table>");
// writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
// writer.WriteLine(" <Selected/>");
// writer.WriteLine(" <Panes>");
// writer.WriteLine(" <Pane>");
// writer.WriteLine(" <Number>3</Number>");
// writer.WriteLine(" <ActiveRow>1</ActiveRow>");
// writer.WriteLine(" </Pane>");
// writer.WriteLine(" </Panes>");
// writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
// writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
// writer.WriteLine(" </WorksheetOptions>");
// writer.WriteLine(" </Worksheet>");
// writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
// writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
// writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
// writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
// writer.WriteLine(" </WorksheetOptions>");
// writer.WriteLine(" </Worksheet>");
// writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
// writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
// writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
// writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
// writer.WriteLine(" </WorksheetOptions>");
// writer.WriteLine(" </Worksheet>");
// writer.WriteLine("</Workbook>");
// writer.Close();
// // Response.Write("<script language=\"javascript\">" + "alert('" + "convert completed!')" + "</script>");
// }
// catch (Exception ex)
// {
// // Response.Write("<script language=\"javascript\">" + "alert('" + "error! " + ex.Message + "')" + "</script>");
// }
//}
private void btnDownloadExcel_Click(object sender, EventArgs e)
{
try
{
if (rdoMatched1.Checked == true || rdoMatched2.Checked == true || rdoExtrarows1.Checked == true || rdoExtrarows2.Checked == true || rdoDuplicateRowsExcel1.Checked == true || rdoDuplicateRowsExcel2.Checked == true)
{
SaveFileDialog saveExcel = new SaveFileDialog();
//saveExcel.Filter = "Excel Files(*.xls)|*.xls;|(*.xlsx)|*.xlsx";
//saveExcel.Filter = "Excel Files(*.xls)|*.xls;";
saveExcel.Filter = "Microsoft Excel 97/2000/XP(*.xls)|*.xls;";
saveExcel.Title = "Save an Excel File";
if (rdoMatched1.Checked == true && lblMatchedCount1.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Matched_Rows_in_"+chkExcel1.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
//convertToExcel(dsExcelData);
Export1(saveExcel.FileName, dsExcelData.Tables["ResultDataTableCommon1"]);
// ExportToExcel(dsExcelData.Tables[4], saveExcel.FileName);
//write();
//ExcelExport(dsExcelData.Tables[4], saveExcel.FileName);
////a(dsExcelData.Tables[4]);
// Export all the details to Excel
// RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");
// objExport.ExportDetails(dsExcelData.Tables[4], Export.ExportFormat.Excel, saveExcel.FileName.ToString());
//// ExcelExport export = new ExcelExport();
//// export.ExportDataTable(dsExcelData.Tables[4], "Reconciler_Matched");
/////exportToExcel(dsExcelData.Tables[4], saveExcel.FileName + ".xls");
////exportDataTableToExcel(dsExcelData.Tables[4], saveExcel.FileName);
MessageBox.Show(saveExcel.FileName + " is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (rdoMatched2.Checked == true && lblMatchedCount2.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Mathed_Rows_in_" + chkExcel2.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
Export1(saveExcel.FileName, dsExcelData.Tables["ResultDataTableCommon2"]);
//saveExcel.FileName = "Reconciler_UnMatched";
//if (saveExcel.ShowDialog() == DialogResult.OK)
//{
// MessageBox.Show(saveExcel.FileName + "is saved Sucessfully");
//}
MessageBox.Show(saveExcel.FileName + "\n is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (rdoExtrarows1.Checked == true && lblExtraRowsinExcel11.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Diff_Rows_in_" + chkExcel1.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
Export1(saveExcel.FileName, dsExcelData.Tables["ExtraRowsinExcel1"]);
//saveExcel.FileName = "Reconciler_UnMatched";
//if (saveExcel.ShowDialog() == DialogResult.OK)
//{
// MessageBox.Show(saveExcel.FileName + "is saved Sucessfully");
//}
MessageBox.Show(saveExcel.FileName + "\n is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (rdoExtrarows2.Checked == true && lblExtraRowsinExcel22.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Diff_Rows_in_" + chkExcel2.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
Export1(saveExcel.FileName, dsExcelData.Tables["ExtraRowsinExcel2"]);
//saveExcel.FileName = "Reconciler_UnMatched";
//if (saveExcel.ShowDialog() == DialogResult.OK)
//{
// MessageBox.Show(saveExcel.FileName + "is saved Sucessfully");
//}
MessageBox.Show(saveExcel.FileName + "\n is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (rdoDuplicateRowsExcel1.Checked == true && lblDuplicateRowsInexcel1count.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Dup_Rows_in_" + chkExcel1.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
Export1(saveExcel.FileName, dsExcelData.Tables["dtduplicatesinexcel1"]);
//saveExcel.FileName = "Reconciler_UnMatched";
//if (saveExcel.ShowDialog() == DialogResult.OK)
//{
// MessageBox.Show(saveExcel.FileName + "is saved Sucessfully");
//}
MessageBox.Show(saveExcel.FileName + "\n is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (rdoDuplicateRowsExcel2.Checked == true && lblDuplicateRowsInexcel2count.Text != "0".ToString())
{
saveExcel.FileName = "Reconciled_Dup_Rows_in_" + chkExcel2.Text;
if (saveExcel.ShowDialog() == DialogResult.OK)
{
Export1(saveExcel.FileName, dsExcelData.Tables["dtduplicatesinexcel2"]);
//saveExcel.FileName = "Reconciler_UnMatched";
//if (saveExcel.ShowDialog() == DialogResult.OK)
//{
// MessageBox.Show(saveExcel.FileName + "is saved Sucessfully");
//}
MessageBox.Show(saveExcel.FileName + "\n is saved Sucessfully", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("No records to download", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Please select one option", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
logError.Logflatfile_Ex(ex);
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public static void exportToExcel(DataTable source, string fileName)
//{
// Excel.Application oXL;
// Excel.Workbook oWB;
// Excel.Worksheet oSheet;
// Excel.Range oRange;
// // Start Excel and get Application object.
// oXL = new Excel.Application();
// // Set some properties
// oXL.Visible = true;
// oXL.DisplayAlerts = false;
// // Get a new workbook.
// oWB = oXL.Workbooks.Add(Missing.Value);
// // Get the active sheet
// oSheet = (Excel.Worksheet)oWB.ActiveSheet;
// oSheet.Name = "Customers";
// // Process the DataTable
// // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
// DataTable dt = source;
// int rowCount = 1;
// foreach (DataRow dr in dt.Rows)
// {
// rowCount += 1;
// for (int i = 1; i < dt.Columns.Count + 1; i++)
// {
// // Add the header the first time through
// if (rowCount == 2)
// {
// oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
// }
// oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
// }
// }
// // Resize the columns
// oRange = oSheet.get_Range(oSheet.Cells[1, 1],
// oSheet.Cells[rowCount, dt.Columns.Count]);
// oRange.EntireColumn.AutoFit();
// // Save the sheet and close
// oSheet = null;
// oRange = null;
// oWB.SaveAs("C:\\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
// oWB.Close(Missing.Value, Missing.Value, Missing.Value);
// oWB = null;
// oXL.Quit();
// // Clean up
// // NOTE: When in release mode, this does the trick
// GC.WaitForPendingFinalizers();
// GC.Collect();
// GC.WaitForPendingFinalizers();
// GC.Collect();
//}
//public static void exportToExcel(System.Data.DataTable source, string fileName)
//{
// System.IO.StreamWriter excelDoc;
// excelDoc = new System.IO.StreamWriter(fileName);
// //const string startExcelXML = "<Workbook>";
// const string startExcelXML = "<xml version>\r\n<Workbook " +
// //"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
// //" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
// //"xmlns:x=\"urn:schemas- microsoft-com:office:" +
// //"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
// "office:spreadsheet\">\r\n <Styles>\r\n " +
// "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
// "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
// "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
// "\r\n <Protection/>\r\n </Style>\r\n " +
// "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
// "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
// "<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
// " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
// "ss:ID=\"Decimal\">\r\n <NumberFormat " +
// "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
// "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
// "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
// "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
// "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
// "</Styles>\r\n ";
// const string endExcelXML = "</Workbook>";
// int rowCount = 0;
// int sheetCount = 1;
// excelDoc.Write(startExcelXML);
// excelDoc.Write("<Worksheet ss:Name=\"Test" + sheetCount + "\">");
// excelDoc.Write("<Table>");
// excelDoc.Write("<Row>");
// for (int x = 0; x < source.Columns.Count; x++)
// {
// excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
// excelDoc.Write(source.Columns[x].ColumnName);
// excelDoc.Write("</Data></Cell>");
// }
// excelDoc.Write("</Row>");
// foreach (DataRow x in source.Rows)
// {
// rowCount++;
// //if the number of rows is > 64000 create a new page to continue output
// if (rowCount == 64000)
// {
// rowCount = 0;
// sheetCount++;
// excelDoc.Write("</Table>");
// excelDoc.Write(" </Worksheet>");
// excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
// excelDoc.Write("<Table>");
// }
// excelDoc.Write("<Row>"); //ID=" + rowCount + "
// for (int y = 0; y < source.Columns.Count; y++)
// {
// System.Type rowType;
// rowType = x[y].GetType();
// switch (rowType.ToString())
// {
// case "System.String":
// string XMLstring = x[y].ToString();
// XMLstring = XMLstring.Trim();
// XMLstring = XMLstring.Replace("&", "&");
// XMLstring = XMLstring.Replace(">", ">");
// XMLstring = XMLstring.Replace("<", "<");
// excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
// "<Data ss:Type=\"String\">");
// excelDoc.Write(XMLstring);
// excelDoc.Write("</Data></Cell>");
// break;
// case "System.DateTime":
// //Excel has a specific Date Format of YYYY-MM-DD followed by
// //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
// //The Following Code puts the date stored in XMLDate
// //to the format above
// DateTime XMLDate = (DateTime)x[y];
// string XMLDatetoString = ""; //Excel Converted Date
// XMLDatetoString = XMLDate.Year.ToString() +
// "-" +
// (XMLDate.Month < 10 ? "0" +
// XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
// "-" +
// (XMLDate.Day < 10 ? "0" +
// XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
// "T" +
// (XMLDate.Hour < 10 ? "0" +
// XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
// ":" +
// (XMLDate.Minute < 10 ? "0" +
// XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
// ":" +
// (XMLDate.Second < 10 ? "0" +
// XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
// ".000";
// excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
// "<Data ss:Type=\"DateTime\">");
// excelDoc.Write(XMLDatetoString);
// excelDoc.Write("</Data></Cell>");
// break;
// case "System.Boolean":
// excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
// "<Data ss:Type=\"String\">");
// excelDoc.Write(x[y].ToString());
// excelDoc.Write("</Data></Cell>");
// break;
// case "System.Int16":
// case "System.Int32":
// case "System.Int64":
// case "System.Byte":
// excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
// "<Data ss:Type=\"Number\">");
// excelDoc.Write(x[y].ToString());
// excelDoc.Write("</Data></Cell>");
// break;
// case "System.Decimal":
// case "System.Double":
// excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
// "<Data ss:Type=\"Number\">");
// excelDoc.Write(x[y].ToString());
// excelDoc.Write("</Data></Cell>");
// break;
// case "System.DBNull":
// excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
// "<Data ss:Type=\"String\">");
// excelDoc.Write("");
// excelDoc.Write("</Data></Cell>");
// break;
// default:
// throw (new Exception(rowType.ToString() + " not handled."));
// }
// }
// excelDoc.Write("</Row>");
// }
// excelDoc.Write("</Table>");
// excelDoc.Write(" </Worksheet>");
// excelDoc.Write(endExcelXML);
// excelDoc.Close();
//}
//private void lstExcel1Columns_Leave(object sender, EventArgs e)
//{
// if (lstExcel1Columns.SelectedItems.Count > 0)
// {
// lstExcel1Columns.SelectedIndex = -1;
// }
//}
//private void lstExcel2Columns_Leave(object sender, EventArgs e)
//{
// if (lstExcel2Columns.SelectedItems.Count > 0)
// {
// lstExcel2Columns.SelectedIndex = -1;
// }
//}
private void lstExcel1Columns_SelectedIndexChanged(object sender, EventArgs e)
{
lstExcel2Columns.SelectedIndex = -1;
//if (lstExcel1Columns.SelectedItems.Count > 0)
//{
// //lstExcel1Columns.SelectedIndex = -1;
//}
}
private void lstExcel2Columns_SelectedIndexChanged(object sender, EventArgs e)
{
lstExcel1Columns.SelectedIndex = -1;
//if (lstExcel2Columns.SelectedItems.Count > 0)
//{
// //lstExcel2Columns.SelectedIndex = -1;
//}
}
private void ExcelComparison_Load(object sender, EventArgs e)
{
this.Text = this.Text + " v" + Assembly.GetExecutingAssembly().GetName().Version.ToString().Substring(0, 3);
}
}
}
