拆分工作簿至文件夹

 

 

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 IWshRuntimeLibrary;
namespace zizhuTools
{
    public partial class cfshttowb : Form
    {
        Stopwatch sw = new Stopwatch();
        Excel.Application EApp;
        public cfshttowb()
        {
            InitializeComponent();
        }

        private void cfshttowb_Load(object sender, EventArgs e)
        {
            EApp = Globals.ThisAddIn.Application; //获得控制权
            wb = EApp.ActiveWorkbook;
            //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");
        }


        private void button1_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            fbd.ShowDialog();
            textBox1.Text = fbd.SelectedPath;

            this.WindowState = FormWindowState.Normal;
        }

        Excel.Workbook wb;

        Excel.Workbook newwb;
        Excel.Worksheet sht;
        Excel.Worksheet newsht;
      

        //public void 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");
        //}


       
        private void button2_Click(object sender, EventArgs e)
        {
            

            this.Text = "拆分工作表至工作簿(程序运行中)";
            sw.Start();
            
            string path = "";
            EApp.DisplayAlerts = false;
            EApp.ScreenUpdating = false;

            wb = EApp.ActiveWorkbook;

            string p = textBox1.Text;
            if (p.Length > 3)
            {
                p = p + "\\";
            }

            for (int i = 1; i <= wb.Worksheets.Count; i++)
            {
                sht = wb.Worksheets[i];


                path = p + sht.Name + comboBox1.SelectedItem;

                VBComponent vbc = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
                vbc.CodeModule.InsertLines(1, "Sub 拆分工作簿yc2()");
                vbc.CodeModule.InsertLines(2, "Sheets(" + i + ").Select");
                vbc.CodeModule.InsertLines(3, "Sheets(" + i + ").Copy");
                vbc.CodeModule.InsertLines(4, "Sheets(1).Name = \""+ sht.Name + "\"");
                vbc.CodeModule.InsertLines(5, " ActiveWorkbook.SaveAs Filename:=\"" + path+ "\"");
                vbc.CodeModule.InsertLines(6, "ActiveWorkbook.Close (True)");
                vbc.CodeModule.InsertLines(7, "End Sub");
                EApp.Run("拆分工作簿yc2");
                wb.VBProject.VBComponents.Remove(vbc);
                //sht = wb.Worksheets[i];
                //newwb = EApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                //newsht = newwb.Worksheets[1];
                //path = textBox1.Text + "\\" + sht.Name + ".xls";
                //sht.Copy(newsht);
                //newsht.Delete();
                //newwb.Close(true, path, Type.Missing);           
            }
            this.Text = "拆分工作表至工作簿";
            sw.Stop();
            MessageBox.Show("完成拆分:" + sw.Elapsed);
            EApp.DisplayAlerts = true;
            EApp.ScreenUpdating = true;
            this.TopMost = true;
            this.TopMost = false;
           
        }

       
    }
}