/// <summary> /// 释放内存 /// </summary> public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet); CurSheet = null; CurBook.Close(false, mValue, mValue); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook); CurBook = null; CurExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel); CurExcel = null; GC.Collect(); GC.WaitForPendingFinalizers(); } catch (System.Exception) { // Response.Write("在释放Excel内存空间时发生了一个错误:" + ex); } finally { foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) //if (pro.StartTime < DateTime.Now) pro.Kill(); } System.GC.SuppressFinalize(this); }
// closes Excel public static void CloseExcel(Excel.Application xlApp, Excel.Workbook wb) { // Close Excel object misValue = System.Reflection.Missing.Value; wb.Close(false, misValue, misValue); xlApp.Quit(); }
public void CloseBook(Excel.Workbook book, bool save) { Log.Info(LoggerConstants.ENTER); if (book == null) { Log.Warn(LoggerConstants.BAD_VALIDATION); Log.Info(LoggerConstants.EXIT); return; } try { Log.Info("Book name is `" + book.Name + "`"); if (save) book.Save(); book.Close(); } catch (COMException e) { Log.Warn("COMException with closing `" + book.Name + "`", e); } catch (Exception e) { Log.Warn("Exception with closing `" + book.Name + "`", e); } Log.Info(LoggerConstants.EXIT); }
protected void ReleaseExcel(ref MSOffice.Application excelApp, ref MSOffice.Workbook excelBook, ref MSOffice.Worksheet excelSheet) { try { excelBook.Close(false, null, null); excelApp.Quit(); GC.Collect(); IntPtr ptr = new IntPtr(excelApp.Hwnd); int pid = 0; GetWindowThreadProcessId(ptr, out pid); System.Diagnostics.Process proc = System.Diagnostics.Process.GetProcessById(pid); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelBook); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelSheet); excelApp = null; excelBook = null; excelSheet = null; //最后尝试结束进程,出错表示已销毁 try { proc.Kill(); } catch (Exception) { } } catch (Exception ex) { throw ex; } }
private static void CloseAndReleaseExcelObjects(Excel.Application xlApp, Excel.Workbook xlworkBook, Excel.Worksheet xlWorksheet) { xlworkBook.Close(false, null, null); xlApp.Quit(); ReleaseObject(xlWorksheet); ReleaseObject(xlworkBook); ReleaseObject(xlApp); }
/// <summary> /// 저장및 메모리 해제 /// </summary> /// <param name="excelApp"></param> /// <param name="wb"></param> /// <param name="workSheet"></param> public static void ExcelDispose(Excel.Application excelApp, Excel.Workbook wb, Excel._Worksheet workSheet) { wb.SaveAs(@"C:\users\user\Desktop\수강신청시간표", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(Type.Missing, Type.Missing, Type.Missing); excelApp.Quit(); releaseObject(excelApp); releaseObject(workSheet); releaseObject(wb); }
public static void CloseWorkingWorkbook(Excel.Workbook workbook) { if (workbook != null) { if (!workbook.Saved) workbook.Save(); workbook.Close(); ExcelUtilies.ReleaseComObject(workbook); } }
public void WriteData() { string fp = ChooseFilePath(); // Uses function below to find file path Excel excel = new Excel(fp, 1); // Creates a new instance of the Excel class excel.WriteToCell(6, 6, "BOOM"); excel.SaveAs(@"BOOM3.xlsx"); //The @ symbol adds the file path This PC/ My Documents excel.Close(); addListItem("File with new content added saved in ThisPC/My Documents/ BOOM3.xlsx"); //Message saying what has happened }
public static void SaveExcel(string filePath, Excel.Workbook workbook) { try { object misValue = System.Reflection.Missing.Value; workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); } catch (Exception ex) { throw ex; } }
public static void closeWorkBook(Excel.Workbook wb) { wb.Close(false, Type.Missing, Type.Missing); }
/// <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; }
public static void CloseWB(Excel.Workbook xlWb) { xlWb.Close(false, false, System.Type.Missing); }
/// <summary> /// Close Excel workbook, quit excel and wait until finished /// </summary> /// <param name="app">Excel handle to quit</param> /// <param name="wb">Excel workbook to close</param> private static void ExcelQuit(Excel.Application app, Excel.Workbook wb) { wb.Close(SaveChanges: false); app.Quit(); while (Marshal.ReleaseComObject(app) != 0) { } app = null; GC.Collect(); GC.WaitForPendingFinalizers(); }
public static void CloseWorkbook(Excel.Workbook workbook) { workbook.Saved = true; // disable any prompts to save the data workbook.Close(); _openWorkbooks.Remove(workbook); }
public static void SaveTempWorkbook(Excel.Workbook tempWorkbook) { if (tempWorkbook != null) { //string folder = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Remove(System.Reflection.Assembly.GetExecutingAssembly().CodeBase.LastIndexOf("/")); //folder = folder.Replace(@"file:///", ""); //if (!Directory.Exists(folder + "/temp")) // Directory.CreateDirectory(folder + "/temp"); //tempWorkbook.SaveAs(folder + "/temp/book1.xls"); tempWorkbook.SaveAs("book1.xls"); string fullname = tempWorkbook.FullName; tempWorkbook.Close(); if (File.Exists(fullname)) File.Delete(fullname); ExcelUtilies.ReleaseComObject(tempWorkbook); } }
private void SaveExcel(string file, MSExcel.Application excel, MSExcel.Workbook wbook) { try { File.Delete(file); } catch //(Exception ex) { wbook.Close(false); excel.Quit(); return; } try { wbook.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, MSExcel.XlSaveAsAccessMode.xlExclusive); } catch//(Exception ex) { } wbook.Close(false); excel.Quit(); }