首页 > 代码库 > 实战基础技能(21)--------多数据拼凑成表插入到数据库中

实战基础技能(21)--------多数据拼凑成表插入到数据库中

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Collections;namespace TechnologyProject.项目管理.加计扣除.ERP导入{    public partial class superDeduction : Form    {        DataGridViewRow row = new DataGridViewRow();//科研项目表的选红行        DataSet ds_Data = http://www.mamicode.com/new DataSet();//excel DS        DataTable datatable_ERP = null;//ERP导入DS        Hashtable hs_superDeductionERP = new Hashtable();//datagridview        public superDeduction()        {            InitializeComponent();            this.cmbMatchShow.SelectedIndex = 0;            hs_superDeductionERP.Add("ID", "ID");            hs_superDeductionERP.Add("项目唯一标识", "kj9000a");            hs_superDeductionERP.Add("局级项目编号", "kj0065a");            hs_superDeductionERP.Add("厂处级项目编号", "kj0068a");            hs_superDeductionERP.Add("项目名称", "kj8101a");            hs_superDeductionERP.Add("项目内码", "kj8102a");            hs_superDeductionERP.Add("项目编号", "kj8103a");            hs_superDeductionERP.Add("二级单位", "kj8104a");            hs_superDeductionERP.Add("专业", "kj8105a");            hs_superDeductionERP.Add("项目级别", "kj8106a");            hs_superDeductionERP.Add("年度", "kj8107a");            hs_superDeductionERP.Add("标识", "kj8108a");            hs_superDeductionERP.Add("加计扣除标识", "kj8128a");            hs_superDeductionERP.Add("加计扣除类型", "kj8129a");            hs_superDeductionERP.Add("条款内容", "kj8130a");            hs_superDeductionERP.Add("8大项类型", "kj8403a");            hs_superDeductionERP.Add("金额", "kj8114a");            hs_superDeductionERP.Add("备注", "kj8115a");            btnCheck.Enabled = false;            btnSave.Enabled = false;        }        public void FormShow(DataGridViewRow Srow)        {            row = Srow;            this.ShowDialog();        }        #region 选择Excel        private void btnChoose_Click(object sender, EventArgs e)        {            try            {                OpenFileDialog openFD = new OpenFileDialog();//打开文件                openFD.Filter = "所有 Office Excel 文件|(*.xls;*.xl*;*.xlt;*.xlsx;*.xlc;*.xlw)";//选择器                if (openFD.ShowDialog() != DialogResult.OK)                    return;                this.txtPath.Text = openFD.FileName;                if (!System.IO.File.Exists(this.txtPath.Text))//如果要导入的名字和选中的不同,返回                    return;                PrintStatement print = new PrintStatement();                string filedir = string.Empty;                string[] tablenames = print.Print_ERP导入(ref ds_Data, this.txtPath.Text);//数据源和路径                if (tablenames == null)                    return;                SetAllSheet(tablenames);//通过数据库文件路径获取所有的Excel工作簿名称                btnCheck.Enabled = true;            }            catch (Exception ex)            {            }        }        /// <summary>获取所有的Excel工作簿名称</summary>        void SetAllSheet(string[] tabnames)        {            datatable_ERP = SetDT(ds_Data);//处理excel DS            BindDataSource(datatable_ERP);        }        //处理数据表        private DataTable SetDT(DataSet ds)        {            DataTable dt = new DataTable();            dt.Columns.Add("kj0083a");//项目名称            dt.Columns.Add("kj8403a");//8大项类型            dt.Columns.Add("kj8114a");//金额            dt.Columns.Add("kj0086a");//专业            dt.Columns.Add("kj0087a");//年度            dt.Columns.Add("kj0084a");//项目级别            dt.Columns.Add("kj9000a");//项目唯一标识            dt.Columns.Add("kj0065a");//局级项目编号            dt.Columns.Add("kj0068a");//厂处级项目编号            dt.Columns.Add("kj8128a");//加计扣除标识            dt.Columns.Add("kj8129a");//加计扣除类型            dt.Columns.Add("kj8130a");//条款内容            dt.Columns.Add("kj8112a");//八大项内码            dt.Columns.Add("kj8113a");//排序号            DataTable dtData = http://www.mamicode.com/ds.Tables[0];            for (int i = 0, j = 0; i < dtData.Rows.Count; i++)            {                if (string.IsNullOrEmpty(dtData.Rows[i][0].ToString()))                {                    continue;                }//如果8大项类型为空,Excel导入的DataTable开始循环下一行,跳出当前i的所在循环                dt.Rows.Add();                dt.Rows[j]["kj0083a"] = row.Cells["xmmc"].Value.ToString();//项目名称                dt.Rows[j]["kj0086a"] = row.Cells["zy"].Value.ToString();//专业                dt.Rows[j]["kj0087a"] = row.Cells["nd"].Value.ToString();//年度                dt.Rows[j]["kj0084a"] = row.Cells["xmjb"].Value.ToString();//项目级别                dt.Rows[j]["kj8403a"] = dtData.Rows[i][0];//8大项类型                dt.Rows[j]["kj8114a"] = dtData.Rows[i][1];//金额                dt.Rows[j]["kj9000a"] = row.Cells["ID"].Value.ToString();//项目唯一标识                dt.Rows[j]["kj0065a"] = row.Cells["kj0065a"].Value.ToString();//局级项目编号                dt.Rows[j]["kj0068a"] = row.Cells["kj0068a"].Value.ToString();//厂处级项目编号                dt.Rows[j]["kj8128a"] = row.Cells["kj8128a"].Value.ToString();//加计扣除标识                dt.Rows[j]["kj8129a"] = row.Cells["kj8129a"].Value.ToString();//加计扣除类型                dt.Rows[j]["kj8130a"] = row.Cells["kj8130a"].Value.ToString();//条款内容                j++;            }            return dt;        }        //绑定数据源        private void BindDataSource(DataTable dtSource)        {            this.dataGV1.AutoGenerateColumns = false;            dataGV1.DataSource = dtSource;        }        #endregion        #region 显示匹配项改变事件        private void cmbMatchShow_SelectedIndexChanged(object sender, EventArgs e)        {            try            {                BindDataSouce(cmbMatchShow.Text);                SetDGVColor();            }            catch (Exception ex)            {            }        }        public void BindDataSouce(string combotext)        {            string RowFilter = string.Empty;            DataView dv = datatable_ERP.DefaultView;            if (combotext == "已匹配")                RowFilter = "Flag is not null";            else if (combotext == "未匹配")                RowFilter = "Flag is null";            dv.RowFilter = RowFilter;            this.dataGV1.AutoGenerateColumns = false;            this.dataGV1.DataSource = dv;        }        public void SetDGVColor()        {            //改变当前单元格的颜色            foreach (DataGridViewRow dgvr in this.dataGV1.Rows)            {                DataRowView drv = (DataRowView)dgvr.DataBoundItem;                if (string.IsNullOrEmpty(drv["Flag"].ToString()))                    dgvr.DefaultCellStyle.ForeColor = System.Drawing.Color.Black;                else                    dgvr.DefaultCellStyle.ForeColor = System.Drawing.Color.Blue;            }        }        #endregion        #region 导入数据        private void btnSave_Click(object sender, EventArgs e)        {            try            {                if (dataGV1.CurrentRow == null) return;                Cursor.Current = Cursors.WaitCursor;                DataSet ds = new DataSet();//实例化一个ds                DataTable dt = datatable_ERP.Copy();//将datagridview的datatable复制,再赋给dt                DataView dv = dt.DefaultView;//将dt的默认表的视图赋给dv                dv.RowFilter = "Flag is  null";//dataview的过滤器,Flag不等于空                dt.Columns.Remove("Flag");                dt.Columns.Remove("kj8403a");                ds.Tables.Add(dv.ToTable());//将dv转换为表,加到ds里                OperaterClass.NewInsert(Const.LISTNAME_JJCC, Const.CONTENTTYPE_JJCC, ref ds, UserInfo.Department);                this.btnSave.Enabled = false;                this.btnCheck.Enabled = false;                MessageBox.Show("已成功导入 " + ds.Tables[0].Rows.Count + " 条信息!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);            }            catch (Exception ex)            {                MessageBox.Show("ERP数据导入失败!\n\r请检查您选择的Excel模板是否正确!\n\r。系统提示:" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);            }        }        #endregion        #region 检查数据        private void btnCheck_Click(object sender, EventArgs e)        {            /*如何在一个datagridview中绑定数据源拼凑成另一张表             * 数据源本身来源于一个导入的Excel数据源和传入的DataRow数据组成的一张DataTable,             * 利用Excel数据源内容从字典表中查到的id,id加到目前的DataTable             * 将这个DataTable的数据存到数据库中             * for()循环要查的内容dr             * {             *      for()固定的数据字典dr             *      {break;}内容在数据字典中找到,得到id,break跳出for循环             *                   *      for()用得到的id到另一张表中查询,如果有表示查询到了数据,isExists = true,dr             *      {}             *                   *      如果isExists = true,在内容表中标记这列为已将存在             *      将id加到内容dr中                  * }             */            try            {                btnSave.Enabled = true;                //得到八大项字典表数据                DataTable dtBDX = OperaterClass.SearchData(Const.LISTNAME_JJKC8DXZDB, Const.CONTENTTYPE_JJKC8DX, "", null).Tables[0];                //根据项目名称,得到该项目对应的所有加计扣除列表数据                CamlBuilder cbl = new CamlBuilder();                cbl.AddWhere("Eq", "kj0083a", row.Cells["xmmc"].Value.ToString(), "Text");                //DataTable dtJJKC = OperaterClass.SearchData(Const.LISTNAME_JJCC, Const.CONTENTTYPE_JJCC, cbl.GetCamlString(), null).Tables[0];                DataSet dsJJKC = OperaterClass.SearchData(Const.LISTNAME_JJCC, Const.CONTENTTYPE_JJCC, cbl.GetCamlString(), null);                DataTable dtJJKC = new DataTable();                if (dsJJKC != null)                {                     dtJJKC = dsJJKC.Tables[0];                }                datatable_ERP.Columns.Add("Flag");//标识列,表示改加计扣除项是否存在                for (int i = 0; i < datatable_ERP.Rows.Count; i++)                {                    string id = string.Empty;//8大项ID                    string paNum = string.Empty;//排序号                    string lx = datatable_ERP.Rows[i]["kj8403a"].ToString();//8大项类型                    for (int j = 0; j < dtBDX.Rows.Count; j++)                    {                        if (lx == "1、仪器" && dtBDX.Rows[j]["kj8402a"].ToString() == "10")                        {                            lx = "1、仪器1";                        }                        else if (lx == "2、设备" && dtBDX.Rows[j]["kj8402a"].ToString() == "10")                        {                            lx = "2、设备1";                        }                        else if (lx == "1、仪器" && dtBDX.Rows[j]["kj8402a"].ToString() == "13")                        {                            lx = "1、仪器2";                        }                        else if (lx == "2、设备" && dtBDX.Rows[j]["kj8402a"].ToString() == "13")                        {                            lx = "2、设备2";                        }                        if (lx == dtBDX.Rows[j]["kj8403a"].ToString())                        {                            id = dtBDX.Rows[j]["ID"].ToString();                            paNum = dtBDX.Rows[j]["kj8405a"].ToString();                        }                        if (!string.IsNullOrEmpty(id)) break;//如果找到了,就跳出for循环                    }                    bool isExists = false;//加计扣除项是否存在                    for (int k = 0; k < dtJJKC.Rows.Count; k++)                    {                        if (id == dtJJKC.Rows[k]["kj8112a"].ToString())                            isExists = true;                        if (isExists) break;                    }                    if (isExists)                    {                        datatable_ERP.Rows[i]["Flag"] = "1";                    }                    datatable_ERP.Rows[i]["kj8112a"] = id;                    datatable_ERP.Rows[i]["kj8113a"] = paNum;                    id = string.Empty;                    paNum = string.Empty;                }                BindDataSouce(cmbMatchShow.Text);                SetDGVColor();            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        #endregion        private void btnClose_Click(object sender, EventArgs e)        {            this.Close();        }    }}

 break,return,continue的使用区别

continue是跳出开始循环的一次新迭代,如下:

for(var i=1;i<=10;i++) {
  
if(i==6)
  {
    continue;
  } document.write(i); }
//输出结果:1234578910

 

break是跳出包含在最内层循环的循环体或者退出一个switch语句,如下:

for(var i=1;i<=10;i++) { 
  
if(i==6)
  {
  break;
  } document.write(i); }
//输出结果:12345

return是用于指定函数返回的值。return语句只能出现在函数体内,出现在代码中的其他任何地方都会造成语法错误!当执行return语句时,即使函数主体中还有其他语句,函数执行也会停止!

实战基础技能(21)--------多数据拼凑成表插入到数据库中