using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace WFormReadData_SMR { public partial class JZ_ReadDate : Form { public DBHelper oCN = new DBHelper(); DataTable dataTable = new DataTable(); private json objJsonResult = new json(); public JZ_ReadDate() { InitializeComponent(); } private void txtLzk_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { SelectHBardCode(this.txtLzk.Text); } } //查询条形码 public void SelectHBardCode(string HBillNo) { try { DataSet ds = oCN.RunProcReturn("select * from h_v_Sc_ProcessExchangeBillQuerySub where 单据号='" + HBillNo + "' and 工序代码<>'9999' order by HEntryID desc", "h_v_Sc_ProcessExchangeBillQuerySub"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("流转卡:"+ HBillNo + ",查无数据!"); } else { string HProjectNum = ds.Tables[0].Rows[0]["HProjectNum"].ToString(); this.txtPcm.Text = HProjectNum; this.txtLzk.Text = ds.Tables[0].Rows[0]["单据号"].ToString(); this.txtLzk.ReadOnly = true; this.cmbPro.DataSource = ds.Tables[0]; this.cmbPro.DisplayMember = "工序名称"; this.cmbPro.ValueMember = "HProcID"; this.cmbPro.SelectedIndex = 0; } } catch (Exception e) { MessageBox.Show(this, e.Message, "提示"); } } private void btnLj_Click(object sender, EventArgs e) { string txtLzk = this.txtLzk.Text; if (txtLzk == "") { MessageBox.Show("流转卡没有扫描!"); } else { //路径赋值给文本 using (OpenFileDialog openFile = new OpenFileDialog()) { openFile.Multiselect = true; // 允许选择多个文件 DialogResult result = openFile.ShowDialog(); if (result == DialogResult.OK) { //保存按钮灰掉 this.btnSave.Enabled = true; string selectedFolderPath = openFile.FileName; // 使用 selectedFolderPath 进行后续操作，例如显示在文本框中 this.txtLj.Text = selectedFolderPath; GetExcel_Read(openFile.FileNames); } } } } //根据路径找到路径下面的所有文件进行数据读取 EXCEL public void GetExcel_Read(string[] files) { DataSet ds = new DataSet(); for (int i = 0; i < files.Length; i++) { bool flag =false; //根据文件路径找到对应的文件 flag = File.Exists(files[i]); if (flag) { //获取Excel对应的数据 objJsonResult = Xt_ExcelReadText(files[i], i); if (objJsonResult.count == 1) { DataTable dt = objJsonResult.dataTable; ds.Tables.Add(dt); } else { MessageBox.Show("错误:" + objJsonResult.Message); break; } } else { MessageBox.Show("路径:" + files[i] + ",文件不存在!"); break; } } //显示数据 ShowData(ds); } //根据文件路径找到对应文件 并获取对应的数据 public json Xt_ExcelReadText(string FilePath, int num) { try { DataTable dt = new DataTable(num.ToString()); IWorkbook workbook; using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read)) { // 创建一个WorkbookFactory对象来读取文件 workbook = WorkbookFactory.Create(file); ISheet sheet = workbook.GetSheetAt(0); // 替换为实际的工作表名称 if (sheet != null) { int nums = 0; // 迭代工作表中的行 for (int rowNum = 6; rowNum <= sheet.LastRowNum; rowNum++) { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(rowNum); if (row != null) // 空行可能为null { // 迭代每一行的单元格 for (int colNum = 0; colNum < row.LastCellNum; colNum++) { ICell cell = row.GetCell(colNum); if (cell != null) // 单元格可能为null { string cellValue = cell.ToString(); if (cellValue == "等级") { break; } if (rowNum == 6 && nums == 0) { dt.Columns.Add(cellValue); } else { dr[dt.Columns[colNum]] = cellValue; } } } if (rowNum != 6&&row.Cells != null) { dt.Rows.Add(dr); } } //if (rowNum == 12 && nums == 0) //{ // rowNum = -1; // nums = 1; //} } } } objJsonResult.count = 1; objJsonResult.Message = ""; objJsonResult.dataTable = dt; return objJsonResult; } catch (Exception e) { objJsonResult.count = 0; objJsonResult.Message = e.Message; objJsonResult.dataTable = null; return objJsonResult; } } //把数据显示在页面上 public void ShowData(DataSet ds) { //清空网格内容 ListData.Items.Clear(); //清空表格内容 dataTable.Columns.Clear(); dataTable.Rows.Clear(); for (int i = 0; i < ds.Tables.Count; i++) { DataTable dt = ds.Tables[i]; for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.Contains("mH")) { dt.Columns[j].ColumnName = dt.Columns[j].ColumnName.Replace("mH", "uH"); for (int k = 0; k < dt.Rows.Count; k++) { decimal dec = 0.0M; if (decimal.TryParse(dt.Rows[k][dt.Columns[j].ColumnName].ToString(), out dec)) { dt.Rows[k][dt.Columns[j].ColumnName] = dec * 1000; } } } } string[] str = { "Ls(uH)_10kHz", "Ls(uH)_100kHz", "Z(Ω)_600kHz", "Ls(uH)_1MHz", "Ls(uH)_2MHz", "Z(Ω)_2MHz", "Z(Ω)_1MHz", "Z(Ω)_10MHz", "Z(Ω)_100kHz" }; for (int o = 0; o < str.Length; o++) { for (int o1 = 0; o1 < dt.Columns.Count; o1++) { if (dt.Columns[o1].ColumnName.Contains(str[o])) { dataTable.Columns.Add(str[o]); } } } for (int j = 0; j < dt.Rows.Count; j++) { DataRow dr = dataTable.NewRow(); for (int k = 0; k < dt.Columns.Count; k++) { for (int k1 = 0; k1 < dataTable.Columns.Count; k1++) { if (dt.Columns[k].ColumnName.Contains(dataTable.Columns[k1].ColumnName)) { dr[dataTable.Columns[k1].ColumnName] = dt.Rows[j][dataTable.Columns[k1].ColumnName].ToString(); ListData.Items.Add("参数:"+ dataTable.Columns[k1].ColumnName + ",实测值:" + dt.Rows[j][dataTable.Columns[k1].ColumnName].ToString()); break; } } } if (dr.ItemArray.Length > 0) { dataTable.Rows.Add(dr); } } } } private void JZ_ReadDate_Load(object sender, EventArgs e) { this.cmbPro.DropDownStyle = ComboBoxStyle.DropDownList; this.txtLj.ReadOnly = true; this.txtHMakerNumber.ReadOnly = true; } private void JZ_ReadDate_FormClosing(object sender, FormClosingEventArgs e) { if (MessageBox.Show("确定要关闭吗？", "确认", MessageBoxButtons.YesNo) == DialogResult.No) { e.Cancel = true; } } //保存 private void btnSave_Click(object sender, EventArgs e) { string txtLzk = this.txtLzk.Text; string txtLj = this.txtLj.Text; if (txtLzk == "") { MessageBox.Show("流转卡没有扫描!"); } else if (txtLj == "") { MessageBox.Show("请选择路径!"); } else { //保存按钮灰掉 this.btnSave.Enabled = false; FinalInspectionForm_Save(); } } //末检检验单 public void FinalInspectionForm_Save() { try { string HProcID = this.cmbPro.SelectedValue.ToString(); //修改是在哪个设备上进行的数据采集 men.HNumber CMR-E-026 LMJC 浸渍 铁芯检测 设备 DataSet dataSet = oCN.RunProcReturn(@"select b.HAnalysisMethod,b.HInspectInstruMentID,a.HInterID HQCSchemeID,b.HQCCheckItemID, ch.HNumber HQCCheckItemNumber,m.HNumber,HUpLimit,HDownLimit,HTargetVal from Sc_ProcessExchangeBillMain pr with(nolock) inner join Gy_QCCheckProjectMain a with(nolock) on pr.HMaterID=a.HMaterID and a.HProcID="+ HProcID + @" inner join Gy_QCCheckProjectSub b with(nolock) on a.HInterID=b.HInterID inner join Gy_InspectInstruMent men with(nolock) on b.HInspectInstruMentID=men.HItemID and men.HNumber='CMR-E-026' left join Gy_Material m with(nolock) on a.HMaterID=m.HItemID left join Gy_QCCheckItem ch with(nolock) on b.HQCCheckItemID=ch.HItemID where pr.HBillNo='" + this.txtLzk.Text + "'", "Gy_QCCheckProjectMain"); //保存数据 if (dataSet.Tables[0].Rows.Count > 0) { DataSet ds = oCN.RunProcReturn(@"select staus.HSourceID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,a.HPlanQty,a.HInterID HProcExchInterID, staus.HSourceEntryID HProcExchEntryID,a.HBillNo HProcExchBillNo,a.HQty,a.HMaterID,staus.HProcID from Sc_ProcessExchangeBillMain a with(nolock) inner join Sc_ICMOBillStatus_Tmp staus with(nolock) on a.HInterID=staus.HSourceInterID where a.HBillNo='" + this.txtLzk.Text + "' and staus.HProcID=" + HProcID, "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("当前工单没有派单数据!"); } else { //oCN.BeginTran(); if (dataSet.Tables[0].Rows.Count <= dataTable.Columns.Count) { string sExeReturnInfo = ""; string BillType = "7507"; long HInterID = oCN.CreateBillID_Prod(BillType, ref sExeReturnInfo); string HBillNo = oCN.CreateBillCode(BillType, ref sExeReturnInfo, true); int HLastResults = 1; string HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString(); string HICMOInterID = ds.Tables[0].Rows[0]["HICMOInterID"].ToString(); string HICMOEntryID = ds.Tables[0].Rows[0]["HICMOEntryID"].ToString(); string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString(); string HPlanQty = ds.Tables[0].Rows[0]["HPlanQty"].ToString(); string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString(); string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString(); string HProcExchBillNo = ds.Tables[0].Rows[0]["HProcExchBillNo"].ToString(); string HQty = ds.Tables[0].Rows[0]["HQty"].ToString(); string HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString(); HProcID = ds.Tables[0].Rows[0]["HProcID"].ToString(); string HQCSchemeID = dataSet.Tables[0].Rows[0]["HQCSchemeID"].ToString(); int num = 0; for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { //int HLastResult = 1; string HAnalysisMethod = dataSet.Tables[0].Rows[i]["HAnalysisMethod"].ToString(); decimal HUpLimit = 0; decimal HDownLimit = 0; if (HAnalysisMethod == "2") { HUpLimit = decimal.Parse(dataSet.Tables[0].Rows[i]["HTargetVal"].ToString()) + decimal.Parse(dataSet.Tables[0].Rows[i]["HUpLimit"].ToString()); HDownLimit = decimal.Parse(dataSet.Tables[0].Rows[i]["HTargetVal"].ToString()) + decimal.Parse(dataSet.Tables[0].Rows[i]["HDownLimit"].ToString()); } for (int j = 0; j < dataTable.Columns.Count; j++) { string HQCCheckItemName = ""; switch (dataTable.Columns[j].ColumnName) { case "Ls(uH)_10kHz": HQCCheckItemName = "JZXN-001"; break; case "Ls(uH)_100kHz": HQCCheckItemName = "JZXN-002"; break; case "Z(Ω)_600kHz": HQCCheckItemName = "JZXN-003"; break; case "Ls(uH)_1MHz": HQCCheckItemName = "XN-004"; break; case "Ls(uH)_2MHz": HQCCheckItemName = "JZXN-005"; break; case "Z(Ω)_2MHz": HQCCheckItemName = "JZXN-006"; break; case "Z(Ω)_1MHz": HQCCheckItemName = "XN-007"; break; case "Z(Ω)_10MHz": HQCCheckItemName = "XN-008"; break; case "Z(Ω)_100kHz": HQCCheckItemName = "XN-009"; break; } if (dataSet.Tables[0].Rows[i]["HQCCheckItemNumber"].ToString().Contains(HQCCheckItemName)) { for (int k = 11; k < dataTable.Rows.Count; k++) { if (dataTable.Rows[k][dataTable.Columns[j].ColumnName].ToString() != "") { decimal HInSpectValue = 0.0M; int HInSpectResult = 1; if (decimal.TryParse(dataTable.Rows[k][dataTable.Columns[j].ColumnName].ToString(), out HInSpectValue)) { if (HAnalysisMethod == "2") { HInSpectResult = (HInSpectValue > HUpLimit || HDownLimit > HInSpectValue) ? 2 : 1; } } DBHelper.CustomWriteLog("Insert into QC_ProcessCheckBillSub " + " (HInterID,HBillNo_bak,HEntryID,HSNO,HCloseMan" + ",HEntryCloseDate,HCloseType,HRemark,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HQCCheckClassID,HQCCheckItemID,HQCStd,HQCRelValue,HResult" + ",HProcCheckEmp,HProcCheckTime,HSampleSchemeID,HSampleQty,HSampleDamageQty,HAcceptQty" + ",HRejectQty,HSampleUnRightQty,HStatus,HUnitID,HInspectVal,HTargetVal" + ",HUpLimit,HDownLimit,HUpOffSet,HDownOffSet,HKeyInspect,HAnalysisMethod,HInspectInstruMentID,HInspectResult" + ",HQCNote) values(" + HInterID + ",'" + HBillNo + "'," + num + "," + (k -10) + "''" + ",getdate(),0,''," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "','',0,0" + ",0," + dataSet.Tables[0].Rows[i]["HQCCheckItemID"].ToString() + ",'" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "','','" + HInSpectResult + "'" + ",0,'',0,0,0,0" + ",0,0,'','0','" + HInSpectValue + "','" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "','" + dataSet.Tables[0].Rows[i]["HUpLimit"].ToString() + "','" + dataSet.Tables[0].Rows[i]["HDownLimit"].ToString() + "','','',0,'2'," + dataSet.Tables[0].Rows[i]["HInspectInstruMentID"].ToString() + ",'" + HInSpectResult + "'" + ",'" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "') ", DateTime.Now.ToString("yyyy-MM-dd")); num += 1; oCN.RunProc("Insert into QC_ProcessCheckBillSub " + " (HInterID,HBillNo_bak,HEntryID,HSNO,HCloseMan" + ",HEntryCloseDate,HCloseType,HRemark,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" + ",HQCCheckClassID,HQCCheckItemID,HQCStd,HQCRelValue,HResult" + ",HProcCheckEmp,HProcCheckTime,HSampleSchemeID,HSampleQty,HSampleDamageQty,HAcceptQty" + ",HRejectQty,HSampleUnRightQty,HStatus,HUnitID,HInspectVal,HTargetVal" + ",HUpLimit,HDownLimit,HUpOffSet,HDownOffSet,HKeyInspect,HAnalysisMethod,HInspectInstruMentID,HInspectResult" + ",HQCNote) values(" + HInterID + ",'" + HBillNo + "'," + num + "," + (k - 10) + ",''" + ",getdate(),0,''," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "','',0,0" + ",0," + dataSet.Tables[0].Rows[i]["HQCCheckItemID"].ToString() + ",'" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "','','" + HInSpectResult + "'" + ",0,'',0,0,0,0" + ",0,0,'','0','" + HInSpectValue + "','" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "','" + dataSet.Tables[0].Rows[i]["HUpLimit"].ToString() + "','" + dataSet.Tables[0].Rows[i]["HDownLimit"].ToString() + "','','',0,'2'," + dataSet.Tables[0].Rows[i]["HInspectInstruMentID"].ToString() + ",'" + HInSpectResult + "'" + ",'" + dataSet.Tables[0].Rows[i]["HTargetVal"].ToString() + "') "); if (HInSpectResult == 1 && HLastResults != 0) { HLastResults = 1; } else { HLastResults = 0; } } } DataSet dataSetMaxin = oCN.RunProcReturn(@"select max(cast(HInspectVal as decimal(18,8))) HMax,min(cast(HInspectVal as decimal(18,8))) HMin from QC_ProcessCheckBillSub b where b.HInterID = " + HInterID + " and b.HQCCheckItemID=" + dataSet.Tables[0].Rows[i]["HQCCheckItemID"].ToString(), "QC_ProcessCheckBillSub"); string HMax = dataSetMaxin.Tables[0].Rows[0]["HMax"].ToString(); string HMin = dataSetMaxin.Tables[0].Rows[0]["HMin"].ToString(); oCN.RunProc("update QC_ProcessCheckBillSub set HMax='" + HMax + "' ,HMin='" + HMin + "' where HInterID=" + HInterID + " and HQCCheckItemID=" + dataSet.Tables[0].Rows[i]["HQCCheckItemID"].ToString()); break; } } } //主表 oCN.RunProc("Insert Into QC_ProcessCheckBillMain " + "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate" + ",HYear,HPeriod,HRemark" + ",HICMOInterID,HICMOBillNo,HProcExchInterID,HProcExchEntryID,HProcExchBillNo" + ",HMaterID,HProcID,HSourceID,HEmpID,HInStockQty" + ",HCheckQty,HRightQty,HBadQty,HFirstCheckEmp,HLastResult" + ",HBarCode,HLBatchNo,HCusID,HSortBillNo,HContrctBatchNo" + ",HProdAreaID,HProdTypeID,HProdStoveNo,HRecipeID,HDiameter1" + ",HDiameter2,HRoutingInterID,HDrawingDireID,HPackTypeID" + ",HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo,HMainSourceBillType,HICMOEntryID,HQCSchemeID,HICMOQty" + ",HProcExchQty,HShiftsID,HErrTreatment" + ",HTakeSampleCheckBillID,HTakeSampleCheckBillNo" + ",HBatchNo,HCheckerResult) " + " values('" + BillType + "','" + BillType + "'," + HInterID + ",'" + HBillNo + "',1,getdate(),'" + this.txtHMaker.Text + "',getdate()" + "," + DateTime.Now.Year + "," + DateTime.Now.Month + ",''" + "," + HICMOInterID + ",'" + HICMOBillNo + "'," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "'" + "," + HMaterID + "," + HProcID + "," + HSourceID + ",0,0,0,0,0," + this.labHMakerID.Text + ",'" + HLastResults + "'" + ",'','',0,'',''" + ",0,0,'',0,0,0,0,'',0," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "',''," + HICMOEntryID + "," + HQCSchemeID + "," + HPlanQty + "," + HQty + ",0,''" + ",0,''" + ",'" + this.txtPcm.Text + "','"+ HLastResults + "') "); } //oCN.Commit(); MessageBox.Show("保存成功!"); } } else { this.btnSave.Enabled = true; MessageBox.Show("当前物料没有对应的检验方案!"); } } catch (Exception e) { //oCN.RollBack(); MessageBox.Show("保存失败!" + e.Message); } } private void txtHMaker_Enter(object sender, EventArgs e) { if (txtHMaker.Text.Trim() == "请输入用户编码!") { txtHMaker.Text = ""; this.txtHMaker.ForeColor = Color.Black; } } private void txtHMaker_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == Keys.Enter) { SelectHMaker(this.txtHMaker.Text); } } //查询职员信息 public void SelectHMaker(string txtHMaker) { try { DataSet ds = oCN.RunProcReturn("select HItemID,HName,HNumber from Gy_Employee where HNumber='" + txtHMaker + "' or HName ='" + txtHMaker + "'", "Gy_Employee"); if (ds.Tables[0].Rows.Count > 0) { string HName = ds.Tables[0].Rows[0]["HName"].ToString(); string HMakerID = ds.Tables[0].Rows[0]["HItemID"].ToString(); string HNumber = ds.Tables[0].Rows[0]["HNumber"].ToString(); this.txtHMaker.Text = HName; this.txtHMakerNumber.Text = HNumber; this.labHMakerID.Text = HMakerID; this.txtHMaker.ReadOnly = true; } else { MessageBox.Show(this, txtHMaker + ",查无数据,可能是输入编码有误!", "提示"); } } catch (Exception e) { MessageBox.Show(this, e.Message, "提示"); } } private void txtHMaker_Leave(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(txtHMaker.Text)) { this.txtHMaker.ForeColor = Color.LightGray; txtHMaker.Text = "请输入用户编码!"; } } private void btnSelect_Click(object sender, EventArgs e) { this.txtLzk.Text = ""; this.txtLzk.ReadOnly = false; this.txtPcm.Text = ""; this.cmbPro.DataSource = null ; this.ListData.Items.Clear(); this.txtLj.Text = ""; } } }