Example #1
0
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void biImporData_Click(object sender, EventArgs e)
        {
            //合并Open\check和Import代码
            OpenFileDialog openFileDialog = new OpenFileDialog()
            {
                Filter = "Microsoft Excel 文件|*.xlsx;*.xls|所有文件|*.*"
            };

            if (openFileDialog.ShowDialog() == DialogResult.OK && !string.IsNullOrEmpty(openFileDialog.FileName))
            {
                string[] SheetNames = ExcelHelper.GetExcelSheetNames(openFileDialog.FileName);
                if (SheetNames != null && SheetNames.Length > 1)//Excel有不只一个sheet,弹出选择窗口
                {
                    FrmSheetsSelecter frmSheetsSelecter = new FrmSheetsSelecter();
                    frmSheetsSelecter.SheetList = SheetNames;
                    if (frmSheetsSelecter.ShowDialog() != DialogResult.Retry)
                    {
                        if (frmSheetsSelecter.SelectedSheetName != "")
                        {
                            dt = ExcelHelper.LoadDataFromExcel(openFileDialog.FileName, frmSheetsSelecter.SelectedSheetName);
                        }
                        else
                        {
                            dt = (DataTable)null;
                            dataGridView1.DataSource = dt;
                            MessageBox.Show("请先选择一个工作簿",
                                            "系统错误",
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Stop);
                            return;
                        }
                    }
                }
                else if (SheetNames != null && SheetNames.Length == 1)//只有一个sheet
                {
                    string SelectedSheetName = SheetNames[0];
                    dt = ExcelHelper.LoadDataFromExcel(openFileDialog.FileName, SelectedSheetName);
                }
                else
                {
                    dt = (DataTable)null;
                    dataGridView1.DataSource = dt;
                    MessageBox.Show("打开Excel文件出错!",
                                    "系统错误",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Error);
                    return;
                }
            }
            else
            {
                dt = (DataTable)null;
                dataGridView1.DataSource = dt;
                MessageBox.Show("请先打开Excel文件或者文件出错!",
                                "系统错误",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Error);
                return;
            }

            if (dt != null && dt.Rows.Count > 0)
            {
                ///去掉统计行
                dt.Rows.RemoveAt(index: dt.Rows.Count - 1);
                //绑定、显示数据
                dataGridView1.DataSource = dt;
                //1、检查Excel文件中列完整性
                string ErrMsg = ExcelHelper.ChkColumnsName(new string[] { "单据编号", "日期", "商品长代码", "批号", "单位", "实收数量", "单价", "收料仓库", "基本单位实收数量", "验收", "保管", "制单" }, dt);
                if (ErrMsg == "")
                {
                    //2、检查数据同步情况
                    int ErrCount = checkData(dt);
                    if (ErrCount == 0)
                    {
                        //3、如果检查数据成功就开始导入数据
                        if (SubCompany.ToLower() == "wuhan")
                        {
                            Excel2DB(dt);
                        }
                        else
                        {
                            Excel2DB1(dt);
                        }
                    }
                    else
                    {
                        MessageBoxEx.Show(String.Format("总共测试数据 {0} 行,其中测试失败记录有 {1} 项!", dt.Rows.Count, ErrCount),
                                          "系统提示",
                                          MessageBoxButtons.OK,
                                          MessageBoxIcon.Stop);
                        return;
                    }
                }
                else
                {
                    //dt = (DataTable)null;
                    //dataGridView1.DataSource = dt;
                    MessageBox.Show("请检查Excel文件,不存在的字段有:" + ErrMsg.Substring(0, ErrMsg.Length - 1),
                                    "系统提示",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Stop);
                    return;
                }
            }
            else
            {
                dt = (DataTable)null;
                dataGridView1.DataSource = dt;
                MessageBox.Show(" 打开Excel文件失败或无数据",
                                "系统提示",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Stop);
                return;
            }
        }
