MES_Client/TGMES/Core.StlMes.Client.Plan.Order/PlanComm/ExclReportHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
using System.Collections;
using System.Windows.Forms;
using System.Diagnostics;

namespace Core.StlMes.Client.Plan.Order
{
    public partial class ExclReportHelper
    {
        #region excel报表界面的基础方法
        public Microsoft.Office.Interop.Excel.Application excel = null;
        public Object refmissing = System.Reflection.Missing.Value;
        public Workbook mybook = null;
        public Worksheet mySheet = null;
        public ArrayList pids = new ArrayList();

        //修改注册表，解决嵌入excel弹出问题
        public struct regkey
        {
            public RegistryKey rootKey;
            public string sKey;
            public string sItem;
            public int val;
            public bool isExist;
        }
        public regkey[] aryKeys = new regkey[5];

        public void RegSet()
        {
            try
            {
                //RegistryPermission f = new RegistryPermission(RegistryPermissionAccess.Read | RegistryPermissionAccess.Write,
                //    "HKEY_LOCAL_MACHINE\\SOFTWARE\\Classes");
                //f.AddPathList(RegistryPermissionAccess.Read | RegistryPermissionAccess.Write, "HKEY_CLASSES_ROOT");

                RegistryKey rKey = Registry.LocalMachine;
                aryKeys[0].rootKey = rKey;
                aryKeys[0].sKey = "SOFTWARE\\Classes\\Excel.Sheet.12";
                aryKeys[0].isExist = false;
                aryKeys[0].sItem = "BrowserFlags";
                RegistryKey subKey = rKey.OpenSubKey(aryKeys[0].sKey, true);
                if (subKey != null)
                {
                    if (subKey.GetValue(aryKeys[0].sItem, null) != null)
                    {
                        aryKeys[0].val = (int)subKey.GetValue(aryKeys[0].sItem);
                        aryKeys[0].isExist = true;
                        subKey.SetValue(aryKeys[0].sItem, -(0xFFFFFFFF - 0x80000A00 + 1), RegistryValueKind.DWord);
                        subKey.Close();


                        aryKeys[1].rootKey = rKey;
                        aryKeys[1].sKey = "SOFTWARE\\Classes\\Excel.Sheet.8";
                        aryKeys[1].isExist = false;
                        aryKeys[1].sItem = "BrowserFlags";
                        subKey = rKey.OpenSubKey(aryKeys[1].sKey, true);
                        if (subKey != null)
                        {
                            aryKeys[1].val = (int)subKey.GetValue(aryKeys[1].sItem);
                            aryKeys[1].isExist = true;
                            subKey.SetValue(aryKeys[1].sItem, -(0xFFFFFFFF - 0x80000A00 + 1), RegistryValueKind.DWord);//,
                            subKey.Close();
                        }

                        aryKeys[2].rootKey = rKey;
                        aryKeys[2].sKey = "SOFTWARE\\Classes\\Excel.SheetMacroEnabled.12";
                        aryKeys[2].isExist = false;
                        aryKeys[2].sItem = "BrowserFlags";
                        subKey = rKey.OpenSubKey(aryKeys[2].sKey, true);
                        if (subKey != null)
                        {
                            aryKeys[2].val = (int)subKey.GetValue(aryKeys[2].sItem);
                            aryKeys[2].isExist = true;
                            subKey.SetValue(aryKeys[2].sItem, -(0xFFFFFFFF - 0x80000A00 + 1), RegistryValueKind.DWord);
                            subKey.Close();
                        }

                        aryKeys[3].rootKey = rKey;
                        aryKeys[3].sKey = "SOFTWARE\\Classes\\Excel.SheetBinaryMacroEnabled.12";
                        aryKeys[3].isExist = false;
                        aryKeys[3].sItem = "BrowserFlags";
                        subKey = rKey.OpenSubKey(aryKeys[3].sKey, true);
                        if (subKey != null)
                        {
                            aryKeys[3].val = (int)subKey.GetValue(aryKeys[3].sItem);
                            aryKeys[3].isExist = true;
                            subKey.SetValue(aryKeys[3].sItem, -(0xFFFFFFFF - 0x80000A00 + 1), RegistryValueKind.DWord);
                            subKey.Close();
                        }
                    }
                }
                rKey.Close();

                rKey = Registry.ClassesRoot;
                aryKeys[4].rootKey = rKey;
                aryKeys[4].sKey = "Excel.Sheet.8";
                aryKeys[4].isExist = false;
                aryKeys[4].sItem = "EditFlags";
                subKey = rKey.OpenSubKey(aryKeys[4].sKey, true);
                if (subKey != null)
                {
                    if (!(subKey.GetValue(aryKeys[4].sItem) is Array))
                        aryKeys[4].val = (int)subKey.GetValue(aryKeys[4].sItem);
                    else
                        aryKeys[4].val = BitConverter.ToInt32((byte[])subKey.GetValue(aryKeys[4].sItem), 0);

                    aryKeys[4].isExist = true;
                    subKey.SetValue(aryKeys[4].sItem, 0x00010000, RegistryValueKind.DWord);
                    subKey.Close();
                }
                rKey.Close();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message, "提示");
            }
        }
        //注册表改回
        public void RegBack()
        {
            try
            {
                RegistryKey rKey, subKey;
                //if (aryKeys[0].isExist)
                //{
                //    rKey = Registry.LocalMachine;
                //    subKey = rKey.OpenSubKey(aryKeys[0].sKey, true);
                //}
                for (int i = 0; i < aryKeys.Length; i++)
                {
                    if (aryKeys[i].isExist)
                    {
                        rKey = aryKeys[i].rootKey;
                        subKey = rKey.OpenSubKey(aryKeys[i].sKey, true);
                        subKey.SetValue(aryKeys[i].sItem, aryKeys[i].val, RegistryValueKind.DWord);
                        subKey.Close();
                        rKey.Close();
                    }
                }
            }
            catch { }
        }


