/// <summary> /// 打印FPC软板批量卡和单体图以及辅料卡 /// </summary> /// <param name="xlBook">当前工作簿</param> /// <param name="row">取得的数据行</param> /// <param name="lotId">当前批量卡序号</param> /// <param name="danTiTuFullPath">当前单体图全路径</param> /// <param name="dgvRow">当前dgv行</param> /// <param name="cadApp">cad对象</param> /// <param name="xlApp">excel对象</param> /// <param name="productNum">生产编号</param> /// <param name="iMaxPnlQty">最多多少pnl需要打一张辅料卡</param> private void PrintFpcLotCard( Excel.Workbook xlBook, DataRow row, string lotId, string danTiTuFullPath, DataGridViewRow dgvRow, GcadApplication cadApp, Excel.Application xlApp, string productNum, decimal iMaxPnlQty ) { //当前工作表 var xlSheet = (Excel.Worksheet)xlBook.Worksheets[1]; //设置值 //流程卡卡号 xlSheet.Shapes.AddTextEffect( MsoPresetTextEffect.msoTextEffect2, lotId, "宋体", 18f, MsoTriState.msoFalse, MsoTriState.msoFalse, 430f, 15f ); //所有需要填写数据的单元格 var xlRange = xlSheet.Range[xlSheet.Cells[5, 13], xlSheet.Cells[17, 15]]; xlRange.ShrinkToFit = true; //下单日期 xlSheet.Range["N5"].Value = Convert.ToDateTime(row["order_date"]).ToString("yyyy-MM-dd"); //交货日期 xlSheet.Range["N6"].Value = Convert.ToDateTime(row["need_date"]).ToString("yyyy-MM-dd"); //订单数量 xlSheet.Range["N7"].Value = string.Format("{0} pcs", row["order_pcs_qty"]); //总投料量 xlSheet.Range["N8"].Value = string.Format("{0} pcs", row["total_pcs_qty"]); //本批数量 xlSheet.Range["N9"].Value = string.Format("{0} pnl", row["total_pnl_qty"]); //单元格拆分 xlRange = xlSheet.Range[xlSheet.Cells[11, 13], xlSheet.Cells[17, 15]]; xlRange.MergeCells = false; //合并单元格 for (int i = 11; i < 16; i++) { xlRange = xlSheet.Range[string.Format("N{0}:O{0}", i)]; xlRange.Merge(); } for (int i = 16; i < 18; i++) { xlRange = xlSheet.Range[string.Format("M{0}:O{0}", i)]; xlRange.Merge(); } //边框线 xlRange = xlSheet.Range[xlSheet.Cells[5, 13], xlSheet.Cells[17, 15]]; xlRange.ShrinkToFit = true; xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; xlRange.Borders.Weight = Excel.XlBorderWeight.xlThin; //投料单号 xlSheet.Range["N10"].Value = row["order_num"].ToString(); //起始卡号 xlSheet.Range["M11"].Value = "起始卡号"; xlSheet.Range["N11"].Value = row["first_lot_id"].ToString(); //结束卡号 xlSheet.Range["M12"].Value = "结束卡号"; xlSheet.Range["N12"].Value = row["last_lot_id"].ToString(); //单据状态 xlSheet.Range["M13"].Value = "单据状态"; xlSheet.Range["N13"].Value = row["state"].ToString(); //pnl含pcs数 xlSheet.Range["M14"].Value = "pnl含pcs数"; xlSheet.Range["N14"].Value = row["pnl_count_pcs"].ToString(); //pnl面积 xlSheet.Range["M15"].Value = "pnl面积"; xlSheet.Range["N15"].Value = string.Format("{0} m2", row["pnl_area"]); //lot数 var iFirst = Convert.ToInt32(row["first_lot_id"].ToString().Substring(4).Replace("S", string.Empty)); var iLast = Convert.ToInt32(row["last_lot_id"].ToString().Substring(4).Replace("S", string.Empty)); if (iLast < iFirst) { var i = iLast; iLast = iFirst; iFirst = i; } var iCurrent = Convert.ToInt32(lotId.Substring(4).Replace("S", string.Empty)); xlSheet.Range["M16"].Value = string.Format("{0} LOT 共 {1} LOT", iCurrent - iFirst + 1, iLast - iFirst + 1); //本卡开料pnl数 xlRange = xlSheet.Range["M17"]; xlRange.Font.Bold = true; xlRange.Font.Size = 18; xlRange.Value = string.Format("{0} pnl", row["pnl_qty"]); //当前路径 string strPath = Application.ExecutablePath; int j = strPath.LastIndexOf(@"\"); strPath = strPath.Remove(j + 1); //待打印成的图片文件的名称 string imgName = string.Format("{0}dwg2jpg.jpg", strPath); //将单体图读取到lot卡中 switch (cboPrintSettings.Text) { case "不打印只保存单元图": case "不打印保存批量卡和cov卡以及单元图": case "只打印单元图": case "打印批量卡和cov卡以及单元图": //显示状态 dgvRow.Cells["print_msg"].Value = "正在打开单体图..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //删除之前的文件 if (File.Exists(imgName)) { File.Delete(imgName); } //显示状态 dgvRow.Cells["print_msg"].Value = "正在生成单体图图片..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //执行打印单体图生成图片文件 string strOutPut = ydDwg2Jpg.PrintDanTiTuImage(cadApp, danTiTuFullPath, imgName, cboDwgLayout.Text, cboDwgWay.SelectedIndex); //for (int iii = 0; iii < 12; iii++) //{ // ydDwg2Jpg.PrintDanTiTuImage(cadApp, danTiTuFullPath, imgName + iii + ".jpg", iii); //} //检测文件是否存在 if (strOutPut.Length > 0 || !File.Exists(imgName)) { //返回错误 dgvRow.Cells["print_msg"].Value = strOutPut; return; } else { //显示状态 dgvRow.Cells["print_msg"].Value = "正在插入单体图图片到批量卡..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //设置为活动 ((Excel._Workbook)xlBook).Activate(); ((Excel._Worksheet)xlSheet).Select(); //在excel中插入该图片并设置分页 string strError = string.Empty; if (!InsertDanTiTuImageToExcelSheet(xlSheet, imgName, out strError)) { //返回错误 dgvRow.Cells["print_msg"].Value = "插入单体图图片到批量卡失败!"; return; } } //退出 break; } //待打印成的文件名称 string tmpFileName = string.Format("{0}lotcard.xls", strPath); //删除之前的文件 if (File.Exists(tmpFileName)) { File.Delete(tmpFileName); } //打印设置 var xlPageSetup = xlSheet.PageSetup; xlPageSetup.FitToPagesWide = 1; xlPageSetup.FitToPagesTall = 1; xlPageSetup.RightFooter = @"&""宋体,倾斜""&10打印时间:&D &T"; xlPageSetup.FooterMargin = xlApp.InchesToPoints(0.18d); //保存该批量卡 xlBook.SaveAs(tmpFileName); //不打印只保存批量卡和cov卡 //不打印只保存单元图 //不打印保存批量卡和cov卡以及单元图 //只打印批量卡和cov卡 //只打印单元图 //打印批量卡和cov卡以及单元图 //打印 switch (cboPrintSettings.Text.Trim()) { case "打印批量卡和cov卡以及单元图": //显示状态 dgvRow.Cells["print_msg"].Value = "正在打印批量卡和cov卡以及单元图..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //打印LOT卡和单体图 xlSheet.PrintOut(From: 1, To: 2, Copies: 1, Collate: true); break; case "只打印批量卡": //显示状态 dgvRow.Cells["print_msg"].Value = "正在打印批量卡..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //打印LOT卡 xlSheet.PrintOut(From: 1, To: 1, Copies: 1, Collate: true); break; case "只打印批量卡和cov卡": //显示状态 dgvRow.Cells["print_msg"].Value = "正在打印批量卡和cov卡..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //打印LOT卡 xlSheet.PrintOut(From: 1, To: 1, Copies: 1, Collate: true); break; case "只打印单元图": //显示状态 dgvRow.Cells["print_msg"].Value = "正在打印单元图..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //打印单体图 xlSheet.PrintOut(From: 2, To: 2, Copies: 1, Collate: true); break; } //打印辅料卡 switch (cboPrintSettings.Text.Trim()) { case "只打印批量卡和cov卡": case "打印批量卡和cov卡以及单元图": //之前已经累加存入数量 int iTotalPnlQty = 0; //检测是否已经打印过辅料卡 if (!_dicProductNums.ContainsKey(productNum)) { //添加到已经打印过的生产编号清单中 _dicProductNums.Add(productNum, Convert.ToInt32(row["pnl_qty"])); } else { //获取数量 _dicProductNums.TryGetValue(productNum, out iTotalPnlQty); //检测数量 if (iTotalPnlQty >= iMaxPnlQty) { //总数量置0 iTotalPnlQty = 0; } //累加已经打印过的生产编号的数量 _dicProductNums[productNum] = iTotalPnlQty + Convert.ToInt32(row["pnl_qty"]); } //总数量为0则需要打印辅料卡 if (iTotalPnlQty == 0 && xlBook.Worksheets.Count > 1) { //取第二张表 var xlSheet2 = (Excel.Worksheet)xlBook.Worksheets[2]; //显示状态 dgvRow.Cells["print_msg"].Value = "正在打印cov卡..."; //各列列宽 dgvList.AutoResizeColumns(); //显示 Application.DoEvents(); //设置为最多两页高 xlSheet2.PageSetup.FitToPagesWide = 1; xlSheet2.PageSetup.FitToPagesTall = 2; //打印辅料卡 try { xlSheet2.PrintOut(From: 1, To: 1, Copies: 1, Collate: true); xlSheet2.PrintOut(From: 2, To: 2, Copies: 1, Collate: true); } catch { } //注销excel相关对象 xlSheet2 = null; } //退出 break; } //关闭工作表 xlBook.Close(SaveChanges: false); //删除生成的图片文件 if (File.Exists(imgName)) { File.Delete(imgName); } //返回成功 //不打印只保存批量卡 //不打印只保存批量卡和cov卡 //不打印只保存单元图 //不打印保存批量卡和cov卡以及单元图 //只打印批量卡 //只打印批量卡和cov卡 //只打印单元图 //打印批量卡和cov卡以及单元图 //打印 switch (cboPrintSettings.Text.Trim()) { case "不打印只保存批量卡": case "不打印只保存批量卡和cov卡": case "不打印只保存单元图": case "不打印保存批量卡和cov卡以及单元图": dgvRow.Cells["print_msg"].Value = "保存成功!"; //退出 break; default: dgvRow.Cells["print_msg"].Value = "打印成功!"; //退出 break; } //注销excel相关对象 xlSheet = null; }
/// <summary> /// 格式化EXCEL /// </summary> /// <param name="excelApp">excelApp</param> /// <param name="excelSheet">excelSheet</param> /// <param name="DetRowCnt">行数</param> private void FormatExcel(ref MSOffice.Application excelApp, ref MSOffice.Worksheet excelSheet, int DetRowCnt) { MSOffice.Range rng; int intDtEnd = 4 + (int)System.Math.Ceiling(DetRowCnt / (double)2); #region 设置全局 //全局字体及大小 excelSheet.get_Range("A1", "G" + Convert.ToString(intDtEnd + 3)).Font.Size = 10; excelSheet.get_Range("A1", "G" + Convert.ToString(intDtEnd + 3)).Font.Name = "宋体"; #endregion #region 调整列宽 excelSheet.get_Range("A1", "A1").ColumnWidth = 18; excelSheet.get_Range("B1", "B1").ColumnWidth = 18; excelSheet.get_Range("C1", "C1").ColumnWidth = 0.3; excelSheet.get_Range("D1", "D1").ColumnWidth = 18; excelSheet.get_Range("E1", "E1").ColumnWidth = 18; #endregion #region 调整行高 excelSheet.get_Range("A1", "A1").RowHeight = 22.5; excelSheet.get_Range("A2", "A2").RowHeight = 22.5; excelSheet.get_Range("A3", "A3").RowHeight = 23.25; excelSheet.get_Range("A4", "A4").RowHeight = 21; excelSheet.get_Range("A" + Convert.ToString(5), "A" + Convert.ToString(intDtEnd + 2)).RowHeight = 14.25; #endregion #region 设置线条 rng = excelSheet.get_Range("A4", "E" + Convert.ToString(intDtEnd)); rng.Borders[MSOffice.XlBordersIndex.xlEdgeTop].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlEdgeTop].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlEdgeBottom].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlEdgeBottom].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlEdgeLeft].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlEdgeLeft].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlEdgeRight].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlEdgeRight].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlInsideHorizontal].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlInsideVertical].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng = excelSheet.get_Range("C4", "C" + Convert.ToString(intDtEnd)); rng.Borders[MSOffice.XlBordersIndex.xlEdgeLeft].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlEdgeRight].Weight = MSOffice.XlBorderWeight.xlMedium; rng.Borders[MSOffice.XlBordersIndex.xlInsideHorizontal].LineStyle = MSOffice.XlLineStyle.xlLineStyleNone; rng = excelSheet.get_Range("A4", "E4"); rng.Borders[MSOffice.XlBordersIndex.xlEdgeBottom].Weight = MSOffice.XlBorderWeight.xlMedium; #endregion #region 设置字体格式与排列 //名称居左,金额居右 excelSheet.get_Range("A5", "A" + Convert.ToString(intDtEnd)).HorizontalAlignment = MSOffice.XlHAlign.xlHAlignLeft; excelSheet.get_Range("B5", "B" + Convert.ToString(intDtEnd)).HorizontalAlignment = MSOffice.XlHAlign.xlHAlignRight; excelSheet.get_Range("D5", "D" + Convert.ToString(intDtEnd)).HorizontalAlignment = MSOffice.XlHAlign.xlHAlignLeft; excelSheet.get_Range("E5", "E" + Convert.ToString(intDtEnd)).HorizontalAlignment = MSOffice.XlHAlign.xlHAlignRight; #endregion //几个特殊单元格 excelSheet.get_Range("A1", "E1").Merge(true); excelSheet.get_Range("A2", "E2").Merge(true); rng = excelSheet.get_Range("A1", "E2"); rng.Font.Bold = true; rng.Font.Size = 18; rng.Font.Name = "楷体_GB2312"; rng.HorizontalAlignment = MSOffice.XlHAlign.xlHAlignCenter; rng.VerticalAlignment = MSOffice.XlVAlign.xlVAlignBottom; ////////////////////测试///////////////////////////// rng.Font.Background = 1; rng.Font.Bold = true; rng.Font.Size = 18; rng.Font.Name = ""; rng.Font.Underline = true; rng.Font.Underline = MSOffice.XlUnderlineStyle.xlUnderlineStyleSingle; rng.Font.ThemeFont = MSOffice.XlThemeFont.xlThemeFontNone; excelApp.ActiveCell.FormulaR1C1 = ""; rng.Borders[MSOffice.XlBordersIndex.xlInsideVertical].LineStyle = MSOffice.XlLineStyle.xlContinuous; rng.Borders[MSOffice.XlBordersIndex.xlInsideVertical].Weight = MSOffice.XlBorderWeight.xlMedium; rng.AutoFit(); rng.Rows.AutoFit(); rng.Columns.AutoFit(); rng = excelSheet.get_Range("A3", "A3"); rng.Font.Bold = false; rng.Font.Size = 11; rng.Font.Name = "楷体_GB2312"; rng.HorizontalAlignment = MSOffice.XlHAlign.xlHAlignLeft; rng = excelSheet.get_Range("A4", "E4"); rng.Font.Bold = true; rng.Font.Size = 12; rng.HorizontalAlignment = MSOffice.XlHAlign.xlHAlignCenter; #region 设置打印格式 excelSheet.PageSetup.PaperSize = MSOffice.XlPaperSize.xlPaperA4; //excelSheet.PageSetup.FitToPagesWide = 1; //excelSheet.PageSetup.FitToPagesTall = 100; //excelSheet.PageSetup.Zoom = false; excelSheet.PageSetup.LeftMargin = excelApp.InchesToPoints(1 / 2.54 * 1.3); excelSheet.PageSetup.RightMargin = excelApp.InchesToPoints(1 / 2.54 * 1.3); excelSheet.PageSetup.TopMargin = excelApp.InchesToPoints(1 / 2.54 * 1.9); excelSheet.PageSetup.BottomMargin = excelApp.InchesToPoints(1 / 2.54 * 1.9); excelSheet.PageSetup.FooterMargin = excelApp.InchesToPoints(1 / 2.54 * 0.5); excelSheet.PageSetup.Orientation = MSOffice.XlPageOrientation.xlLandscape; #endregion }