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

namespace iDiTect.Excel.Demo
{
    public static class SortHelper
    {
        public static void SortColumn()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("SortColumn");

            //Input some data to column B
            worksheet.Cell("B1").Value = 9.01;
            worksheet.Cell("B2").Value = null;
            worksheet.Cell("B3").Value = 16;
            worksheet.Cell("B5").Value = 9;
            worksheet.Cell("B6").Value = "";

            //Set cell background color, to view more intuitive change after sorting
            worksheet.Cell("B1").Style.Fill.BackgroundColor = ExcelColor.Red;
            worksheet.Cell("B2").Style.Fill.BackgroundColor = ExcelColor.Green;
            worksheet.Cell("B3").Style.Fill.BackgroundColor = ExcelColor.Blue;
            worksheet.Cell("B4").Style.Fill.BackgroundColor = ExcelColor.Orange;
            worksheet.Cell("B5").Style.Fill.BackgroundColor = ExcelColor.Pink;
            worksheet.Cell("B6").Style.Fill.BackgroundColor = ExcelColor.Gray;

            //Sort column B by descending, default is Ignore Blanks, Ignore Case
            var column = worksheet.Column(2);
            column.Sort(SortOrder.Descending);

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

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

            //Input data to column A and B
            worksheet.Cell("A1").Value = "A";
            worksheet.Cell("A2").Value = "a";
            worksheet.Cell("A3").Value = "A";
            worksheet.Cell("A4").Value = "a";
            worksheet.Cell("A5").Value = "a";
            worksheet.Cell("A6").Value = "A";

            worksheet.Cell("B1").Value = "a";
            worksheet.Cell("B2").Value = "A";
            worksheet.Cell("B3").Value = "a";
            worksheet.Cell("B4").Value = "b";
            worksheet.Cell("B5").Value = "B";
            worksheet.Cell("B6").Value = "b";

            //Get range by used cells
            var range = worksheet.Range("A1:B6");

            //Add sort rule to column 1, sorting by ascending, Ignore Blanks, Match Case
            range.SortColumns.Add(1, SortOrder.Ascending, true, true);
            //Add sort rule to column 2, sorting by descending, Ignore Blanks, Ignore Case
            range.SortColumns.Add(2, SortOrder.Descending, true, false);
            //Sort ranges by rules defined above
            range.Sort();

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

        public static void SortTable()
        {
            //Fill data in DataTable
            DataTable dt = new DataTable("Fruit Sales");
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Sale", typeof(double));
            dt.Columns.Add("OrderDate", typeof(DateTime));

            dt.Rows.Add(new object[] { "Orange", 63, "2000-01-01" });
            dt.Rows.Add(new object[] { "Apple", 70, "2000-01-01" });
            dt.Rows.Add(new object[] { "Banana", 83, "2000-01-02" });
            dt.Rows.Add(new object[] { "Pear", 43, "2000-01-02" });
            dt.Rows.Add(new object[] { "Grape", 17, "2000-01-03" });
            dt.Rows.Add(new object[] { "Watermelon", 55, "2000-01-03" });

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

            //Create table from DataTable
            var table = worksheet.Cell(1, 1).InsertTable(dt);


            ///Following 3 sorting ways will get the same sorting result, 
            ///column 'OrderDate' Descending. column 'Sale' Ascending, default is Ignore Blanks, Ignore Case
            
            //Sort table column by column name
            table.Sort("OrderDate Desc, Sale Asc");

            //Sort table column by column id
            //table.Sort("3 Dese, 2 Asc");

            //Sort table by adding SortColumns
            //table.SortColumns.Add(3, SortOrder.Descending);
            //table.SortColumns.Add(2, SortOrder.Ascending);
            //table.Sort();
            
            worksheet.Columns().AutoFitContents();

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

       


    }
}
