C#WinForm实现对Excel的数据处理

这是一个简单的WinForm程序，功能是导入Excel文件，根据文件中的某一列从数据库里获取需要的数据，再将二者整合后导出到一个新的Excel文件里存储。
1.界面设计如图

2.获取文件路径

        private void openButton_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog()
                {Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx"})
            {
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    pathText.Text = openFileDialog.FileName;//文件路径
                }
                else
                {
                    msgText.Text = "文件框无法打开！";
                }
            }
        }

3.根据excel的路径把第一个sheet中的内容放入datatable

        private DataTable ReadExcelToTable(string path, int x) //excel存放的路径
        {
            try
            {
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 
                string conStr =
                    string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;",
                        path);
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(conStr))
                {
                    conn.Open();
                    DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    string sheetName = dtSheet.Rows[x]["Table_Name"].ToString();
                    string sql = string.Format("select * from [{0}]", sheetName);
                    oda.SelectCommand = new OleDbCommand(sql, conn);
                    oda.Fill(ds);
                    dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {
                msgText.Text = ex.Message;
                return null;
            }
        }

4.根据某一列值从数据库里获取需要的数据

        private DataTable GetNeedBySn(List<string> snList)
        {
            msgText.Text = "正在查询数据，请稍等！";
            string sql = "...";//sql语句,自己写
            snList.ForEach(it => { sql += it; });//使用传入数据
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    "Persist Security Info=False;User ID=XXX;Password=XXX;Initial Catalog=XXX;Server=XXX";//数据库相关配置
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Connection = conn;
                cmd.CommandTimeout = 600;

                using (SqlDataAdapter sda1 = new SqlDataAdapter(cmd))
                {
                    DataTable dt1 = new DataTable();
                    sda1.Fill(dt1);
                    msgText.Text = "数据查询成功";
                    return dt1;
                }
            }
            catch (Exception ex)
            {
                msgText.Text = ex.Message;
                return null;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

5.点击转换按钮匹配EXCEL模板文件，进入CreatEXCEL方法

        private void transButton_Click(object sender, EventArgs e)
        {
            string FilePath = Path.Combine(Path.Combine(Application.StartupPath, "Templates"), "模板文件.xlsx");
            if (!File.Exists(FilePath))
            {
                msgText.Text = "模板文件" + FilePath + "未找到!";
            }

            string result = CreatEXCEL(FilePath);   //生成EXCEL
            if (!result.Contains("pass"))
            {
                msgText.Text = result;
            }
            else
            {
                msgText.Text = "文件导出成功！";
            }
        }

6.设置生成的文件里的数据

        private string CreatEXCEL(string inPath)
        {
            try
            {
                Workbook workbook = new Workbook();
                workbook.LoadTemplateFromFile(inPath);

                for (int i = 0; i < 4; i++)
                {
                    Worksheet sheet = workbook.Worksheets[i];
                    var dt = ReadExcelToTable(pathText.Text, i);
                    List<string> snList = new List<string>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        var s = dr[0].ToString().Trim();
                        if (s.Length == 13)
                        {
                            snList.Add(s);
                        }
                    }

                    if (snList != null && snList.Count != 0)
                    {
                        var temp = GetNeedBySn(snList);
                        //dt.Merge(temp);
                        var res = GetMergeTable(dt, temp);
                        if (res != null)
                        {
                            sheet.InsertDataTable(res, false, 2, 1, true);
                        }
                        else
                        {
                            return "查询的数据数目有问题！";
                        }
                    }
                    else
                    {
                        sheet.InsertDataTable(dt, false, 2, 1, true);
                    }
                }
                ShowSaveFileDialog(workbook);
                workbook.Dispose();
                return "pass";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

7.初始表格的dadatable和查询数据的datatable组合成一个datatable

        private DataTable GetMergeTable(DataTable dt1, DataTable dt2)
        {
            DataTable dt = dt2.Clone();
            
            if (dt1.Rows.Count != dt2.Rows.Count)
            {
                return null;
            }
            else
            {
                dt1.Columns.Add("NVSN", Type.GetType("System.String"));
                dt1.Columns.Add("STATION", Type.GetType("System.String"));
                dt1.Columns.Add("START_TIME", Type.GetType("System.String"));
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    var m = dt1.Rows[i][0].ToString();//Unique Unit ID
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        var n = dt2.Rows[j][0].ToString();//NVSN
                        if (m.Equals(n))
                        {
                            DataRow row = dt2.Rows[j];
                            dt.Rows.Add(row.ItemArray);
                        }
                    }
                }
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    dt1.Rows[i][dt1.Columns.Count - 3] = dt.Rows[i][0];
                    dt1.Rows[i][dt1.Columns.Count - 2] = dt.Rows[i][1];
                    dt1.Rows[i][dt1.Columns.Count - 1] = dt.Rows[i][2];
                }
                return dt1;
            }
        }

8.生成新的文件

private void ShowSaveFileDialog(Workbook workbook)
        {
            //string localFilePath, fileNameExt, newFileName, FilePath; 
            SaveFileDialog sfd = new SaveFileDialog();
            //设置文件类型 
            sfd.Filter = "Excel文件|*.xlsx|.xls|";
            //设置默认文件类型显示顺序 
            sfd.FilterIndex = 1;
            //保存对话框是否记忆上次打开的目录 
            sfd.RestoreDirectory = true;
            //设置默认文件扩展名。
            sfd.DefaultExt = "xlsx";
            //设置默认的文件名
            sfd.FileName = "生成文件" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            //点了保存按钮进入 
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                string localFilePath = sfd.FileName; //获得文件路径 
                workbook.SaveToFile(localFilePath, ExcelVersion.Version2010);
            }
        }