C#处理多个Excel合并,复制Sheet

项目中需要把多个Excel报表进行合并, 用一个Excel的多个Sheet来表现,合并报表代码如下:  

 

       private void btnExport_Click(object sender, EventArgs e)

        {

            string fileName = "";

            System.Windows.Forms.FileDialog dlg = null;

            dlg = new SaveFileDialog();

            //根据格式设置文件字符串

            dlg.Filter = this.GetFileFilterString();

            //打开文件

            if (dlg.ShowDialog() == DialogResult.OK)

            {

                fileName = dlg.FileName;

            }

            if (dlg.FileName.Equals(""))

            {

                return;

            }

            //生成报表

            this.Export(fileName);

            string dir = fileName.Substring(0, fileName.LastIndexOf("//"));

            

            //报表合并

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            Microsoft.Office.Interop.Excel.Workbook workbook1 = excel.Workbooks.Open(dir + "//file0.xls",

                   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                   Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Workbook workbook2 = excel.Workbooks.Open(dir + "//file1.xls",

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Workbook workbook3 = excel.Workbooks.Open(dir + "//file2.xls",

                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                     Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Workbook workbook4 = excel.Workbooks.Open(dir + "//file3.xls",

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Workbook workbook5 = excel.Workbooks.Open(dir + "//file4.xls",

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Workbook workbook6 = excel.Workbooks.Open(dir + "//file5.xls",

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                    Type.Missing, Type.Missing);

            Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook1.Sheets["Sheet1"];

            Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook2.Sheets["Sheet1"];

            Microsoft.Office.Interop.Excel.Worksheet worksheet3 = (Microsoft.Office.Interop.Excel.Worksheet)workbook3.Sheets["Sheet1"];

            Microsoft.Office.Interop.Excel.Worksheet worksheet4 = (Microsoft.Office.Interop.Excel.Worksheet)workbook4.Sheets["Sheet1"];

            Microsoft.Office.Interop.Excel.Worksheet worksheet5 = (Microsoft.Office.Interop.Excel.Worksheet)workbook5.Sheets["Sheet1"];

            Microsoft.Office.Interop.Excel.Worksheet worksheet6 = (Microsoft.Office.Interop.Excel.Worksheet)workbook6.Sheets["Sheet1"];

            //设置报表Sheet Name

            worksheet1.Name = "实时资产信息";

            worksheet2.Name = "实时持仓清单";

            worksheet3.Name = "资产信息";

            worksheet4.Name = "证券余额";

            worksheet5.Name = "资金流水汇总";

            worksheet6.Name = "资金流水汇总1";

            //合并报表

            worksheet6.Copy(Missing.Value, worksheet1);

            worksheet5.Copy(Missing.Value, worksheet1);

            worksheet4.Copy(Missing.Value, worksheet1);

            worksheet3.Copy(Missing.Value, worksheet1);

            worksheet2.Copy(Missing.Value, worksheet1);

       

            workbook1.Save();

            workbook1.Close(false, Type.Missing, Type.Missing);

           

            workbook2.Close(false, Type.Missing, Type.Missing);

            workbook3.Close(false, Type.Missing, Type.Missing);

            workbook4.Close(false, Type.Missing, Type.Missing);

            workbook5.Close(false, Type.Missing, Type.Missing);

            workbook6.Close(false, Type.Missing, Type.Missing);

            //如果报表文件存在，先删除

            if (File.Exists(fileName))

            {

                File.Delete(fileName);

            }

            File.Copy(dir + "//file0.xls", fileName);

            //删除临时文件

            for (int i = 0; i < 6; i++)

            {

                File.Delete(dir + "//file" + i + ".xls");

            }

        }