using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using Spread11Test.Properties;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Threading;
using System.Windows.Forms;

namespace Spread11Test
{
    public partial class Form1 : Form
    {
        public const String Co = "Co";
        public const String Sp = "Sp";
        private List<CellProperty> _cellProperties;
        private Dictionary<String, List<Int32>> _dicAccountRowContoControverso;
        private Dictionary<Row, Descriptor> _dicRowDescriptor;
        private Color _grigioChiaro;
        private Stopwatch _sw;
        private Int32 _year;

        public Form1()
        {
            Cursor.Current = Cursors.WaitCursor;

            InitializeComponent();
            InitializeVars();
            InitializeSpread(_sw);
            GetSpreadCellProperties();
            SetSpreadVisibilityRows();
            GC.Collect();

            Cursor.Current = Cursors.Default;
        }

        private enum ColGr
        {
            ContoUtente = 0,
            ContoUtenteDescrizione = 1,
            Expand = 2,
            ContoGb = 3,
            ContoGbDescrizione = 4,
            CeeD = 5,
            CeeA = 6,
            Pe = 7,
            Apcr = 8,
            Iidd = 9,
            Irap = 10,
            AnnoCorrDare = 11,
            AnnoCorrAvere = 12,
            AnnoCorrRip = 13,
            AnnoCorrPar = 14,
            AnnoPrecDare = 15,
            AnnoPrecAvere = 16,
            AnnoPrecRip = 17,
            AnnoPrecPar = 18,
            Tipo = 19,
            CliFor = 20,
            AttivoPassivo = 21,
            TagFormula = 22,
            Query = 23,
            Albero = 24,
            Tab = 25
        }

        private enum Utils
        {
            DareAvere,
            AvereDare
        }

        private Dictionary<Row, Descriptor> DicRowCliFor
        {
            get
            {
                return _dicRowDescriptor
                    .Where(x => (x.Value.RowProperties.CliFor == 1 || x.Value.RowProperties.CliFor == 2) && x.Value.RowProperties.Tipo == 1)
                    .Reverse()
                    .ToDictionary(x => x.Key, y => y.Value);
            }
        }

        private Dictionary<Row, Descriptor> DicRowTipo1
        {
            get
            {
                return _dicRowDescriptor
                    .Where(x => x.Value.RowProperties.Tipo == 1)
                    .Reverse()
                    .ToDictionary(x => x.Key, y => y.Value);
            }
        }

        private Dictionary<Row, Descriptor> DicRowTipo2
        {
            get
            {
                return _dicRowDescriptor
                    .Where(x => x.Value.RowProperties.Tipo == 2)
                    .Reverse()
                    .ToDictionary(x => x.Key, y => y.Value);
            }
        }

        private Dictionary<Row, Descriptor> DicRowTipo2A
        {
            get
            {
                return DicRowTipo2
                    .Where(x => x.Value.RowProperties.AttivoPassivo == "A")
                    .Reverse()
                    .ToDictionary(x => x.Key, y => y.Value);
            }
        }

        private Dictionary<Row, Descriptor> DicRowTipo2P
        {
            get
            {
                return DicRowTipo2
                    .Where(x => x.Value.RowProperties.AttivoPassivo == "P")
                    .Reverse()
                    .ToDictionary(x => x.Key, y => y.Value);
            }
        }

        private Int32 FormatRowIndex
        {
            get
            {
                var row = _dicRowDescriptor
                    .ToList()
                    .Find(x => x.Value.RowProperties.Tipo == 9)
                    .Key;

                return row == null
                    ? 0
                    : row.Index;
            }
        }

        public static ButtonCellType CellTypeButton()
        {
            var result = new ButtonCellType
            {
                Text = " ...",
                TextOrientation = TextOrientation.TextHorizontal
            };

            return result;
        }

        public static ImageCellType CellTypeImage()
        {
            return new ImageCellType(RenderStyle.Normal);
        }

        public static NumberCellType CellTypeNumber(Int32 decimalPlaces, Int32 pow, Boolean negative = false)
        {
            var result = new NumberCellType
            {
                ShowSeparator = true,
                DecimalPlaces = decimalPlaces,
                DecimalSeparator = ",",
                MaximumValue = Math.Pow(10, pow),
                Separator = "."
            };

            if (negative) result.NegativeFormat = NegativeFormat.NegativeSignBefore;
            else result.MinimumValue = 0;

            return result;
        }

        public static PercentCellType CellTypePercent(Int32 decimalPlaces, Boolean negative = false)
        {
            var result = new PercentCellType
            {
                ShowSeparator = true,
                DecimalPlaces = decimalPlaces,
                DecimalSeparator = ","
            };

            if (negative) result.NegativeFormat = PercentNegativeFormat.PercentBefore;

            return result;
        }

        public static TextCellType CellTypeText()
        {
            return new TextCellType();
        }

        private static MemoryStream DecompressFile(Byte[] file)
        {
            using (var source = new MemoryStream(file))
            {
                return DecompressFile(source);
            }
        }

        private static MemoryStream DecompressFile(MemoryStream stream)
        {
            var memStreamRes = new MemoryStream();
            using (var decomStream = new MemoryStream(stream.ToArray()))
            {
                decomStream.Position = 0;
                using (var zipStream = new GZipStream(decomStream, CompressionMode.Decompress))
                {
                    zipStream.CopyTo(memStreamRes);
                }
            }

            memStreamRes.Position = 0;
            return memStreamRes;
        }

        private static String GetAlphabetCharFromIndex(ColGr index, Boolean startFromZero = false)
        {
            return (index.ToInt() + (startFromZero ? 1 : 0) + 64).ToChar().ToString();
        }

        private static Int32 GetRowIndexContoControverso(ContoControversoInfo cc, Dictionary<Row, Descriptor> dic)
        {
            var startRow = dic.Keys.ToList().Find(row => row.Index == cc.StartRow);
            var otherRow = dic.Keys.ToList().Find(row => row.Index == cc.OtherRow);
            var otherRowIndex = otherRow != null ? otherRow.Index : 0;
            var startRowIndex = startRow != null ? startRow.Index : 0;

            return startRowIndex != 0
                ? startRowIndex
                : otherRowIndex != 0
                    ? otherRowIndex
                    : 0;
        }

        private Row FirstVisibleRow()
        {
            return spread.Sheets[0].Rows.OfType<Row>().ToList()
                .OrderBy(x => x.Index)
                .ToList()
                .Find(row => row.Visible);
        }

        private void Form1_Shown(Object sender, EventArgs e)
        {
            _sw.Stop();
            MessageBox.Show(String.Format("Loading time: {0}s", _sw.ElapsedMilliseconds / 1000));
        }

