Hi,
My question is not about the component. it is also about with sql. i know how can i import excels in c# form, but i could'nt compair them.
i cant do thes in excel documents, it must be in c#, automaticly.
theese are codes,
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.Runtime.InteropServices;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog DialogA = new OpenFileDialog();
DialogA.CheckFileExists = true;
DialogA.Title = "Select a File";
DialogA.ShowDialog();
textBox1.Text = DialogA.FileName;
/*
if (DialogA.ShowDialog() == DialogResult.OK)
{
textBox1.Text = DialogA.FileName;
}
else if (textBox1.Text == "")
{
MessageBox.Show("please select a file");
}
*/
}
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog DiaologB = new OpenFileDialog();
DiaologB.CheckFileExists = true;
DiaologB.Title = "select a file";
DiaologB.ShowDialog();
textBox2.Text = DiaologB.FileName;
}
private void button3_Click(object sender, EventArgs e)
{
string filename1 = textBox1.Text;
string filename2 = textBox2.Text;
string file1_sheet = GetExcelSheets(filename1);
string file2_sheet = GetExcelSheets(filename2);
String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename1 + ";" + "Extended Properties=Excel 12.0;";
String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename2 + ";" + "Extended Properties=Excel 12.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString1);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt1 = objDataset1.Tables[0];
objConn.Close();
objConn = new OleDbConnection(sConnectionString2);
objConn.Open();
objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt2 = objDataset1.Tables[0];
objConn.Close();
objConn.Open();
objCmdSelect = new OleDbCommand("Select * from [" + file1_sheet + "$], [" + file2_sheet + "$] where [" + file1_sheet + "$].barkod == [" + file2_sheet + "$].barkod", objConn);
objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData"); // " OleDbException was unhandled " this error is giving
DataTable dt3 = objDataset1.Tables[0];
objConn.Close();
DataRow[] rows1 = dt1.Select("", "barkod ASC");
DataRow[] rows2 = dt2.Select("", "barkod ASC");
DataRow[] rows3 = dt3.Select("", "barkod ASC");
dataGridView1.DataSource = dt3;
//DataRow datarow1, datarow2;
}
public string GetExcelSheets(string excelFileName)
{
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
excel.Sheets sheets = workBookObject.Worksheets;
// get the first and only worksheet from the collection of worksheets
excel.Worksheet worksheet = (excel.Worksheet)sheets.get_Item(1);
MessageBox.Show(worksheet.Name);
return worksheet.Name;
}
}
}