        public void RecrodPIDs()
        {
            //在创建进程前将所有excel进程记录下来，结束后将增加的excel进程kill
            pids.Clear();
            foreach (Process process in System.Diagnostics.Process.GetProcesses())
            {
                if (process.ProcessName.ToUpper().Equals("EXCEL"))
                {
                    pids.Add(process.Id);
                }
            }
        }

        public void KillExcelProcess()
        {
            foreach (Process process in System.Diagnostics.Process.GetProcesses())
            {
                if (process.ProcessName.ToUpper().Equals("EXCEL"))
                {
                    if (!pids.Contains(process.Id))
                    {
                        process.Kill();
                    }
                }
            }
        }

        public void KillExcelProcess(Process process)
        {
            if (System.Diagnostics.Process.GetProcesses().Contains(process))
            {
                if (process.ProcessName.ToUpper().Equals("EXCEL"))
                {
                    process.Kill();
                }
            }
        }

        /// <summary>
        /// 设置单元格数据
        /// </summary>
        /// <param name="cellPosition"></param>
        /// <param name="value"></param>
        public void SetCellRangeValue(object cellPosition, string value)
        {
            string[] ranges = GetRange(cellPosition.ToString(), cellPosition.ToString());
            try
            {
                mySheet.get_Range(ranges[0], ranges[0]).Value2 = value;
                //mySheet.get_Range(ranges[0], ranges[0]).HorizontalAlignment = XlHAlign.xlHAlignRight;
            }
            catch
            { }
        }

        public void SetExcelRowHeight(int RowSum)
        {
            ((Microsoft.Office.Interop.Excel.Range)mySheet.Rows["3:" + RowSum + 3, System.Type.Missing]).RowHeight = 20;
        }

