Exemplo n.º 1
0
        public void readexcel1(DataTable dt)
        {
            string path = System.Windows.Forms.Application.StartupPath + "\\export.xlsx";

            filePath = System.Windows.Forms.Application.StartupPath + "\\exportcopy" + DateTime.Now.ToString().Replace('/', '-').Replace("-", "").Replace(":", "").Replace(" ", "") + ".xlsx";
            if (!File.Exists(path))
            {
                MessageBox.Show("模板文件不存在!");
                return;
            }

            File.Copy(path, filePath, true);
            object missing = Type.Missing;

            Microsoft.Office.Interop.Excel.Application ExcelRS;
            Microsoft.Office.Interop.Excel.Workbook    RSbook;
            Microsoft.Office.Interop.Excel.Worksheet   RSsheet;
            ExcelRS = new Microsoft.Office.Interop.Excel.Application();
            //打开目标文件filePath
            RSbook = ExcelRS.Workbooks.Open(filePath, missing, missing, missing, missing, missing,
                                            missing, missing, missing, missing, missing, missing, missing, missing, missing);
            RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
            try
            {
                //  ExcelRS = null;
                //实例化ExcelRS对象
                //   MessageBox.Show("开始保存");
                RSsheet.Activate();

                //设置第一个工作溥


                //   int row = 2;
                for (int r = 0; r < 10; r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        RSsheet.Cells[r + 2, c + 1] = dt.Rows[r][c].ToString();
                    }
                    //   row++;
                    this.Invoke(new Action(() =>
                    {
                        richTextBox4.AppendText(DateTime.Now.ToString() + "--保存第" + (r + 1).ToString() + "记录中... ");
                    }));
                }
                richTextBox4.AppendText(DateTime.Now.ToString() + "--保存记录完成... ");
            }
            catch (Exception er)
            {
                MessageBox.Show(er.ToString());
            }
            finally
            {
                ExcelRS.DisplayAlerts = false;
                RSbook.Save();
                ExcelRS.Visible = false;
            }
        }
