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;
});
}
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();
}
