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