        public void SetExcelBorderStyle(object cellPosition, string value)
        {
            string[] ranges = GetRange(cellPosition.ToString(), cellPosition.ToString());
            try
            {
                mySheet.get_Range(ranges[0], ranges[0]).Borders.LineStyle = XlLineStyle.xlContinuous;
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        /// <summary>
        /// 根据行数和列数（列数为字母，并且行号和列号放在一起）取出对应的数字数组
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public int[] GetPosition(string str)
        {
            char[] chars = str.ToUpper().ToCharArray();
            int[] position = new int[2];
            string column = "";
            string row = "";
            for (int i = 0; i < chars.Length; i++)
            {
                if ('A' <= chars[i] && chars[i] <= 'Z')
                    column += chars[i].ToString();
                if ('0' <= chars[i] && chars[i] <= '9')
                    row += chars[i].ToString();
            }
            position[0] = ReplaceString(column);
            position[1] = int.Parse(row);

            return position;
        }


        /// <summary>
        /// 根据excel中的两点，取出这两点范围的位置。如"A1,A2"，以逗号分隔
        /// </summary>
        /// <param name="fromPosition"></param>
        /// <param name="toPosition"></param>
        /// <returns></returns>
        public string[] GetRange(string fromPosition, string toPosition)
        {
            int[] _formPosition = GetPosition(fromPosition);
            int[] _toPosition = GetPosition(toPosition);
            if (_formPosition[0] > _toPosition[0])
            {
                int temp = _toPosition[0];
                _toPosition[0] = _formPosition[0];
                _formPosition[0] = temp;
            }
            if (_formPosition[1] > _toPosition[1])
            {
                int temp = _toPosition[1];
                _toPosition[1] = _formPosition[1];
                _formPosition[1] = temp;
            }

            string result = "";
            for (int i = _formPosition[0]; i <= _toPosition[0]; i++)
            {
                for (int j = _formPosition[1]; j <= _toPosition[1]; j++)
                {
                    result += ReplaceNumber(i) + j.ToString() + ",";
                }
            }
            if (result.EndsWith(","))
                result = result.Substring(0, result.Length - 1);
            return result.Split(',');
        }

        /// <summary>
        /// 把在excel中的字母列转换成相应的数字
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public string ReplaceNumber(int index)
        {
            int i = index;
            int j = 0;
            if (index > 26)
            {
                i = index % 26;
                j = index / 26;
            }
            if (j == 0)
                return ((char)((int)'A' + i - 1)).ToString();
            else
                return ((char)((int)'A' + j - 1)).ToString() + ((char)((int)'A' + i - 1)).ToString();
        }

        /// <summary>
        /// 与ReplaceNumber相反
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public int ReplaceString(string str)
        {
            str = str.ToUpper();
            char[] chars = str.ToCharArray();
            int index = 0;
            if (chars.Length == 1)
                index = (int)chars[0] - (int)'A' + 1;
            else if (chars.Length == 2)
            {
                index = (int)chars[1] - (int)'A' + 1;
                index += ((int)chars[0] - (int)'A' + 1) * 26;
            }
            return index;
        }

        /// <summary>
        /// 根据行号和列号取值
        /// </summary>
        /// <param name="position">如:B6</param>
        /// <returns></returns>
        public object GetCellValue(string position)
        {
            object obj = null;
            try
            {
                //obj = mySheet.get_Range(position, position).Value2;
                //obj = mySheet.get_Range("A1", Type.Missing).Value2;
                obj = mySheet.get_Range(position, Type.Missing).Value2;

            }
            catch (Exception exp)
            {
                throw exp;
            }
            return obj;
        }

        /// <summary>
        /// 取出范围内的单元格值
        /// </summary>
        /// <param name="fromPosition"></param>
        /// <param name="toPosition"></param>
        /// <returns></returns>
        public object[] GetCellRangeValue(string fromPosition, string toPosition)
        {
            string[] ranges = GetRange(fromPosition, toPosition);
            object[] obj = new object[ranges.Length];
            for (int i = 0; i < obj.Length; i++)
            {
                obj[i] = GetCellValue(ranges[i]);
            }
            return obj;
        }

        #endregion

        /// <summary>
        /// 根据excel中的两点，取出这两点范围的位置。如"A1,A2"，以逗号分隔
        /// </summary>
        /// <param name="fromPosition"></param>
        /// <param name="toPosition"></param>
        /// <returns></returns>
        public string[,] GetRange2(string fromPosition, string toPosition)
        {
            int[] _formPosition = GetPosition(fromPosition);
            int[] _toPosition = GetPosition(toPosition);
            if (_formPosition[0] > _toPosition[0])
            {
                int temp = _toPosition[0];
                _toPosition[0] = _formPosition[0];
                _formPosition[0] = temp;
            }
            if (_formPosition[1] > _toPosition[1])
            {
                int temp = _toPosition[1];
                _toPosition[1] = _formPosition[1];
                _formPosition[1] = temp;
            }

            int colum = _toPosition[0] - _formPosition[0] + 1;
            int row = _toPosition[1] - _formPosition[1] + 1;
            string[,] result = new string[row,colum];

            for (int i = _formPosition[0]; i <= _toPosition[0]; i++)
            {
                for (int j = _formPosition[1]; j <= _toPosition[1]; j++)
                {
                    result[(j - _formPosition[1]), (i - _formPosition[0])] = ReplaceNumber(i) + j.ToString();
                }
            }
            return result;
        }

        /// <summary>
        /// 取出范围内的单元格值
        /// </summary>
        /// <param name="fromPosition"></param>
        /// <param name="toPosition"></param>
        /// <returns></returns>
        public string[,] GetCellRangeValue2(string fromPosition, string toPosition)
        {
            try
            {
                object[,] obj = (object[,])mySheet.get_Range(fromPosition, toPosition).Value2;
                int rows = obj.GetLength(0);   //获取m_objRangeArrayValue的行数
                int columns = obj.GetLength(1);  //获取m_objRangeArrayValue的列数
                object[,] param = new object[rows, columns];   //转化为起始为0的数组

                Array.Copy(obj, param, rows * columns);  //临时结果，学习用

                string[,] str = new string[param.GetLength(0), param.GetLength(1)];

                for (int i = 0; i < str.GetLength(0); i++)
                {
                    for (int j = 0; j < str.GetLength(1); j++)
                    {
                        if (param[i, j] == null)
                        {
                            str[i, j] = "";
                        }
                        else
                        {
                            str[i, j] = param[i, j].ToString();
                        }
                    }
                }
                return str;
            }
            catch
            {
                return null;
            }
        }
    }
}