public partial class Form1 : Form { private System.Data.DataSet myDataSet; public Form1() { InitializeComponent(); }
private void button1_Click(object sender, EventArgs e) { GetConnect(); dataGridView1.DataMember = "[Sheet1$]"; dataGridView1.DataSource = myDataSet; dataGridView2.DataMember = "[Sheet2$]"; dataGridView2.DataSource = myDataSet; }
private void GetConnect() { string strCon = textBox1.Text.Trim(); OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [Sheet1$] "; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myDataSet = new DataSet(); myCommand.Fill(myDataSet, "[Sheet1$]"); myConn.Close();
strCom = " SELECT * FROM [Sheet2$] "; myConn.Open(); myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(myDataSet, "[Sheet2$]"); myConn.Close(); }
private void button2_Click(object sender, EventArgs e) { for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView2.Rows.Count; j++) { if (dataGridView1.Rows[i].Cells[0].Value.ToString() == dataGridView2.Rows[j].Cells[0].Value.ToString()) { dataGridView1.Rows[i].Cells[1].Value = Decimal.Parse(dataGridView1.Rows[i].Cells[1].Value.ToString()) + Decimal.Parse(dataGridView2.Rows[j].Cells[1].Value.ToString()); } } }
for (int i = 0; i < dataGridView2.Rows.Count; i++) { int j = 0; for (j = 0; j < dataGridView1.Rows.Count; j++) { if (dataGridView2.Rows[i].Cells[0].Value.ToString() == dataGridView1.Rows[j].Cells[0].Value.ToString()) { break; } } if (j == dataGridView1.Rows.Count) { DataRow dr = myDataSet.Tables["[Sheet1$]"].NewRow(); dr[0] = dataGridView2.Rows[i].Cells[0].Value.ToString(); dr[1] = dataGridView2.Rows[i].Cells[1].Value.ToString(); myDataSet.Tables["[Sheet1$]"].Rows.Add(dr); } } }
private void button3_Click(object sender, EventArgs e) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); for (int i = 0; i < dataGridView1.Rows.Count;i++ ) { excel.Cells[i + 1, 1] = dataGridView1.Rows[i].Cells[0].Value.ToString(); excel.Cells[i + 1, 2] = dataGridView1.Rows[i].Cells[1].Value.ToString(); } //excel.Cells[1, 1] = "第一行第一列"; //excel.Cells[1, 2] = "第一行第二列"; //excel.Cells[2, 1] = "第二行第一列"; //excel.Cells[2, 2] = "第二行第二列"; //excel.Cells[3, 1] = "第三行第一列"; //excel.Cells[3, 2] = "第三行第二列";
excel.Visible = true;
}
}
注意细节：
1、在visual Studio 命令提示工具中，定位到Excel安装目录，运行“tlbimp Excel.EXE Excel.dll”命令，把Excel.exe编译为 Excel.dll
2、为项目添加 EXCEL.DLL的引用方法如下: 项目 -> 添加引用 -> 浏览 -> 定位到EXCEL.DLL
