C#用winform实现excel数据合并

爱被打了一巴掌 2022-07-19 05:13 402阅读 0赞

1、问题描述

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

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

2、结果

![Center][]

点击确定，完成转换。

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

![Center 1][]

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

![Center 2][]

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][http_bbs.csdn.net_topics_90003668]

[Center]: /images/20220718/7c3d1ba6772c4cf6a5b86b4dfb2b3e1e.png
[Center 1]: /images/20220718/f9032138e40642d18f4950f6761288e0.png
[Center 2]: /images/20220718/1e03e38b18074401815c040e384c5e32.png
[http_bbs.csdn.net_topics_90003668]: http://bbs.csdn.net/topics/90003668