using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Diagnostics;
using Microsoft.Vbe.Interop;
using System.Threading;
namespace zizhuTools
{
public partial class cfgzb : Form
{
Excel.Application EApp;
int btnum;//表头行数
int bwnum;//表尾行数
Excel.Worksheet sht;//工作表
//获得工作表的使用区域
Excel.Range rng_used;//工作表已使用单元格范围
int rowsc;//总行数
int cols; //总列数
Excel.Range rng_bt;//表头单元格
List<string> listA_str = new List<string> { };//表头的字段
List<string> listB_str = new List<string> { };//表头的字段
List<string> listC_str = new List<string> { };//表头的字段
Excel.Workbook wb;
Stopwatch sw = new Stopwatch();
public cfgzb()
{
InitializeComponent();
}
System.Timers.Timer timer1 = new System.Timers.Timer(15000);
public void theout(object source, System.Timers.ElapsedEventArgs e)
{
timer1.Stop();
timer1.Enabled = false;
MessageBox.Show("程序可能已经奔溃,终止运行", "运行速度异常");
// System.Windows.Forms.Application.Exit();
System.Environment.Exit(0);
//this.Invoke(new stop_form(stop_method));
}
//public delegate void stop_form();
//public void stop_method()
//{
// this.Close();
// this.Dispose();
//}
private void cfgzb_Load(object sender, EventArgs e)
{
EApp = Globals.ThisAddIn.Application; //获得控制权
timer1.Elapsed += new System.Timers.ElapsedEventHandler(theout);
timer1.AutoReset = true;
//EApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityByUI;
wb = EApp.ActiveWorkbook;
sht = EApp.ActiveSheet;
zdy_tb.Text = DateTime.Now.Date.ToString("yyyyMMdd");
rng_used = sht.Range["A2"].CurrentRegion;//获得工作表的使用区域
rowsc = rng_used.Rows.Count;//总行数
cols = rng_used.Columns.Count;//总列数
ldgjz();
// test1 = houtai;
// EApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;
//------------------------------------------------------------------
}
string jsgs;//计算公式的TEXT
int rowc_a;//表尾的起始行 表尾为1，总行数为14行，起始行为第14行
int data_rows;//数据行数范围 比如A1:A14,表头2行，表尾2行，数据范围是A2:A12
int gjz_a;//关键字1的所在列 A
int gjz_b;//关键字2的所在列 B
string zdcol;//指定计算列
Excel.Range rng_a;//关键字1的单元格列A2:A13
Excel.Range rng_b;//关键字2的单元格列B2:B13
Excel.Range rng_c;//黏贴条件的单元格 H1
Excel.Worksheet sht_use;//新工作表[拆分为表]
Excel.Workbook wb_new;//新工作簿[拆分为簿]
int newShtCount; //筛选条件的行数确定要新增的表格数 9
int shtc;//总表格item数 3
int rows_rng;//新表的空白行
string fa_str;
string new_shtname;
string save_path;
int col;
int col2;
string ja;
string jb;
public delegate void test();
bool bvbc = false;
//public int trustMacro()
//{
// string sVersion = EApp.Version;
// string sKey = "HKEY_CURRENT_USER\\Software\\Microsoft\\Office\\" + sVersion + "\\Excel\\Security\\";
// string sValue = "AccessVBOM";
// WshShell wsc = new WshShell();
// wsc.RegWrite(sKey + "\\" + sValue, 1, "REG_DWORD");
// int s = wsc.RegRead(sKey + "\\" + sValue);
// //wsc.SendKeys("%TMS%T%V{ENTER}", Type.Missing);
// return s;
//}
private void button2_Click(object sender, EventArgs e)
{
this.Text = "拆分工作表(此功能响应超时将会提示)(程序运行中)";
sw.Start();
EApp.ScreenUpdating = false;
EApp.DisplayAlerts = false;
rng_used.Copy();
wb.Worksheets.Add(wb.Worksheets[1], Type.Missing, 1);
sht = wb.Worksheets[1];
sht.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAll);
btnum = int.Parse(btnum_dud.Value.ToString());
bwnum = int.Parse(bwnum_dud.Value.ToString());
rowsc = sht.Range["A1"].CurrentRegion.Rows.Count;//总行数
cols = sht.Range["A1"].CurrentRegion.Columns.Count;//总列数
if (gjzl_rb.Checked && gjz_cb.Visible)
{
two_crt();
}
else if (gjzl_rb.Checked)
{
one_crt();
}
else
{
rows_cf();
}
//this.Invoke(test1);
sht.Delete();
sw.Stop();
this.Text = "拆分工作表(此功能响应超时将会提示)";
MessageBox.Show("程序运行结束:"+sw.Elapsed.ToString(),"提示");
//if (!bvbc)
//{
// EApp.SendKeys("%TMS%T%%V{ENTER}");
//}
EApp.ScreenUpdating = true;
EApp.DisplayAlerts = true;
this.TopMost = true;
this.TopMost = false;
}
public void process_base_book2(VBComponent vbc, string k) //拆分成工作簿基础
{
vbc.CodeModule.InsertLines(2, "set wb = Thisworkbook");
vbc.CodeModule.InsertLines(3, "wb.Activate");
vbc.CodeModule.InsertLines(4, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col + ", Criteria1:=Array(\"" + ja + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(5, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col2 + ", Criteria1:=Array(\"" + jb + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(7, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(8, "Range(\"1:" + rowsc + "\").Select");
vbc.CodeModule.InsertLines(9, "Selection.Copy");
vbc.CodeModule.InsertLines(10, "Set wb_new = Workbooks.Add");
vbc.CodeModule.InsertLines(11, "wb_new.sheets(1).Select");
vbc.CodeModule.InsertLines(12, "wb_new.sheets(1).PasteSpecial xlPasteColumnWidths");
vbc.CodeModule.InsertLines(13, "wb_new.sheets(1).Paste");
vbc.CodeModule.InsertLines(15, "icol = wb_new.sheets(1).UsedRange.Rows.Count");
}
public void process_base_sht2(VBComponent vbc, string k)//拆分成工作表基础
{
vbc.CodeModule.InsertLines(2, "set wb = Thisworkbook");
vbc.CodeModule.InsertLines(3, "wb.Activate");
vbc.CodeModule.InsertLines(4, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col + ", Criteria1:=Array(\"" + ja + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(5, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col2 + ", Criteria1:=Array(\"" + jb + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(7, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(8, "Range(\"1:" + rowsc + "\").Select");
vbc.CodeModule.InsertLines(9, "Selection.Copy");
vbc.CodeModule.InsertLines(10, "sheets(\"" + sht_use.Name + "\").Select");
vbc.CodeModule.InsertLines(11, "sheets(\"" + sht_use.Name + "\").PasteSpecial xlPasteColumnWidths");
vbc.CodeModule.InsertLines(12, "sheets(\"" + sht_use.Name + "\").Paste");
vbc.CodeModule.InsertLines(13, "icol = sheets(\"" + sht_use.Name + "\").UsedRange.Rows.Count");
}
public void two_crt()
{
//shtc = wb.Worksheets.Count;//总表格item数 3
rowc_a = bwnum == 0 ? rowsc : rowsc - bwnum;//表尾的起始行
data_rows = rowc_a - btnum;//数据行数范围 比如A1:A14,表头2行，表尾2行，数据范围是A2:A12
//关键字1的范围
gjz_a = index_cb.SelectedIndex+1;//表头关键字1所在单元格 A2
//关键字2的范围
gjz_b = gjz_cb.SelectedIndex + 1; ;//表头关键字2所在单元格 B2
rng_a = sht.Range[sht.Cells[btnum, gjz_a], sht.Cells[rowc_a, gjz_a]];//关键字1的单元格列
rng_b = sht.Range[sht.Cells[btnum, gjz_b], sht.Cells[rowc_a, gjz_b]];//关键字2的单元格列
rowc_a = bwnum == 0 ? rowsc : rowsc - bwnum + 1;//表尾的起始行
// string ax = rng_a.get_Address();
//拿到筛选条件的关键字进行筛选
Dictionary<string, string> dic = new Dictionary<string, string>();
// Dictionary<string, string> dic2 = new Dictionary<string, string>();
// int rowsc = sht.UsedRange.Rows.Count;
object[,] arr = rng_a.Value2;
object[,] arr2 = rng_b.Value2;
for (int i = 2; i <= arr.GetUpperBound(0); i++)
{
try
{
string k1 = arr[i, 1] != null ? arr[i, 1].ToString().Trim() : "";
string k2 = arr2[i, 1] != null ? arr2[i, 1].ToString().Trim() : "";
dic[k1 + "_" + k2] = "";
}
catch(Exception ex)
{
MessageBox.Show("第" + i.ToString() + "行发生异常：" + ex.Message,"异常信息");
}
}
//拿到筛选条件的关键字进行筛选
//----------------------------------------筛选
//添加工作表
//wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], newShtCount-1, Excel.XlSheetType.xlWorksheet);//3,9
//根据条件进行筛选并复制黏贴至新表
// //int x = 1;
//根据条件进行筛选并复制黏贴至新表
col = index_cb.SelectedIndex + 1;
col2 = gjz_cb.SelectedIndex + 1;
//--------------------------------------------------超级混乱的大型判断现场
// 有表尾----------------------------------------------------------------------------------------------
if (bwnum != 0)
{
//计算----------------------------------------------------------------------------------------------
if (zdljs_rb.Checked)
{
jsgs = jsgs_cb.Text;//计算公式的TEXT
zdcol = jscol_cb.Text.Substring(1, 1);//指定计算列(公式所在列)C
int kbt = btnum + 1;
string gs = "";
switch (jsgs)//判断计算方式
{
case "求和": gs = "SUM";
break;
case "计算平均值": gs = "AVERAGE";
break;
}
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_gs_book(vbc, kbt, gs);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_gs_sht(vbc, kbt, gs);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k+zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_gs_book(vbc, kbt, gs);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k + zdy_tb.Text ;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_gs_sht(vbc, kbt, gs);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
//不计算----------------------------------------------------------------------------------------------
else
{
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
}
//没有表尾----------------------------------------------------------------------------------------------------------------------------
else
{
//是否计算
//计算----------------------------------------------------------------------------------------------------------------------------
if (zdljs_rb.Checked)
{
jsgs = jsgs_cb.Text;//计算公式的TEXT
zdcol = jscol_cb.Text.Substring(1, 1);//指定计算列(公式所在列)C
int kbt = btnum + 1;
string gs = "";
switch (jsgs)//判断计算方式
{
case "求和": gs = "SUM";
break;
case "计算平均值": gs = "AVERAGE";
break;
}
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_gs_book(vbc, kbt, gs);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_gs_sht(vbc, kbt, gs);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + ( k + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
process_gs_book(vbc, kbt, gs);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
process_gs_sht(vbc, kbt, gs);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
//不计算----------------------------------------------------------------------------------------------------------------------------
else
{
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + ( k + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book2(vbc, k);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (string k in dic.Keys)
{
int j = k.IndexOf("_");
ja = k.Substring(0, j);
jb = k.Substring(j + 1);
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k + zdy_tb.Text ;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht2(vbc, k);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
}
sht.AutoFilterMode = false;
}
public void one_crt()
{
rowc_a = bwnum == 0 ? rowsc : rowsc - bwnum ;//表尾的起始行
data_rows = rowc_a - btnum;//数据行数范围 比如A1:A14,表头2行，表尾2行，数据范围是A2:A12
//关键字1的范围
gjz_a = index_cb.SelectedIndex + 1; ;//表头关键字1所在单元格 A2
rng_a = sht.Range[sht.Cells[btnum, gjz_a], sht.Cells[rowc_a, gjz_a]];//关键字1的单元格列
int m = rng_a.Column;
//拿到筛选条件的关键字进行筛选
rowc_a = bwnum == 0 ? rowsc : rowsc - bwnum + 1;//表尾的起始行
Dictionary<string, string> dic = new Dictionary<string, string>();
// int rowsc = sht.UsedRange.Rows.Count;
object[,] arr = rng_a.Value2;
for (int i = 2; i <= arr.GetUpperBound(0); i++)
{
try
{
string keya = arr[i, 1] != null ? arr[i, 1].ToString().Trim() : "空白";
dic[keya] = arr[i, 1] != null ? arr[i, 1].ToString().Trim() :"";
}
catch (Exception ex)
{
MessageBox.Show("第" + i.ToString() + "行发生异常：" + ex.Message, "异常信息");
}
}
col = index_cb.SelectedIndex + 1;
// 有表尾----------------------------------------------------------------------------------------------
if (bwnum != 0)
{
//计算----------------------------------------------------------------------------------------------
if (zdljs_rb.Checked)
{
jsgs = jsgs_cb.Text;//计算公式的TEXT
zdcol = jscol_cb.Text.Substring(1, 1);//指定计算列(公式所在列)C
int kbt = btnum + 1;
string gs = "";
switch (jsgs)//判断计算方式
{
case "求和": gs = "SUM";
break;
case "计算平均值": gs = "AVERAGE";
break;
}
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k.Key + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_gs_book(vbc, kbt, gs);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_gs_sht(vbc, kbt, gs);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k.Key + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_gs_book(vbc, kbt, gs);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_gs_sht(vbc, kbt, gs);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
//不计算----------------------------------------------------------------------------------------------
else
{
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k.Key + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k.Key + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_bw_book(vbc);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_bw_sht(vbc);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
}
//没有表尾----------------------------------------------------------------------------------------------------------------------------
else
{
//是否计算
//计算----------------------------------------------------------------------------------------------------------------------------
if (zdljs_rb.Checked)
{
jsgs = jsgs_cb.Text;//计算公式的TEXT
zdcol = jscol_cb.Text.Substring(1, 1);//指定计算列(公式所在列)C
int kbt = btnum + 1;
string gs = "";
switch (jsgs)//判断计算方式
{
case "求和": gs = "SUM";
break;
case "计算平均值": gs = "AVERAGE";
break;
}
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k.Key + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_gs_book(vbc, kbt, gs);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_gs_sht(vbc, kbt, gs);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k.Key + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
process_gs_book(vbc, kbt, gs);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
process_gs_sht(vbc, kbt, gs);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
//不计算----------------------------------------------------------------------------------------------------------------------------
else
{
//使用关键字命名
if (sygjz_rb.Checked)
{
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + k.Key + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
string bas = wb.Name;
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
}
}
}
else //使用自定义命名
{
//int x = 1;
if (dlwb_cb.Checked)//拆分工作簿
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
new_shtname = save_path + (k.Key + zdy_tb.Text) + xwbgs_cb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc()");
process_base_book(vbc, k.Value);
vbc.CodeModule.InsertLines(47, "Application.SheetsInNewWorkbook =1");
vbc.CodeModule.InsertLines(48, "wb_new.SaveAs \"" + new_shtname + "\"");
vbc.CodeModule.InsertLines(49, "wb_new.Close");
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作簿yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
else//拆分工作表
{
foreach (KeyValuePair<string, string> k in dic)
{
VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = k.Key + zdy_tb.Text;
vbc.CodeModule.InsertLines(1, "Sub 拆分工作表yc()");
process_base_sht(vbc, k.Value);
vbc.CodeModule.InsertLines(50, "End Sub");
timer1.Enabled = true;
timer1.Start();
EApp.Run("拆分工作表yc");
timer1.Stop();
timer1.Enabled = false;
wb.VBProject.VBComponents.Remove(vbc);
//x += 1;
}
}
}
}
}
sht.AutoFilterMode = false;
}
public void process_base_book(VBComponent vbc, string k) //拆分成工作簿基础
{
vbc.CodeModule.InsertLines(2, "set wb = Thisworkbook");
vbc.CodeModule.InsertLines(3, "wb.Activate");
vbc.CodeModule.InsertLines(5, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col + ", Criteria1:=Array(\"" + k + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(7, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(8, "Range(\"1:" + rowsc + "\").Select");
vbc.CodeModule.InsertLines(9, "Selection.Copy");
vbc.CodeModule.InsertLines(10, "Set wb_new = Workbooks.Add");
vbc.CodeModule.InsertLines(11, "wb_new.sheets(1).Select");
vbc.CodeModule.InsertLines(12, "wb_new.sheets(1).PasteSpecial xlPasteColumnWidths");
vbc.CodeModule.InsertLines(13, "wb_new.sheets(1).Paste");
vbc.CodeModule.InsertLines(15, "icol = wb_new.sheets(1).UsedRange.Rows.Count");
}
public void process_gs_book(VBComponent vbc, int kbt, string gs)//含有公式时插入工作簿
{
vbc.CodeModule.InsertLines(16, "sygs_adr = wb_new.sheets(1).Range(\"" + (zdcol + kbt) + "\").Resize(icol -" + btnum + ", 1).Address");
vbc.CodeModule.InsertLines(17, "wb_new.sheets(1).Range(\"" + zdcol + "\"&(icol+1)).Select");
vbc.CodeModule.InsertLines(18, "ActiveCell.Formula = \"=" + gs + "(\"& sygs_adr & \")\"");
vbc.CodeModule.InsertLines(18, "icol = icol + 1");
}
public void process_bw_book(VBComponent vbc)//含有表尾时插入工作簿
{
vbc.CodeModule.InsertLines(19, "wb.Activate");
vbc.CodeModule.InsertLines(20, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(21, "Range(Cells(" + rowc_a + ", 1),Cells(" + rowsc + ", " + cols + ")).Select");
vbc.CodeModule.InsertLines(22, "Selection.Copy");
vbc.CodeModule.InsertLines(23, "wb_new.Activate");
vbc.CodeModule.InsertLines(24, "wb_new.sheets(1).Select");
vbc.CodeModule.InsertLines(25, "wb_new.sheets(1).Cells(icol+1,1).Select");
vbc.CodeModule.InsertLines(26, "wb_new.sheets(1).Paste");
}
public void process_base_sht(VBComponent vbc, string k)//拆分成工作表基础
{
vbc.CodeModule.InsertLines(2, "set wb = Thisworkbook");
vbc.CodeModule.InsertLines(3, "wb.Activate");
vbc.CodeModule.InsertLines(5, "sheets(\"" + sht.Name + "\").Range(\"" + rng_bt.Address + "\").AutoFilter Field:=" + col + ", Criteria1:=Array(\"" + k + "\"), Operator:=xlFilterValues '定义数据范围，注意标题行范围");
vbc.CodeModule.InsertLines(7, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(8, "Range(\"1:" + rowsc + "\").Select");
vbc.CodeModule.InsertLines(9, "Selection.Copy");
vbc.CodeModule.InsertLines(10, "sheets(\"" + sht_use.Name + "\").Select");
vbc.CodeModule.InsertLines(11, "sheets(\"" + sht_use.Name + "\").PasteSpecial xlPasteColumnWidths");
vbc.CodeModule.InsertLines(12, "sheets(\"" + sht_use.Name + "\").Paste");
vbc.CodeModule.InsertLines(13, "icol = sheets(\"" + sht_use.Name + "\").UsedRange.Rows.Count");
}
public void process_gs_sht(VBComponent vbc, int kbt, string gs)//含有公式时插入工作表
{
vbc.CodeModule.InsertLines(15, "sygs_adr = sheets(\"" + sht_use.Name + "\").Range(\"" + (zdcol + kbt) + "\").Resize(icol -" + btnum + ", 1).Address");
vbc.CodeModule.InsertLines(16, "sheets(\"" + sht_use.Name + "\").Range(\"" + zdcol + "\"&(icol+1)).Select");
vbc.CodeModule.InsertLines(17, "ActiveCell.Formula = \"=" + gs + "(\"& sygs_adr & \")\"");
vbc.CodeModule.InsertLines(18, "icol = icol + 1");
}
public void process_bw_sht(VBComponent vbc)//含有表尾时插入工作表
{
vbc.CodeModule.InsertLines(20, "sheets(\"" + sht.Name + "\").Select");
vbc.CodeModule.InsertLines(21, "Range(Cells(" + rowc_a + ", 1),Cells(" + rowsc + ", " + cols + ")).Select");
vbc.CodeModule.InsertLines(22, "Selection.Copy");
vbc.CodeModule.InsertLines(23, "sheets(\"" + sht_use.Name + "\").Select");
vbc.CodeModule.InsertLines(24, "sheets(\"" + sht_use.Name + "\").Cells(icol+1,1).Select");
vbc.CodeModule.InsertLines(25, "sheets(\"" + sht_use.Name + "\").Paste");
}
public void rows_cf()
{
sht.Copy(Type.Missing, sht);
// shtc = wb.Worksheets.Count;//总表格item数 3
Excel.Worksheet sht_copy = wb.Worksheets[2];
//使用行数拆分，表名只能自定义
int cfhs = int.Parse(cfh_dud.Value.ToString());
rowc_a = rowsc - bwnum + 1;//表尾的起始行
data_rows = rowsc - bwnum;//数据行数 A1:A5，表头表尾各1行，数据行数为A1:A4
//float fnum = (data_rows - btnum) / cfhs;
int fornum = (data_rows - btnum) / cfhs;
fornum = (data_rows - btnum) % cfhs > 0 ? fornum + 1 : fornum;
// sht_copy.Name = zdy_tb.Text + "_" + (fornum+1);
for (int i = 1; i <= fornum; i++)
{
try
{
new_shtname = zdy_tb.Text + "_" + i;//使用自定义+序号命名
if (dlwb_cb.Checked)//拆分工作簿或工作表
{
wb_new = EApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//新建一个工作簿[拆分工作簿]
sht_use = wb_new.Worksheets[1];//需要粘贴内容的工作表[拆分工作簿]
sht_use.Name = new_shtname;
//保存工作簿[拆分方式为工作簿]
new_shtname = save_path + new_shtname + xwbgs_cb.Text;
wb_new.SaveAs(new_shtname, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
shtc = wb.Worksheets.Count;//总表格item数 3
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtc], 1, Excel.XlSheetType.xlWorksheet);//3,9
sht_use = EApp.Worksheets[shtc + 1];//需要粘贴内容的工作表[拆分工作表]
sht_use.Name = new_shtname;
}
//添加筛选条件后复制粘贴数据
sht_copy.Range["A1"].Resize[btnum + cfhs, cols].Copy();
// sht.Range[sht.Cells[1, 1], sht.Cells[cfhs + btnum, cols]].Copy();//每隔多少行复制
//sht.Range[sht.Cells[btnum+1, 1], sht.Cells[cfhs + btnum, cols]].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
sht_use.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);//粘贴列宽
sht_use.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);//粘贴内容
sht_copy.Range["A1"].Offset[btnum, 0].Resize[cfhs, cols].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
if (zdljs_rb.Checked)//计算求和或平均值
{
jsgs = jsgs_cb.Text;//计算公式的TEXT
zdcol = jscol_cb.Text.Substring(1, 1);//指定计算列(公式所在列)C
rows_rng = sht_use.UsedRange.Rows.Count;//新表的空白行
string sygs_adr = sht_use.Range[zdcol + (btnum + 1)].Resize[rows_rng - btnum, 1].get_Address();//使用公式的数据列 B2:B13
switch (jsgs)//判断计算方式
{
case "求和": sht_use.Range[zdcol + (rows_rng + 1)].Value = "=SUM(" + sygs_adr + ")";
break;
case "计算平均值": sht_use.Range[zdcol + (rows_rng + 1)].Value = "=AVERAGE(" + sygs_adr + ")";
break;
}
}
//获得拆分后的区域，黏贴表尾
if (bwnum != 0)
{
rows_rng = sht_use.UsedRange.Rows.Count + 1;//新建表的使用区域
sht.Range[sht.Cells[rowc_a, 1], sht.Cells[rowsc, cols]].Copy();//复制表尾的数据
sht_use.Cells[rows_rng, 1].PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);//粘贴数据至新表
}
if (dlwb_cb.Checked)
{
wb_new.Close(true, Type.Missing, Type.Missing);
}
}
catch (Exception ex)
{
MessageBox.Show("程序第" + i.ToString() + "行发生异常:" + ex.Message, "异常信息");
}
}
sht_copy.Delete();
sht.AutoFilterMode = false;
}
//表头数字更改时，联动字段combobox更新关键字
private void btnum_dud_ValueChanged(object sender, EventArgs e)
{
ldgjz();
}
public void ldgjz()
{
index_cb.Items.Clear();
gjz_cb.Items.Clear();
jscol_cb.Items.Clear();
btnum = int.Parse(btnum_dud.Value.ToString());
rng_bt = sht.Range[sht.Cells[btnum, 1], sht.Cells[btnum, cols]];
object[,] arr = rng_bt.Value;
for(int i=1;i<=arr.GetLength(1);i++)
{
string value = arr[1, i] != null ? arr[1, i].ToString() : "Cols" + i;
index_cb.Items.Add(value);
gjz_cb.Items.Add(value);
jscol_cb.Items.Add(value);
}
index_cb.SelectedIndex = 0;
gjz_cb.SelectedIndex = 0;
jscol_cb.SelectedIndex = 0;
}
private void zjzd_lb_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
if (gjz_cb.Visible == true)
{
gjz_cb.Visible = false;
}
else
{
gjz_cb.Visible = true;
}
}
private void bwnum_dud_ValueChanged(object sender, EventArgs e)
{
bwnum = int.Parse(bwnum_dud.Value.ToString());
}
private void fp_btn_Click(object sender, EventArgs e)
{
fbd_path.ShowDialog();
string p = fbd_path.SelectedPath.ToString();
if (p.Length <= 3)
{
save_path = p;
}
else
{
save_path = p + "\\";
}
// save_path = fbd_path.SelectedPath.ToString() + "\\";
}
private void zdljs_rb_Click(object sender, EventArgs e)
{
zdljs_rb.Checked = zdljs_rb.Checked ? false : true;
}
private void zscf_rb_CheckedChanged(object sender, EventArgs e)
{
zdym_rb.Checked = true;
sygjz_rb.Enabled = zscf_rb.Checked ? false : true;
}
private void dlwb_cb_CheckedChanged(object sender, EventArgs e)
{
if (dlwb_cb.Checked)
{
fbd_path.ShowDialog();
string p = fbd_path.SelectedPath.ToString();
if (p.Length <= 3)
{
save_path = p;
}
else
{
save_path = p +"\\";
}
}
}
private void button3_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
