using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel;
using System.Reflection;
namespace PMS
{
public partial class DrxzForm : Form
{
// 定义下拉列表框
private ComboBox cmb_Temp = new ComboBox();
private ComboBox cmb_Temp1 = new ComboBox();
public DataSet ds=new DataSet();
public DrxzForm()
{
InitializeComponent();
}
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataSet ExcelToDS(string Path)
{
Excel.Application m_Excel = new Excel.Application();//创建一个Excel对象(同时启动EXCEL.EXE进程)
Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Open(Path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));//打开工作簿
Excel._Worksheet m_Sheet = (Excel._Worksheet)(m_Book.Worksheets.get_Item(1));//读取第一个工作表 m_Book.Sheets.Count为最后一个
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [" + m_Sheet.Name + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds);
m_Book.Close(false, Missing.Value, Missing.Value);
m_Excel.Quit();
m_Book = null;
m_Sheet = null;
m_Excel = null;
return ds;
}
private void button3_Click(object sender, EventArgs e)
{
this.Close();
}
private void DrxzForm_Load(object sender, EventArgs e)
{
this.button1.Enabled = true;
this.button2.Enabled = false;
}
// 当用户选择下拉列表框时改变DataGridView单元格的内容
private void cmb_Temp_SelectedIndexChanged(object sender, EventArgs e)
{
if (((ComboBox)sender).Text != dataGridView1.CurrentCell.Value.ToString())
{
dataGridView1.CurrentCell.Value = cmb_Temp.Text.Trim();
}
}
private void button2_Click(object sender, EventArgs e)
{
//for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
//{
if (dataGridView1.Rows[0].Cells[2].Value.ToString() ==""|| dataGridView1.Rows[0].Cells[1].Value.ToString().Trim() == dataGridView1.Rows[0].Cells[2].Value.ToString().Trim())
{
//将数据写入数据库
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
Base bb = new Base();
if (int.Parse(bb.ReturnValue("select count(*) from TblZz where 员工编号='" + ds.Tables[0].Rows[i][0].ToString().Trim() + "'", 0)) <= 0 && ds.Tables[0].Rows[i][0].ToString().Trim() != "")
{
Base bb1 = new Base();
//在这边设置对应字段名即可
if (bb1.ExeSQL("insert into TblZz(员工编号,姓名,所属部门) values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "')") > 0)
{
MessageBox.Show("数据导入成功" + Convert.ToString(i) + "行");
}
else { MessageBox.Show("数据导入错误" + Convert.ToString(i) + "行"); }
}
else
{
//将重复数据覆盖
Base bb1 = new Base();
if (bb1.ExeSQL("update TblZz set 姓名='" + ds.Tables[0].Rows[i][1].ToString() + "',所属部门='" + ds.Tables[0].Rows[i][2].ToString() + "' where 员工编号='" + ds.Tables[0].Rows[i][0].ToString().Trim() + "'") > 0)
{
MessageBox.Show("数据覆盖成功" + Convert.ToString(i) + "行");
}
}
}
//dataGridView1.Rows[i].Cells[2].Value = dataGridView1.Rows[i].Cells[1].Value;
ZzForm.ZForm.DBDataGrid();
GC.Collect();//强制垃圾回收,否则EXCEL.EXE进程不能及时退出
this.Close();
}
else
{
MessageBox.Show("第 " + Convert.ToString(1)+" 行的系统[员工编号]字段对应的Excel字段值不能为空或不一致!");
return;
}
//}
}
// 当用户移动到性别这一列时单元格显示下拉列表框
private void dataGridView1_CurrentCellChanged(object sender, EventArgs e)
{
try
{
if (this.dataGridView1.CurrentCell.ColumnIndex == 2)
{
System.Drawing.Rectangle rect = dataGridView1.GetCellDisplayRectangle(dataGridView1.CurrentCell.ColumnIndex, dataGridView1.CurrentCell.RowIndex, false);
cmb_Temp.Text = dataGridView1.CurrentCell.Value.ToString();
cmb_Temp.Left = rect.Left;
cmb_Temp.Top = rect.Top;
cmb_Temp.Width = rect.Width;
cmb_Temp.Height = rect.Height;
cmb_Temp.Visible = true;
}
else
{
cmb_Temp.Visible = false;
}
}
catch
{
}
}
private void dataGridView1_Scroll(object sender, ScrollEventArgs e)
{
this.cmb_Temp.Visible = false;
}
private void dataGridView1_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)
{
this.cmb_Temp.Visible = false;
}
private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
//for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
//{
// if (dataGridView1.Rows[i].Cells[2].Value != null && dataGridView1.Rows[i].Cells[2].ColumnIndex == 2)
// {
// dataGridView1.Rows[i].Cells[2].Value = dataGridView1.Rows[i].Cells[1].Value;
// }
//}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog OpenFileDialog1 = new OpenFileDialog();
OpenFileDialog1.Filter = "Excel文件(*.xls)|*.xls";
if (OpenFileDialog1.ShowDialog() == DialogResult.OK)
{
ds = ExcelToDS(@OpenFileDialog1.FileName);
int x = ds.Tables.Count;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.AddRange(
new DataColumn[]{
new DataColumn("col1",typeof(string)),
new DataColumn("col2",typeof(string)),
new DataColumn("col3",typeof(string)),
new DataColumn("col4",typeof(string)),
new DataColumn("col5",typeof(string)),
new DataColumn("col6",typeof(string)),
new DataColumn("col7",typeof(string)),
new DataColumn("col8",typeof(string)),
new DataColumn("col9",typeof(string)),
new DataColumn("col10",typeof(string)),
new DataColumn("col11",typeof(string)),
new DataColumn("col12",typeof(string)),
new DataColumn("col13",typeof(string)),
new DataColumn("col14",typeof(string))
});
//填充表格
cmb_Temp.Visible = false;
// 添加下拉列表框事件
cmb_Temp.SelectedIndexChanged += new EventHandler(cmb_Temp_SelectedIndexChanged);
// 将下拉列表框加入到DataGridView控件中
this.dataGridView1.Controls.Add(cmb_Temp);
Base bb = new Base();
System.Data.DataTable mytable= bb.GetSearchForTblZz().Tables[0];
mytable.Columns.Add("Excel字段名");
//填充下拉框
cmb_Temp.Items.Clear();
cmb_Temp.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox1.Items.Clear();
for (int i = 0; i <= ds.Tables[0].Columns.Count - 1; i++)
{
if (ds.Tables[0].Rows[0][i].ToString().Length <= 0)
continue;
cmb_Temp.Items.Add(ds.Tables[0].Rows[0][i].ToString().Trim().Replace(" ", ""));
comboBox1.Items.Add(ds.Tables[0].Rows[0][i].ToString().Trim().Replace(" ", ""));
}
for (int i = 0; i <= mytable.Rows.Count-1; i++)
{
if (mytable.Rows[i]["Excel字段名"].ToString() == "" && mytable.Rows[i]["字段名"].ToString() == cmb_Temp.Items[i].ToString())//如果该行该列数据为空,则添加
{
mytable.Rows[i]["Excel字段名"]= mytable.Rows[i]["字段名"];
cmb_Temp.SelectedText = mytable.Rows[i]["字段名"].ToString();
}
}
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1].ToString().Length <= 0)
continue;
DataRow dr = dt.NewRow();
dr.ItemArray = ds.Tables[0].Rows[i].ItemArray;
dt.Rows.Add(dr);
}
this.dataGridView1.DataSource = mytable;
this.button2.Enabled = true;
this.dataGridView2.DataSource = dt;
this.dataGridView2.AutoGenerateColumns = false;
}
}
catch (Exception Mye)
{
MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.button1.Enabled = false;
GC.Collect();//强制垃圾回收,否则EXCEL.EXE进程不能及时退出
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}