贯彻对EXCEL钦赐单元格实行操作

By admin in 4858.com on 2019年4月13日
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;
        }
    }
}

4858.com,贯彻对EXCEL钦赐单元格实行操作。  

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 美高梅手机版4858 版权所有