修改EXCEL并保存

原创 2012年03月28日 17:03:07

        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
        }

 

修改后无法保存的excel文档

  • 2011年11月22日 10:38
  • 24KB
  • 下载

jsp嵌入EXCEL可修改保存至服务器

  • 2011年06月30日 00:42
  • 2.89MB
  • 下载

用C#语言实现读取修改保存EXCEL表格

using System; using System.Collections.Generic; using System.Linq; using System.Text; using org....

EXCEL打开修改后重新保存

  • 2013年02月04日 09:50
  • 39KB
  • 下载

excel提示磁盘空间不足磁盘已满无法打开或保存

办公软件是Office2013,打开Excel文件时可能会出现“磁盘空间或内存不足,无法打开或保存”这样的提示,但实际的磁盘空间和内存非常富余。我自己也遇到了这样的问题,尝试过右键→属性→解除锁定等几...

HTML:C语言打印excel表格并在HTML上点击保存

=================================================================================/******C语言打印EXCEL表格...
  • xxgxgx
  • xxgxgx
  • 2015年10月20日 08:46
  • 329
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:修改EXCEL并保存
举报原因:
原因补充:

(最多只允许输入30个字)