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();
}
private void Confirm_Click(object sender, EventArgs e)
{
Excel.Application ExcelApp = Globals.ThisAddIn.Application;
Excel.Workbook WBK = ExcelApp.ActiveWorkbook;
Excel.Worksheet WST = ExcelApp.ActiveSheet;
Excel.Worksheet wst;
//存储行数到我的文档
string strPath = Environment.GetFolderPath(Environment.SpecialFolder.CommonDocuments);
ClsThisAddinConfig clsConfig = new ClsThisAddinConfig(strPath);
clsConfig.WriteConfig("WorkSheet", "HeadRows", NumUpDown.Value.ToString());
//合并表名
string SheetNameStr = "合并表";
if (FunC.SheetExist(SheetNameStr))
{
for (int i = 1; i < 11; i++)
{
if (!FunC.SheetExist(SheetNameStr + i))
{
SheetNameStr = SheetNameStr + i;
break;
}
}
if (SheetNameStr == "合并表")
{
MessageBox.Show("已存在多个“合并表”表，请删除或重命名后再试");
return;
}
}
ExcelApp.ScreenUpdating = false;
//添加新表
WST = (Excel.Worksheet)ExcelApp.ActiveWorkbook.Worksheets.Add(ExcelApp.ActiveWorkbook.Worksheets[1]);
WST.Name = SheetNameStr;
//被合并表开始行数
int StartRows = FunC.TI(NumUpDown.Value) + 1;
int AllColumns;
int AllRows;
int NewColumns;
int MaxRows = WST.Rows.Count;
//合并表行数
int UnionRows = StartRows;
//创建数组
object[,] ORG;
//读取表头
if (StartRows != 1)
{
for (int i = 0; i < ListBox.Items.Count; i++)
{
if (ListBox.GetItemChecked(i))
{
AllColumns = 2;
AllRows = 0;
wst = WBK.Worksheets[ListBox.GetItemText(ListBox.Items[i])];
//获取列数
for (int i1 = 1; i1 <= StartRows; i1++)
{
NewColumns = ((Excel.Range)(wst.Cells[i1, "IV"])).End[Excel.XlDirection.xlToLeft].Column;
AllColumns = Math.Max(AllColumns, NewColumns);
}
//获取行数
for (int i1 = 1; i1 <= 10; i1++)
{
NewColumns = ((Excel.Range)(wst.Cells[wst.Rows.Count, i1])).End[Excel.XlDirection.xlUp].Row;
AllRows = Math.Max(AllRows, NewColumns);
}
if (AllRows < 2)
{
continue;
}
//读到数组并存储到合并表
ORG = wst.Range["A1:" + FunC.CName(AllColumns) + (StartRows - 1)].Value2;
WST.Range["B1:" + FunC.CName(AllColumns + 1) + (StartRows - 1)].Value2 = ORG;
WST.Range["A1"].Value2 = "工作表名";
ORG = null;
break;
}
}
}
//读取表内容
for (int i = 0; i < ListBox.Items.Count; i++)
{
if (ListBox.GetItemChecked(i))
{
AllColumns = 2;
AllRows = 0;
wst = WBK.Worksheets[ListBox.GetItemText(ListBox.Items[i])];
//获取列数
for (int i1 = 1; i1 <= StartRows; i1++)
{
NewColumns = ((Excel.Range)(wst.Cells[i1, "IV"])).End[Excel.XlDirection.xlToLeft].Column;
AllColumns = Math.Max(AllColumns, NewColumns);
}
//获取行数
for (int i1 = 1; i1 <= 10; i1++)
{
NewColumns = ((Excel.Range)(wst.Cells[wst.Rows.Count, i1])).End[Excel.XlDirection.xlUp].Row;
AllRows = Math.Max(AllRows, NewColumns);
}
if (AllRows + UnionRows >= MaxRows)
{
ExcelApp.ScreenUpdating = true;
//关闭窗体
this.Close();
MessageBox.Show("表格行数不够，请检查！");
return;
}
//读到内容并存储到合并表
ORG = wst.Range["A" + StartRows + ":" + FunC.CName(AllColumns) + AllRows].Value2;
WST.Range["B" + UnionRows + ":" + FunC.CName(AllColumns + 1) + (UnionRows + AllRows - StartRows)].Value2 = ORG;
WST.Range["A" + UnionRows + ":A" + (UnionRows + AllRows - StartRows)].Value2 = wst.Name;
UnionRows = UnionRows + AllRows - StartRows + 1;
ORG = null;
}
}
ExcelApp.ScreenUpdating = true;
this.Close();
MessageBox.Show("合并完成！");
}
