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

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

            //Get cell by cell name
            var cellA1 = worksheet.Cell("A1");
            //Modify cell value
            cellA1.Value = "A1";
            //Set cell style
            cellA1.Style.Font.FontColor = ExcelColor.Red;

            //Get cell by row and column coordinate
            var cellB1 = worksheet.Cell(1, 2);
            cellB1.Value = "B1";

            //Get cell by row id and column name
            var cellC5 = worksheet.Cell(5, "C");
            cellC5.Value = "C5";
            cellC5.Style.Fill.BackgroundColor = ExcelColor.Red;

            //Get left cell of current cell
            var cellLeft = cellC5.CellLeft();
            cellLeft.Value = "left C5";
            //Get the second cell at the right of current cell
            cellC5.CellRight(2).Value = "the second cell at right of C5 (E5)";
            cellC5.CellAbove().Value = "above C5";
            cellC5.CellBelow(2).Value = "the second cell at below of C5 (C7)";

            worksheet.Columns().AutoFitContents();

            workbook.Save("ModifyCells.xlsx");
        }
        
        public static void ModifyRange()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ModifyRange");

            //Select A2 as a Range
            var range1 = worksheet.Range("A2");
            range1.Value = "this is long text";

            //Get a Range from start/stop row and column id
            var range2 = worksheet.Range(3, 3, 5, 5);
            //Input value to cell located in the first column and row of range
            range2.Cell(1, 1).Value = "merged from C3:E5";
            //Merge all cells in the range
            range2.Merge();
            //Set range alignment style
            range2.Style.Alignment.Horizontal = AlignmentHorizontalValues.Center;
            range2.Style.Alignment.Vertical = AlignmentVerticalValues.Center;

            //Get a Range from start/stop cell name
            var range3 = worksheet.Range("A6:B8");
            //Input value to cell located in the second row and first column in the range
            range3.Cell(2, 1).Value = "value in the range";
            //Set range border style
            range3.Style.Border.OutsideBorder = BorderStyleValues.Thick;
            range3.Style.Border.OutsideBorderColor = ExcelColor.Red;
            
            worksheet.Column(1).AutoFitContents();
                       
            workbook.Save("ModifyRange.xlsx");
        }

        public static void NamedRange()
        {
            var workbook = new Workbook();

            var worksheet1 = workbook.Worksheets.Add("Sheet1");
            var worksheet2 = workbook.Worksheets.Add("Sheet2");

            //Input some data to sheet1
            worksheet1.Cell("A1").Value = "Name";
            worksheet1.Cell("A2").Value = "Orange";
            worksheet1.Cell("A3").Value = "Apple";
            worksheet1.Cell("A4").Value = "Banana";

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

            //Apply a named range for workbook
            var header = workbook.NamedRanges.Add("Header", worksheet1.Range("A1:B1"));
            //Change named range style
            header.Ranges.Style.Fill.BackgroundColor = ExcelColor.Red;

            //Apply a named range for workshhet1, this named range can't be used in worksheet2
            worksheet1.NamedRanges.Add("HeaderAndData", worksheet1.Range("A1:B4"));
            //Format target named range to new style
            worksheet1.NamedRanges.NamedRange("HeaderAndData").Ranges.Style.Font.FontSize = 20;

            //Apply a named range for current worksheet
            worksheet1.Range("A2:A4").AddToNamed("FruitName", Scope.Worksheet);

            //Apply a named range for workbook
            worksheet1.Range("B2:B4").AddToNamed("FruitPrice", Scope.Workbook);

            //Use named range in forula           
            worksheet2.Cell("A1").Formula = "AVERAGE(FruitPrice)";
                       
            //Use named range in value
            worksheet2.Cell("A3").Value = worksheet1.Range("HeaderAndData");

            worksheet1.Columns().AutoFitContents();
            worksheet2.Columns().AutoFitContents();

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

        public static void TransposeRange()
        {
            var workbook = new Workbook();

            var worksheet = workbook.Worksheets.Add("TransposeRange");

            //Input some data to sheet1
            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;

            //Select a range contains the data you want to transpose
            var range = worksheet.Range("A1:B4");
            range.Transpose(TransposeOptions.MoveCells);

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