Exemplo n.º 2
0
        public void readexcel()
        {
            if (openFileDialog1.FileName != "")
            {
                this.Invoke(new Action(() =>
                {
                    richTextBox1.AppendText("开始导入数据..." + "\r\n");
                }));
                int       col      = 0;
                int       totalnum = 0;
                DataTable dt       = ora.GetDt("select '' as NOROW, GOODSID,GOODSNAME,QUANTITY,PLACECODE from temp_zhy_excption_2017th");
                DataRow   dr;
                // Sqlexec sqlex = new Sqlexec(getelement("connstr"));
                object missing = Type.Missing;
                Microsoft.Office.Interop.Excel.Application ExcelRS;
                Microsoft.Office.Interop.Excel.Workbook    RSbook;
                Microsoft.Office.Interop.Excel.Worksheet   RSsheet;
                //  ExcelRS = null;
                //实例化ExcelRS对象
                ExcelRS = new Microsoft.Office.Interop.Excel.Application();
                //打开目标文件filePath
                RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
                                                missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //设置第一个工作溥
                RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
                //激活当前工作溥
                RSsheet.Activate();
                StringBuilder errmsg = new StringBuilder();
                // object missing = Type.Missing;
                errmsg.Append("错误信息:");
                try
                {
                    string GOODSID, GOODSNAME, QUANTITY, PLACECODE;
                    int    Qty;
                    int    i      = 0;
                    int    result = 0;
                    string insql;

                    for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
                    {
                        this.Invoke(new Action(() =>
                        {
                            richTextBox1.AppendText("读取第" + row.ToString() + "行记录中..." + "\r\n");
                            this.button5.Enabled = false;
                        }));
                        GOODSID   = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 1]).Text;
                        QUANTITY  = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Text;
                        PLACECODE = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 3]).Text;
                        if (int.TryParse(QUANTITY, out Qty))
                        {
                        }
                        else
                        {
                            this.Invoke(new Action(() =>
                            {
                                richTextBox1.AppendText("第" + row.ToString() + "行记录数量不是正确格式" + "\r\n");
                            }));
                            Qty = 0;
                        }
                        totalnum += Qty;
                        dr        = dt.NewRow();
                        dr[0]     = row;
                        dr[1]     = GOODSID;
                        dr[2]     = GetGoodsName(GOODSID);
                        dr[3]     = Qty;
                        dr[4]     = PLACECODE;
                        dt.Rows.Add(dr);
                        insql  = $"insert into temp_zhy_excption_2017th(GOODSID,GOODSNAME,QUANTITY,PLACECODE) values('{GOODSID}','{dr[1].ToString()}',{Qty},'{PLACECODE}')";
                        result = ora.ExecuteNonQuery(insql);
                        if (result > 0)
                        {
                            this.Invoke(new Action(() =>
                            {
                                richTextBox1.AppendText("第" + row.ToString() + "行记录保存至数据库成功" + "\r\n");
                            }));
                        }
                        else
                        {
                            this.Invoke(new Action(() =>
                            {
                                richTextBox1.AppendText("第" + row.ToString() + "行记录保存至数据库失败" + "\r\n");
                            }));
                        }
                        i++;
                    }

                    dr    = dt.NewRow();
                    dr[0] = "";
                    dr[1] = "";
                    dr[2] = "合计";
                    dr[3] = totalnum;
                    dr[4] = "";
                    dt.Rows.Add(dr);

                    ExcelRS.Visible       = false;
                    ExcelRS.DisplayAlerts = false;

                    RSbook.Save();
                    this.Invoke(new Action(() =>
                    {
                        dataGridView2.DataSource = dt;
                    }));



                    MessageBox.Show("成功导入记录:" + i.ToString() + "条!");
                }
                catch (Exception er)
                {
                    MessageBox.Show(er.ToString());
                }
                finally
                {
                    ExcelRS.Quit();
                    IntPtr t = new IntPtr(ExcelRS.Hwnd);
                    int    k = 0;
                    GetWindowThreadProcessId(t, out k);
                    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                    p.Kill();
                    this.Invoke(new Action(() =>
                    {
                        this.button5.Enabled = true;
                    }));
                }
            }
        }