        private ContoControversoInfo GetContoControversoInfo(Int32 rowIndex)
        {
            var rowIndex1 = _dicAccountRowContoControverso[GetContoSelezionato(rowIndex)][0];
            var rowIndex2 = _dicAccountRowContoControverso[GetContoSelezionato(rowIndex)][1];

            var startRow = 0;
            var otherRow = 0;
            if (rowIndex1 == rowIndex)
            {
                startRow = rowIndex1;
                otherRow = rowIndex2;
            }
            else if (rowIndex2 == rowIndex)
            {
                startRow = rowIndex2;
                otherRow = rowIndex1;
            }

            return new ContoControversoInfo(startRow, otherRow);
        }

        private String GetContoSelezionato(Int32 row = 0)
        {
            var rowIndex = row == 0
                ? spread.ActiveSheet.ActiveRow.Index
                : row;

            return spread.GbCell(rowIndex, ColGr.ContoGb).Text;
        }

        private Dictionary<Row, Descriptor> GetDicQuery()
        {
            var listaQuery = _dicRowDescriptor
                .Where(
                    x =>
                        x.Value.RowProperties.Query.IsStringNotNullOrEmpty() &&
                        x.Value.RowProperties.Tipo.ToInt() == 1 &&
                        x.Value.RowProperties.Tab != Co.ToUpper())
                .Reverse() //N.B.
                .ToDictionary(x => x.Key, y => y.Value);

            return listaQuery;
        }

        private Int32 GetFatherRow(Int32 rowIndex)
        {
            return DicRowTipo1.Keys.Select(x => x.Index).Where(x => x <= rowIndex).Max();
        }

        private Interval GetFormulaInterval(Interval interval, Int32 rowIndex)
        {
            if (interval.End == 0 &&
                spread.GbCell(rowIndex, ColGr.Tipo).Text == 2.ToString() && (
                    spread.GbCell(rowIndex, ColGr.CliFor).Text == 1.ToString() ||
                    spread.GbCell(rowIndex, ColGr.CliFor).Text == 2.ToString()))
            {
                if (interval.Start == 0) { interval.Start = rowIndex + 1; }
                GetFormulaInterval(interval, ++rowIndex);
            }
            else if ((spread.GbCell(rowIndex - 1, ColGr.CliFor).Text == 1.ToString() || spread.GbCell(rowIndex - 1, ColGr.CliFor).Text == 2.ToString()) &&
                     spread.GbCell(rowIndex, ColGr.CliFor).Text.IsStringNullOrEmpty())
            {
                if (interval.End == 0) { interval.End = rowIndex; }
            }
            else
            {
                GetFormulaInterval(interval, ++rowIndex);
            }

            return interval;
        }

        private Dictionary<String, CoppiaPiacoAnno> GetQueryResult(Int32 anno)
        {
            var dicAnnoQueryResult = new Dictionary<Int32, List<ContPiaco>>();
            for (var year = anno; year >= anno - 1; year--)
            {
                var random = new Random();
                var queryResult = new List<ContPiaco>();

                //TODO @GrapeCity: 100 identify the section of the spread, (one section is one father row). Each section can have a random number of son rows
                for (var i = 0; i < 100; i++)
                {
                    var cp = new ContPiaco
                    {
                        APCR = random.Next(500, 599).ToString(),
                        CEEA = random.Next(600, 699).ToString(),
                        CEED = random.Next(700, 799).ToString(),
                        Descrizione = "",
                        PE = random.Next(1, 2) == 1 ? "P" : "E",
                        Conto = random.Next(1000, 10000).ToString(),
                        ValRiapertura = random.Next(-5000, 5000)
                    };
                    queryResult.Add(cp);
                }

                dicAnnoQueryResult.Add(year, queryResult);
            }

            var listaContiAnnoCorr = dicAnnoQueryResult[_year].Select(x => x.Conto).ToList();
            var listaContiAnnoPrec = dicAnnoQueryResult[_year - 1].Select(x => x.Conto).ToList();
            var listaContiAmboAnni = new List<String>();
            var listaCoppie = new List<CoppiaPiacoAnno>();

            listaContiAmboAnni.AddRange(listaContiAnnoPrec.Intersect(listaContiAnnoCorr));
            listaContiAmboAnni.AddRange(listaContiAnnoCorr.Except(listaContiAnnoPrec));
            listaContiAmboAnni.AddRange(listaContiAnnoPrec.Except(listaContiAnnoCorr));

            foreach (var conto in listaContiAmboAnni)
            {
                var piacoAnnoCorr = new PiacoAnno(_year);
                var piacoAnnoPrec = new PiacoAnno(_year - 1);

                piacoAnnoCorr.ContPiaco = dicAnnoQueryResult[_year].Find(x => x.Conto == conto);
                piacoAnnoPrec.ContPiaco = dicAnnoQueryResult[_year - 1].Find(x => x.Conto == conto);
                listaCoppie.Add(new CoppiaPiacoAnno(piacoAnnoCorr, piacoAnnoPrec));
            }

            var result = new Dictionary<String, CoppiaPiacoAnno>();
            foreach (var coppia in listaCoppie)
            {
                if (coppia.PiacoAnnoCorr.ContPiaco != null)
                {
                    result.Add(coppia.PiacoAnnoCorr.ContPiaco.Conto, coppia);
                }
                else if (coppia.PiacoAnnoPrec.ContPiaco != null)
                {
                    result.Add(coppia.PiacoAnnoPrec.ContPiaco.Conto, coppia);
                }
            }

            return result.OrderBy(x => x.Key).ToDictionary(x => x.Key, y => y.Value);
        }

        private void GetSpreadCellProperties()
        {
            spread.GbCell(FormatRowIndex, ColGr.AnnoCorrDare).BackColor = Color.LightYellow;
            spread.GbCell(FormatRowIndex, ColGr.AnnoCorrAvere).BackColor = Color.LightYellow;
            spread.GbCell(FormatRowIndex, ColGr.AnnoPrecDare).BackColor = Color.LightYellow;
            spread.GbCell(FormatRowIndex, ColGr.AnnoPrecAvere).BackColor = Color.LightYellow;

            foreach (Column col in spread.GbCols())
            {
                var colIndex = col.Index;
                _cellProperties.Add(new CellProperty
                {
                    Index = colIndex,
                    BackColor = spread.GbCell(FormatRowIndex, colIndex).BackColor,
                    Border = spread.GbCell(FormatRowIndex, colIndex).Border,
                    CellType = spread.GbCell(FormatRowIndex, colIndex).CellType,
                    Font = spread.GbCell(FormatRowIndex, colIndex).Font,
                    ForeColor = spread.GbCell(FormatRowIndex, colIndex).ForeColor,
                });
            }
        }

