示例#1
0
        public async Task SheetToValueAsync()
        {
            FunCtion FunC = new FunCtion();
            await Task.Run(() => {
                Excel.Application ExcelApp = Globals.ThisAddIn.Application;
                Excel.Workbook WBK         = ExcelApp.ActiveWorkbook;
                Excel.Worksheet wst;
                Excel.Range Rg;
                int AllRows;
                int AllColumns;

                double OneStep = Math.Round(1 / double.Parse(WBK.Worksheets.Count.ToString()) * 100, 6);//WBK.Worksheets.Count;
                double i       = 0;

                //遍历工作表
                var SheetE = WBK.Worksheets.GetEnumerator();
                while (SheetE.MoveNext())
                {
                    wst                = (Excel.Worksheet)SheetE.Current;
                    AllRows            = Math.Max(FunC.AllRows(wst, "A", 13), 2);
                    AllColumns         = Math.Max(FunC.AllColumns(wst, 1, 13), 2);
                    Rg                 = wst.Range[String.Format("A1:{0}{1}", FunC.CName(AllColumns), AllRows)];
                    Rg.Value2          = Rg.Value2;
                    ExcelApp.StatusBar = "已完成 " + Math.Round(i += OneStep, 2) + "%";
                }

                ExcelApp.StatusBar = false;
            });
        }
示例#2
0
        private void ConfirmBtn_Click(object sender, EventArgs e)
        {
            string TempStr = comboBox.Text;

            if (TempStr == null)
            {
                return;
            }
            if (TempStr.Length < 1)
            {
                return;
            }

            //如果是选择的下拉框,拆分内容
            if (TempStr.Contains(": "))
            {
                TempStr = TempStr.Split(':')[1].Trim();
            }
            if (TempStr == null)
            {
                return;
            }
            if (TempStr.Length < 1)
            {
                return;
            }

            try
            {
                Regex regex = new Regex(TempStr);
            }
            catch
            {
                MessageBox.Show("输入内容不符合正则表达式规范,请检查!");
                return;
            }

            ExcelApp = Globals.ThisAddIn.Application;
            WST      = (Excel.Worksheet)ExcelApp.ActiveSheet;

            //读取选中区域
            Excel.Range rg;
            try
            {
                rg = ExcelApp.Selection;
            }
            catch
            {
                return;
            }

            //如果只选中一个单元格
            if (rg.Count == 1)
            {
                if (rg.Value2 != null)
                {
                    if (ChangeBtn.Text == "Replace")
                    {
                        rg.Value2 = Regex.Replace(FunC.TS(rg.Value2), TempStr, "");
                    }
                    else
                    {
                        rg.Value2 = FunC.TS(Regex.Match(FunC.TS(rg.Value2), TempStr));
                    }
                }
                return;
            }

            //如果选中了一个区域
            int AllRows;
            int AllColumns;

            object[,] ORGv; //原始数组ORGv 读取值
            object[,] NRG;  //新数组NRG

            ORGv = rg.Value2;

            //限制列数,防止选择整行时多余的计算
            AllColumns = FunC.AllColumns(rg.Row, FunC.AllRows(FunC.CName(rg.Column)) + 10) - rg.Column + 1;//坑
            AllColumns = Math.Min(AllColumns, ORGv.GetLength(1));
            AllColumns = Math.Max(1, AllColumns);

            //限制行数

            AllRows = FunC.AllRows(FunC.CName(rg.Column), AllColumns) - rg.Row + 1;
            AllRows = Math.Min(AllRows, ORGv.GetLength(0));
            AllRows = Math.Max(1, AllRows);

            //定义新数组
            NRG = new object[AllRows, AllColumns];

            for (int i = 1; i <= AllColumns; i++)
            {
                for (int i1 = 1; i1 <= AllRows; i1++)
                {
                    if (ORGv[i1, i] != null)
                    {
                        if (ChangeBtn.Text == "Replace")
                        {
                            NRG[i1 - 1, i - 1] = Regex.Replace(FunC.TS(ORGv[i1, i]), TempStr, "");
                        }
                        else
                        {
                            NRG[i1 - 1, i - 1] = FunC.TS(Regex.Match(FunC.TS(ORGv[i1, i]), TempStr));
                        }
                    }
                }
            }

            //赋值
            WST.Range[FunC.CName(rg.Column) + rg.Row + ":" + FunC.CName(rg.Column + AllColumns - 1) + (rg.Row + AllRows - 1)].Value2 = NRG;

            ORGv = null;
            NRG  = null;
            this.Close();
        }