关闭

修改EXCEL并保存

标签: excelstringregexnullkillbutton
527人阅读 评论(0) 收藏 举报

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Title = "请选择文件";
            fileDialog.Filter = "excel03文件(*.xls)|*.xls";
            fileDialog.FilterIndex = 1;
            fileDialog.RestoreDirectory = true;

            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                Microsoft.Office.Interop.Excel.Application xlsapp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook xlswkbk = default(Microsoft.Office.Interop.Excel.Workbook);
                xlswkbk = xlsapp.Workbooks.Open(fileDialog.FileName, Missing.Value,false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Microsoft.Office.Interop.Excel.Worksheet xlsst = default(Microsoft.Office.Interop.Excel.Worksheet);
                for (int i = 1; i <= xlswkbk.Sheets.Count;i++ )
                {
                    xlsst = (Microsoft.Office.Interop.Excel.Worksheet)xlswkbk.Sheets[i];
                    int intLastColNum = xlsst.UsedRange.Columns.Count;
                    int intLastRowNum = xlsst.UsedRange.Rows.Count;
                    for (int j = 2; j <= intLastRowNum; j++)
                    {
                        //获取第一列数据
                        string fstMan = ((Microsoft.Office.Interop.Excel.Range)xlsst.Cells[j, 1]).Text.ToString();
                        //string scdMan = ((Microsoft.Office.Interop.Excel.Range)xlsst.Cells[j, 31]).Text.ToString();
                        StrFomat(fstMan, xlsst, j, 1);
                        //StrFomat(scdMan, xlsst, j,31);

                    }
                }
                xlswkbk.Save();
                MessageBox.Show("格式化成功!");
                xlswkbk.Close(null, null, null);
                xlsapp.Workbooks.Close();
                xlsapp.Application.Quit();
                xlsapp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsst);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlswkbk);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsapp);
                Kill(xlsapp);
                GC.Collect();//强行销毁

            }

 

        /// <summary>
        /// 字符串格式化
        /// </summary>
        /// <param name="contctMan">初始字符串</param>
        /// <param name="xlsst">工作sheet</param>
        /// <param name="row">行</param>
        /// <param name="col">列</param>
        private void StrFomat(string contctMan, Worksheet xlsst,int row,int col)
        {
            if (string.IsNullOrEmpty(contctMan) == false)
            {
                Match result = Regex.Match(contctMan, @"[\u4e00-\u9fa5]+", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                if (result.Success)
                {
                    string name = result.Value;
                    contctMan = contctMan.Replace(name, "").ToString().Trim();
                    if (contctMan.Length > 0)
                    {
                        //根据一个或多个空格分割字符串
                        string[] contctWay = Regex.Split(contctMan, "\\s{1,}");
                        string tel = "";
                        string phone = "";
                        Regex regphone = new Regex(@"^1[3458]\d{9}$", RegexOptions.IgnoreCase);
                        switch (contctWay.Length)
                        {
                            case 1:
                                if (contctWay[0].Contains('-') == false && regphone.IsMatch(contctWay[0].ToString()))
                                {
                                    phone = contctWay[0].ToString();
                                }
                                else
                                {
                                    tel = contctWay[0].ToString();
                                }
                                break;
                            case 2:
                                if (contctWay[0].Contains('-') == false && regphone.IsMatch(contctWay[0].ToString()))
                                {
                                    phone = contctWay[0].ToString();
                                    tel = contctWay[1].ToString();
                                }
                                else
                                {
                                    tel = contctWay[0].ToString();
                                    phone = contctWay[1].ToString();
                                }
                                break;
                        }
                        xlsst.Cells[row, col] = name;
                        xlsst.Cells[row, col + 1] = tel;
                        xlsst.Cells[row, col + 2] = phone;
                    }

                }
                else
                {
                    contctMan = "无" + contctMan;
                    StrFomat(contctMan, xlsst, row, col);
                }
            }
        }

        /// <summary>
        /// 杀死当前格式化调用的Excel进程
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄,具体作用是得到这块内存入口 
            int k = 0;
            GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
            p.Kill(); //关闭进程k
        }

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:562844次
    • 积分:5768
    • 等级:
    • 排名:第4448名
    • 原创:64篇
    • 转载:177篇
    • 译文:0篇
    • 评论:49条
    最新评论