        private void gr_CellClick(Object sender, CellClickEventArgs e)
        {
            var rowIndex = spread.ActiveSheet.ActiveRowIndex;

            if (ModifierKeys == Keys.Alt && IsContoControverso(rowIndex))
            {
                const Int32 offset = 0;

                var ccInfo = GetContoControversoInfo(rowIndex);
                var selRow = ccInfo.OtherRow - offset;

                spread.SetViewportTopRow(0, selRow);
                if (!spread.GbRow(selRow + offset).Visible) { MouseLeftButtonClick(GetFatherRow(selRow + offset)); }
                spread.GbSelection(selRow + offset, 0, 1, spread.GbCols().Count);
                spread.GbSetActiveCell(selRow, spread.GbActiveColIndex());

                return;
            }

            switch (e.Button)
            {
                case MouseButtons.Left: MouseLeftButtonClick(e.Row); break;
            }
        }

        private void InitializeSpread(Stopwatch sw)
        {
            var dr = MessageBox.Show(this,
                "Load the working sample?",
                "Sample",
                MessageBoxButtons.YesNo,
                MessageBoxIcon.Question);

            if (dr == DialogResult.Yes)
            {
                sw.Start();
                using (var stream = DecompressFile(Resources.CeeAnaliticoMicroimprese_1_xml))
                {
                    spread.Open(stream);
                }

                SetDicRowDescriptor(true);
            }
            else
            {
                MessageBox.Show(this,
                    "Now I will load some random fake rows to replicate the issue",
                    "Wrong sample",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Exclamation);

                sw.Start();
                using (var stream = new MemoryStream(Resources.CeeAnaMicroimprese_2016))
                {
                    spread.Open(stream);
                }

                SetDicRowDescriptor(true);
                SetContPiaco(GetDicQuery());
            }

            SetSpreadFormatRow();
            SetSpreadVisibilityCols(false);
            SetSpreadHeader();
            SetSpreadProperties();

            SetDicRowDescriptor(true);
            SetSpreadLockFormulaCellType();
            SetSpreadFormula();

            var emptyRows = DicRowTipo2.Keys.Where(row => spread.GbCell(row.Index, ColGr.ContoGb).Text.Trim().IsStringNullOrEmpty()).ToList();
            foreach (var row in emptyRows) { spread.Sheets[0].RemoveRows(row.Index, 1); }
            SetDicRowDescriptor(true);

            spread.ActiveSheet.RowHeader.Columns[0].Width = 50;
            spread.Sheets[0].FrozenRowCount = 2;
            spread.Sheets[0].OperationMode = OperationMode.RowMode;
            spread.RowSplitBoxPolicy = SplitBoxPolicy.Never;
            spread.ColumnSplitBoxPolicy = SplitBoxPolicy.Never;
            spread.Sheets[0].RowHeaderVisible = true;
            spread.Sheets[0].ColumnHeaderVisible = true;
        }

        private void InitializeVars()
        {
            _year = 2016;
            _grigioChiaro = Color.FromArgb(224, 224, 224);
            _dicRowDescriptor = new Dictionary<Row, Descriptor>();
            _dicAccountRowContoControverso = new Dictionary<String, List<Int32>>();
            _cellProperties = new List<CellProperty>();
            _sw = new Stopwatch();
        }

        private void InsertContPiaco(ContPiacoInsertRequest r)
        {
            var dicPiacoPerQuery = r.DicPiacoPerQuery;
            var dicQueryPair = r.DicQueryPair;

            if (!dicPiacoPerQuery.Any()) { return; }
            if (spread.GbRow(dicQueryPair.Key.Index).Tag == null) { spread.GbRow(dicQueryPair.Key.Index).Tag = dicPiacoPerQuery.Count; }

            foreach (var piacoAnno in dicPiacoPerQuery.Values)
            {
                var year = 0;
                ContPiaco contPiaco = null;
                if (piacoAnno.PiacoAnnoCorr != null)
                {
                    year = piacoAnno.PiacoAnnoCorr.Anno;
                    contPiaco = piacoAnno.PiacoAnnoCorr.ContPiaco;

                    if (contPiaco == null)
                    {
                        year = piacoAnno.PiacoAnnoPrec.Anno;
                        contPiaco = piacoAnno.PiacoAnnoPrec.ContPiaco;
                    }
                }
                else if (piacoAnno.PiacoAnnoPrec != null)
                {
                    year = piacoAnno.PiacoAnnoPrec.Anno;
                    contPiaco = piacoAnno.PiacoAnnoPrec.ContPiaco;
                }

                if (contPiaco == null) { return; }

                var rowIndex = dicQueryPair.Key.Index + ++r.RowCount;
                if (dicPiacoPerQuery.Count > 1 && year != _year - 1 || piacoAnno.PiacoAnnoCorr.ContPiaco == null)
                {
                    spread.Sheets[0].Rows.Add(rowIndex, 1);
                    spread.GbRow(rowIndex).Visible = false;
                }

                var svp = new SpreadValueParams
                {
                    Albero = r.Albero,
                    AttivoPassivo = r.AttivoPassivo,
                    CliFor = r.CliFor,
                    ContPiacoCorr = piacoAnno.PiacoAnnoCorr.ContPiaco,
                    ContPiacoPrec = piacoAnno.PiacoAnnoPrec.ContPiaco,
                    RowIndex = rowIndex,
                    Tab = r.Tab,
                    TagFormula = r.TagFormula,
                    Tipo = r.Tipo
                };

                SetSpreadValue(svp);
            }
        }

        private Boolean IsContoControverso(Int32 rowIndex)
        {
            return !spread.GbCell(rowIndex, ColGr.CeeD).Text.Trim().IsNullDbNullOrEmpty() &&
                   !spread.GbCell(rowIndex, ColGr.CeeA).Text.Trim().IsNullDbNullOrEmpty();
        }

        private void MouseLeftButtonClick(Int32 rowIndex)
        {
            Thread.Sleep(100);
            var count = 0;
            var rowCount =
                spread.GbRow(rowIndex).Tag != null &&
                spread.GbRow(rowIndex).Tag.ToString().IsNumeric()
                    ? spread.GbRow(rowIndex).Tag.ToInt()
                    : -1;

            while (count < rowCount)
            {
                count++;
                spread.GbRow(rowIndex + count).Visible = !spread.GbRow(rowIndex + count).Visible;
            }

            if (spread.GbRow(rowIndex + 1).Tag == null)
            {
                SetSpreadFormat(rowIndex, rowCount);
                SetSpreadContoControversoFormat();
                spread.GbRow(rowIndex + 1).Tag = "Formatted";
            }

            spread.GbCell(rowIndex, ColGr.Expand).CellType = CellTypeImage();
        }

