using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace Shlukova_Analyza_BP
{
    class ExcelVypis
    {
        private Excel.Application excelApp;
        private Excel.Workbook excelWorkBook;
        private Excel.Worksheet excelWorkSheet;
        private Excel.Range range;
        public ExcelVypis(SAFuzzy fuzzy)
        {
            nastavExcel();
            vypisPrehled(excelWorkSheet, fuzzy);
            vypisMaticovaData2(fuzzy.a, "Scé\\Prom", "Datová Matice");
            vypisMaticovaData2(fuzzy.u1, "Scé\\Shlu", "Příslušnosti");
            vypisMaticovaData2(fuzzy.c, "Cen\\Prom", "Centroidy");
            vypisIntervaly(fuzzy.prisluIntervaly);
            vyrobSiluety(fuzzy.siluety);
            string silueta = fuzzy.siluety.PrumerVsech.ToString().Substring(0, 5);
            string file = Environment.CurrentDirectory + @"\" + "fuzzy" + silueta + " " + DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss");
            excelWorkBook.SaveAs(file);
            uvolniExcel();
        }
        public ExcelVypis(SAHier hier, int pocet)
        {
            nastavExcel();
            excelWorkSheet.Name = "Shlukování";
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[1, 1];
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[hier.Shlukovani.GetLength(0), hier.Shlukovani.GetLength(1)];
            Excel.Range range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = hier.Shlukovani;
            vypisMaticovaData2(hier.datovaMatice, "Scé\\Prom", "Datová Matice"); 
            vyrobSiluety(hier.listSiluet[pocet]);
            string silueta = hier.listSiluet[pocet].PrumerVsech.ToString().Substring(0, 5);
            string file = Environment.CurrentDirectory + @"\" + "hier " + silueta + " " + DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss");
            excelWorkBook.SaveAs(file);
            uvolniExcel();
        }
        public ExcelVypis(SAKMeans kMeans)
        {
            nastavExcel();
            vypisPrehledKMeans(excelWorkSheet, kMeans);
            vypisMaticovaData2(kMeans.a, "Scé\\Prom", "Datová Matice");
            vypisMaticovaData2(kMeans.c, "Cen\\Prom", "Centroidy");
            vypisPrvkyShluku(kMeans.u1);
            vyrobSiluety(kMeans.siluety);
            string silueta = kMeans.siluety.PrumerVsech.ToString().Substring(0, 5);
            string file = Environment.CurrentDirectory + @"\" + "kmeans" + silueta + " " + DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss");
            excelWorkBook.SaveAs(file);
            uvolniExcel();
        }
        private void vyrobSiluety(Siluety siluety)
        {
            double[,] tabulka = siluety.PrehlednaTabulka;
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add();
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 1];
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[tabulka.GetLength(0) + 1, tabulka.GetLength(1)];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = tabulka;
            excelWorkSheet.Name = "Siluety";

            Excel.ChartObjects xlCharts = (Excel.ChartObjects)excelWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = xlCharts.Add(200, 20, 900, 600);
            Excel.Chart chartPage = myChart.Chart;

            int pomocnySloupec = 30;
            tabulka = siluety.GrafovaTabulka;
            vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, pomocnySloupec];
            vpravoDule = (Excel.Range)excelWorkSheet.Cells[tabulka.GetLength(0) + 1, pomocnySloupec - 1 + tabulka.GetLength(1)];
            Excel.Range chartRange;
            chartRange = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            chartRange.Value2 = tabulka;

            Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart.Chart.SeriesCollection(Type.Missing);
            //List<Excel.Series> seznamSerii;

            for (int i = 0; i < tabulka.GetLength(1); i++)
            {
                Excel.Series series1 = oSeriesCollection.NewSeries();
                vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, pomocnySloupec + i];
                vpravoDule = (Excel.Range)excelWorkSheet.Cells[tabulka.GetLength(0) + 1, pomocnySloupec + i];
                chartRange = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
                //chartPage.SetSourceData(chartRange);
                series1.Values = chartRange;
                series1.Name = string.Format("Shluk {0}", tabulka.GetLength(1) - i);
            }
            chartPage.ChartType = Excel.XlChartType.xlColumnStacked;
            //chartPage.Legend.Clear();
            chartPage.HasAxis[Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary] = false;

            excelWorkSheet.Cells[1, 1] = "Shluk";
            excelWorkSheet.Cells[1, 1].Font.Bold = true;
            excelWorkSheet.Cells[1, 2] = "Soused";
            excelWorkSheet.Cells[1, 2].Font.Bold = true;
            excelWorkSheet.Cells[1, 3] = "s(i)";
            excelWorkSheet.Cells[1, 3].Font.Bold = true;
            excelWorkSheet.Cells[1, 4] = "Scénář";
            excelWorkSheet.Cells[1, 4].Font.Bold = true;

            vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 1];
            vlevoHore.Activate();
            vlevoHore.Application.ActiveWindow.FreezePanes = true; // nastavi zamrznuti horniho radku
        }
        private void nastavExcel()
        {
            excelApp = new Excel.Application();
            excelWorkBook = excelApp.Workbooks.Add();
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.get_Item(1);
        }
        private void uvolniExcel()
        {
            excelApp.Quit();
            releaseObject(excelWorkSheet);
            releaseObject(excelWorkBook);
            releaseObject(excelApp);
            excelWorkSheet = null;
            excelWorkBook = null;
            excelApp = null;
            GC.Collect();
        }
        private void nastavRadekSloupec(Excel.Worksheet excelWorkSheet, int radku, int sloupcu, string str)
        {
            int[,] horniRadek = new int[1, sloupcu];
            int[,] levySloupec = new int[radku, 1];
            for (int i = 0; i < sloupcu; i++)
            {
                horniRadek[0, i] = i + 1;
            }
            for (int i = 0; i < radku; i++)
            {
                levySloupec[i, 0] = i + 1;
            }
            // nastaveni horniho radku
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[1, 2];
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[1, sloupcu + 1];
            Excel.Range range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = horniRadek;
            // nastaveni leveho sloupce
            vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 1];
            vpravoDule = (Excel.Range)excelWorkSheet.Cells[radku + 1, 1];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = levySloupec;
            // nastaveni bunky A1
            excelWorkSheet.Cells[1, 1] = str;
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            }
            catch
            {

            }
        }
        private void vypisMaticovaData(List<double[]> data, string A1, string nazev)
        {
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add();
            double[,] matice = new double[data.Count, data[0].Length]; // vyrobení datové matice
            for (int i = 0; i < data.Count; i++)
            {
                for (int j = 0; j < data[0].Length; j++)
                {
                    matice[i, j] = data[i][j];
                }
            }
            for (int i = 0; i < data[0].Length; i++) // nastaví formát buněk (po sloupcich)
            {
                Excel.Range hore = (Excel.Range)excelWorkSheet.Cells[2, i + 2];
                Excel.Range dule = (Excel.Range)excelWorkSheet.Cells[data.Count + 1, i + 2];
                range = excelWorkSheet.get_Range(hore, dule);
                range.NumberFormat = "0.000";
            }
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 2];
            vlevoHore.Activate();
            vlevoHore.Application.ActiveWindow.FreezePanes = true; // nastavi zamrznuti horniho a leveho radku
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[data.Count + 1, data[0].Length + 1];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = matice; // předání dat
            excelWorkSheet.Name = nazev;
            nastavRadekSloupec(excelWorkSheet, data.Count, data[0].Length, A1);
        }
        private void vypisMaticovaData2(double[][] data, string A1, string nazev)
        {
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add();
            double[,] matice = new double[data.GetLength(0), data[0].Length]; // vyrobení datové matice
            for (int i = 0; i < data.GetLength(0); i++)
            {
                for (int j = 0; j < data[0].Length; j++)
                {
                    matice[i, j] = data[i][j];
                }
            }
            for (int i = 0; i < data[0].Length; i++) // nastaví formát buněk (po sloupcich)
            {
                Excel.Range hore = (Excel.Range)excelWorkSheet.Cells[2, i + 2];
                Excel.Range dule = (Excel.Range)excelWorkSheet.Cells[data.GetLength(0) + 1, i + 2];
                range = excelWorkSheet.get_Range(hore, dule);
                range.NumberFormat = "0.000";
            }
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 2];
            vlevoHore.Activate();
            vlevoHore.Application.ActiveWindow.FreezePanes = true; // nastavi zamrznuti horniho a leveho radku
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[data.GetLength(0) + 1, data[0].Length + 1];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = matice; // předání dat
            excelWorkSheet.Name = nazev;
            nastavRadekSloupec(excelWorkSheet, data.GetLength(0), data[0].Length, A1);
        }
        private void vypisIntervaly(double[,] prisluIntervaly)
        {
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add();
            Excel.Range vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 2];
            vlevoHore.Activate();
            vlevoHore.Application.ActiveWindow.FreezePanes = true; // nastavi zamrznuti horniho a leveho radku
            Excel.Range vpravoDule = (Excel.Range)excelWorkSheet.Cells[prisluIntervaly.GetLength(0) + 1, prisluIntervaly.GetLength(1) + 1];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = prisluIntervaly; // předání dat
            excelWorkSheet.Name = "Intervaly";

            // nastaveni leveho sloupce
            int radku = prisluIntervaly.GetLength(0);
            int[,] levySloupec = new int[radku, 1];
            for (int i = 0; i < radku; i++)
            {
                levySloupec[i, 0] = i + 1;
            }
            vlevoHore = (Excel.Range)excelWorkSheet.Cells[2, 1];
            vpravoDule = (Excel.Range)excelWorkSheet.Cells[radku + 1, 1];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = levySloupec;

            // nastaveni horniho radku
            string[,] horniRadek = new string[,] { { "<1, 0.9)" , "<0.9, 0.8)", "<0.8, 0.7)", "<0.7, 0.6)", "<0.6, 0.5)",
                                                    "<0.5, 0.4)","<0.4, 0.3)","<0.3, 0.2)","<0.2, 0.1)" } };
            vlevoHore = (Excel.Range)excelWorkSheet.Cells[1, 2];
            vpravoDule = (Excel.Range)excelWorkSheet.Cells[1, 10];
            range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
            range.Value2 = horniRadek;
            excelWorkSheet.Cells[1, 1] = "Shlu\\Int";
        }
        private void vypisPrehled(Excel.Worksheet excelWorkSheet, SAFuzzy fuzzy)
        {
            excelWorkSheet.Name = "Přehled";
            excelWorkSheet.Cells[1, 1] = "Přehled";             
            excelWorkSheet.Cells[2, 1] = "Hodnota obj. funkce"; excelWorkSheet.Cells[2, 2] = fuzzy.j;
            excelWorkSheet.Cells[3, 1] = "Počet iterací";       excelWorkSheet.Cells[3, 2] = fuzzy.iteraci;
            excelWorkSheet.Cells[4, 1] = "Počet scénářů";       excelWorkSheet.Cells[4, 2] = fuzzy.n;
            excelWorkSheet.Cells[5, 1] = "Počet proměnných";    excelWorkSheet.Cells[5, 2] = fuzzy.p;
            excelWorkSheet.Cells[7, 1] = "Nastavení";
            excelWorkSheet.Cells[8, 1] = "Počet shluků";        excelWorkSheet.Cells[8, 2] = fuzzy.k;
            excelWorkSheet.Cells[9, 1] = "Konstanta m";         excelWorkSheet.Cells[9, 2] = fuzzy.m;
            excelWorkSheet.Cells[1, 1].Font.Bold = true;
            excelWorkSheet.Cells[7, 1].Font.Bold = true;
            Excel.Range horni = (Excel.Range)excelWorkSheet.Cells[1, 1];
            Excel.Range dolni = (Excel.Range)excelWorkSheet.Cells[9, 1];
            range = excelWorkSheet.get_Range(horni, dolni);
            range.EntireColumn.AutoFit();
        }
        private void vypisPrvkyShluku(List<HashSet<int>> u)
        {
            Excel.Range vlevoHore;
            Excel.Range vpravoDule;
            excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Add();
            excelWorkSheet.Name = "Shluky";
            for (int i = 0; i < u.Count; i++)
            {
                int[] prvky = new int[u[i].Count];
                int j = 0;
                foreach (int item in u[i])
                {
                    prvky[j] = item + 1;
                    j++;
                }
                vlevoHore = (Excel.Range)excelWorkSheet.Cells[i + 1, 2];
                vpravoDule = (Excel.Range)excelWorkSheet.Cells[i + 1, prvky.Length + 1];
                range = excelWorkSheet.get_Range(vlevoHore, vpravoDule);
                range.Value2 = prvky;
            }
        }
        private void vypisPrehledKMeans(Excel.Worksheet excelWorkSheet, SAKMeans kMeans)
        {
            excelWorkSheet.Name = "Přehled";
            excelWorkSheet.Cells[1, 1] = "Přehled";
            excelWorkSheet.Cells[2, 1] = "Hodnota obj. funkce"; excelWorkSheet.Cells[2, 2] = kMeans.J;
            excelWorkSheet.Cells[3, 1] = "Počet iterací"; excelWorkSheet.Cells[3, 2] = kMeans.iteraci;
            excelWorkSheet.Cells[4, 1] = "Počet scénářů"; excelWorkSheet.Cells[4, 2] = kMeans.n;
            excelWorkSheet.Cells[5, 1] = "Počet proměnných"; excelWorkSheet.Cells[5, 2] = kMeans.p;
            excelWorkSheet.Cells[7, 1] = "Nastavení";
            excelWorkSheet.Cells[8, 1] = "Počet shluků"; excelWorkSheet.Cells[8, 2] = kMeans.k;
            excelWorkSheet.Cells[1, 1].Font.Bold = true;
            excelWorkSheet.Cells[7, 1].Font.Bold = true;
            Excel.Range horni = (Excel.Range)excelWorkSheet.Cells[1, 1];
            Excel.Range dolni = (Excel.Range)excelWorkSheet.Cells[8, 1];
            range = excelWorkSheet.get_Range(horni, dolni);
            range.EntireColumn.AutoFit();
        }
        private void Siluety(SAKMeans kMeans)
        {
            
        }
    }
}
