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

namespace iDiTect.Excel.Demo
{
    public static class DataImportHelper
    {
        public static void ImportDataFromArrays()
        {
            //Create a data array
            var arrayData = new List<object[]>()
            {
                new object[] {"Name", "BuyIn", "SaleOut", "OrderDate"},
                new object[] {"Orange", 63, 45, "2000-04-25"},
                new object[] {"Apple", 89, null, "2000-01-12"},
                new object[] {"Banana", 67, 83, "2000-05-21"},
                new object[] {"Pear", 21, 43, null},
                new object[] {"Grape", null, 17, "2000-02-03"},
                new object[] {"Watermelon", 33, 55, "2000-03-09"},
            };

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

            //Insert arrays to worksheet cells
            var dataRange = worksheet.Cell("A1").InsertData(arrayData);
            dataRange.Style.Alignment.Horizontal = AlignmentHorizontalValues.Center;
            dataRange.Style.Alignment.Vertical = AlignmentVerticalValues.Center;

            worksheet.Cells("D2:D7").Style.NumberFormat.Format = "yyyy-mm-dd";
            worksheet.Columns().AutoFitContents();

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

        public static void ImportDataFromDataTable()
        {
            //Create a data table
            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(long));
            dt.Columns.Add("Tuesday", typeof(long));
            dt.Columns.Add("Wednesday", typeof(long));
            dt.Columns.Add("Thursday", typeof(long));
            dt.Columns.Add("Friday", typeof(long));
            dt.Columns.Add("Saturday ", typeof(long));
            dt.Columns.Add("Sunday", typeof(long));

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

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

            //The data inserted will not show column title, only show data in the DataTable
            worksheet.Cell("A1").InsertData(dt);
            //If you want to show column title, try to use InsertTable() to instead
            //var table = worksheet.Cell("A1").InsertTable(dt);
            //table.Theme = TableTheme.None;
            //table.ShowAutoFilter = false;

            worksheet.Columns().AutoFitContents();

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


        public static void ImportDataFromIEnumerable()
        {
            //Creat data from list of customized object
            List<Product> fruits = new List<Product>();
            var names = new string[] { "Orange", "Apple", "Banana", "Pear", "Grape", "Watermelon" };
            Random r = new Random();
            for (int i = 0; i < 50; i++)
            {
                fruits.Add(
                    new Product()
                    {
                        Name = names[r.Next(6)],
                        BuyIn = r.Next(50, 200),
                        SaleOut = r.Next(50, 200),
                        OrderDate = new DateTime(2000, 1, 1).AddDays(r.Next(1000)),
                    });
            }

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

            //Insert IEnumerable<T> to worksheet cells
            worksheet.Cell("A1").InsertData(fruits);
            worksheet.Cells("B1:B50").Style.NumberFormat.Format = "#,##0";
            worksheet.Cells("C10:C50").Style.NumberFormat.Format = "$ #,##0.00";
            worksheet.Cells("D20:D50").Style.NumberFormat.Format = "yyyy-mm-dd";

            worksheet.Columns().AutoFitContents();

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



        public class Product
        {
            public string Name { get; set; }
            public int BuyIn { get; set; }
            public int SaleOut { get; set; }
            public DateTime OrderDate { get; set; }
        }
    }
}
