using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.Office.Tools.Ribbon;

using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

using System.IO;

using System.Diagnostics;

using System.Windows.Forms;

namespace GatherDataTool

{

    public partial class Ribbon1

    {

        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)

        {

        }

        private void ButtonGather_Click(object sender, RibbonControlEventArgs e)

        {

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

            GatherDataTool.ThisWorkbook wb = Globals.ThisWorkbook;

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

            sht.Cells.Clear();

            Excel.Application xlApp = wb.Application;

            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)

            {

                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]);

                        }

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

                    }

                }

            }

            xlApp.ScreenUpdating = true;

            xlApp.DisplayAlerts = true;

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

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

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

        }

    }

}