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

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

            //Get single column by column name
            var columnA = worksheet.Column("A");
            //Set column style
            columnA.Style.Fill.BackgroundColor = ExcelColor.Red;
            //Set single column width
            columnA.Width = 50;

            //Get contiguous columns by start/end columnd id
            var columnBD = worksheet.Columns(2, 4);
            columnBD.Style.Fill.BackgroundColor = ExcelColor.Blue;

            //Get contiguous columns by column name
            var columnEF = worksheet.Columns("E:F");
            columnEF.Style.Fill.BackgroundColor = ExcelColor.Cadet;

            //Get contiguous columns by column id
            var columnGH = worksheet.Columns("7:8");
            columnGH.Style.Fill.BackgroundColor = ExcelColor.Dandelion;

            //Get spread columns by column name
            var columnIJ_LM = worksheet.Columns("I:J,L:M");
            columnIJ_LM.Style.Fill.BackgroundColor = ExcelColor.Ecru;

            //Set multi-column width
            worksheet.Columns("B:E").Width = 5;

            //Get single column by column id
            var columnG = worksheet.Column(7);
            //Insert 2 columns after current column (column G)
            var insertedColumn = columnG.InsertColumnsAfter(2);
            insertedColumn.Style.Fill.BackgroundColor = ExcelColor.Orange;

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

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

            //Get single row by row id
            var row1 = worksheet.Row(1);
            //Set row style
            row1.Style.Fill.BackgroundColor = ExcelColor.Red;
            //Set single row height
            row1.Height = 30;

            //Get contiguous rows by start/end row id
            var row24 = worksheet.Rows(2, 4);
            row24.Style.Fill.BackgroundColor = ExcelColor.Blue;

            //Get contiguous rows by row id
            var row56 = worksheet.Rows("5:6");
            row56.Style.Fill.BackgroundColor = ExcelColor.Cadet;

            //Get spread rows by row id
            var row78_1011 = worksheet.Rows("7:8,10:11");
            row78_1011.Style.Fill.BackgroundColor = ExcelColor.Dandelion;

            //Set multi-row height
            worksheet.Rows("2:5").Height = 5;
                       
            //Insert 2 rows above current row (row 7)
            var insertedRow = worksheet.Row(7).InsertRowsAbove(2);
            insertedRow.Style.Fill.BackgroundColor = ExcelColor.Orange;

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

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

            //Add some data to cells
            for (int row = 1; row < 10; row++)
            {
                for (int column = 1; column < 10; column++)
                {
                    worksheet.Cell(row, column).Value = row + "-" + column;
                }
            }

            //Select C3:E5 as a range
            var range = worksheet.Range("C3:E5");
            range.Style.Fill.BackgroundColor = ExcelColor.Red;

            //Select the second column in the range
            var columnInRange = range.Column(2);
            //Change column style
            columnInRange.Style.Font.FontSize = 20;

            //Select the second row in the range
            var rowInRange = range.Row(2);
            //Chnage row style
            rowInRange.Style.Border.OutsideBorder = BorderStyleValues.Thick;
            rowInRange.Style.Border.OutsideBorderColor = ExcelColor.Blue;
            
            worksheet.Columns().AutoFitContents();

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


    }
}
