using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace Excelhebing
{
    class MergeExcel
    {
        Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //保存目标的对象
        Excel.Workbook bookDest = null;
        Excel.Worksheet sheetDest = null;
        //读取数据的对象 
        Excel.Workbook bookSource = null;
        Excel.Worksheet sheetSource = null;
        string[] _sourceFiles = null;
        public static string[,] _dff;
        string _destFile = string.Empty;
        string _columnEnd = string.Empty;
        int _headerRowCount = 1;
        int _currentRowCount = 0;
        public MergeExcel(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            sheetDest.Name = "Data";
            _sourceFiles = sourceFiles;
            _dff = new string[sourceFiles.Count(), 3];
            _destFile = destFile;
            _columnEnd = columnEnd;
            _headerRowCount = headerRowCount;
        }
        /// <summary>
        /// 打开工作表
        /// </summary>
        /// <param name="fileName"></param>
        void OpenBook(string fileName)
        {
            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
        }
        /// <summary>
        /// 关闭工作表
        /// </summary>
        void CloseBook()
        {
            bookSource.Close(false, Missing.Value, Missing.Value);
        }
        /// <summary>
        /// 复制表头
        /// </summary>
        void CopyHeader()
        {
            Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
            range.Copy(sheetDest.get_Range("A1", Missing.Value));
            _currentRowCount += _headerRowCount;
        }
        /// <summary>
        /// 复制数据
        /// </summary>
        void CopyData(int filesint)
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            int money = 0;
            for (int i = 4; i < sheetRowCount; i++)
            {
                //cellsstr = ;
                if (((Excel.Range)sheetSource.Cells[i, 1]).Text.ToString() == "" && ((Excel.Range)sheetSource.Cells[i, 5]).Text.ToString() == "")
                {
                    sheetRowCount = i - 1;
                    break;
                }
                else 
                {
                    money = money+int.Parse(((Excel.Range)sheetSource.Cells[i, 6]).Text.ToString());
                }
            }
            
            _dff[filesint, 0] = ((Excel.Range)sheetSource.Cells[sheetRowCount - 1, 2]).Text.ToString();
            _dff[filesint, 1] = (sheetRowCount-3).ToString();
            _dff[filesint, 2] = money.ToString();
            sheetSource.Cells[sheetRowCount, 8] = (sheetRowCount - 3).ToString();
            sheetSource.Cells[sheetRowCount, 9] = money.ToString();
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount + 1), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
        //void DeleteRows(int rowIndex)
        //{
        //    Excel.Range range = (Excel.Range)sheetSource.Rows[rowIndex,];
        //    range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        //}
        public static string[,] sfff()
        {
            return _dff;
        }
         void Delete()
         {
             int sheetRowCount = sheetSource.UsedRange.Rows.Count;
             int rowint = 3;
             //string cellsstr = "";
             for (int i = 3; i < sheetRowCount;i++ )
             {
                 //cellsstr = ;
                 if (((Excel.Range)sheetSource.Cells[i, 1]).Text.ToString()=="")
                 {
                     rowint = i;
                     break;
                 }
             }
             if(rowint!=3)
             {             
                 Excel.Range range = sheetSource.get_Range(sheetSource.Cells[rowint, 1], sheetSource.Cells[sheetRowCount, 10]);
                  range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
             }
     
             //Excel.Range range = sheetSource.get_Range(sheetSource.Cells[startRow, startColumn], sheetSource.Cells[endRow, endColumn]);
             //range.Select();
             //range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
         }
        /// <summary>
        /// 保存结果
        /// </summary>
        void Save()
        {
            bookDest.Saved = true;
            bookDest.SaveCopyAs(_destFile);
        }
        /// <summary>
        /// 退出进程
        /// </summary>
        void Quit()
        {
            app.Quit();
        }
        /// <summary>
        /// 合并
        /// </summary>
        void DoMerge()
        {
            bool b = false;
            int filesint = 0;
            foreach (string strFile in _sourceFiles)
            {
                OpenBook(strFile);
                if (b == false)
                {
                    CopyHeader();
                    b = true;
                }
                Delete();
                CopyData(filesint);
                filesint = filesint + 1;
                CloseBook();
            }
            Save();
            Quit();
        }
        /// <summary>
        /// 合并表格
        /// </summary>
        /// <param name="sourceFiles">源文件</param>
        /// <param name="destFile">目标文件</param>
        /// <param name="columnEnd">最后一列标志</param>
        /// <param name="headerRowCount">表头行数</param>
        public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
        }
    }
}