Exemplo n.º 3
0
        private void saveFileDialog4_FileOk(object sender, CancelEventArgs e)
        {
            if (saveFileDialog4.FileName != "")
            {
                string path = System.Windows.Forms.Application.StartupPath + "\\temp.xls";
                if (!File.Exists(path))
                {
                    MessageBox.Show("模板文件不存在!");
                    return;
                }
                string filePath = saveFileDialog4.FileName;
                File.Copy(path, filePath, true);

                if (listView1.Items.Count == 0)
                {
                    MessageBox.Show("数据不存在!");
                    return;
                }

                try
                {
                    object missing = Type.Missing;
                    Microsoft.Office.Interop.Excel.Application ExcelRS;
                    Microsoft.Office.Interop.Excel.Workbook    RSbook;
                    Microsoft.Office.Interop.Excel.Worksheet   RSsheet;



                    ExcelRS = null;
                    //实例化ExcelRS对象
                    ExcelRS = new Microsoft.Office.Interop.Excel.Application();
                    //打开目标文件filePath
                    RSbook = ExcelRS.Workbooks.Open(filePath, missing, missing, missing, missing, missing,
                                                    missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    //设置第一个工作溥
                    RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
                    //激活当前工作溥
                    RSsheet.Activate();
                    List <TakeCode> list = takeBll.GetList().ToList();
                    for (int i = 0; i < list.Count; i++)
                    {
                        TakeCode takeinfo = (TakeCode)list[i];


                        RSsheet.Cells[i + 4, 1] = (i + 1).ToString();
                        RSsheet.Cells[i + 4, 3] = takeinfo.ItemName;
                        RSsheet.Cells[i + 4, 2] = takeinfo.BrandName;
                        RSsheet.Cells[i + 4, 4] = takeinfo.CodeBard;
                        RSsheet.Cells[i + 4, 5] = takeinfo.TV1;
                        RSsheet.Cells[i + 4, 6] = takeinfo.TV2;
                        RSsheet.Cells[i + 4, 7] = takeinfo.TV3;
                    }


                    //保存目标文件
                    RSbook.Save();
                    //设置DisplayAlerts
                    ExcelRS.DisplayAlerts = false;
                    ExcelRS.Visible       = true;
                    //ExcelRS.DisplayAlerts = true;

                    //释放对象
                    RSsheet = null;
                    RSbook  = null;
                    ExcelRS = null;
                }
                catch (Exception er)
                {
                    MessageBox.Show(er.ToString());
                }
            }
        }
Exemplo n.º 4
0
        public void readexcel()
        {
            if (openFileDialog1.FileName != "")
            {
                this.Invoke(new Action(() =>
                {
                    this.lblprocessmsg.Text = "开始导入数据...";
                    this.button1.Enabled    = false;
                }));
                object missing = Type.Missing;
                Microsoft.Office.Interop.Excel.Application ExcelRS;
                Microsoft.Office.Interop.Excel.Workbook    RSbook;
                Microsoft.Office.Interop.Excel.Worksheet   RSsheet;
                //  ExcelRS = null;
                //实例化ExcelRS对象
                ExcelRS = new Microsoft.Office.Interop.Excel.Application();
                //打开目标文件filePath
                RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
                                                missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //设置第一个工作溥
                RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
                //激活当前工作溥
                RSsheet.Activate();
                StringBuilder errmsg = new StringBuilder();
                // object missing = Type.Missing;
                errmsg.Append("错误信息:");
                try
                {
                    int    col = 0;
                    string CodeBard, ItemName, ItemCode;

                    #region 读取工作薄内容
                    for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
                    {
                        this.Invoke(new Action(() =>
                        {
                            this.lblprocessmsg.Text = "读取第" + row.ToString() + "条记录中...";
                            this.button1.Enabled    = false;
                            this.button3.Enabled    = false;
                        }));
                        CodeBard = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Text;
                        CodeBard = CodeBard.Replace('.', ' ').Trim();
                        ItemName = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox4.Text)]).Text;
                        if (checkbar(CodeBard))
                        {
                            //  if (checkbar_forproduct(CodeBard,ItemName)<3)
                            //  {    //我司存在的条码标为红色
                            ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Font.Color = ColorTranslator.ToOle(Color.Red);
                        }
                        else
                        {
                            List <NoexitsBar> nblist = nbBll.GetList().Where(n => n.CodeBard.Equals(CodeBard)).ToList();
                            if (nblist.Count > 0)
                            {
                                //我司不存在但已经登记的条码标为蓝色
                                ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Font.Color = ColorTranslator.ToOle(Color.Blue);
                            }
                            else
                            {
                                NoexitsBar nb = new NoexitsBar();

                                nb.ItemName = ItemName;
                                nb.CodeBard = CodeBard;
                                if (nbBll.Add(nb))
                                {
                                    this.Invoke(new Action(() =>
                                    {
                                        this.lblprocessmsg.Text = "插入" + CodeBard + "记录成功";
                                        this.button1.Enabled    = false;
                                        this.button3.Enabled    = false;
                                    }));
                                    col++;
                                }
                            }
                            // ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Font.Color = ColorTranslator.ToOle(Color.Red);
                        }
                    }


                    // DataTable dt= oleGetDataSet("select BARCODE,SPDM,GG1DM,GG2DM,N_SL,SL,MARK FROM WPHYCD");
                    ExcelRS.Visible       = false;
                    ExcelRS.DisplayAlerts = false;

                    RSbook.Save();



                    MessageBox.Show("成功导入记录:" + col.ToString() + "条,未成功导入的记录将以红色标记,请打开原文件查看!");
                }
                catch (Exception er)
                {
                    MessageBox.Show(er.ToString());
                }
                finally
                {
                    ExcelRS.Quit();
                    IntPtr t = new IntPtr(ExcelRS.Hwnd);
                    int    k = 0;
                    GetWindowThreadProcessId(t, out k);
                    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                    p.Kill();
                    this.Invoke(new Action(() =>
                    {
                        this.lblprocessmsg.Text = "导入完成";
                        this.button1.Enabled    = true;
                        this.button3.Enabled    = true;
                    }));
                }
            }
        }
