/// <summary> /// 产生SMT程式料站表 /// </summary> /// <param name="IsCreateExcel">是否保存Excel文件</param> /// <param name="aa">null</param> /// <param name="bb">null</param> /// <param name="dt">null</param> private void SmtSoftKpListCompareBomData(string IsCreateExcel, string aa, string bb, DataTable dt) { try { DataTable newtable = new DataTable(); this.SmtKpTable = new DataTable(); newtable.Columns.Add("产品料号", System.Type.GetType("System.String")); newtable.Columns.Add("产品描述", System.Type.GetType("System.String")); newtable.Columns.Add("机器编号", System.Type.GetType("System.String")); newtable.Columns.Add("BOM版本", System.Type.GetType("System.String")); newtable.Columns.Add("FEEDER类型", System.Type.GetType("System.String")); newtable.Columns.Add("料站总数", System.Type.GetType("System.String")); newtable.Columns.Add("料站", System.Type.GetType("System.String")); newtable.Columns.Add("组件料号", System.Type.GetType("System.String")); newtable.Columns.Add("替代组", System.Type.GetType("System.String")); newtable.Columns.Add("优先级", System.Type.GetType("System.String")); newtable.Columns.Add("品名与规格", System.Type.GetType("System.String")); newtable.Columns.Add("数量", System.Type.GetType("System.String")); newtable.Columns.Add("面别", System.Type.GetType("System.String")); newtable.Columns.Add("程式名", System.Type.GetType("System.String")); newtable.Columns.Add("组件位置", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("partnumber", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("lineId", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("pcbside", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("bomver", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("stationno", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("kpnumber", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("kpdesc", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("replacegroup", System.Type.GetType("System.String")); SmtKpTable.Columns.Add("loction", System.Type.GetType("System.String")); this.ShowMsg(LogMsgType.Outgoing, "正在处理料站表格式......."); int i = 0; this.SetPb2MaxValue(this.SmtSoftKpList.Rows.Count); foreach (DataRow dr in this.SmtSoftKpList.Rows) { i++; DataRow[] arrDr = this.BomDataTable.Select(string.Format("kpnumber='{0}'", dr["kpnumber"].ToString())); //判断料在BOM中是否存在 if (arrDr.Length < 1) { throw new Exception(string.Format("料站表中的物料[{0}] 在BOM中没有找到", dr["kpnumber"].ToString())); } //判断是否存在替代料 if (!string.IsNullOrEmpty(arrDr[0]["kpgroup"].ToString().Trim())) { DataRow[] _arrDr = this.BomDataTable.Select(string.Format("kpgroup='{0}'", arrDr[0]["kpgroup"].ToString())); foreach (DataRow drItem in _arrDr) { newtable.Rows.Add(this.mPartnumber, this.mProductname, dr["lineId"].ToString(), drItem["bomver"].ToString(), dr["feeder"].ToString(), dr["stationcount"].ToString(), dr["stationId"].ToString(), drItem["kpnumber"].ToString(), drItem["kpgroup"].ToString(), "0", drItem["kpdesc"].ToString(), drItem["qty"].ToString(), dr["pcbside"].ToString(), dr["smtsoftname"].ToString(), dr["loction"].ToString()); this.SmtKpTable.Rows.Add(this.mPartnumber, dr["lineId"].ToString(), dr["pcbside"].ToString(), drItem["bomver"].ToString(), dr["stationId"].ToString(), drItem["kpnumber"].ToString(), drItem["kpdesc"].ToString(), drItem["kpgroup"].ToString(), dr["loction"].ToString()); } } else { newtable.Rows.Add(this.mPartnumber, this.mProductname, dr["lineId"].ToString(), arrDr[0]["bomver"].ToString(), dr["feeder"].ToString(), dr["stationcount"].ToString(), dr["stationId"].ToString(), dr["kpnumber"].ToString(), "", "0", arrDr[0]["kpdesc"].ToString(), arrDr[0]["qty"].ToString(), dr["pcbside"].ToString(), dr["smtsoftname"].ToString(), dr["loction"].ToString()); this.SmtKpTable.Rows.Add(this.mPartnumber, dr["lineId"].ToString(), dr["pcbside"].ToString(), arrDr[0]["bomver"].ToString(), dr["stationId"].ToString(), dr["kpnumber"].ToString(), arrDr[0]["kpdesc"].ToString(), "", dr["loction"].ToString()); } this.Showpb1(i); } this.ShowMsg(LogMsgType.Incoming, "料站表格式处理完成。"); DataTable _dt = newtable.DefaultView.ToTable(true, "面别"); FrmBLL.ClsAllExcel exl = new FrmBLL.ClsAllExcel(); this.SetPb2MaxValue(_dt.Rows.Count); if (IsCreateExcel == "YES") { this.ShowMsg(LogMsgType.Outgoing, "正在输出料站表...."); this.ShowMsg(LogMsgType.Outgoing, "累计" + _dt.Rows.Count + "份料站表"); i = 0; string filename = string.Empty; foreach (DataRow _itm in _dt.Rows) { i++; this.ShowMsg(LogMsgType.Outgoing, "正在输出第" + i + "份料站表...."); DataTable _dta = FrmBLL.publicfuntion.getNewTable(newtable, string.Format("面别='{0}'", _itm["面别"].ToString())); string smtsoftname = _dta.Rows[0]["程式名"].ToString().Trim(); _dta.Columns.Remove("面别"); _dta.Columns.Remove("程式名"); if (File.Exists(string.Format("{0}Excel\\{1}.xls", System.AppDomain.CurrentDomain.BaseDirectory, smtsoftname))) { File.Delete(string.Format("{0}Excel\\{1}.xls", System.AppDomain.CurrentDomain.BaseDirectory, smtsoftname)); } exl.ExportExcel(_dta, filename = string.Format("{0}Excel\\{1}.xls", System.AppDomain.CurrentDomain.BaseDirectory, smtsoftname), string.Format("S{2}{3}L-{0}M {1}", _itm["面别"].ToString(), smtsoftname, int.Parse(newtable.Rows[0]["机器编号"].ToString().Substring(0, 2)), int.Parse(newtable.Rows[0]["机器编号"].ToString().Substring(4, 2)).ToString("D2"))); this.Showpb1(i); this.ShowMsg(LogMsgType.Incoming, "第" + i + "份料站表输出完成,保存路径:\n" + filename); } this.ShowMsg(LogMsgType.Incoming, "全部完成"); } this.ShowData2(newtable); } catch (Exception ex) { this.ShowMsg(LogMsgType.Error, ex.Message); } }
private void SaveExcelSmtSoftKpList(string[] sFileName) { this.ShowMsg(LogMsgType.Warning, "开始读取Excel."); DataTable dtTemp = new DataTable(); dtTemp.Columns.Add("IID", System.Type.GetType("System.String")); dtTemp.Columns.Add("smtsoftname", System.Type.GetType("System.String")); dtTemp.Columns.Add("pcbside", System.Type.GetType("System.String")); dtTemp.Columns.Add("LineId", System.Type.GetType("System.String")); dtTemp.Columns.Add("stationId", System.Type.GetType("System.String")); dtTemp.Columns.Add("kpnumber", System.Type.GetType("System.String")); dtTemp.Columns.Add("feeder", System.Type.GetType("System.String")); dtTemp.Columns.Add("loction", System.Type.GetType("System.String")); foreach (string item in sFileName) { DataTable dt1 = new DataTable(); FrmBLL.ClsAllExcel cls = new FrmBLL.ClsAllExcel(); cls.OpenFileName = item; cls.OpenExcelFile(); dt1 = cls.getAllCellsValue(); cls.CloseExcelFile(); foreach (DataRow dr in dt1.Rows) { dtTemp.ImportRow(dr); } } this.ShowMsg(LogMsgType.Incoming, "读取Execel完成."); this.ShowMsg(LogMsgType.Warning, "开始处理料站表..."); SmtSoftKpList = dtTemp; this.SetPb2MaxValue(SmtSoftKpList.Rows.Count); int i = 0; FrmBLL.cdbAccess ass = new FrmBLL.cdbAccess(); ass.ExecuteOracleCommand("delete from smtsoftkplist"); foreach (DataRow dr in SmtSoftKpList.Rows) { string sql = string.Format("insert into smtsoftkplist(smtsoftname,pcbside,lineId,stationId,kpnumber,feeder,stationcount,loction) values('{0}','{1}','{2}','{3}','{4}','{5}','0','{6}')", dr["smtsoftname"].ToString(), dr["pcbside"].ToString(), dr["lineId"].ToString(), dr["stationId"].ToString(), dr["kpnumber"].ToString(), dr["feeder"].ToString(), dr["loction"].ToString()); ass.ExecuteOracleCommand(sql); this.Showpb1(i++); } string cmd = "select lineId,count(stationId) as total from smtsoftkplist group by lineId"; DataTable dt = ass.GetDatatable(cmd); foreach (DataRow dr in dt.Rows) { cmd = string.Format("update smtsoftkplist set stationcount='{0}' where lineId='{1}'", dr["total"].ToString(), dr["lineId"].ToString()); ass.ExecuteOracleCommand(cmd); } SmtSoftKpList = ass.GetDatatable("select * from smtsoftkplist"); this.ShowMsg(LogMsgType.Incoming, "Excel料站表处理完成."); this.ShowData2(SmtSoftKpList); }
private void bt_expkpmaster_Click(object sender, EventArgs e) { try { if (this.RecDr == null) { this.mFrm.ShowPrgMsg("请双击需要打印的料表编号", MainParent.MsgType.Warning); return; } if (!File.Exists(string.Format("{0}Excel\\KpMaster.xlt", System.AppDomain.CurrentDomain.BaseDirectory))) { FrmBLL.Ftp_MyFtp ftp = new FrmBLL.Ftp_MyFtp(); ftp.DownloadFile("KpMaster.xlt", System.AppDomain.CurrentDomain.BaseDirectory + "Excel", "KpMaster.xlt"); } string FileNameTemp = string.Format("{0}Excel\\待审核_{1}_{2}_{3}_{4}.xls", System.AppDomain.CurrentDomain.BaseDirectory, this.RecDr.Cells["masterId"].Value.ToString(), this.RecDr.Cells["partnumber"].Value.ToString(), this.RecDr.Cells["machine"].Value.ToString(), this.RecDr.Cells["pcbside"].Value.ToString()); FrmBLL.ClsAllExcel excel = new FrmBLL.ClsAllExcel(); this.mFrm.ShowPrgMsg("正在生成报表", MainParent.MsgType.Outgoing); excel.OpenFileName = string.Format("{0}Excel\\KpMaster.xlt", System.AppDomain.CurrentDomain.BaseDirectory); // FileNameTemp; excel.OpenExcelFile(); //SMT料站表 excel.setOneCellValue(1, 1, this.RecDr.Cells["reserve2"].Value.ToString() != "审核通过" ? "SMT料站表--待审核" : "SMT料站表"); //excel.setOneCellValue(2, 7, this.RecDr.Cells["userId"].Value.ToString());//制作人 excel.setOneCellValue(2, 2, this.RecDr.Cells["masterId"].Value.ToString()); //料表编号 excel.setOneCellValue(3, 2, this.RecDr.Cells["machine"].Value.ToString()); //机器编号 excel.setOneCellValue(4, 2, this.RecDr.Cells["partnumber"].Value.ToString()); //产品料号 excel.setOneCellValue(5, 2, this.RecDr.Cells["bomver"].Value.ToString()); //BOM版本 excel.setOneCellValue(5, 7, this.RecDr.Cells["reserve1"].Value.ToString()); //SMT程式 excel.setOneCellValue(4, 4, this.RecDr.Cells["modelname"].Value.ToString()); //产品型号 excel.setOneCellValue(5, 4, this.RecDr.Cells["pcbside"].Value.ToString()); //PCB面 for (int i = 0; i < this.dgv_ShowKPDetalt.Rows.Count - 1; i++) { excel.setOneCellValue(8 + i, 1, this.dgv_ShowKPDetalt["stationno", i].Value.ToString()); excel.setOneCellValue(8 + i, 2, this.dgv_ShowKPDetalt["kpnumber", i].Value.ToString()); excel.setOneCellValue(8 + i, 3, this.dgv_ShowKPDetalt["kpdesc", i].Value.ToString()); excel.setOneCellValue(8 + i, 4, this.dgv_ShowKPDetalt["kpdistinct", i].Value.ToString()); excel.setOneCellValue(8 + i, 5, this.dgv_ShowKPDetalt["replacegroup", i].Value.ToString()); excel.setOneCellValue(8 + i, 6, this.dgv_ShowKPDetalt["priorityclass", i].Value.ToString()); excel.setOneCellValue(8 + i, 7, this.dgv_ShowKPDetalt["loction", i].Value.ToString()); excel.setOneCellValue(8 + i, 8, this.dgv_ShowKPDetalt["_reserve", i].Value.ToString()); } excel.CellsDrawFrame(8, 1, this.dgv_ShowKPDetalt.Rows.Count - 2 + 8, 8, true, true, true, true, true, true, false, false, Excel.XlLineStyle.xlDashDot, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexNone); excel.SaveFileName = FileNameTemp; bool isView = false; if (MessageBoxEx.Show("是否预览文件?\n是 请选择[Yes] 否则请选择[NO]", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes) { isView = true; } excel.SaveAsExcel(isView); excel.CloseExcelApplication(); this.mFrm.ShowPrgMsg("报表生成完成", MainParent.MsgType.Outgoing); } catch (Exception ex) { this.mFrm.ShowPrgMsg(ex.Message, MainParent.MsgType.Error); } }