Example #2
0
        private void biImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog()
            {
                Filter = "Microsoft Excel 文件|*.xlsx;*.xls|所有文件|*.*"
            };

            if (openFileDialog.ShowDialog() == DialogResult.OK && !string.IsNullOrEmpty(openFileDialog.FileName))
            {
                string[] SheetNames = ExcelHelper.GetExcelSheetNames(openFileDialog.FileName);
                if (SheetNames != null && SheetNames.Length > 1)//Excel有不只一个sheet,弹出选择窗口
                {
                    FrmSheetsSelecter frmSheetsSelecter = new FrmSheetsSelecter();
                    frmSheetsSelecter.SheetList = SheetNames;
                    if (frmSheetsSelecter.ShowDialog() != DialogResult.Retry)
                    {
                        if (frmSheetsSelecter.SelectedSheetName != "")
                        {
                            dt = ExcelHelper.LoadDataFromExcel(openFileDialog.FileName, frmSheetsSelecter.SelectedSheetName);
                        }
                        else
                        {
                            dt = (DataTable)null;
                            dataGridView1.DataSource = dt;
                            MessageBox.Show("请先选择一个工作簿",
                                            "系统错误",
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Stop);
                            return;
                        }
                    }
                }
                else if (SheetNames != null && SheetNames.Length == 1)//只有一个sheet
                {
                    string SelectedSheetName = SheetNames[0];
                    dt = ExcelHelper.LoadDataFromExcel(openFileDialog.FileName, SelectedSheetName);
                }
                else
                {
                    dt = (DataTable)null;
                    dataGridView1.DataSource = dt;
                    MessageBox.Show("打开Excel文件出错!",
                                    "系统错误",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Error);
                    return;
                }
            }
            else
            {
                dt = (DataTable)null;
                dataGridView1.DataSource = dt;
                MessageBox.Show("请先打开Excel文件或者文件出错!",
                                "系统错误",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Error);
                return;
            }

            if (dt != null && dt.Rows.Count > 0)
            {
                ///去掉统计行
                dt.Rows.RemoveAt(index: dt.Rows.Count - 1);
                //绑定、显示数据
                dataGridView1.DataSource = dt;
                //1、检查Excel文件中列完整性
                string[] whColumns = new string[] {
                    "单据编号", "日期", "发货仓库", "销售单价", "购货单位代码", "订单号", "基本单位实发数量",
                    "进货单号", "验收单号", "订单日期", "交货日期", "审核日期A", "产品长代码",
                    "实发数量", "销售单价", "销售金额", "批号", "单位", "F件数", "F含量",
                    "F细数", "F赠品", "F税率", "F未税金额", "F价税合计", "F保质日期", "F生产日期",
                    "F单位", "F规格", "F商品", "F包装", "F箱数", "F零头", "F税额", "F售价"
                };
                string[] jzColumns = new string[] {
                    "单据编号", "日期", "发货仓库", "销售单价", "购货单位代码", "订单号", "基本单位实发数量",
                    "产品长代码", "实发数量", "销售单价", "销售金额", "批号", "单位"
                };
                string[] chkColumn = SubCompany.ToLower() == "wuhan" ? whColumns : jzColumns;
                string   ErrMsg    = ExcelHelper.ChkColumnsName(chkColumn, dt);
                if (ErrMsg == "")
                {
                    //2、检查数据完整性
                    int ErrCount = checkData(dt);
                    if (ErrCount == 0)
                    {
                        //3、导入数据
                        if (SubCompany.ToLower() == "wuhan")
                        {
                            Excel2DB(dt);
                        }
                        else
                        {
                            Excel2DB1(dt);
                        }
                    }
                    else
                    {
                        MessageBoxEx.Show(String.Format("总共测试数据 {0} 行,其中测试失败记录有 {1} 项!", dt.Rows.Count, ErrCount),
                                          "系统提示",
                                          MessageBoxButtons.OK,
                                          MessageBoxIcon.Stop);
                        return;
                    }
                }
                else
                {
                    MessageBox.Show("请检查Excel文件,不存在的字段有:" + ErrMsg.Substring(0, ErrMsg.Length - 1),
                                    "系统提示",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Stop);
                    return;
                }
            }
            else
            {
                dt = (DataTable)null;
                dataGridView1.DataSource = dt;
                MessageBox.Show(" 打开Excel文件失败或无数据",
                                "系统提示",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Stop);
                return;
            }
        }