C#用winform实现excel数据合并

1、问题描述

       一共有3表，A是源操作表，B、C目的操作表。A B C 中都有两个个相同的字段。问题：现在用A中的这个关键字段分别去B和C中取得对应项的数据，在写回A表中的对应的另外一个字段。

         这个工具目前设定的是;操作excel表，文件格式：.csv  | .xls 

2、结果

    

   点击确定，完成转换。

3、代码

     A、打开工具箱：添加openFileDialog控件，命名采用默认的就行了。

     B、这次的代码做了stylecop规范检查。

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.OleDb;using System.Drawing;using System.IO;using System.Linq;using System.Reflection;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace ModifyData{    /// <summary>    ///  窗口类    /// </summary>    public partial class Form1 : Form    {        #region private member        /// <summary>        /// 新建文件保存路径        /// </summary>        private string savePath = string.Empty;        /// <summary>        /// CSV文件数据集        /// </summary>        private DataSet fundCSVDs = null;        /// <summary>        /// 交易支付数据集        /// </summary>        private DataSet payXLSDs = null;        /// <summary>        /// 交易退款数据集        /// </summary>        private DataSet backXLSDs = null;        /// <summary>        ///  数据库连接        /// </summary>        private OleDbConnection conn;        /// <summary>        /// 源文件        /// </summary>        private string orinFileName = string.Empty;        /// <summary>        /// 目标文件        /// </summary>        private string[] aimFileName = new string[2];        /// <summary>        /// 批量读取        /// </summary>        private string[] everyRead = new string[300];        #endregion        #region 构造函数        /// <summary>        /// 构造函数        /// </summary>        public Form1()        {            this.InitializeComponent();            tbOrign.ReadOnly = true;            tbAim1.ReadOnly = true;            tbAim2.ReadOnly = true;        }        #endregion        #region 控件方法        /// <summary>        /// 添加源浏览文件        /// </summary>        /// <param name="sender">消息对象</param>        /// <param name="e">消息体</param>        private void BtnOrinSelect_Click(object sender, EventArgs e)        {            openFileDialog1.Filter = "Excel表格(CSV格式(*.csv)|*.csv";            openFileDialog1.FileName = string.Empty;            openFileDialog1.ShowDialog();            string selectFileName = openFileDialog1.FileName;            // 获取保存文件名路径            this.savePath = selectFileName;            tbOrign.Text = selectFileName;            // 获取文件名            this.orinFileName = tbOrign.Text.Remove(0, selectFileName.LastIndexOf("\\") + 1);        }        /// <summary>        /// 添加目的文件1        /// </summary>        /// <param name="sender">消息对象</param>        /// <param name="e">消息体</param>        private void BtnAim1_Click(object sender, EventArgs e)        {            openFileDialog1.Filter = "Excel表格(*.xls)|*.xls";            openFileDialog1.FileName = string.Empty;            openFileDialog1.ShowDialog();            string selectFileName = openFileDialog1.FileName;            // 文件名显示框            tbAim1.Text = selectFileName;                        // 保存文件名            this.aimFileName[0] = tbAim1.Text.Remove(0, selectFileName.LastIndexOf("\\") + 1);        }        /// <summary>        /// 添加目的文件2        /// </summary>        /// <param name="sender">消息对象</param>        /// <param name="e">消息体</param>        private void BtnAim2_Click(object sender, EventArgs e)        {            openFileDialog1.Filter = "Excel表格(*.xls)|*.xls";            openFileDialog1.FileName = string.Empty;            openFileDialog1.ShowDialog();            string selectFileName = openFileDialog1.FileName;            tbAim2.Text = selectFileName;            this.aimFileName[1] = selectFileName;            // 保存文件名            this.aimFileName[1] = tbAim2.Text.Remove(0, selectFileName.LastIndexOf("\\") + 1);        }        /// <summary>        /// 转换        /// </summary>        /// <param name="sender">消息对象</param>        /// <param name="e">消息体</param>        private void BtnChange_Click(object sender, EventArgs e)        {            try            {                // 1、读取 csv文件数据                this.AnalysisCSV(tbOrign.Text);                // 2、解析交易 支付                this.AnalysisPayXLS(tbAim1.Text);                // 3、解析交易退款                this.AnalysisBackXLS(tbAim2.Text);                // 先比较交易支付表 与 资金报表                this.CompareTable(this.payXLSDs, this.fundCSVDs);                // 再比较交易退款报表 表 与 资金报表                this.CompareTable(this.backXLSDs, this.fundCSVDs);                // 删除资金报表，重新生成                this.SaveFile(this.fundCSVDs.Tables[0], tbOrign.Text);                MessageBox.Show("完成");            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        /// <summary>        /// 窗口加载程序        /// </summary>        /// <param name="sender">消息对象</param>        /// <param name="e">消息体</param>        private void Form1_Load(object sender, EventArgs e)        {            MessageBox.Show("请保证操作的文件没有被打开");        }        /*            * ================  拖动文件        /// <summary>        /// 允许拖动文件        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void Form1_DragEnter(object sender, DragEventArgs e)        {            if (e.Data.GetDataPresent(DataFormats.FileDrop))            {                e.Effect = DragDropEffects.Link;            }            else            {                e.Effect = DragDropEffects.None;            }        }        private void Form1_DragDrop(object sender, DragEventArgs e)        {            string filePath = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();            if (filePath.Contains(".CSV") || filePath.Contains(".csv"))            {                tbOrign.Text = filePath;            }            else if (filePath.Contains("支付"))            {                tbAim1.Text = filePath;                // 保存文件名                aimFileName[0] = filePath;            }            else            {                aimFileName[1] = filePath;                tbAim2.Text = filePath;            }        }         * ====================== 拖动文件 -======================         * */        #endregion        #region 解析文件CSV文件        /// <summary>        /// 解析 csv文件        /// </summary>        /// <param name="filePath">全路径</param>        private void AnalysisCSV(string filePath)        {            try            {                string connStr = this.GetConnStr(filePath);                            OleDbDataAdapter myCommand = null;                using (this.conn = new OleDbConnection(connStr))                {                    this.conn.Open();                    string strExcel = "Select   *   From   [" + this.orinFileName + "]";                    myCommand = new OleDbDataAdapter(strExcel, this.conn);                    DataSet ds = new DataSet();                    myCommand.Fill(ds, "table1");                    this.fundCSVDs = ds;                    GC.Collect();                }            }            catch (System.Exception ex)            {                MessageBox.Show(ex.Message);            }        }        #endregion        #region 解析 .xls文件        /// <summary>        /// 解析交易支付报表        /// </summary>        /// <param name="filePath">支付报表全路径</param>        private void AnalysisPayXLS(string filePath)        {            try            {                // 获得连接                string connStr = this.GetConnStr(filePath);                if (System.IO.File.Exists(filePath))                {                    OleDbDataAdapter myCommand = null;                    DataSet ds = null;                    using (this.conn = new OleDbConnection(connStr))                    {                        this.conn.Open();                        // 得到所有sheet的名字                        DataTable sheetsName = this.conn.GetOleDbSchemaTable(                            OleDbSchemaGuid.Tables,                             new object[] { null, null, null, "Table" });                        // 得到第一个sheet的名字                        string firstSheetName = sheetsName.Rows[0][2].ToString();                         string strExcel = "Select 支付订单号, 支付人 From   [" + firstSheetName + "]";                        myCommand = new OleDbDataAdapter(strExcel, this.conn);                        ds = new DataSet();                        myCommand.Fill(ds, "Sheet");                        this.payXLSDs = ds;                    }                    GC.Collect();                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        /// <summary>        ///  解析交易退款报表        /// </summary>        /// <param name="filePath">交易退款报表全路径</param>        private void AnalysisBackXLS(string filePath)        {            try            {                string connStr = this.GetConnStr(filePath);                if (System.IO.File.Exists(filePath))                {                    OleDbDataAdapter myCommand = null;                    DataSet ds = null;                    using (this.conn = new OleDbConnection(connStr))                    {                        this.conn.Open();                        DataTable sheetsName = this.conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); // 得到所有sheet的名字                        string firstSheetName = sheetsName.Rows[0][2].ToString(); // 得到第一个sheet的名字                        string strExcel = "Select 支付订单号, 支付人 From   [" + firstSheetName + "]";                        myCommand = new OleDbDataAdapter(strExcel, this.conn);                        ds = new DataSet();                        myCommand.Fill(ds, "Sheet");                        this.backXLSDs = ds;                    }                    GC.Collect();                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        #endregion        #region 修改        /// <summary>        /// 修改        /// </summary>        /// <param name="outDs">外循环Ds</param>        /// <param name="inDs">内循环Ds</param>        private void CompareTable(DataSet outDs, DataSet inDs)        {            for (int i = 0; i < outDs.Tables[0].Rows.Count; i++)            {                string cell = outDs.Tables[0].Rows[i][0].ToString().Trim();                for (int j = 0; j < inDs.Tables[0].Rows.Count; j++)                {                    // 找到订单号对应的支付人，将其填写B2B账户                    if (cell == inDs.Tables[0].Rows[j][4].ToString().Trim())                    {                        inDs.Tables[0].Rows[j][14] = outDs.Tables[0].Rows[i][1];                        break;                    }                }            }        }        #endregion        #region 连接语句        /// <summary>        /// 获取连接sql语句        /// </summary>        /// <param name="filePath">报表全路径</param>        /// <returns>连接sql语句</returns>        private string GetConnStr(string filePath)        {            string connStr = string.Empty;            if (filePath.Contains(".xls"))            {               connStr  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";            }            else            {                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath.Remove(filePath.LastIndexOf("\\") + 1) + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";            }            return connStr;        }        #endregion        #region 保存文件        /// <summary>        /// 保存文件        /// </summary>        /// <param name="dt">DataTable</param>        /// <param name="fullPath">保存的文件路径</param>        private void SaveFile(DataTable dt, string fullPath)        {            // 存在该文件，就删除，重新建立            if (File.Exists(fullPath))            {                File.Delete(fullPath);            }            FileInfo fi = new FileInfo(fullPath);            if (!fi.Directory.Exists)            {                fi.Directory.Create();            }            FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write);            StreamWriter sw = new StreamWriter(fs, Encoding.Default);                         string data = string.Empty;            // 输出列名称             for (int i = 0; i < dt.Columns.Count; i++)            {                data += dt.Columns[i].ColumnName.ToString() + ",";            }            data = data.Substring(0, data.Length - 1);            sw.WriteLine(data);            // 输出各行数据             for (int i = 0; i < dt.Rows.Count; i++)            {                data = string.Empty;                for (int j = 0; j < dt.Columns.Count; j++)                {                    string str = dt.Rows[i][j].ToString();                    str = str.Replace("\"", "\"\"");                    // 替换英文引号                     if (str.Contains(',') || str.Contains('"') || str.Contains('\r') || str.Contains('\n'))                    {                        // 含逗号、引号、换行符的需要放到引号中                         str = string.Format("\"{0}\"", str);                    }                    data += str;                    if (j < dt.Columns.Count - 1)                    {                        data += ",";                    }                }                sw.WriteLine(data);            }            sw.Close();            fs.Close();        }        #endregion    }}

4、拖拽文件到窗口上，显示路径

      本来是做了拖拽功能的，但是，需求变更。所以就没有写上去了，我在上面的代码中注释了，下面重新贴出来

       A、选中窗口，选择其属性，设置AllowDrop 为true

            

 

      B、选择窗口事件，设置事件如下：

      C，我的实现

        private void Form1_DragEnter(object sender, DragEventArgs e)        {            if (e.Data.GetDataPresent(DataFormats.FileDrop))            {                e.Effect = DragDropEffects.Link;            }            else            {                e.Effect = DragDropEffects.None;            }        }        private void Form1_DragDrop(object sender, DragEventArgs e)        {            string filePath = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();            if (filePath.Contains(".CSV") || filePath.Contains(".csv"))            {                tbOrign.Text = filePath;            }            else if (filePath.Contains("支付"))            {                tbAim1.Text = filePath;                // 保存文件名                aimFileName[0] = filePath;            }            else            {                aimFileName[1] = filePath;                tbAim2.Text = filePath;            }        }

           关于拖拽文件到窗口上，参考连接：http://bbs.csdn.net/topics/90003668