/// <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();
}