using System; using System.Collections.Generic; using System.Text; using Microsoft.Office.Interop.Excel; using System.IO; using System.Reflection; using System.Runtime.InteropServices; using Microsoft.Office.Core; namespace HustCAD.IntePLM.Win.BatchEnterWinUI { public class SighExcel { #region DllImport Methods [System.Runtime.InteropServices.DllImport( "User32.dll" , CharSet = System.Runtime.InteropServices.CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); #endregion Microsoft.Office.Interop.Excel.ApplicationClass application = null ; Microsoft.Office.Interop.Excel.Workbook workBook = null ; Microsoft.Office.Interop.Excel.Worksheet wSheet = null ; /// <summary> /// 对EXCEL指定单元格进行操作 /// </summary> /// <param name="filePath">EXCEL表格所在路径</param> /// <param name="row">行号</param> /// <param name="column">列号</param> /// <param name="code">内容</param> /// <returns></returns> public bool signExcel( string filePath, int row, int column, string code) { System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo( "en-US" ); object missing = Type.Missing; try { try { application = new Microsoft.Office.Interop.Excel.ApplicationClass(); } catch (Exception e) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! ,错误:" + e.Message.ToString() + "。 " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); } if (application == null ) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); } application.AlertBeforeOverwriting = false ; application.AskToUpdateLinks = false ; application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow; application.DisplayAlerts = false ; workBook = application.Workbooks.Open(filePath, missing, false , missing, missing, missing, true , missing, missing, missing, missing, missing, missing, missing, missing); workBook.CheckCompatibility = false ; //兼容性检查 workBook.DoNotPromptForConvert = true ; wSheet = (Worksheet)workBook.Worksheets[1]; wSheet.Cells[row, column] = code; Microsoft.Office.Interop.Excel.Range rtemp = wSheet.get_Range(wSheet.Cells[3, 4], wSheet.Cells[3, 6]); rtemp.Font.Name = "宋体" ; rtemp.Font.Size = 12; object RouteWorkbook = false ; object SaveChanges = XlSaveAction.xlSaveChanges; //wBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workBook.Save(); workBook.Close(SaveChanges, filePath, RouteWorkbook); return true ; } catch (Exception e) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! ,错误:" + e.Message.ToString() + "。 " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); return false ; } finally { if (workBook != null ) { workBook.Close( true , missing, missing); workBook = null ; } if (application != null ) { application.Quit(); KillSpecialExcel(application); application = null ; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } /// <summary> /// Kill Special Excel Process /// </summary> public static void KillSpecialExcel(Application m_objExcel) { try { if (m_objExcel != null ) { int lpdwProcessId; GetWindowThreadProcessId( new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } } catch (Exception) {} } public string getCellValue( string filePath, int row, int column) { System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo( "en-US" ); object missing = Type.Missing; try { try { application = new Microsoft.Office.Interop.Excel.ApplicationClass(); } catch (Exception e) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! ,错误:" + e.Message.ToString() + "。 " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); } if (application == null ) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); } application.AlertBeforeOverwriting = false ; application.AskToUpdateLinks = false ; application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow; application.DisplayAlerts = false ; workBook = application.Workbooks.Open(filePath, missing, false , missing, missing, missing, true , missing, missing, missing, missing, missing, missing, missing, missing); workBook.CheckCompatibility = false ; //兼容性检查 workBook.DoNotPromptForConvert = true ; wSheet = (Worksheet)workBook.Worksheets[1]; return ((Microsoft.Office.Interop.Excel.Range)wSheet.Cells[row, column]).Text.ToString().Trim(); } catch (Exception e) { System.IO.File.AppendAllText(System.IO.Path.Combine(GetCurrentPath(), "signExcellog.txt" ), "文件" + filePath + "签入编号失败! ,错误:" + e.Message.ToString() + "。 " + DateTime.Now.ToString( "yyyy-MM-dd-hh:mm:ss" )); return "" ; } finally { if (workBook != null ) { workBook.Close( true , missing, missing); workBook = null ; } if (application != null ) { application.Quit(); KillSpecialExcel(application); application = null ; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } } /// <summary> /// 得到当前程序的路径 /// </summary> /// <returns></returns> static public string GetCurrentPath() { string asstring = Assembly.GetExecutingAssembly().Location; string [] aa = asstring.Split( '\\' ); string path = string .Empty; foreach ( string var in aa) { if ( var != aa[aa.Length - 1]) path += var + @"\" ; } return path; } } } |