PMC在EXCEL中做好生产排期，根据排期数据，结合ERP中的物料状况，计算出每个供应商的交货表，供应商根据排期表来交货，防止缺料和供应商超交。
实现：用vsto插件来读取excel数据，并把数据传入存储过程，计算供应商排期。
新建VSTO项目
操作同winform差不多，主要操作就是读取相应的sheet
_showErrMsg = string.Empty;saveFileDialog1.FileName = $"{_company}_{sheetName}_欠料表_{DateTime.Now.ToString("yyyyMMdd HHmmssfff")}";if (saveFileDialog1.ShowDialog() != DialogResult.OK){return;}string _saveFile = saveFileDialog1.FileName;Worksheet sheet = null;try{sheet = Globals.ThisAddIn.Application.Worksheets[sheetName];}catch{MessageBox.Show($"excel中未找到表单\"{sheetName}\"", "!!!", MessageBoxButtons.OK, MessageBoxIcon.Error);return;}int lastRow = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;string woFilter = $"-{_company}-";int startIndex = (int)Bin26.From26(schStartIndex);int endIndex = (int)Bin26.From26(string.IsNullOrEmpty(schEndIndex) ? "AY" : schEndIndex.ToUpper());Dictionary<int, string> cols = new Dictionary<int, string>(); //excel列名与数字的对应关系，如A=1,B=2...Dictionary<int, string> schDays = new Dictionary<int, string>(); //第二行日期与数字的对应System.Data.DataTable dt = new System.Data.DataTable();dt.Columns.Add(new DataColumn("JOBNO"));dt.Columns.Add(new DataColumn("P_CODE"));dt.Columns.Add(new DataColumn("SCHDAY"));dt.Columns.Add(new DataColumn("QTY", typeof(decimal)));dt.Columns.Add(new DataColumn("CO"));DateTime today = DateTime.Now.Date;_isProcessing = true;btnCompute.Enabled = false;Task.Factory.StartNew(() =>{try{if (Int32.Parse(_db.ExecScalar("select count(*) from YJY_APPLOCKER where APPID='OverMtrl' and LOCKED=1").ToString()) > 0){_showErrMsg = "其他用户正在跑欠料，请稍后再试";return;}_db.ExeNoQuery($"update YJY_APPLOCKER set LOCKED=1,LOCKTIME='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff")}' where APPID='OverMtrl'"); //锁定程序this.notify.Text = "锁定程序成功";}catch (Exception ex){this.notify.Text = $"锁定程序失败\r\n{ex.Message};{ex.StackTrace}";_showErrMsg= $"锁定程序失败\r\n{ex.Message};{ex.StackTrace}";return;}if (!cb_UseLastSchData.Checked){notify.Text = "正在读取排期信息";#region 统计排期日期for (int j = startIndex; j <= endIndex; j++){string colIndex = Bin26.To26(j).ToUpper();string day = sheet.Range[$"{colIndex}2", $"{colIndex}2"].Value?.ToString();if (!string.IsNullOrEmpty(day) && DateTime.TryParse(day, out DateTime date)){cols.Add(j, colIndex);schDays.Add(j, day);}else{_showErrMsg = $"排期列范围 {colIndex} 值不是日期";return;}}if (schDays.Count < 1){_showErrMsg = "排期列范围异常，无法继续";return;}#endregion#region 读取排期，并插入datatable//先读取整个range的值，再循环处理try{var range = sheet.Range["B3", $"C{lastRow}"];for (int i = 3; i <= lastRow; i++){string wo = range.Range[$"A{i - 2}", $"A{i - 2}"].Value;string pcode = range.Range[$"B{i - 2}", $"B{i - 2}"].Value;if (string.IsNullOrEmpty(wo) || string.IsNullOrEmpty(pcode))continue;if ((wo.Length > 10 && wo.Trim().Substring(2, 4).Equals(woFilter))||wo.Equals("FC",StringComparison.InvariantCultureIgnoreCase)){object[,] vals = sheet.Range[$"{cols[startIndex]}{i}", $"{cols[endIndex]}{i}"].Value;//range的值为二维数组，且最小下标为1for (int j = startIndex; j <= endIndex; j++){string v = vals[1, j - startIndex + 1]?.ToString();if (!string.IsNullOrEmpty(v))if (decimal.TryParse(v, out decimal q))dt.Rows.Add(wo, pcode, schDays[j], q, _company);}}}if (dt.Rows.Count < 1){notify.Text = "未读取到排期信息";return;}}catch(Exception ex){this.BeginInvoke(new System.Action(()=> {notify.Text = "读取排期异常";_showErrMsg = $"读取排期异常\r\n{ex.Message}\r\n{ex.StackTrace}";}));return;}#endregion#region 小于当前日期的排期，统一修改为当前日期try{var list = (from dr in dt.AsEnumerable() //小于等于当前日期的排期where Convert.ToDateTime(dr.Field<string>("SCHDAY")) <= todayselect dr).ToList();var listCopy = (from dr in list //小于等于当前日期的排期改为当前日期select new{JOBNO = dr.Field<string>("JOBNO"),P_CODE = dr.Field<string>("P_CODE"),SCHDAY = today,QTY = dr.Field<decimal>("QTY")}).ToList();foreach (var dr in list){dt.Rows.Remove(dr);}var todayList = (from d in listCopy //小于等于当前日期的排期汇总group d by new { d.JOBNO, d.P_CODE, d.SCHDAY }into gselect new{g.Key.JOBNO,g.Key.P_CODE,g.Key.SCHDAY,qty = g.Sum(d => d.QTY)}).ToList();todayList.ForEach(t => dt.Rows.Add(t.JOBNO, t.P_CODE, t.SCHDAY, t.qty, _company));/*根据工单、成品、日期分组，判断是否有重复排期*/var groups = from dr in dt.AsEnumerable()group dr by new { JOBNO = dr.Field<string>("JOBNO"), P_CODE = dr.Field<string>("P_CODE"), SCHDAY = dr.Field<string>("SCHDAY") }into gselect new{g.Key.JOBNO,g.Key.P_CODE,g.Key.SCHDAY,count = g.Count()};var reList = groups.Where(g => g.count > 1).ToList();if (reList.Count >= 1){MessageBox.Show(this, $"有重复的排期！！！{string.Join("\r\n", (from s in reList select s.JOBNO + "," + s.P_CODE).ToList())}", "！！！", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}}catch(Exception ex){_showErrMsg = $"异常\r\n{ex.Message}\r\n{ex.StackTrace}";return;}#endregion#region 比较工单排期数量是否超出系统工单数量，是否有半成品工单参与排期/*var sumJobs = (from dr in dt.AsEnumerable()group dr by new { JOBNO = dr.Field<string>("JOBNO"), P_CODE = dr.Field<string>("P_CODE") }into gselect new { g.Key.JOBNO, g.Key.P_CODE, SCHQTY = g.Sum(dr => dr.Field<decimal>("QTY")) }).ToList();var jobNums = (from job in sumJobsselect job.JOBNO).ToList();var dtJobs = GetMoList(string.Join("','", jobNums));//_db.GetDataTable($"select JOBNO,CQTY,MASJOBNO,CODE from amcl..JOBT where JOBNO in ('{string.Join("','",jobNums)}')");var allJobs = (from j in sumJobsjoin DataRow j1 in dtJobs.AsEnumerable() on new { j.JOBNO, j.P_CODE } equals new { JOBNO = j1.Field<string>("JOBNO"), P_CODE = j1.Field<string>("CODE") }into tfrom tt in tselect new{JOBNO = tt.Field<string>("JOBNO"),P_CODE = tt.Field<string>("CODE"),MJOBNO = tt.Field<string>("MASJOBNO"),CQTY = tt.Field<decimal>("QTY"),SCHQTY = j.SCHQTY}).ToList();*//*判断半成品工单是否参与了排期*//*var mJobs = allJobs.Where(j => j.JOBNO != j.MJOBNO).ToList();if (mJobs.Count > 0){string err = string.Join("\r\n", (from j in mJobs select j.JOBNO + ":" + j.P_CODE).ToList());this.BeginInvoke(new System.Action(() => {Clipboard.SetText(err);notify.Text = "导入排期失败，排期中有半成品工单！！！";MessageBox.Show(this, $"半成品工单不能参与排期:\r\n{err}", "！！！", MessageBoxButtons.OK, MessageBoxIcon.Warning);}));return;}*//*判断排期数量是否超过了系统工单数量*//*var errJobs = allJobs.Where(j => j.SCHQTY > j.CQTY).ToList();if (errJobs.Count > 0){Worksheet sheetErr = null;try{sheetErr = Globals.ThisAddIn.Application.Worksheets["错误！！！"];}catch { }if (sheetErr == null){sheetErr = Globals.ThisAddIn.Application.Worksheets.Add(Type.Missing,Type.Missing, 1, XlSheetType.xlWorksheet);sheetErr.Name = "错误！！！";}else{sheetErr.Rows.Clear();}sheetErr.Activate();sheetErr.Range["A1","A1"].Value = "工单";sheetErr.Range["B1", "B1"].Value = "成品";sheetErr.Range["C1", "C1"].Value = "未入库数";sheetErr.Range["D1", "D1"].Value = "排期数";for(int j = 0; j <errJobs.Count; j++){sheetErr.Range[$"A{j+2}", $"A{j + 2}"].Value = errJobs[j].JOBNO;sheetErr.Range[$"B{j + 2}", $"B{j + 2}"].Value =errJobs[j].P_CODE;sheetErr.Range[$"C{j + 2}", $"C{j + 2}"].Value = errJobs[j].CQTY;sheetErr.Range[$"D{j + 2}", $"D{j + 2}"].Value = errJobs[j].SCHQTY;}//string err = string.Join("\r\n", (from j in errJobs select j.JOBNO + ":" + j.P_CODE + ":" + j.CQTY + ":" + j.SCHQTY).ToList());this.BeginInvoke(new System.Action(() => {// Clipboard.SetText(err);notify.Text = "导入排期失败，排期中有工单超过系统数量！！！";//MessageBox.Show(this, $"工单排期数量超过系统中工单数量", "！！！", MessageBoxButtons.OK, MessageBoxIcon.Warning);}));//return;}*/#endregiontry{notify.Text = "导入排期";_db.ExeNoQuery($"DELETE YJY_JOBSCHDULE where CO='{_company}'");_db.SqlBulkCopy("YJY_JOBSCHDULE", dt);}catch (Exception ex){notify.Text = "导入排期失败，写入数据库异常！！！";_showErrMsg = $"导入排期数据出错，请检查格式\r\n{ex.Message}\r\n{ex.StackTrace}";return;}}notify.Text = "正在运行存储过程[YJY_Schedule_Compute]";Dictionary<string, object> _params = new Dictionary<string, object>();_params.Add("@CO", _company);_params.Add("@LT", _LT);_params.Add("@BeginDate", dpPrddateBegin.Text);_params.Add("@EndDate", dpPrddateEnd.Text);_params.Add("@BeginWo", txtMoBegin.Text.Trim());_params.Add("@EndWo", txtMoEnd.Text.Trim());_params.Add("@BeginMtrl", txtMtrlBegin.Text.Trim());_params.Add("@EndMtrl", txtMtrlEnd.Text.Trim());try{using (var ds = _db.GetDtRunProc("YJY_Schedule_Compute", _params)){notify.Text = "存储过程[YJY_Schedule_Compute]执行完成";Thread.Sleep(100);notify.Text = "生成欠料表，并设置格式";using (ExcelPackage pk = new ExcelPackage(new System.IO.FileInfo(_saveFile))){var sheetShort = pk.Workbook.Worksheets.Add("欠料表");using (var allCells = sheetShort.Cells["A1"].LoadFromDataTable(ds.Tables[0], true)){allCells.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; //设置单元格边框allCells.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;allCells.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;allCells.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;}sheetShort.Column(1).Width = 30; //成品 单元格sheetShort.Column(1).Style.WrapText = true;sheetShort.Column(2).Width = 18; //物料编码 单元格sheetShort.Column(3).Width = 20; //物料描述 单元格sheetShort.Column(3).Style.WrapText = true;sheetShort.Column(8).Width = 50; //在途PO 单元格sheetShort.Column(8).Style.WrapText = true;sheetShort.Column(9).Style.ShrinkToFit = true; //在途PO数量 单元格//设置数量列格式为千分位分隔sheetShort.Column(9).Style.Numberformat.Format = "#,###";sheetShort.Column(10).Style.ShrinkToFit = true; // 工单欠料 单元格sheetShort.Column(10).Style.Numberformat.Format = "#,###";sheetShort.Column(11).Style.ShrinkToFit = true; // FC欠料 单元格sheetShort.Column(11).Style.Numberformat.Format = "#,###";sheetShort.Column(12).Style.ShrinkToFit = true; //总库存 单元格sheetShort.Column(12).Style.Numberformat.Format = "#,###";sheetShort.Column(13).Style.ShrinkToFit = true; //IQC总数 单元格sheetShort.Column(13).Style.Numberformat.Format = "#,###";sheetShort.Column(14).Style.ShrinkToFit = true; //工单需求 单元格sheetShort.Column(14).Style.Numberformat.Format = "#,###";sheetShort.Column(15).Style.ShrinkToFit = true; //FC需求 单元格sheetShort.Column(15).Style.Numberformat.Format = "#,###";//设置欠料数量为红色using (var range = sheetShort.Cells[$"Q2:{sheetShort.Dimension.End.Address}"]){range.Style.Numberformat.Format = "#,###";range.Style.Font.Color.SetColor(Color.Red);}sheetShort.View.FreezePanes(1, 17); //冻结列//FC欠料列设置为黄色for(int i = 17; i <= ds.Tables[0].Columns.Count; i++){if (ds.Tables[0].Columns[i-1].ColumnName.StartsWith("FC")){sheetShort.Column(i).Style.Fill.PatternType = ExcelFillStyle.Solid;sheetShort.Column(i).Style.Fill.BackgroundColor.SetColor(Color.Yellow);}}var sheetIQC = pk.Workbook.Worksheets.Add("IQC急料表");sheetIQC.Cells["A1"].LoadFromDataTable(ds.Tables[1], true);var sheetBak = pk.Workbook.Worksheets.Add("YJY_JOBSCHDULE_SCHEDULE");sheetBak.Cells["A1"].LoadFromDataTable(_db.GetDataTable($"select * from YJY_JOBSCHDULE_SCHEDULE"), true);var sheetBak1 = pk.Workbook.Worksheets.Add("YJY_JOBSCHDULE_NOPICKED");sheetBak1.Cells["A1"].LoadFromDataTable(_db.GetDataTable($"select * from YJY_JOBSCHDULE_NOPICKED"), true);var sheetBak2 = pk.Workbook.Worksheets.Add("YJY_JOBSCHDULE_RPT1_INIT");sheetBak2.Cells["A1"].LoadFromDataTable(_db.GetDataTable($"select * from YJY_JOBSCHDULE_RPT1_INIT"), true);var sheetBak3 = pk.Workbook.Worksheets.Add("YJY_JOBSCHDULE_RPT1");sheetBak3.Cells["A1"].LoadFromDataTable(_db.GetDataTable($"select * from YJY_JOBSCHDULE_RPT1"), true);pk.Save();}}notify.Text = "报表生成成功！！！";}catch (Exception ex){notify.Text = $"执行存储失败，{ex.Message}";_showErrMsg = $"执行存储失败\r\n{ex.Message}\r\n{ex.StackTrace}";//MessageBox.Show(this, $"执行存储失败\r\n{ex.Message}\r\n{ex.StackTrace}", "！！！", MessageBoxButtons.OK, MessageBoxIcon.Warning);}}).ContinueWith(t =>{_isProcessing = false;if (!string.IsNullOrEmpty(_showErrMsg)){MessageBox.Show(this,_showErrMsg,"生成报表失败", MessageBoxButtons.OK, MessageBoxIcon.Error);}try{_db.ExeNoQuery($"update YJY_APPLOCKER set LOCKED=0,LOCKTIME='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff")}' where APPID='OverMtrl'"); //解锁程序}catch(Exception exp){this.notify.Text = $"解锁程序失败！！！\r\n{exp.Message}\r\n{exp.StackTrace}";}btnCompute.Enabled = true;});
插件安装后，EXCEL中会出现名为“欠料表”的选项卡
文章转载自不想做程序员，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