        private void SetContPiaco(Dictionary<Row, Descriptor> dicQuery, Boolean setRowLikeSonAfterAccountAdded = false)
        {
            foreach (var dicQueryPair in dicQuery)
            {
                if (spread.GbRowCount() == dicQueryPair.Key.Index + 1) { continue; }

                InsertContPiaco(new ContPiacoInsertRequest
                {
                    Albero = spread.GbCell(dicQueryPair.Key.Index + 1, ColGr.Albero).Text,
                    AttivoPassivo = spread.GbCell(dicQueryPair.Key.Index + 1, ColGr.AttivoPassivo).Text,
                    CliFor = spread.GbCell(dicQueryPair.Key.Index, ColGr.CliFor).Text,
                    DicPiacoPerQuery = GetQueryResult(_year),
                    DicQueryPair = dicQueryPair,
                    RowCount = 0,
                    Tab = spread.GbCell(dicQueryPair.Key.Index + 1, ColGr.Tab).Text,
                    TagFormula = spread.GbCell(dicQueryPair.Key.Index + 1, ColGr.TagFormula).Text,
                    Tipo = setRowLikeSonAfterAccountAdded ? "2" : spread.GbCell(dicQueryPair.Key.Index + 1, ColGr.Tipo).Text
                });
            }
        }

        private void SetDicRowDescriptor(Boolean forceUpdate)
        {
            if (_dicRowDescriptor.Count == 0 || forceUpdate)
            {
                _dicRowDescriptor.Clear();
                foreach (Row row in spread.GbRows())
                {
                    var rowIndex = row.Index;
                    if (rowIndex > 0 &&
                        spread.Sheets[0].Cells[rowIndex, (Int32)ColGr.Tipo].Text.IsStringNotNullOrEmpty() &&
                        !spread.GbCell(rowIndex, ColGr.Tipo).Text.Contains("Intestazione") &&
                        (spread.GbCell(rowIndex, ColGr.Tipo).Text.ToInt() == 0 ||
                         spread.GbCell(rowIndex, ColGr.Tipo).Text.ToInt() == 1 ||
                         spread.GbCell(rowIndex, ColGr.Tipo).Text.ToInt() == 2 ||
                         spread.GbCell(rowIndex, ColGr.Tipo).Text.ToInt() == 9))
                    {
                        var rowProperties = new RowProperty
                        {
                            AttivoPassivo = spread.GbCell(rowIndex, ColGr.AttivoPassivo).Text,
                            BackColorInput = spread.GbCell(rowIndex, ColGr.AnnoCorrDare).BackColor,
                            CliFor = spread.GbCell(rowIndex, ColGr.CliFor).Text.IsNumeric()
                                ? spread.GbCell(rowIndex, ColGr.CliFor).Text.ToInt()
                                : 0,
                            Conto = spread.GbCell(rowIndex, ColGr.ContoGb).Text,
                            Query = spread.GbCell(rowIndex, ColGr.Query).Text,
                            Tab = spread.GbCell(rowIndex, ColGr.Tab).Text,
                            Tipo = spread.GbCell(rowIndex, ColGr.Tipo).Text.ToInt()
                        };

                        _dicRowDescriptor.Add(row, new Descriptor { RowProperties = rowProperties });
                    }
                }
            }
        }

