/// <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; } }
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; } }