using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.Office.Tools.Ribbon;

using System.Diagnostics;

using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

using System.Windows.Forms;

using System.IO;

namespace GatherData

{

    public partial class Ribbon1

    {

        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)

        {

        }

        private void ButtonGather_Click(object sender, RibbonControlEventArgs e)

        {

            Excel.Application xlApp = Globals.ThisAddIn.Application;

            Excel.Workbook wb = xlApp.ActiveWorkbook;

            Excel.Worksheet sht = (Excel.Worksheet)wb.ActiveSheet;

            sht.Cells.Clear();

            TimeSpan StartTime = new TimeSpan(DateTime.Now.Ticks); ;

            xlApp.ScreenUpdating = false;

            xlApp.DisplayAlerts = false;

            Excel.Workbook openWb;

            Excel.Worksheet openSht;

            Excel.Range rng;

            int index = 0;

            Office.FileDialog fd = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogFolderPicker];

            fd.InitialFileName = xlApp.ActiveWorkbook.Path;

            if (fd.Show() == -1)

            {

                StartTime = new TimeSpan(DateTime.Now.Ticks);

                string folderPath = fd.SelectedItems.Item(1);

                string[] filePaths = Directory.GetFiles(folderPath, "*.xls*");

                foreach (string filepath in filePaths)

                {

                    if (filepath != wb.FullName)

                    {

                        // Debug.Print(filepath);

                        index++;

                        openWb = xlApp.Workbooks.Open(filepath);

                        openSht = openWb.Worksheets[1];

                        long endrow = openSht.Cells[openSht.Rows.Count, 3].End(Excel.XlDirection.xlUp).Row;

                        if (index == 1)

                        {

                            rng = openSht.Range[openSht.Cells[1, 1], openSht.Cells[endrow, 17]];

                            rng.Copy(sht.Cells[1, 1]);

                        }

                        else

                        {

                            rng = openSht.Range[openSht.Cells[3, 1], openSht.Cells[endrow, 17]];

                            long nextRow = sht.Cells[sht.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row + 1;

                            rng.Copy(sht.Cells[nextRow, 1]);

                        }

                        xlApp.StatusBar = "正在汇总第" + index + "个文件，请耐心等候！";

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

                    }

                }

            }

            xlApp.ScreenUpdating = true;

            xlApp.DisplayAlerts = true;

            xlApp.StatusBar = false;

            TimeSpan EndTime = new TimeSpan(DateTime.Now.Ticks);

            TimeSpan duration = StartTime.Subtract(EndTime).Duration();

            MessageBox.Show("汇总完成，耗时：" +duration.Minutes.ToString()+"分"+ duration.Seconds.ToString() + "秒！");

        }

    }

}