        private void SetSpreadContoControversoColors(Int32 rowIndex, Int32 colIndex)
        {
            var contoSelezionatoAssente = spread.GbCell(rowIndex, colIndex).Tag != null && !spread.GbCell(rowIndex, colIndex).Tag.Equals("ContoNonPresente");
            if (!_dicAccountRowContoControverso.ContainsKey(GetContoSelezionato(rowIndex)) || contoSelezionatoAssente) return;

            var cc = GetContoControversoInfo(rowIndex);
            var ap = spread.GbCell(cc.OtherRow, ColGr.AttivoPassivo).Text;
            switch (ap)
            {
                case "A":
                    if (colIndex == ColGr.AnnoCorrDare.ToInt() || colIndex == ColGr.AnnoCorrAvere.ToInt())
                    {
                        if (!spread.GbCell(cc.StartRow, ColGr.AnnoCorrAvere).Value.ToDoubleRound2().Equals(0d))
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).BackColor = _grigioChiaro;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Locked = true;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Tag = "ContoControverso";
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Value = null;
                        }
                        else
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).BackColor = Color.LightYellow;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Locked = false;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Tag = null;
                            if (spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Value == null) { spread.GbCell(cc.OtherRow, ColGr.AnnoCorrDare).Value = 0d; }
                        }
                    }

                    if (colIndex == ColGr.AnnoPrecDare.ToInt() || colIndex == ColGr.AnnoPrecAvere.ToInt())
                    {
                        if (!spread.GbCell(cc.StartRow, ColGr.AnnoPrecAvere).Value.ToDoubleRound2().Equals(0d))
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).BackColor = _grigioChiaro;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Locked = true;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Tag = "ContoControverso";
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Value = null;
                        }
                        else
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).BackColor = Color.LightYellow;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Locked = false;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Tag = null;
                            if (spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Value == null) { spread.GbCell(cc.OtherRow, ColGr.AnnoPrecDare).Value = 0d; }
                        }
                    }
                    break;

                case "P":
                    if (colIndex == ColGr.AnnoCorrDare.ToInt() || colIndex == ColGr.AnnoCorrAvere.ToInt())
                    {
                        if (!spread.GbCell(cc.StartRow, ColGr.AnnoCorrDare).Value.ToDoubleRound2().Equals(0d))
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).BackColor = _grigioChiaro;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Locked = true;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Tag = "ContoControverso";
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Value = null;
                        }
                        else
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).BackColor = Color.LightYellow;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Locked = false;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Tag = null;
                            if (spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Value == null) { spread.GbCell(cc.OtherRow, ColGr.AnnoCorrAvere).Value = 0d; }
                        }
                    }

                    if (colIndex == ColGr.AnnoPrecDare.ToInt() || colIndex == ColGr.AnnoPrecAvere.ToInt())
                    {
                        if (!spread.GbCell(cc.StartRow, ColGr.AnnoPrecDare).Value.ToDoubleRound2().Equals(0d))
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).BackColor = _grigioChiaro;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Locked = true;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Tag = "ContoControverso";
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Value = null;
                        }
                        else
                        {
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).BackColor = Color.LightYellow;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Locked = false;
                            spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Tag = null;
                            if (spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Value == null) { spread.GbCell(cc.OtherRow, ColGr.AnnoPrecAvere).Value = 0d; }
                        }
                    }
                    break;
            }
        }

        private void SetSpreadContoControversoFormat(Int32 singleRowIndex = 0)
        {
            //LC 03/03/2017: se non è una riga di tipo Patrimoniale non può essere un ContoControverso
            if (singleRowIndex != 0 && spread.GbCell(singleRowIndex, ColGr.Pe).Text != "P") { return; }

            if (singleRowIndex == 0) //formattazione multipla per conti multipli
            {
                //si scorrono le righe "Attive" della griglia colorando di "grigio" le celle corrispondenti alla colonna "AnnoAvere" e "AnnoPrecedenteAvere"
                foreach (var row in DicRowTipo2A.Keys)
                {
                    var rowIndex = row.Index;
                    if (!spread.GbRow(rowIndex).Visible) { continue; }
                    SetSpreadContoControversoFormatSingle(rowIndex, Utils.DareAvere);
                    SetSpreadContoControversoColors(rowIndex, ColGr.AnnoCorrAvere.ToInt());
                    SetSpreadContoControversoColors(rowIndex, ColGr.AnnoPrecAvere.ToInt());
                }

                //si scorrono le righe "Passive" della griglia colorando di "grigio" le celle corrispondenti alla colonna "AnnoDare" e "AnnoPrecedenteDare"
                foreach (var row in DicRowTipo2P.Keys)
                {
                    var rowIndex = row.Index;
                    if (!spread.GbRow(rowIndex).Visible) { continue; }
                    SetSpreadContoControversoFormatSingle(rowIndex, Utils.AvereDare);
                    SetSpreadContoControversoColors(rowIndex, ColGr.AnnoCorrDare.ToInt());
                    SetSpreadContoControversoColors(rowIndex, ColGr.AnnoPrecDare.ToInt());
                }
            }
            else //formattazione singola riga per conti singoli
            {
                if (!IsContoControverso(singleRowIndex)) return;
                var cc = GetContoControversoInfo(singleRowIndex);

                var rowIndex = GetRowIndexContoControverso(cc, DicRowTipo2A);
                SetSpreadContoControversoFormatSingle(rowIndex, Utils.DareAvere);
                SetSpreadContoControversoColors(rowIndex, ColGr.AnnoCorrAvere.ToInt());
                SetSpreadContoControversoColors(rowIndex, ColGr.AnnoPrecAvere.ToInt());

                rowIndex = GetRowIndexContoControverso(cc, DicRowTipo2P);
                SetSpreadContoControversoFormatSingle(rowIndex, Utils.AvereDare);
                SetSpreadContoControversoColors(rowIndex, ColGr.AnnoCorrDare.ToInt());
                SetSpreadContoControversoColors(rowIndex, ColGr.AnnoPrecDare.ToInt());
            }
        }

        private void SetSpreadContoControversoFormatSingle(Int32 rowIndex, Utils order)
        {
            //se emtrabi i raccordi CEE sono valorizzati si va avanti
            if (spread.GbCell(rowIndex, ColGr.CeeD).Text.Trim().IsStringNullOrEmpty() ||
                spread.GbCell(rowIndex, ColGr.CeeA).Text.Trim().IsStringNullOrEmpty()) return;

            var colIndex1 = 0;
            var colIndex2 = 0;
            var colIndex3 = 0;
            var colIndex4 = 0;

            switch (order)
            {
                case Utils.DareAvere:
                    colIndex1 = ColGr.AnnoCorrDare.ToInt();
                    colIndex2 = ColGr.AnnoCorrAvere.ToInt();
                    colIndex3 = ColGr.AnnoPrecDare.ToInt();
                    colIndex4 = ColGr.AnnoPrecAvere.ToInt();
                    break;

                case Utils.AvereDare:
                    colIndex1 = ColGr.AnnoCorrAvere.ToInt();
                    colIndex2 = ColGr.AnnoCorrDare.ToInt();
                    colIndex3 = ColGr.AnnoPrecAvere.ToInt();
                    colIndex4 = ColGr.AnnoPrecDare.ToInt();
                    break;
            }

            SetSpreadContoControversoFormatSingleCol(rowIndex, colIndex1, colIndex2);
            SetSpreadContoControversoFormatSingleCol(rowIndex, colIndex3, colIndex4);
        }

        private void SetSpreadContoControversoFormatSingleCol(Int32 rowIndex, Int32 colIndexDare, Int32 colIndexAvere)
        {
            spread.GbCell(rowIndex, colIndexAvere).BackColor = _grigioChiaro;
            spread.GbCell(rowIndex, colIndexAvere).Locked = true;
            spread.GbCell(rowIndex, colIndexAvere).Value = null;

            var tag = spread.GbCell(rowIndex, colIndexDare).Tag;
            if (tag == null || tag.ToString() != "ContoControverso") return;

            spread.GbCell(rowIndex, colIndexDare).BackColor = _grigioChiaro;
            spread.GbCell(rowIndex, colIndexDare).Locked = true;
            spread.GbCell(rowIndex, colIndexDare).Value = null;
        }

        private void SetSpreadContPiacoProperties(Int32 rowIndex, ContPiaco contPiaco)
        {
            spread.GbCell(rowIndex, ColGr.ContoGb).Value = contPiaco.Conto.Trim();
            spread.GbCell(rowIndex, ColGr.ContoGbDescrizione).Value = contPiaco.Descrizione;
            spread.GbCell(rowIndex, ColGr.CeeD).Value = contPiaco.CEED;
            spread.GbCell(rowIndex, ColGr.CeeA).Value = contPiaco.CEEA;
            spread.GbCell(rowIndex, ColGr.Pe).Value = contPiaco.PE;
            spread.GbCell(rowIndex, ColGr.Apcr).Value = contPiaco.APCR;

            if (contPiaco.PercFisc == -1.ToDecimal())
            {
                spread.GbCell(rowIndex, ColGr.Iidd).CellType = CellTypeText();
                spread.GbCell(rowIndex, ColGr.Iidd).Value = "-";
            }
            else
            {
                spread.GbCell(rowIndex, ColGr.Iidd).Value = contPiaco.PercFisc / 100;
            }

            if (contPiaco.PercIrap == -1.ToDecimal())
            {
                spread.GbCell(rowIndex, ColGr.Irap).CellType = CellTypeText();
                spread.GbCell(rowIndex, ColGr.Irap).Value = "-";
            }
            else
            {
                spread.GbCell(rowIndex, ColGr.Irap).Value = contPiaco.PercIrap / 100;
            }
        }

        private void SetSpreadContPiacoPropertiesColor(Int32 rowIndex, Int32 colIndexDare, Int32 colIndexAvere)
        {
            spread.GbCell(rowIndex, colIndexDare).BackColor = _grigioChiaro;
            spread.GbCell(rowIndex, colIndexDare).Locked = true;
            spread.GbCell(rowIndex, colIndexDare).Tag = "ContoNonPresente";
            spread.GbCell(rowIndex, colIndexDare).Value = null;

            spread.GbCell(rowIndex, colIndexAvere).BackColor = _grigioChiaro;
            spread.GbCell(rowIndex, colIndexAvere).Locked = true;
            spread.GbCell(rowIndex, colIndexAvere).Tag = "ContoNonPresente";
            spread.GbCell(rowIndex, colIndexAvere).Value = null;
        }

        private void SetSpreadFormat(Int32 startRow, Int32 rowCount)
        {
            var rowIndexList = new List<Int32>();
            rowCount = rowCount < spread.GbRowCount() ? rowCount : spread.GbRowCount() - 1;
            for (var row = 0; row <= rowCount; row++)
            {
                Int32 tipo;
                Int32.TryParse(spread.GbCell(startRow + row, ColGr.Tipo).Text, out tipo);
                if (tipo == 2) { rowIndexList.Add(startRow + row); }
            }

            foreach (var rowIndex in rowIndexList)
            {
                foreach (var colIndex in spread.GbColsIndex())
                {
                    var cell = _cellProperties[colIndex];
                    if (spread.GbCell(rowIndex, colIndex).BackColor != _grigioChiaro) { spread.GbCell(rowIndex, colIndex).BackColor = cell.BackColor; }
                    spread.GbCell(rowIndex, colIndex).Border = cell.Border;
                    spread.GbCell(rowIndex, colIndex).Font = cell.Font;
                    spread.GbCell(rowIndex, colIndex).ForeColor = cell.ForeColor;

                    if (colIndex != ColGr.Iidd.ToInt() &&
                        colIndex != ColGr.Irap.ToInt() &&
                        colIndex != ColGr.AnnoCorrDare.ToInt() &&
                        colIndex != ColGr.AnnoCorrAvere.ToInt() &&
                        colIndex != ColGr.AnnoPrecDare.ToInt() &&
                        colIndex != ColGr.AnnoPrecAvere.ToInt())
                    {
                        spread.GbCell(rowIndex, colIndex).CellType = cell.CellType;
                    }
                }
            }
        }

        private void SetSpreadFormatRow()
        {
            spread.GbCell(FormatRowIndex, ColGr.AnnoCorrRip).CellType = CellTypeButton();
            spread.GbCell(FormatRowIndex, ColGr.AnnoCorrPar).CellType = CellTypeButton();
            spread.GbCell(FormatRowIndex, ColGr.AnnoPrecRip).CellType = CellTypeButton();
            spread.GbCell(FormatRowIndex, ColGr.AnnoPrecPar).CellType = CellTypeButton();
        }

        private void SetSpreadFormula(Boolean cliFor = false)
        {
            var formulaRows = DicRowTipo1.Keys.Reverse().Select(x => x.Index).ToList();
            var formulaRowsCliFor = DicRowCliFor.Keys.Reverse().Select(x => x.Index).ToList();
            var formulaCols = new List<Int32>
            {
                ColGr.AnnoCorrDare.ToInt(),
                ColGr.AnnoCorrAvere.ToInt(),
                ColGr.AnnoPrecDare.ToInt(),
                ColGr.AnnoPrecAvere.ToInt()
            };

            var colTag = GetAlphabetCharFromIndex(ColGr.TagFormula, true);
            var colAnnoDare = GetAlphabetCharFromIndex(ColGr.AnnoCorrDare, true);
            var colAnnoAvere = GetAlphabetCharFromIndex(ColGr.AnnoCorrAvere, true);
            var colAnnoPrecedenteDare = GetAlphabetCharFromIndex(ColGr.AnnoPrecDare, true);
            var colAnnoPrecedenteAvere = GetAlphabetCharFromIndex(ColGr.AnnoPrecAvere, true);

            foreach (var rowIndex in cliFor ? formulaRowsCliFor : formulaRows)
            {
                foreach (var colIndex in formulaCols)
                {
                    var cell = spread.GbCell(rowIndex, colIndex);
                    if (!cell.Formula.Contains("$") && !cliFor) { continue; }

                    if (cliFor)
                    {
                        var num1Index = cell.Formula.IndexOf(':');
                        var num2Index = cell.Formula.IndexOf(';');
                        if (num1Index <= 11) { continue; }

                        var interval = new Interval();
                        var startInterval = GetFormulaInterval(interval, rowIndex).Start;
                        var endInterval = GetFormulaInterval(interval, rowIndex).End;

                        var num1 = cell.Formula.Substring(11, num1Index - 11);
                        var num2 = cell.Formula.Substring(13 + num1.Length, num2Index - (13 + num1.Length));

                        cell.Formula = cell.Formula.Replace(num1 + ":", startInterval + ":");
                        cell.Formula = cell.Formula.Replace(num2 + ";", endInterval + ";");
                        cell.Formula = cell.Formula.Replace(num2 + ")", endInterval + ")");

                        continue;
                    }

                    var formula = cell.Formula.Replace("$", String.Empty);
                    var startRow = rowIndex + 2;

                    var increment = spread.GbRow(rowIndex).Tag.ToInt();
                    var endRow = startRow + (increment == 1 ? 0 : increment);
                    cell.Formula = formula
                        .Replace(String.Concat(colTag, ":"), colTag + startRow + ":")
                        .Replace(String.Concat(colTag, ";"), colTag + endRow + ";")
                        .Replace(String.Concat(colTag, ")"), colTag + endRow + ")")
                        .Replace(String.Concat(colAnnoDare, ":"), colAnnoDare + startRow + ":")
                        .Replace(String.Concat(colAnnoDare, ";"), colAnnoDare + endRow + ";")
                        .Replace(String.Concat(colAnnoDare, ")"), colAnnoDare + endRow + ")")
                        .Replace(String.Concat(colAnnoAvere, ":"), colAnnoAvere + startRow + ":")
                        .Replace(String.Concat(colAnnoAvere, ";"), colAnnoAvere + endRow + ";")
                        .Replace(String.Concat(colAnnoAvere, ")"), colAnnoAvere + endRow + ")")
                        .Replace(String.Concat(colAnnoPrecedenteDare, ":"), colAnnoPrecedenteDare + startRow + ":")
                        .Replace(String.Concat(colAnnoPrecedenteDare, ";"), colAnnoPrecedenteDare + endRow + ";")
                        .Replace(String.Concat(colAnnoPrecedenteDare, ")"), colAnnoPrecedenteDare + endRow + ")")
                        .Replace(String.Concat(colAnnoPrecedenteAvere, ":"), colAnnoPrecedenteAvere + startRow + ":")
                        .Replace(String.Concat(colAnnoPrecedenteAvere, ";"), colAnnoPrecedenteAvere + endRow + ";")
                        .Replace(String.Concat(colAnnoPrecedenteAvere, ")"), colAnnoPrecedenteAvere + endRow + ")");
                }
            }
        }

        private void SetSpreadHeader()
        {
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrDare).Locked = true;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrDare).Value = _year;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrDare).CellType = new GeneralCellType();
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrAvere).Locked = true;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrAvere).Value = _year;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoCorrAvere).CellType = new GeneralCellType();

            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecDare).Locked = true;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecDare).Value = _year - 1;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecDare).CellType = new GeneralCellType();
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecAvere).Locked = true;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecAvere).Value = _year - 1;
            spread.GbCell(FirstVisibleRow().Index, ColGr.AnnoPrecAvere).CellType = new GeneralCellType();
        }

        private void SetSpreadLockFormulaCellType()
        {
            var buttonCellType = CellTypeButton();
            var percentCellType = CellTypePercent(2);
            var numberCellType = CellTypeNumber(2, 12);

            spread.GbCol(ColGr.Iidd).CellType = percentCellType;
            spread.GbCol(ColGr.Irap).CellType = percentCellType;

            foreach (var row in DicRowTipo2.Keys)
            {
                var rowIndex = row.Index;
                if (spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Formula.IsStringNotNullOrEmpty())
                {
                    spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Locked = true;
                    spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Locked = true;
                    spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Locked = true;
                    spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Locked = true;
                }

                spread.GbCell(rowIndex, ColGr.AnnoCorrDare).CellType = numberCellType;
                spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).CellType = numberCellType;
                spread.GbCell(rowIndex, ColGr.AnnoCorrRip).CellType = buttonCellType;
                spread.GbCell(rowIndex, ColGr.AnnoCorrPar).CellType = buttonCellType;

                spread.GbCell(rowIndex, ColGr.AnnoPrecDare).CellType = numberCellType;
                spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).CellType = numberCellType;
                spread.GbCell(rowIndex, ColGr.AnnoPrecRip).CellType = buttonCellType;
                spread.GbCell(rowIndex, ColGr.AnnoPrecPar).CellType = buttonCellType;

                spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Locked = spread.GbCell(rowIndex, ColGr.AnnoCorrDare).BackColor == _grigioChiaro;
                spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Locked = spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).BackColor == _grigioChiaro;
                spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Locked = spread.GbCell(rowIndex, ColGr.AnnoPrecDare).BackColor == _grigioChiaro;
                spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Locked = spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).BackColor == _grigioChiaro;
            }
        }

        private void SetSpreadProperties()
        {
            spread.AllowCellOverflow = false; //LC 19/10/2016: se messo a true, qualsiasi ciclo sulla spread ci impiega il 1000% in più
            spread.AllowSheetMove = false;
            spread.AllowUserFormulas = false;
            spread.BorderStyle = BorderStyle.FixedSingle;
            spread.ButtonDrawMode = ButtonDrawModes.Always;
            spread.EditModeReplace = true;
            spread.Font = new Font("Verdana", 9F);
            spread.RetainSelectionBlock = false;
            spread.ScrollBarTrackPolicy = ScrollBarTrackPolicy.Both;
            spread.TabStripPolicy = TabStripPolicy.AsNeeded;
            spread.TabStripRatio = 0.5;
            spread.Sheets[0].Protect = true;

            spread.Sheets[0].Columns[(Int32)ColGr.AnnoCorrRip].Locked = false;
            spread.Sheets[0].Columns[(Int32)ColGr.AnnoPrecRip].Locked = false;
        }

        private void SetSpreadValue(SpreadValueParams svp)
        {
            var albero = svp.Albero;
            var attivoPassivo = svp.AttivoPassivo;
            var cliFor = svp.CliFor;
            var contPiacoCorr = svp.ContPiacoCorr;
            var contPiacoPrec = svp.ContPiacoPrec;
            var rowIndex = svp.RowIndex;
            var tab = svp.Tab;
            var tagFormula = svp.TagFormula;
            var tipo = svp.Tipo;

            //Dati indipendente dagli anni ma dipendenti dalla struttura
            spread.GbCell(rowIndex, ColGr.ContoGb).HorizontalAlignment = CellHorizontalAlignment.Left;
            spread.GbCell(rowIndex, ColGr.Iidd).HorizontalAlignment = CellHorizontalAlignment.Center;
            spread.GbCell(rowIndex, ColGr.Irap).HorizontalAlignment = CellHorizontalAlignment.Center;
            spread.GbCell(rowIndex, ColGr.Tipo).Value = tipo;
            spread.GbCell(rowIndex, ColGr.CliFor).Value = cliFor;
            spread.GbCell(rowIndex, ColGr.AttivoPassivo).Value = attivoPassivo;
            spread.GbCell(rowIndex, ColGr.TagFormula).Value = tagFormula;
            spread.GbCell(rowIndex, ColGr.Tab).Value = tab;
            spread.GbCell(rowIndex, ColGr.Albero).Value = albero;

            //AnnoCorrente
            if (contPiacoCorr != null)
            {
                if (contPiacoCorr.ValRiapertura > 0d)
                {
                    if (contPiacoCorr.CEED.Trim().IsStringNotNullOrEmpty() && contPiacoCorr.CEEA.Trim().IsStringNotNullOrEmpty())
                    {
                        if (svp.AttivoPassivo == "A" || svp.AttivoPassivo == String.Empty)
                        {
                            spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Value = contPiacoCorr.ValRiapertura;
                            spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Value = 0d;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                        }
                    }
                    else
                    {
                        spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Value = contPiacoCorr.ValRiapertura;
                        spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Value = 0d;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                    }
                }
                else if (contPiacoCorr.ValRiapertura < 0d)
                {
                    if (contPiacoCorr.CEED.Trim().IsStringNotNullOrEmpty() && contPiacoCorr.CEEA.Trim().IsStringNotNullOrEmpty())
                    {
                        if (svp.AttivoPassivo == "P")
                        {
                            spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Value = 0d;
                            spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Value = -contPiacoCorr.ValRiapertura;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                        }
                    }
                    else
                    {
                        spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Value = 0d;
                        spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Value = -contPiacoCorr.ValRiapertura;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                    }
                }
                else
                {
                    spread.GbCell(rowIndex, ColGr.AnnoCorrDare).Value = 0d;
                    spread.GbCell(rowIndex, ColGr.AnnoCorrAvere).Value = 0d;
                    spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                    spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                }

                SetSpreadContPiacoProperties(rowIndex, svp.ContPiacoCorr);
                if (contPiacoPrec == null)
                {
                    SetSpreadContPiacoPropertiesColor(rowIndex, ColGr.AnnoPrecDare.ToInt(), ColGr.AnnoPrecAvere.ToInt());
                }
            }

            //AnnoPrecedente
            if (contPiacoPrec != null) //se il conto corrente manca ma è presente quello precedente
            {
                if (contPiacoPrec.ValRiapertura > 0d)
                {
                    //se c'è un doppio raccordo e si è in una riga "Attiva" (in caso di riga "Passiva" non si scrive nessun valore)
                    if (contPiacoPrec.CEED.Trim().IsStringNotNullOrEmpty() && contPiacoPrec.CEEA.Trim().IsStringNotNullOrEmpty())
                    {
                        //LC 26/04/2017: gestione dei CO
                        //if (svp.AttivoPassivo == "A")
                        if (svp.AttivoPassivo == "A" || svp.AttivoPassivo == String.Empty)
                        {
                            spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = contPiacoPrec.ValRiapertura;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                        }
                    }
                    else
                    {
                        spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = contPiacoPrec.ValRiapertura;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = 0d;
                    }
                }
                else if (contPiacoPrec.ValRiapertura < 0d)
                {
                    //se c'è un doppio raccordo e si è in una riga "Passiva" (in caso di riga "Attiva" non si scrive nessun valore)
                    if (contPiacoPrec.CEED.Trim().IsStringNotNullOrEmpty() && contPiacoPrec.CEEA.Trim().IsStringNotNullOrEmpty())
                    {
                        if (svp.AttivoPassivo == "P")
                        {
                            spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                            spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = -contPiacoPrec.ValRiapertura;
                        }
                    }
                    else
                    {
                        spread.GbCell(rowIndex, ColGr.AnnoPrecDare).Value = 0d;
                        spread.GbCell(rowIndex, ColGr.AnnoPrecAvere).Value = -contPiacoPrec.ValRiapertura;
                    }
                }
            }

            if (contPiacoCorr == null)
            {
                SetSpreadContPiacoProperties(rowIndex, contPiacoPrec);
                SetSpreadContPiacoPropertiesColor(rowIndex, ColGr.AnnoCorrDare.ToInt(), ColGr.AnnoCorrAvere.ToInt());
            }
        }

        private void SetSpreadVisibilityCols(Boolean visible)
        {
            foreach (Column col in spread.GbCols())
            {
                if (col.Index != (Int32)ColGr.ContoUtente &&
                    col.Index != (Int32)ColGr.ContoUtenteDescrizione &&
                    col.Index != (Int32)ColGr.Expand &&
                    col.Index != (Int32)ColGr.ContoGb &&
                    col.Index != (Int32)ColGr.ContoGbDescrizione &&
                    col.Index != (Int32)ColGr.Iidd &&
                    col.Index != (Int32)ColGr.AnnoCorrDare &&
                    col.Index != (Int32)ColGr.AnnoCorrAvere &&
                    col.Index != (Int32)ColGr.AnnoCorrRip &&
                    col.Index != (Int32)ColGr.AnnoPrecDare &&
                    col.Index != (Int32)ColGr.AnnoPrecAvere &&
                    col.Index != (Int32)ColGr.AnnoPrecRip
                )
                {
                    col.Visible = visible;
                }
            }
        }

        private void SetSpreadVisibilityRows(Boolean forceVisible = false)
        {
            foreach (Row row in spread.Sheets[0].Rows)
            {
                var rowIndex = row.Index;
                if (rowIndex == 0 || rowIndex == 1) { continue; }

                //si mettono visibili le righe delle sezioni diverse da CO e dalle righe vuote
                if (!spread.GbCell(rowIndex, ColGr.Tab).Text.Contains(Co.ToUpper()) &&
                    spread.GbCell(rowIndex, ColGr.Tab).Text.IsStringNotNullOrEmpty() && forceVisible)
                {
                    spread.GbRow(rowIndex).Visible = true;
                    continue;
                }

                //e si nascondono quelle della sezione CO che non deve comparire, quelle vuote e quelle di tipo 2
                if (spread.GbCell(rowIndex, ColGr.Tab).Text.Contains(Co.ToUpper()) ||
                    spread.GbCell(rowIndex, ColGr.Tab).Text.IsStringNullOrEmpty() ||
                    spread.GbCell(rowIndex, ColGr.Tipo).Text == "2")
                {
                    try
                    {
                        spread.GbRow(rowIndex).Visible = false;
                    }
                    catch (Exception)
                    {
                        MessageBox.Show(this,
                            String.Concat("Row index that throws exception: ", rowIndex),
                            "Wrong sample",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Warning);
                        throw;
                    }
                }
            }

            spread.SetViewportTopRow(0, 0);
        }

        public class ContPiaco
        {
            public String APCR { get; set; }
            public String CEEA { get; set; }
            public String CEED { get; set; }
            public String Conto { get; set; }
            public Object Descrizione { get; set; }
            public String PE { get; set; }
            public Decimal PercFisc { get; set; }
            public Decimal PercIrap { get; set; }
            public Double ValRiapertura { get; set; }
        }

        internal class PiacoAnno
        {
            public PiacoAnno(Int32 anno)
            {
                Anno = anno;
            }

            public Int32 Anno { get; set; }
            public ContPiaco ContPiaco { get; set; }
        }

        private class CellProperty
        {
            public Color BackColor { get; set; }
            public IBorder Border { get; set; }
            public ICellType CellType { get; set; }
            public Font Font { get; set; }
            public Color ForeColor { get; set; }
            public Int32 Index { get; set; }
        }

        private class ContoControversoInfo
        {
            public ContoControversoInfo(Int32 startRow, Int32 otherRow)
            {
                StartRow = startRow;
                OtherRow = otherRow;
            }

            public Int32 OtherRow { get; set; }
            public Int32 StartRow { get; set; }
        }

        private class ContPiacoInsertRequest
        {
            public String Albero { get; set; }
            public String AttivoPassivo { get; set; }
            public String CliFor { get; set; }
            public Dictionary<String, CoppiaPiacoAnno> DicPiacoPerQuery { get; set; }
            public KeyValuePair<Row, Descriptor> DicQueryPair { get; set; }
            public Int32 RowCount { get; set; }
            public String Tab { get; set; }
            public String TagFormula { get; set; }
            public String Tipo { get; set; }
        }

        private class CoppiaPiacoAnno
        {
            public CoppiaPiacoAnno(PiacoAnno piacoAnnoCorr, PiacoAnno piacoAnnoPrec)
            {
                PiacoAnnoCorr = piacoAnnoCorr;
                PiacoAnnoPrec = piacoAnnoPrec;
            }

            public PiacoAnno PiacoAnnoCorr { get; private set; }
            public PiacoAnno PiacoAnnoPrec { get; private set; }
        }

        private class Descriptor
        {
            public RowProperty RowProperties { get; set; }
        }

        private class Interval
        {
            public Int32 End { get; set; }
            public Int32 Start { get; set; }
        }

        private class RowProperty
        {
            public String AttivoPassivo { get; set; }
            public Color BackColorInput { get; set; }
            public Int32 CliFor { get; set; }
            public String Conto { get; set; }
            public String Query { get; set; }
            public String Tab { get; set; }
            public Int32 Tipo { get; set; }
        }

        private class SpreadValueParams
        {
            public String Albero { get; set; }
            public String AttivoPassivo { get; set; }
            public String CliFor { get; set; }
            public ContPiaco ContPiacoCorr { get; set; }
            public ContPiaco ContPiacoPrec { get; set; }
            public Int32 RowIndex { get; set; }
            public String Tab { get; set; }
            public String TagFormula { get; set; }
            public String Tipo { get; set; }
        }
    }
}