C#處理多個Excel合并,復制Sheet

2019-11-17 04:07:02

字體：大中小

來源：轉載

供稿：網友

項目中需要把多個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");
            }
        }

上一篇：如何利用java得到當前的時間和前一天的時間

下一篇：C#建立或刪除共享