/// <summary>
/// 导入
/// </summary>
public void import_P_C_Record_Template_previous()
{
string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录：", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir);
if (string.IsNullOrEmpty(xlsFilePath))
{
return;
}
if (!File.Exists(xlsFilePath))
{
return;
}
tbPath.Text = xlsFilePath;
MyExcel myExcel = new MyExcel(xlsFilePath);
myExcel.open();
Tools.AppManagement.add(myExcel.HwndOfApp);
List <Worksheet> wSList = myExcel.getVisualWS();
int notProductsCostFile_Count = 0;
for (int i = 0; i <= wSList.Count - 1; i++)
{
Worksheet wS = wSList[i];
MSG msg = null;
//非成本文件，则忽略。
if (!(msg = isProductsCostRecord(wS)).Flag)
{
notProductsCostFile_Count++;
continue;
}
Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS);
string PN = uEHelper.getSpecificCellValue("A1");
if ("请在此填写成品名称".Equals(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 请填写成品名称！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
if (string.IsNullOrEmpty(PN))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 产品名称不能为空！", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
continue;
}
//判断是否存在该成品的记录
string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN);
System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count > 0)
{
//删除文档
sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN);
OracleDaoHelper.executeSQL(sqlStr);
}
//先导入概要工序。
int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankRow();
//int rowMaxIndex = wS.UsedRange.Rows.Count;
string seq_p_c_record = string.Empty;
string summary_process = string.Empty;
string specific_process = string.Empty;
string man_hours = string.Empty;
string labour_cost = string.Empty;
pb.Maximum = rowMaxIndex - 2;
pb.Value = 0;
pb.Visible = true;
lblResult.Visible = false;
OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str);
theConnForTransaction.Open();
OracleTransaction tran = theConnForTransaction.BeginTransaction();
for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++)
{
seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex);
string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim();
specific_process = uEHelper.getSpecificCellValue("C" + rowIndex);
man_hours = uEHelper.getSpecificCellValue("D" + rowIndex);
labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex);
if (string.IsNullOrEmpty(seq_p_c_record))
{
break;
}
if (string.IsNullOrEmpty(specific_process))
{
break;
}
;
if (string.IsNullOrEmpty(man_hours))
{
break;
}
;
if (string.IsNullOrEmpty(labour_cost))
{
break;
}
;
//若主工序不为空
if (!"".Equals(prepared_Summary_process))
{
summary_process = prepared_Summary_process;
}
//插入该大类中的第一行数据。
sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier)
VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
seq_p_c_record,
PN,
summary_process,
specific_process,
man_hours,
labour_cost,
Program._userInfo.User_Name);
try
{
OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction);
}
catch (Exception ex)
{
if (ex.ToString().Contains("ORA-00001"))
{
MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格，请重新导入 . ", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行，数据导入时发生异常，请检查成本表格,重新导入。" + ex.ToString(), "提示：", MessageBoxButtons.OK, MessageBoxIcon.Information);
tran.Rollback();
theConnForTransaction.Close();
lblPromptForPB.Visible = false;
pb.Visible = false;
myExcel.close();
break;
}
lblPromptForPB.Visible = true;
lblPromptForPB.Text = wS.Index + "." + wS.Name + ": ";
pb.Value++;
continue;
}
tran.Commit();
theConnForTransaction.Close();
pb.Visible = false;
lblPromptForPB.Visible = false;
lblPromptForPB.Text = "";
lblResult.Visible = true;
IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl();
this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN);
DGVHelper.AutoSizeForDGV(dgv);
}
myExcel.close();
if (notProductsCostFile_Count == wSList.Count)
{
ShowResult.show(lblResult, " 共" + wSList.Count + "个表格均非成衣成本文件！依据：第3行4列和第3行5列均非数字！", false);
timerRestoreLblResult.Enabled = true;
lblPromptForPB.Visible = false;
}
}