Exemplo n.º 5
0
        public void readexcel()
        {
            if (openFileDialog1.FileName != "")
            {
                this.Invoke(new Action(() =>
                {
                    this.lblprocessmsg.Text = "开始导入数据...";
                    this.button3.Enabled    = false;
                }));
                Sqlexec sqlex   = new Sqlexec(getelement("connstr"));
                object  missing = Type.Missing;
                Microsoft.Office.Interop.Excel.Application ExcelRS;
                Microsoft.Office.Interop.Excel.Workbook    RSbook;
                Microsoft.Office.Interop.Excel.Worksheet   RSsheet;
                //  ExcelRS = null;
                //实例化ExcelRS对象
                ExcelRS = new Microsoft.Office.Interop.Excel.Application();
                //打开目标文件filePath
                RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
                                                missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //设置第一个工作溥
                RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
                //激活当前工作溥
                RSsheet.Activate();
                StringBuilder errmsg = new StringBuilder();
                // object missing = Type.Missing;
                errmsg.Append("错误信息:");
                try
                {
                    DataTable dt = new DataTable();
                    dt.Columns.Add(new DataColumn("U_clientid", typeof(System.String)));
                    dt.Columns.Add(new DataColumn("U_clientname", typeof(System.String)));
                    dt.Columns.Add(new DataColumn("U_userid", typeof(System.String)));
                    dt.Columns.Add(new DataColumn("U_name", typeof(System.String)));


                    int    col = 0;
                    string U_clientid, U_clientName, U_userid;

                    #region 读取工作薄内容
                    for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
                    {
                        this.Invoke(new Action(() =>
                        {
                            this.lblprocessmsg.Text = "读取第" + row.ToString() + "行记录中...";
                            this.button3.Enabled    = false;
                        }));
                        U_clientName = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Text;
                        U_userid     = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox4.Text)]).Text;
                        DataRow dr = dt.NewRow();

                        dr[0] = getClientid(U_clientName);
                        dr[1] = U_clientName;
                        dr[2] = U_userid;
                        dr[3] = getUserName(U_userid);

                        dt.Rows.Add(dr);
                        this.Invoke(new Action(() =>
                        {
                            this.lblprocessmsg.Text = "读取" + U_clientName + "记录成功";
                            this.button3.Enabled    = false;
                        }));
                        col++;


                        // ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Font.Color = ColorTranslator.ToOle(Color.Red);
                    }


                    // DataTable dt= oleGetDataSet("select BARCODE,SPDM,GG1DM,GG2DM,N_SL,SL,MARK FROM WPHYCD");
                    ExcelRS.Visible       = false;
                    ExcelRS.DisplayAlerts = false;

                    RSbook.Save();
                    this.Invoke(new Action(() =>
                    {
                        dataGridView6.DataSource = dt;
                    }));



                    MessageBox.Show("成功导入记录:" + col.ToString() + "条!");
                }
                catch (Exception er)
                {
                    MessageBox.Show(er.ToString());
                }
                finally
                {
                    ExcelRS.Quit();
                    IntPtr t = new IntPtr(ExcelRS.Hwnd);
                    int    k = 0;
                    GetWindowThreadProcessId(t, out k);
                    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                    p.Kill();
                    this.Invoke(new Action(() =>
                    {
                        this.lblprocessmsg.Text = "导入完成";

                        this.button3.Enabled = true;
                    }));
                }
            }
        }