using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace iDiTect.Excel.Demo
{
    public static class TableHelper
    {
        public static void AddTableFromDataSource()
        {
            var dt = GetDataTable();

            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("Table");

            //Create a table from datatable, the data can also be from IEnumerable<T>
            var table = worksheet.Cell(1, 1).InsertTable(dt);

            //Choose table style
            table.Theme = TableTheme.Light1;
            //Showing additional end row
            table.ShowTotalsRow = true;
            //Customize Totals Row Label
            table.Field(0).TotalsRowLabel = "Income";
            //Set table functions
            table.Field(1).TotalsRowFunction = TotalsRowFunction.Sum;
            table.Field("Tuesday").TotalsRowFunction = TotalsRowFunction.Average;
            //Set table formula
            table.Field("Wednesday").TotalsRowFormula = "Max(D2:D7)";

            
            worksheet.Columns("B,C,D").Style.NumberFormat.Format = "$ #,##0";
            worksheet.Columns().AutoFitContents();

            workbook.Save("Table.xlsx");

        }

        public static void AddTableFromRange()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("Table");

            //Assume you already have a sheet filled with data
            //here we input the data to cells manually
            worksheet.Cell("A1").Value = "Name";
            worksheet.Cell("A2").Value = "Orange";
            worksheet.Cell("A3").Value = "Apple";
            worksheet.Cell("A4").Value = "Banana";

            worksheet.Cell("B1").Value = "Price";
            worksheet.Cell("B2").Value = 12;
            worksheet.Cell("B3").Value = 16;
            worksheet.Cell("B4").Value = 9;

            worksheet.Cell("C1").Value = "Quantity";
            worksheet.Cell("C2").Value = 50;
            worksheet.Cell("C3").Value = 64;
            worksheet.Cell("C4").Value = 72;


            var range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());
            var table = range.CreateTable();
            //modify the table
            //...

            workbook.Save("Table2.xlsx");
        }



        private static DataTable GetDataTable()
        {
            string[] fruits = { "Orange", "Apple", "Banana", "Pear", "Grape", "Watermelon" };
            DataTable dt = new DataTable("Fruit Sales");
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Monday", typeof(double));
            dt.Columns.Add("Tuesday", typeof(double));
            dt.Columns.Add("Wednesday", typeof(double));
            dt.Columns.Add("Thursday", typeof(double));
            dt.Columns.Add("Friday", typeof(double));
            dt.Columns.Add("Saturday ", typeof(double));
            dt.Columns.Add("Sunday", typeof(double));

            Random r = new Random();
            for (int row = 0; row < 6; row++)
            {
                var dr = dt.NewRow();
                dr[0] = fruits[row];

                for (int col = 1; col < 8; col++)
                {
                    dr[col] = r.Next(10, 100);
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }

    }
}
