using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Win32;
using System.Windows.Xps.Packaging;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Controls;
using System.Windows.Controls.Primitives;

namespace PSPIManager
{
    public class ExcelDataManager
    {
        public List<ExcelFile> DocFiles;
        public int TargetExcelFile;
        public TabControl tabControl_Files;

        public struct ExcelFile
        {
            public String path;
            public String NewFileName;
            public bool WantToSave;
            public List<ExcelSheet> sheets;
            public Application excelApplication;
            public Workbook workbook;
            public Worksheet worksheet;
            public Range range;
            public TabItem tabItem_File;
            public TabControl tabControl_Sheets;
        }

        public struct ExcelSheet
        {
            public String Name;
            public TabItem tabItem_Sheet;
            public DataGrid dataGrid;
        }

        public void Init(TabControl getTabControl_Files)
        {
            DocFiles = new List<ExcelFile>();

            tabControl_Files = getTabControl_Files;
        }

        public void Open_File&#40;String path&#41;
        {
            bool Exist = false;
            for (int i = 0; i < DocFiles xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed>();
                ExcelSheet excelSheet = new ExcelSheet();
                excelSheet.Name = DocFiles[TargetExcelFile].excelApplication.Sheets[i + 1].Name();

                excelSheet.tabItem_Sheet = new TabItem();
                excelSheet.tabItem_Sheet.Name = excelSheet.Name;

                excelSheet.dataGrid = new DataGrid();
                excelSheet.dataGrid.Width = -1;
                excelSheet.dataGrid.Height = -1;
                excelSheet.dataGrid.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
                excelSheet.dataGrid.VerticalAlignment = System.Windows.VerticalAlignment.Stretch;
                excelSheet.dataGrid.Margin = new System.Windows.Thickness(0, 0, 0, 0);

                excelSheet.tabItem_Sheet.Content = excelSheet.dataGrid;

                DocFiles[TargetExcelFile].sheets.Add(excelSheet);
                DocFiles[TargetExcelFile].tabControl_Sheets.Items.Add(excelSheet.tabItem_Sheet);
                DocFiles[TargetExcelFile].tabItem_File.Content = excelSheet.dataGrid;

                DocFiles[TargetExcelFile].worksheet = (Worksheet)DocFiles[TargetExcelFile].workbook.Sheets[DocFiles[TargetExcelFile].excelApplication.Sheets[i + 1].Name];
                DocFiles[TargetExcelFile].range = DocFiles[TargetExcelFile].worksheet.UsedRange;

                for (int column = 1; column <= DocFiles[TargetExcelFile].range.Columns.Count; column++)
                {
                    DataGridTextColumn item = new DataGridTextColumn();
                    item.Header = (DocFiles[TargetExcelFile].range.Cells[1, column] as Range).Value2.ToString();
                    DocFiles[TargetExcelFile].sheets[i].dataGrid.Columns.Add(item);
                }
            }        
        }

        public void Close_File&#40;Boolean WantToSave = true, String NewFileName = "UntitledExcellFile"&#41;
        {
            DocFiles[TargetExcelFile].workbook.Close(WantToSave, NewFileName);
            DocFiles[TargetExcelFile].excelApplication.Quit();
        }
    }
}