<?php
$this->layout = false;

use CrmSpreadsheet\CrmSpreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

$spreadsheet = new CrmSpreadsheet();
$spreadsheet->setDefaults();
$spreadsheet->getDefaultStyle()->getFont()->setSize(8);
$spreadsheet->getDefaultStyle()
            ->getAlignment()
            ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getProperties()->setTitle("Planilha de Controle de Vendas e Cancelamentos");

$headerStyle1 = [
    'font' => [
        'bold' => true,
        'size' => 18
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => '00B050',
        ],
    ],
];
$subHeaderStyle1 = [
    'font' => [
        'bold' => true,
        'size' => 13
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => '92D050',
        ],
    ],
];
$headerStyle2 = [
    'font' => [
        'bold' => true,
        'size' => 18,
        'color' => [
            'rgb' => 'FFFFFF'
        ]
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => 'C00000',
        ],
    ],
];
$subHeaderStyle2 = [
    'font' => [
        'bold' => true,
        'size' => 13
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => 'FF0000',
        ],
    ],
];
$headerStyle3 = [
    'font' => [
        'bold' => true,
        'size' => 18,
        'color' => [
            'rgb' => 'FFFFFF'
        ]
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => '4A86E8',
        ],
    ],
];
$subHeaderStyle3 = [
    'font' => [
        'bold' => true,
        'size' => 13
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => 'D9D9D9',
        ],
    ],
];
$subHeaderStyle4 = [
    'font' => [
        'bold' => true,
        'size' => 10
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => '6AA84F',
        ],
    ],
];
$subHeaderStyle5 = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'rotation' => 90,
        'startColor' => [
            'rgb' => '93C47D',
        ],
    ],
];

$sheet = $spreadsheet->getActiveSheet();

$colunasSomaPositiva = array();
$colunasSomaNegativa = array();
$colunasDiferencaValores = array();
$colunasSomaImplantacoes = array();
foreach ($meses as $mes) {
    $somaImplantacoesRevendas = "A1";
    $somaImplantacoesMatrizFiliais = array();

    #############################
    $nomeMes = Util::getMes($mes);
    $title = ExcelUtil::parseValue($nomeMes);

    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle($title);
    $spreadsheet->setActiveSheetIndexByName($title);
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValueByColumnAndRow(2, 1, "CONTROLE DE VENDAS E CANCELAMENTOS MENSAIS {$ano}");
    $sheet->mergeCells("B1:M1");
    $style = $sheet->getStyle("B1:M1");
    $style->getFont()->setBold(true);
    $style->getFont()->setSize(20);
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getRowDimension('1')->setRowHeight(30);

    $sheet->setCellValueByColumnAndRow(2, 2, "Período: {$title}");
    $sheet->mergeCells("B2:M2");
    $sheet->getStyle("B2:M2")->getFont()->setBold(true);
    $sheet->getStyle("B2:M2")->getFont()->setSize(10);

    $novosClientesRevendas = $novosClientes[$mes]['Revendas'];
    unset($novosClientes[$mes]['Revendas']);

    ############
    $sheet->setCellValueByColumnAndRow(2, 4, "NEGOCIAÇÕES COM VALORES POSITIVOS");

    $sheet->mergeCells("B4:M4");
    $style = $sheet->getStyle("B4:M4");
    $style->applyFromArray($headerStyle1);
    $sheet->getRowDimension('4')->setRowHeight(30);
    ############

    $somaMensalidadesNovosClientes = array();
    # Vendas Revendas
        $sheet->setCellValueByColumnAndRow(2, 5, "1. CLIENTES NOVOS (Revendas)");

        $sheet->mergeCells("B5:M5");
        $style = $sheet->getStyle("B5:M5");
        $style->applyFromArray($subHeaderStyle1);
        $sheet->getRowDimension('5')->setRowHeight(20);
        ############
        $sheet->setCellValueByColumnAndRow(2, 6, "Razão Social");
        $sheet->setCellValueByColumnAndRow(3, 6, "Implantação");
        $sheet->setCellValueByColumnAndRow(4, 6, "Mensalidade");
        $sheet->setCellValueByColumnAndRow(5, 6, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, 6, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, 6, "UF");
        $sheet->setCellValueByColumnAndRow(8, 6, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, 6, "Vendedor");
        $sheet->setCellValueByColumnAndRow(10, 6, "Forma de Venda");
        $sheet->setCellValueByColumnAndRow(11, 6, "Produto");
        $sheet->setCellValueByColumnAndRow(12, 6, "Contato");
        $sheet->setCellValueByColumnAndRow(13, 6, "Obs.");

        $columnLetter = Coordinate::stringFromColumnIndex(12);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle('B6:M6')->getFont()->setBold(true);
        $sheet->getStyle('B6:M6')
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        $linha = 7;
        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;

        $contadorLinha = 1;
        if (!empty($novosClientesRevendas)) {
            $colunasSoma = array();
            foreach ($novosClientesRevendas as $key => $negociacao) {
                $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                $revenda = Revendas::model()->findByPk($negociacao['FEN_IDREVENDA']);
                $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                $comoConheceu = Motivos::model()->findByPk($negociacao['FEN_IDMOTIVO']);
                $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                ############
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                if ($negociacao['CLI_IDMOTIVO'] == 67 || $negociacao['COT_SITUACAO'] == Contratos::PEDIDO_REATIVACAO) {
                    $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                } else {
                    $valor = 0;
                }
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                      ->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['IMPLANTACOES'] = $coluna;
                }
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                      ->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['MENSALIDADE'] = $coluna;
                }
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($vendedor);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($comoConheceu);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($produto);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $linha++;
            }
            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:M{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                foreach ($colunasSoma as $tipo => $coluna) {
                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $coordinate = "{$columnLetter}{$linha}";
                    if ($tipo == "IMPLANTACOES") {
                        $somaImplantacoesRevendas = $coordinate;
                    } else {
                        if (!in_array($coordinate, $somaMensalidadesNovosClientes)) {
                            $somaMensalidadesNovosClientes[] = $coordinate;
                        }
                    }
                }
            }
        } else {
            $sheet->mergeCells("B{$linha}:M{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }

        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 13, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

    # Vendas Matriz e Filiais
        $linha = $linha + 2;
        $sub = 2;
        if (!empty($novosClientes[$mes])) {
            foreach ($novosClientes[$mes] as $codRevenda => $vendasUnidade) {
                $revenda = Revendas::model()->findByPk($codRevenda);

                $sheet->setCellValueByColumnAndRow(2, $linha, "1.{$sub}. CLIENTES NOVOS ({$revenda->REV_FANTASIA})");
                $sub++;

                $sheet->mergeCells("B{$linha}:M{$linha}");
                $style = $sheet->getStyle("B{$linha}:M{$linha}");
                $style->applyFromArray($subHeaderStyle1);
                $sheet->getRowDimension($linha)->setRowHeight(20);
                $linha++;
                ############
                $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social");
                $sheet->setCellValueByColumnAndRow(3, $linha, "Implantação");
                $sheet->setCellValueByColumnAndRow(4, $linha, "Mensalidade");
                $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
                $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
                $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
                $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
                $sheet->setCellValueByColumnAndRow(9, $linha, "Vendedor");
                $sheet->setCellValueByColumnAndRow(10, $linha, "Forma de Venda");
                $sheet->setCellValueByColumnAndRow(11, $linha, "Produto");
                $sheet->setCellValueByColumnAndRow(12, $linha, "Contato");
                $sheet->setCellValueByColumnAndRow(13, $linha, "Obs.");

                $columnLetter = Coordinate::stringFromColumnIndex(12);
                $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

                $sheet->getStyle("B{$linha}:M{$linha}")->getFont()->setBold(true);
                $sheet->getStyle("B{$linha}:M{$linha}")
                      ->getAlignment()
                      ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                $linha++;

                $linhaInicial = $linha;
                $linhaFinal = $linha - 1;

                $contadorLinha = 1;
                if (!empty($vendasUnidade)) {
                    $colunasSoma = array();
                    foreach ($vendasUnidade as $key => $negociacao) {
                        $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                        $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                        $comoConheceu = Motivos::model()->findByPk($negociacao['FEN_IDMOTIVO']);
                        $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                        ############
                        $coluna = 1;

                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                        $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                        $contadorLinha++;
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $sheet
                              ->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        if (!in_array($coluna, $colunasSoma)) {
                            $colunasSoma['IMPLANTACOES'] = $coluna;
                        }
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $sheet
                              ->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        if (!in_array($coluna, $colunasSoma)) {
                            $colunasSoma['MENSALIDADE'] = $coluna;
                        }
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($negociacao['FEN_NROUSUARIOS']);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($cidade);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($cidade->CID_UF);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($revenda);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($vendedor);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($comoConheceu);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($produto);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;
                        #####
                        $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                        $coluna++;

                        $linha++;
                    }
                    $linhaFinal = $linha - 1;

                    if (!empty($colunasSoma)) {
                        $sheet->getStyle("B{$linha}:M{$linha}")->getFont()->setBold(true);
                        $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                        foreach ($colunasSoma as $tipo => $coluna) {
                            $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                            $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                            $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                            $sheet->getStyleByColumnAndRow($coluna, $linha)
                                  ->getNumberFormat()
                                  ->setFormatCode('"R$ "#,##0.00_-');

                            $sumCoordinate = "{$columnLetter}{$linha}";
                            if ($tipo == "IMPLANTACOES") {
                                if (!in_array($sumCoordinate, $somaImplantacoesMatrizFiliais)) {
                                    $somaImplantacoesMatrizFiliais[] = $sumCoordinate;
                                }
                            } else {
                                if (!in_array($sumCoordinate, $somaMensalidadesNovosClientes)) {
                                    $somaMensalidadesNovosClientes[] = $sumCoordinate;
                                }
                            }
                        }
                    }
                } else {
                    $sheet->mergeCells("B{$linha}:M{$linha}");
                    $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
                }

                $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 13, ($linhaFinal+1))
                      ->getBorders()
                      ->getAllBorders()
                      ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

                $linha = $linha + 2;
            }
        }

    # Transferências com acréscimo de valor
        $somaImplantacoesTransferenciasPositivas = "A1";
        $somaMensalidadesTransferenciasPositivas = "A1";

        $sheet->setCellValueByColumnAndRow(2, $linha, "2. TRANSFERÊNCIAS COM ACRÉSCIMO DE VALORES");

        $sheet->mergeCells("B{$linha}:N{$linha}");
        $style = $sheet->getStyle("B{$linha}:N{$linha}");
        $style->applyFromArray($subHeaderStyle1);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        ############
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social do Novo Cliente");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Implantação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Diferença negociada");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Vendedor");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Valor Final");
        $sheet->setCellValueByColumnAndRow(11, $linha, "Produto");
        $sheet->setCellValueByColumnAndRow(12, $linha, "Contato");
        $sheet->setCellValueByColumnAndRow(13, $linha, "Obs.");

        $sheet->getRowDimension($linha)->setRowHeight(30);
        $sheet->getStyleByColumnAndRow(2, $linha)->getAlignment()->setWrapText(true);
        $sheet->getStyleByColumnAndRow(4, $linha)->getAlignment()->setWrapText(true);
        $sheet->mergeCells("M{$linha}:N{$linha}");

        $columnLetter = Coordinate::stringFromColumnIndex(13);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:N{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;
        $transferenciasComAcrescimo = @$transferencias[$mes]['COM_ACRESCIMO'];

        $contadorLinha = 1;
        if (!empty($transferenciasComAcrescimo)) {
            $colunasSoma = array();
            foreach ($transferenciasComAcrescimo as $negociacao) {
                $revenda = Revendas::model()->findByPk($negociacao['FEN_IDREVENDA']);
                $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                $servico = Servicos::model()->findByPk($negociacao['COT_IDSERVICO']);
                ############
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['IMPLANTACOES'] = $coluna;
                }
                $coluna++;
                #####
                $valor = $negociacao['FEN_VLRDIFERENCA'];
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['MENSALIDADE'] = $coluna;
                }
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($vendedor);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($produto);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $sheet->mergeCells("M{$linha}:N{$linha}");

                $linha++;
            }
            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                foreach ($colunasSoma as $tipo => $coluna) {
                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $sumCoordinate = "{$columnLetter}{$linha}";
                    if ($tipo == "IMPLANTACOES") {
                        $somaImplantacoesTransferenciasPositivas = $sumCoordinate;
                    } else {
                        $somaMensalidadesTransferenciasPositivas = $sumCoordinate;
                    }
                }
            }
        } else {
            $sheet->mergeCells("B{$linha}:N{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }

        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 14, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

        $linha = $linha + 2;

    # Adendos com acréscimo de valor
        $somaImplantacoesAdendosPositivos = "A1";
        $somaMensalidadesAdendosPositivos = "A1";

        $sheet->setCellValueByColumnAndRow(2, $linha, "3. ADENDOS COM ACRÉSCIMO DE VALORES");

        $sheet->mergeCells("B{$linha}:N{$linha}");
        $style = $sheet->getStyle("B{$linha}:N{$linha}");
        $style->applyFromArray($subHeaderStyle1);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        ############
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Implantação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Valor do Acréscimo");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Vendedor");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Valor Final");
        $sheet->setCellValueByColumnAndRow(11, $linha, "Produto");
        $sheet->setCellValueByColumnAndRow(12, $linha, "Contato");
        $sheet->setCellValueByColumnAndRow(13, $linha, "Tipo de Serviço");
        $sheet->setCellValueByColumnAndRow(14, $linha, "Obs.");

        $columnLetter = Coordinate::stringFromColumnIndex(13);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:N{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;
        $adendosComAcrescimo = @$adendos[$mes]['COM_ACRESCIMO'];

        $contadorLinha = 1;
        if (!empty($adendosComAcrescimo)) {
            $colunasSoma = array();
            foreach ($adendosComAcrescimo as $negociacao) {
                $revenda = Revendas::model()->findByPk($negociacao['FEN_IDREVENDA']);
                $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                $servico = Servicos::model()->findByPk($negociacao['COT_IDSERVICO']);
                ############
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['IMPLANTACOES'] = $coluna;
                }
                $coluna++;
                #####
                $valor = $negociacao['FEN_VLRDIFERENCA'];
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['MENSALIDADE'] = $coluna;
                }
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($vendedor);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($produto);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($servico);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $linha++;
            }
            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                foreach ($colunasSoma as $tipo => $coluna) {
                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $sumCoordinate = "{$columnLetter}{$linha}";
                    if ($tipo == "IMPLANTACOES") {
                        $somaImplantacoesAdendosPositivos = $sumCoordinate;
                    } else {
                        $somaMensalidadesAdendosPositivos = $sumCoordinate;
                    }
                }
            }
        } else {
            $sheet->mergeCells("B{$linha}:N{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }

        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 14, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

        $linha = $linha + 2;

    $sheet->setCellValueByColumnAndRow(2, $linha, "NEGOCIAÇÕES COM VALORES NEGATIVOS OU SEM ALTERAÇÃO DE VALOR");

    $sheet->mergeCells("B{$linha}:N{$linha}");
    $style = $sheet->getStyle("B{$linha}:N{$linha}");
    $style->applyFromArray($headerStyle2);
    $sheet->getRowDimension($linha)->setRowHeight(30);

    $linha++;
    ############

    # Transferências sem acréscimo de valor
        $somaImplantacoesTransferenciasNegativas = "A1";
        $somaMensalidadesTransferenciasNegativas = "A1";

        $sheet->setCellValueByColumnAndRow(2, $linha, "4. TRANSFERÊNCIAS SEM ACRÉSCIMO DE VALORES (Com redução ou sem alteração de valores)");

        $sheet->mergeCells("B{$linha}:N{$linha}");
        $style = $sheet->getStyle("B{$linha}:N{$linha}");
        $style->applyFromArray($subHeaderStyle2);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        ############
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social do Novo Cliente");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Implantação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Diferença negociada");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Vendedor");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Valor Final");
        $sheet->setCellValueByColumnAndRow(11, $linha, "Produto");
        $sheet->setCellValueByColumnAndRow(12, $linha, "Contato");
        $sheet->setCellValueByColumnAndRow(13, $linha, "Obs.");

        $sheet->getRowDimension($linha)->setRowHeight(30);
        $sheet->getStyleByColumnAndRow(2, $linha)->getAlignment()->setWrapText(true);
        $sheet->getStyleByColumnAndRow(4, $linha)->getAlignment()->setWrapText(true);
        $sheet->mergeCells("M{$linha}:N{$linha}");

        $columnLetter = Coordinate::stringFromColumnIndex(13);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);


        $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:N{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;
        $transferenciasSemAcrescimo = @$transferencias[$mes]['SEM_ACRESCIMO'];

        $contadorLinha = 1;
        if (!empty($transferenciasSemAcrescimo)) {
            $colunasSoma = array();
            foreach ($transferenciasSemAcrescimo as $negociacao) {
                $revenda = Revendas::model()->findByPk($negociacao['FEN_IDREVENDA']);
                $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                $servico = Servicos::model()->findByPk($negociacao['COT_IDSERVICO']);
                ############
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['IMPLANTACOES'] = $coluna;
                }
                $coluna++;
                #####
                $valor = $negociacao['FEN_VLRDIFERENCA'];
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['MENSALIDADE'] = $coluna;
                }
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($vendedor);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($produto);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $sheet->mergeCells("M{$linha}:N{$linha}");

                $linha++;
            }
            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                foreach ($colunasSoma as $tipo => $coluna) {
                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $sumCoordinate = "{$columnLetter}{$linha}";
                    if ($tipo == "IMPLANTACOES") {
                        $somaImplantacoesTransferenciasNegativas = $sumCoordinate;
                    } else {
                        $somaMensalidadesTransferenciasNegativas = $sumCoordinate;
                    }
                }
            }
        } else {
            $sheet->mergeCells("B{$linha}:N{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }

        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 14, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

        $linha = $linha + 2;

    # Adendos negativos ou sem alteração de valor
        $somaMensalidadesAdendosNegativos = "A1";

        $sheet->setCellValueByColumnAndRow(2, $linha, "5. ADENDOS SEM ACRÉSCIMO DE VALORES (Com redução ou sem alteração de valores)");

        $sheet->mergeCells("B{$linha}:N{$linha}");
        $style = $sheet->getStyle("B{$linha}:N{$linha}");
        $style->applyFromArray($subHeaderStyle2);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        ############
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Implantação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Valor da Redução");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Vendedor");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Valor Final");
        $sheet->setCellValueByColumnAndRow(11, $linha, "Produto");
        $sheet->setCellValueByColumnAndRow(12, $linha, "Contato");
        $sheet->setCellValueByColumnAndRow(13, $linha, "Tipo de Serviço");
        $sheet->setCellValueByColumnAndRow(14, $linha, "Obs.");

        $columnLetter = Coordinate::stringFromColumnIndex(13);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:N{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;
        $adendosSemAcrescimo = @$adendos[$mes]['SEM_ACRESCIMO'];

        $contadorLinha = 1;
        if (!empty($adendosSemAcrescimo)) {
            $colunasSoma = array();
            foreach ($adendosSemAcrescimo as $negociacao) {
                $revenda = Revendas::model()->findByPk($negociacao['FEN_IDREVENDA']);
                $cidade = Cidades::model()->findByPk($negociacao['CLI_CIDADE']);
                $vendedor = Funcionarios::model()->findByPk($negociacao['FEN_IDVENDEDOR']);
                $produto = Modulos::model()->findByPk($negociacao['FEN_IDPRODUTO']);
                $servico = Servicos::model()->findByPk($negociacao['COT_IDSERVICO']);
                ############
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRIMPLANTACAO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['IMPLANTACOES'] = $coluna;
                }
                $coluna++;
                #####
                $valor = abs($negociacao['FEN_VLRDIFERENCA']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if (!in_array($coluna, $colunasSoma)) {
                    $colunasSoma['MENSALIDADE'] = $coluna;
                }
                $coluna++;
                #####
                $valor = "-";
                if ($negociacao['FEN_NROUSUARIOS'] != 0) {
                    $valor = $negociacao['FEN_NROUSUARIOS'];
                }
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($vendedor);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($produto);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($negociacao['COT_CLI_CONTATO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($servico);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $linha++;
            }
            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:N{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");
                foreach ($colunasSoma as $tipo => $coluna) {
                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "=SUM({$range})");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $coordinate = "{$columnLetter}{$linha}";
                    if ($tipo == "MENSALIDADE") {
                        $somaMensalidadesAdendosNegativos = $coordinate;
                    }
                }
            }
        } else {
            $sheet->mergeCells("B{$linha}:N{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }
        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 14, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

        $linha = $linha + 2;

    # Cancelamentos
        $somaMensalidadesCancelamentos = "A1";

        $sheet->setCellValueByColumnAndRow(2, $linha, "6. CANCELAMENTOS");

        $sheet->mergeCells("B{$linha}:J{$linha}");
        $style = $sheet->getStyle("B{$linha}:J{$linha}");
        $style->applyFromArray($subHeaderStyle2);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        #########
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Contratação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Valor");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Motivo");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Obs.");

        $columnLetter = Coordinate::stringFromColumnIndex(9);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle("B{$linha}:J{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:J{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;
        $contadorLinha = 1;
        if (!empty($cancelamentos[$mes])) {
            $colunaSoma = "A1";
            foreach ($cancelamentos[$mes] as $cancelamento) {
                $cidade = Cidades::model()->findByPk($cancelamento['CLI_CIDADE']);
                $revenda = Revendas::model()->findByPk($cancelamento['FEN_IDREVENDA']);
                $motivoRescisao = Motivos::model()->findByPk($cancelamento['FEN_IDMOTIVO']);
                ########
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cancelamento['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_DTCONTRATACAO']);
                if ($valor != "-") {
                    $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
                }
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                            ->getStyleByColumnAndRow($coluna, $linha)
                            ->getNumberFormat()
                            ->setFormatCode('dd/mm/yyyy');
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                            ->getStyleByColumnAndRow($coluna, $linha)
                            ->getNumberFormat()
                            ->setFormatCode('"R$ "#,##0.00_-');
                $colunaSoma = $coluna;
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($motivoRescisao);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $linha++;
            }

            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:M{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

                $columnLetter = Coordinate::stringFromColumnIndex($colunaSoma);
                $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                $sheet->setCellValueByColumnAndRow($colunaSoma, $linha, "=SUM({$range})");
                $sheet->getStyleByColumnAndRow($colunaSoma, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $somaMensalidadesCancelamentos = "{$columnLetter}{$linha}";
            }
        } else {
            $sheet->mergeCells("B{$linha}:J{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }
        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 10, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        $linha = $linha + 2;

    # Cancelamentos por transferência
        $sheet->setCellValueByColumnAndRow(2, $linha, "7. CANCELAMENTOS POR TRANSFERÊNCIA");

        $sheet->mergeCells("B{$linha}:J{$linha}");
        $style = $sheet->getStyle("B{$linha}:J{$linha}");
        $style->applyFromArray($subHeaderStyle2);
        $sheet->getRowDimension($linha)->setRowHeight(20);
        $linha++;
        #########
        $sheet->setCellValueByColumnAndRow(2, $linha, "Razão Social");
        $sheet->setCellValueByColumnAndRow(3, $linha, "Contratação");
        $sheet->setCellValueByColumnAndRow(4, $linha, "Valor");
        $sheet->setCellValueByColumnAndRow(5, $linha, "Usuários");
        $sheet->setCellValueByColumnAndRow(6, $linha, "Cidade");
        $sheet->setCellValueByColumnAndRow(7, $linha, "UF");
        $sheet->setCellValueByColumnAndRow(8, $linha, "Revenda");
        $sheet->setCellValueByColumnAndRow(9, $linha, "Motivo");
        $sheet->setCellValueByColumnAndRow(10, $linha, "Obs.");

        $columnLetter = Coordinate::stringFromColumnIndex(9);
        $sheet->getStyle($columnLetter)->getAlignment()->setWrapText(true);

        $sheet->getStyle("B{$linha}:J{$linha}")->getFont()->setBold(true);
        $sheet->getStyle("B{$linha}:J{$linha}")
              ->getAlignment()
              ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $linha++;

        $linhaInicial = $linha;
        $linhaFinal = $linha - 1;

        $contadorLinha = 1;
        if (!empty($cancelamentosTransferencia[$mes])) {
            $colunaSoma = "A1";
            foreach ($cancelamentosTransferencia[$mes] as $cancelamento) {
                $cidade = Cidades::model()->findByPk($cancelamento['CLI_CIDADE']);
                $revenda = Revendas::model()->findByPk($cancelamento['FEN_IDREVENDA']);
                $motivoRescisao = Motivos::model()->findByPk($cancelamento['FEN_IDMOTIVO']);
                ########
                $coluna = 1;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $contadorLinha);
                $sheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(true);
                $contadorLinha++;
                $coluna++;
                #####
                $valor = ExcelUtil::parseValue($cancelamento['CLI_NOME']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_DTCONTRATACAO']);
                if ($valor != "-") {
                    $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
                }
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                            ->getStyleByColumnAndRow($coluna, $linha)
                            ->getNumberFormat()
                            ->setFormatCode('dd/mm/yyyy');
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_VLRNEGOCIADO']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet
                            ->getStyleByColumnAndRow($coluna, $linha)
                            ->getNumberFormat()
                            ->setFormatCode('"R$ "#,##0.00_-');
                $colunaSoma = $coluna;
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cancelamento['FEN_NROUSUARIOS']);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cidade);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($cidade->CID_UF);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($revenda);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;
                ########
                $valor = ExcelUtil::parseValue($motivoRescisao);
                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $coluna++;

                $linha++;
            }

            $linhaFinal = $linha - 1;

            if (!empty($colunasSoma)) {
                $sheet->getStyle("B{$linha}:M{$linha}")->getFont()->setBold(true);
                $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

                $columnLetter = Coordinate::stringFromColumnIndex($colunaSoma);
                $range = "{$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal}";
                $sheet->setCellValueByColumnAndRow($colunaSoma, $linha, "=SUM({$range})");
                $sheet->getStyleByColumnAndRow($colunaSoma, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
            }
        } else {
            $sheet->mergeCells("B{$linha}:J{$linha}");
            $sheet->setCellValueByColumnAndRow(2, $linha, "Nenhum dado disponível");
        }
        $sheet->getStyleByColumnAndRow(2, ($linhaInicial-1), 10, ($linhaFinal+1))
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        $linha = $linha + 2;

    # Indicadores
        $sheet->setCellValueByColumnAndRow(2, $linha, "INDICADORES");

        $sheet->mergeCells("B{$linha}:G{$linha}");
        $style = $sheet->getStyle("B{$linha}:G{$linha}");
        $style->applyFromArray($headerStyle3);
        $sheet->getRowDimension($linha)->setRowHeight(30);
        $linha = $linha + 2;

        # Implantações
            $somas = array();
            $linhaInicial = $linha;
            $sheet->setCellValueByColumnAndRow(2, $linha, "IMPLANTAÇÕES");

            $sheet->mergeCells("B{$linha}:G{$linha}");
            $style = $sheet->getStyle("B{$linha}:G{$linha}");
            $style->applyFromArray($subHeaderStyle3);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;
            ##############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "1. Clientes Novos");
            if (!empty($somaImplantacoesMatrizFiliais)) {
                $formula = "(";
                    foreach ($somaImplantacoesMatrizFiliais as $i => $coordinate) {
                        $formula .= $coordinate;
                        if ($i < count($somaImplantacoesMatrizFiliais)-1) {
                            $formula .= "+";
                        }
                    }
                $formula .= ")";

                $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            } else {
                $sheet->setCellValueByColumnAndRow(5, $linha, "0");
            }
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ###
            $sheet->setCellValueByColumnAndRow(2, $linha, "1. Clientes Novos (Taxa de Reativação de Revendas)");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaImplantacoesRevendas}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ###
            $sheet->setCellValueByColumnAndRow(2, $linha, "2. Transferências com acréscimo de valor");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaImplantacoesTransferenciasPositivas}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ###
            $sheet->setCellValueByColumnAndRow(2, $linha, "3. Adendos positivos");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaImplantacoesAdendosPositivos}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ###
            $sheet->setCellValueByColumnAndRow(2, $linha, "4. Transferências sem acréscimo de valor");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaImplantacoesTransferenciasNegativas}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ###
            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL DE IMPLANTAÇÕES MATRIZ");

            $formula = "(";
                foreach ($somas as $i => $coordinate) {
                    $formula .= $coordinate;
                    if ($i < count($somas)-1) {
                        $formula .= "+";
                    }
                }
            $formula .= ")";
            $formula .= " - {$somas['1']} / 2";
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 5, $linha);
            $style->getFont()->setBold(true);
            $style->getFont()->setSize(10);
            $linha++;
            ######
            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL DE IMPLANTAÇÕES");

            $formula = "(";
                foreach ($somas as $i => $coordinate) {
                    $formula .= $coordinate;
                    if ($i < count($somas)-1) {
                        $formula .= "+";
                    }
                }
            $formula .= ")";
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);

            $colunasSomaImplantacoes[$mes] = "E{$linha}";

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 7, $linha);
            $style->getFont()->setBold(true);
            $style->getFont()->setSize(10);
            $linha++;

            $linhaFinal = $linha;

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 7, ($linhaFinal-1))
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 7, ($linhaFinal-1))
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);

            $linha = $linha + 2;

        # Mensalidades
            $linhaInicial = $linha;

            $somas = array();
            $sheet->setCellValueByColumnAndRow(2, $linha, "MENSALIDADES POSITIVADAS");
            $sheet->mergeCells("B{$linha}:G{$linha}");
            $style = $sheet->getStyle("B{$linha}:G{$linha}");
            $style->applyFromArray($subHeaderStyle3);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;
            ##############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "1. Clientes Novos");
            if (!empty($somaMensalidadesNovosClientes)) {
                $formula = "(";
                    foreach ($somaMensalidadesNovosClientes as $i => $coordinate) {
                        $formula .= $coordinate;
                        if ($i < count($somaMensalidadesNovosClientes)-1) {
                            $formula .= "+";
                        }
                    }
                $formula .= ")";
                $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            } else {
                $sheet->setCellValueByColumnAndRow(5, $linha, "0");
            }
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ##############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "2. Transferências com acréscimo de valor");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaMensalidadesTransferenciasPositivas}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ##############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "3. Adendos positivos");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaMensalidadesAdendosPositivos}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ##############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL DE MENSALIDADES POSITIVADAS");

            $formula = "(";
                foreach ($somas as $i => $coordinate) {
                    $formula .= $coordinate;
                    if ($i < count($somas)-1) {
                        $formula .= "+";
                    }
                }
            $formula .= ")";
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 7, $linha);
            $style->getFont()->setBold(true);
            $style->getFont()->setSize(10);

            $colunasSomaPositiva[$mes] = "E{$linha}";

            $linha++;
            ###############################################
            $sheet->mergeCellsByColumnAndRow(2, $linha, 7, $linha);
            $linha++;
            ############################
            $somas = array();

            $sheet->setCellValueByColumnAndRow(2, $linha, "MENSALIDADES PERDIDAS");
            $sheet->mergeCells("B{$linha}:G{$linha}");
            $style = $sheet->getStyle("B{$linha}:G{$linha}");
            $style->applyFromArray($subHeaderStyle3);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;
            ############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "4. Transferências com redução de valor");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaMensalidadesTransferenciasNegativas}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "5. Adendos negativos");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaMensalidadesAdendosNegativos}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "6. Cancelamentos");
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$somaMensalidadesCancelamentos}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);
            $somas[] = "E{$linha}";
            $linha++;
            ############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL DE MENSALIDADES NEGATIVAS");

            $formula = "(";
                foreach ($somas as $i => $coordinate) {
                    $formula .= $coordinate;
                    if ($i < count($somas)-1) {
                        $formula .= "+";
                    }
                }
            $formula .= ")";
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 7, $linha);
            $style->getFont()->setBold(true);
            $style->getFont()->setSize(10);

            $colunasSomaNegativa[$mes] = "E{$linha}";
            $linha++;
            ###############################################
            $sheet->mergeCellsByColumnAndRow(2, $linha, 7, $linha);
            $linha++;
            ############################
            $sheet->setCellValueByColumnAndRow(2, $linha, "SALDO MENSALIDADES POSITIVAS - PERDIDAS");
            $sheet->getStyleByColumnAndRow(2, $linha)->getAlignment()->setWrapText(true);
            $formula = "{$colunasSomaPositiva[$mes]} - {$colunasSomaNegativa[$mes]}";
            $sheet->setCellValueByColumnAndRow(5, $linha, "={$formula}");
            $sheet->getStyleByColumnAndRow(5, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $colunasDiferencaValores[$mes] = "E{$linha}";
            $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 7, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 7, $linha);
            $style->getFont()->setBold(true);
            $style->getFont()->setSize(10);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 7, $linha);
            $style->getFill()
                  ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
            $style->getFill()
                  ->getStartColor()
                  ->setRGB('FFFF00');
            $sheet->getRowDimension($linha)->setRowHeight(25);

            ##############################################
            $linhaFinal = $linha;

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 7, $linhaFinal)
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 7, $linhaFinal)
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);

            $linha = $linha + 2;

    # Análise Geral
        $sheet->setCellValueByColumnAndRow(2, $linha, "ANÁLISE GERAL {$ano}");
        $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
        $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
        $style->applyFromArray($headerStyle1);
        $sheet->getRowDimension($linha)->setRowHeight(30);
        $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
              ->getBorders()
              ->getOutline()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

        $linha++;

        $linhaInicial = $linha;

        // Mensalidades
            $sheet->setCellValueByColumnAndRow(2, $linha, "MENSALIDADES");
            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle1);
            $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;

            $coluna = 2;
            $sheet->setCellValueByColumnAndRow(2, $linha, "Mês");
            $sheet->setCellValueByColumnAndRow(3, $linha, "Mensalidades Positivas");
            $sheet->setCellValueByColumnAndRow(4, $linha, "Mensalidades Perdidas");
            $sheet->setCellValueByColumnAndRow(5, $linha, "Positivas-Perdidas");
            $sheet->setCellValueByColumnAndRow(6, $linha, "Saldo Acumulado");
            for ($i=2; $i <= 6; $i++) {
                $sheet->getColumnDimensionByColumn($i)->setWidth(23);
            }

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle5);

            $sheet->getRowDimension($linha)->setRowHeight(20);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $linha++;

            foreach ($meses as $mesAnalise) {
                $nome = Util::getMes($mesAnalise);
                $nome = ExcelUtil::parseValue($nome);
                $sheet->setCellValueByColumnAndRow(2, $linha, $nome);

                if (isset($colunasSomaPositiva[$mesAnalise]) && isset($colunasSomaNegativa[$mesAnalise])) {
                    $mensalidadesPositivas = "={$nome}!" . $colunasSomaPositiva[$mesAnalise];
                    $mensalidadesNegativas = "={$nome}!" . $colunasSomaNegativa[$mesAnalise];
                    $diferenca = "={$nome}!" . $colunasDiferencaValores[$mesAnalise];

                    $sheet->setCellValueByColumnAndRow(3, $linha, $mensalidadesPositivas);
                    $sheet->getStyleByColumnAndRow(3, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $sheet->setCellValueByColumnAndRow(4, $linha, $mensalidadesNegativas);
                    $sheet->getStyleByColumnAndRow(4, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    $sheet->setCellValueByColumnAndRow(5, $linha, $diferenca);
                    $sheet->getStyleByColumnAndRow(5, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                    if ($mesAnalise == 1) {
                        $sheet->setCellValueByColumnAndRow(6, $linha, $diferenca);
                    } else {
                        $formula = "=F" . ($linha - 1) . " + E{$linha}";
                        $sheet->setCellValueByColumnAndRow(6, $linha, $formula);
                    }
                    $sheet->getStyleByColumnAndRow(6, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                }

                $linha++;
            }
            $linhaFinal = $linha - 1;

            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

            for ($i=3; $i <= 5 ; $i++) {
                $columnLetter = Coordinate::stringFromColumnIndex($i);
                $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
                $sheet->setCellValueByColumnAndRow($i, $linha, $valor);
                $sheet->getStyleByColumnAndRow($i, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
            }

            $colunaSaldoAcumulado = "E{$linha}";
            $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);
            $sheet->getStyleByColumnAndRow(5, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $linha++;
            $sheet->setCellValueByColumnAndRow(2, $linha, "MÉDIA MENSAL");

            $mesesDivisao = (int) $mes;
            $sheet->setCellValueByColumnAndRow(3, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);

            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

        $linha += 2;

        // Implantações
            $linhaInicial = $linha;

            $sheet->setCellValueByColumnAndRow(2, $linha, "IMPLANTAÇÕES");
            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle1);
            $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;

            $sheet->setCellValueByColumnAndRow(2, $linha, "Mês");
            $sheet->setCellValueByColumnAndRow(3, $linha, "Implantações");
            $sheet->setCellValueByColumnAndRow(5, $linha, "Saldo Acumulado");
            $sheet->mergeCellsByColumnAndRow(3, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle5);

            $sheet->getRowDimension($linha)->setRowHeight(20);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $linha++;

            foreach ($meses as $mesAnalise) {
                $nome = Util::getMes($mesAnalise);
                $nome = ExcelUtil::parseValue($nome);
                $sheet->setCellValueByColumnAndRow(2, $linha, $nome);

                if (isset($colunasSomaImplantacoes[$mesAnalise])) {
                    $somaImplantacoesMes = "={$nome}!" . $colunasSomaImplantacoes[$mesAnalise];

                    $sheet->setCellValueByColumnAndRow(3, $linha, $somaImplantacoesMes);
                    $sheet->getStyleByColumnAndRow(3, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');

                    if ($mesAnalise == 1) {
                        $sheet->setCellValueByColumnAndRow(5, $linha, $somaImplantacoesMes);
                    } else {
                        $saldoAcumuladoAnterior = Coordinate::stringFromColumnIndex(5) . ($linha - 1);
                        $somaMes = Coordinate::stringFromColumnIndex(3) . $linha;

                        $formula = "={$saldoAcumuladoAnterior} + {$somaMes}";
                        $sheet->setCellValueByColumnAndRow(5, $linha, $formula);
                    }
                    $sheet->getStyleByColumnAndRow(5, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                }
                $sheet->mergeCellsByColumnAndRow(3, $linha, 4, $linha);
                $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);

                $linha++;
            }
            $linhaFinal = $linha - 1;

            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

            $columnLetter = Coordinate::stringFromColumnIndex(3);
            $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
            $sheet->setCellValueByColumnAndRow(3, $linha, $valor);
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);
            $colunaSaldoAcumulado = "{$columnLetter}{$linha}";
            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $linha++;

            $sheet->setCellValueByColumnAndRow(2, $linha, "MÉDIA MENSAL");

            $mesesDivisao = (int) $mes;
            $sheet->setCellValueByColumnAndRow(3, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);

            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

    $linha += 2;


    $sheet->getColumnDimensionByColumn(1)->setWidth(5); // coluna vazia
    $sheet->getColumnDimensionByColumn(2)->setWidth(40); // razão social
    $sheet->getColumnDimensionByColumn(3)->setWidth(15); // valor implantação / data contratação
    $sheet->getColumnDimensionByColumn(4)->setWidth(15); // mensalidade, diferença, acréscimo, redução e valor
    $sheet->getColumnDimensionByColumn(5)->setWidth(10); // usuários
    $sheet->getColumnDimensionByColumn(6)->setWidth(20); // cidade
    $sheet->getColumnDimensionByColumn(7)->setWidth(5); // UF
    $sheet->getColumnDimensionByColumn(8)->setWidth(25); // Revenda
    $sheet->getColumnDimensionByColumn(9)->setWidth(20); // Vendedor/Motivo cancelamento
    $sheet->getColumnDimensionByColumn(10)->setWidth(20); // Forma de Venda, valor final e Obs.
    $sheet->getColumnDimensionByColumn(11)->setWidth(15); // Produto
    $sheet->getColumnDimensionByColumn(12)->setWidth(15); // Contato
    $sheet->getColumnDimensionByColumn(13)->setWidth(15); // Obs./Tipo de serviço/Contato
    $sheet->getColumnDimensionByColumn(14)->setWidth(15); // Obs.
}

// Aba Resumo Geral
    $title = "Resumo Geral";
    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle($title);
    $spreadsheet->setActiveSheetIndexByName($title);
    $sheet = $spreadsheet->getActiveSheet();

    $linha = 2;

    $sheet->getColumnDimensionByColumn(1)->setWidth(5);

    // Análise geral ano atual
        $sheet->setCellValueByColumnAndRow(2, $linha, "ANÁLISE GERAL {$ano}");
        $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
        $style->applyFromArray($headerStyle1);
        $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
        $sheet->getRowDimension($linha)->setRowHeight(30);
        $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
              ->getBorders()
              ->getOutline()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

        $linha++;

        $linhaInicial = $linha;

        // Mensalidades
            $sheet->setCellValueByColumnAndRow(2, $linha, "MENSALIDADES");
            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle1);
            $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;

            $coluna = 2;
            $sheet->setCellValueByColumnAndRow(2, $linha, "Mês");
            $sheet->setCellValueByColumnAndRow(3, $linha, "Mensalidades Positivas");
            $sheet->setCellValueByColumnAndRow(4, $linha, "Mensalidades Perdidas");
            $sheet->setCellValueByColumnAndRow(5, $linha, "Positivas-Perdidas");
            $sheet->setCellValueByColumnAndRow(6, $linha, "Saldo Acumulado");
            for ($i=2; $i <= 6; $i++) {
                $sheet->getColumnDimensionByColumn($i)->setWidth(23);
            }

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle5);

            $sheet->getRowDimension($linha)->setRowHeight(20);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $linha++;

            foreach ($meses as $mesAnalise) {
                $nome = Util::getMes($mesAnalise);
                $nome = ExcelUtil::parseValue($nome);
                $sheet->setCellValueByColumnAndRow(2, $linha, $nome);

                $mensalidadesPositivas = "={$nome}!" . $colunasSomaPositiva[$mesAnalise];
                $mensalidadesNegativas = "={$nome}!" . $colunasSomaNegativa[$mesAnalise];
                $diferenca = "={$nome}!" . $colunasDiferencaValores[$mesAnalise];

                $sheet->setCellValueByColumnAndRow(3, $linha, $mensalidadesPositivas);
                $sheet->getStyleByColumnAndRow(3, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $sheet->setCellValueByColumnAndRow(4, $linha, $mensalidadesNegativas);
                $sheet->getStyleByColumnAndRow(4, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $sheet->setCellValueByColumnAndRow(5, $linha, $diferenca);
                $sheet->getStyleByColumnAndRow(5, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                if ($mesAnalise == 1) {
                    $sheet->setCellValueByColumnAndRow(6, $linha, $diferenca);
                } else {
                    $formula = "=F" . ($linha - 1) . " + E{$linha}";
                    $sheet->setCellValueByColumnAndRow(6, $linha, $formula);
                }
                $sheet->getStyleByColumnAndRow(6, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $linha++;
            }
            $linhaFinal = $linha - 1;

            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

            for ($i=3; $i <= 5 ; $i++) {
                $columnLetter = Coordinate::stringFromColumnIndex($i);
                $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
                $sheet->setCellValueByColumnAndRow($i, $linha, $valor);
                $sheet->getStyleByColumnAndRow($i, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
            }

            $colunaSaldoAcumulado = "E{$linha}";
            $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);
            $sheet->getStyleByColumnAndRow(5, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $linha++;
            $sheet->setCellValueByColumnAndRow(2, $linha, "MÉDIA MENSAL");

            $mesesDivisao = 12;
            if ($ano == date('Y')) {
                $mesesDivisao = date('m');
            }
            $sheet->setCellValueByColumnAndRow(3, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);

            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

        $linha += 2;

        // Implantações
            $linhaInicial = $linha;

            $sheet->setCellValueByColumnAndRow(2, $linha, "IMPLANTAÇÕES");
            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle1);
            $sheet->mergeCellsByColumnAndRow(2, $linha, 6, $linha);
            $sheet->getRowDimension($linha)->setRowHeight(20);
            $linha++;

            $sheet->setCellValueByColumnAndRow(2, $linha, "Mês");
            $sheet->setCellValueByColumnAndRow(3, $linha, "Implantações");
            $sheet->setCellValueByColumnAndRow(5, $linha, "Saldo Acumulado");
            $sheet->mergeCellsByColumnAndRow(3, $linha, 4, $linha);
            $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);

            $style = $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha);
            $style->applyFromArray($subHeaderStyle5);

            $sheet->getRowDimension($linha)->setRowHeight(20);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);
            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $linha++;

            foreach ($meses as $mesAnalise) {
                $nome = Util::getMes($mesAnalise);
                $nome = ExcelUtil::parseValue($nome);
                $sheet->setCellValueByColumnAndRow(2, $linha, $nome);

                $somaImplantacoesMes = "={$nome}!" . $colunasSomaImplantacoes[$mesAnalise];

                $sheet->setCellValueByColumnAndRow(3, $linha, $somaImplantacoesMes);
                $sheet->getStyleByColumnAndRow(3, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $sheet->mergeCellsByColumnAndRow(3, $linha, 4, $linha);

                if ($mesAnalise == 1) {
                    $sheet->setCellValueByColumnAndRow(5, $linha, $somaImplantacoesMes);
                } else {
                    $saldoAcumuladoAnterior = Coordinate::stringFromColumnIndex(5) . ($linha - 1);
                    $somaMes = Coordinate::stringFromColumnIndex(3) . $linha;

                    $formula = "={$saldoAcumuladoAnterior} + {$somaMes}";
                    $sheet->setCellValueByColumnAndRow(5, $linha, $formula);
                }
                $sheet->getStyleByColumnAndRow(5, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
                $sheet->mergeCellsByColumnAndRow(5, $linha, 6, $linha);

                $linha++;
            }
            $linhaFinal = $linha - 1;

            $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

            $columnLetter = Coordinate::stringFromColumnIndex(3);
            $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
            $sheet->setCellValueByColumnAndRow(3, $linha, $valor);
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);
            $colunaSaldoAcumulado = "{$columnLetter}{$linha}";
            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $linha++;

            $sheet->setCellValueByColumnAndRow(2, $linha, "MÉDIA MENSAL");

            $mesesDivisao = 12;
            if ($ano == date('Y')) {
                $mesesDivisao = date('m');
            }
            $sheet->setCellValueByColumnAndRow(3, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
            $sheet->getStyleByColumnAndRow(3, $linha)
                  ->getNumberFormat()
                  ->setFormatCode('"R$ "#,##0.00_-');
            $sheet->mergeCellsByColumnAndRow(3, $linha, 6, $linha);

            $sheet->getStyleByColumnAndRow(3, $linha, 6, $linha)
                  ->getAlignment()
                  ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $sheet->getStyleByColumnAndRow(2, $linha, 6, $linha)->getFont()->setBold(true);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getInside()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            $sheet->getStyleByColumnAndRow(2, $linhaInicial, 6, $linha)
                  ->getBorders()
                  ->getOutline()
                  ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

    $linha += 2;

    // Anos anteriores
        if (!empty($analiseGeral)) {
            $colunaInicial = 8;
            foreach ($analiseGeral as $ano => $analise) {
                $linha = 2;
                $coluna = $colunaInicial;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, "ANÁLISE GERAL {$ano}");
                $style = $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+4), $linha);
                $style->applyFromArray($headerStyle1);
                $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+4), $linha);
                $sheet->getRowDimension($linha)->setRowHeight(30);
                $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+4), $linha)
                      ->getBorders()
                      ->getOutline()
                      ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);
                $linha++;

                $linhaInicial = $linha;

                // Mensalidades
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "MENSALIDADES");
                    $style = $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+4), $linha);
                    $style->applyFromArray($subHeaderStyle1);
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+4), $linha);
                    $sheet->getRowDimension($linha)->setRowHeight(20);
                    $linha++;

                    $coluna = $colunaInicial;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Mês");
                    $coluna++;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Mensalidades Positivas");
                    $coluna++;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Mensalidades Perdidas");
                    $coluna++;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Positivas-Perdidas");
                    $coluna++;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Saldo Acumulado");

                    for ($i = $colunaInicial; $i <= $coluna; $i++) {
                        $sheet->getColumnDimensionByColumn($i)->setWidth(23);
                    }

                    $style = $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha);
                    $style->applyFromArray($subHeaderStyle5);

                    $sheet->getRowDimension($linha)->setRowHeight(20);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha)->getFont()->setBold(true);
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $linha++;

                    foreach ($meses as $mesAnalise) {
                        $mesAnalise = ltrim($mesAnalise,"0");
                        $coluna = $colunaInicial;

                        $nome = Util::getMes($mesAnalise);
                        $nome = ExcelUtil::parseValue($nome);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $nome);
                        $coluna++;

                        $mensalidadesPositivas = $analise[$mesAnalise]['mensalidadesPositivas'];
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $mensalidadesPositivas);
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        $coordenadaMensalidadePositiva = Coordinate::stringFromColumnIndex($coluna) . $linha;
                        $coluna++;

                        $mensalidadesNegativas = $analise[$mesAnalise]['mensalidadesNegativas'];
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $mensalidadesNegativas);
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        $coordenadaMensalidadeNegativa = Coordinate::stringFromColumnIndex($coluna) . $linha;
                        $coluna++;

                        $diferenca = "={$coordenadaMensalidadePositiva} - {$coordenadaMensalidadeNegativa}";
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $diferenca);
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        $coluna++;

                        if ($mesAnalise == 1) {
                            $sheet->setCellValueByColumnAndRow($coluna, $linha, $diferenca);
                        } else {
                            $saldoAcumuladoAnterior = Coordinate::stringFromColumnIndex($coluna) . ($linha - 1);
                            $somaMes = Coordinate::stringFromColumnIndex($coluna-1) . $linha;

                            $formula = "={$saldoAcumuladoAnterior} + {$somaMes}";
                            $sheet->setCellValueByColumnAndRow($coluna, $linha, $formula);
                        }
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');

                        $linha++;
                    }
                    $linhaFinal = $linha - 1;

                    $coluna = $colunaInicial;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "TOTAL");
                    $coluna++;

                    for ($i=$coluna; $i <= ($coluna+2); $i++) {
                        $columnLetter = Coordinate::stringFromColumnIndex($i);
                        $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
                        $sheet->setCellValueByColumnAndRow($i, $linha, $valor);
                        $sheet->getStyleByColumnAndRow($i, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                    }
                    $coluna += 2;

                    $colunaSaldoAcumulado = Coordinate::stringFromColumnIndex($coluna) . $linha;
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+1), $linha);
                    $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+1), $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, ($colunaInicial+4), $linha)->getFont()->setBold(true);

                    $linha++;

                    $coluna = $colunaInicial;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "MÉDIA MENSAL");
                    $coluna++;

                    $mesesDivisao = 12;
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+3), $linha);

                    $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+3), $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                    $coluna = $colunaInicial+4;
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha)->getFont()->setBold(true);
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linhaInicial, $coluna, $linha)
                          ->getBorders()
                          ->getInside()
                          ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linhaInicial, $coluna, $linha)
                          ->getBorders()
                          ->getOutline()
                          ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

                $linha += 2;

                // Implantações
                    $linhaInicial = $linha;

                    $sheet->setCellValueByColumnAndRow($colunaInicial, $linha, "IMPLANTAÇÕES");
                    $style = $sheet->getStyleByColumnAndRow($colunaInicial, $linha, ($colunaInicial+4), $linha);
                    $style->applyFromArray($subHeaderStyle1);
                    $sheet->mergeCellsByColumnAndRow($colunaInicial, $linha, ($colunaInicial+4), $linha);
                    $sheet->getRowDimension($linha)->setRowHeight(20);
                    $linha++;

                    $coluna = $colunaInicial;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Mês");
                    $coluna++;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Implantações");
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+1), $linha);
                    $coluna += 2;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "Saldo Acumulado");
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+1), $linha);
                    $coluna++;

                    $style = $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha);
                    $style->applyFromArray($subHeaderStyle5);

                    $sheet->getRowDimension($linha)->setRowHeight(20);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha)->getFont()->setBold(true);
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, $coluna, $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $linha++;

                    foreach ($meses as $mesAnalise) {
                        $mesAnalise = ltrim($mesAnalise,"0");
                        $coluna = $colunaInicial;

                        $nome = Util::getMes($mesAnalise);
                        $nome = ExcelUtil::parseValue($nome);
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $nome);
                        $coluna++;

                        $somaImplantacoesMes = $analise[$mesAnalise]['implantacoes'];
                        $sheet->setCellValueByColumnAndRow($coluna, $linha, $somaImplantacoesMes);
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+1), $linha);
                        $coluna += 2;

                        if ($mesAnalise == 1) {
                            $sheet->setCellValueByColumnAndRow($coluna, $linha, $somaImplantacoesMes);
                        } else {
                            /*$formula = "=J" . ($linha - 1) . " + I{$linha}";
                            $sheet->setCellValueByColumnAndRow(10, $linha, $formula);*/
                            $saldoAcumuladoAnterior = Coordinate::stringFromColumnIndex($coluna) . ($linha - 1);
                            $somaMes = Coordinate::stringFromColumnIndex($coluna-2) . $linha;

                            $formula = "={$saldoAcumuladoAnterior} + {$somaMes}";
                            $sheet->setCellValueByColumnAndRow($coluna, $linha, $formula);
                        }
                        $sheet->getStyleByColumnAndRow($coluna, $linha)
                              ->getNumberFormat()
                              ->setFormatCode('"R$ "#,##0.00_-');
                        $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+1), $linha);

                        $linha++;
                    }
                    $linhaFinal = $linha - 1;
                    $coluna = $colunaInicial;

                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "TOTAL");
                    $coluna++;

                    $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                    $valor = "=SUM({$columnLetter}" . ($linhaInicial+2) . ":{$columnLetter}{$linhaFinal})";
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+3), $linha);
                    $colunaSaldoAcumulado = "{$columnLetter}{$linha}";
                    $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+3), $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, ($colunaInicial+4), $linha)->getFont()->setBold(true);

                    $linha++;

                    $coluna = $colunaInicial;
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "MÉDIA MENSAL");
                    $coluna++;

                    $mesesDivisao = 12;
                    $sheet->setCellValueByColumnAndRow($coluna, $linha, "={$colunaSaldoAcumulado} / {$mesesDivisao}");
                    $sheet->getStyleByColumnAndRow($coluna, $linha)
                          ->getNumberFormat()
                          ->setFormatCode('"R$ "#,##0.00_-');
                    $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna+3), $linha);

                    $sheet->getStyleByColumnAndRow($coluna, $linha, ($coluna+3), $linha)
                          ->getAlignment()
                          ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linha, ($colunaInicial+4), $linha)->getFont()->setBold(true);

                    $coluna = $colunaInicial + 4;
                    $sheet->getStyleByColumnAndRow($colunaInicial, $linhaInicial, $coluna, $linha)
                          ->getBorders()
                          ->getInside()
                          ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

                    $sheet->getStyleByColumnAndRow($colunaInicial, $linhaInicial, $coluna, $linha)
                          ->getBorders()
                          ->getOutline()
                          ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM);

                    $coluna = $colunaInicial + 4;

                $colunaInicial = $coluna + 2;
            }
        }

// Aba Perdas
    $title = "Perdas";
    $sheet = $spreadsheet->createSheet();
    $sheet->setTitle($title);
    $spreadsheet->setActiveSheetIndexByName($title);
    $sheet = $spreadsheet->getActiveSheet();

    $linhaInicial = 2;
    $linha = 2;

    $sheet->getColumnDimensionByColumn(1)->setWidth(5);
    $sheet->getColumnDimensionByColumn(2)->setWidth(35);
    for ($i=3; $i <= 14; $i++) {
        $sheet->getColumnDimensionByColumn($i)->setWidth(15);
    }

    $sheet->setCellValueByColumnAndRow(2, $linha, "PERDAS POR MÊS {$ano}");
    $style = $sheet->getStyleByColumnAndRow(2, $linha, 14, $linha);
    $style->applyFromArray($headerStyle1);
    $sheet->mergeCellsByColumnAndRow(2, $linha, 14, $linha);
    $sheet->getRowDimension($linha)->setRowHeight(25);
    $linha++;

    $coordenadasQtd = array();
    $coordenadasVlr = array();

    // Janeiro a junho
        $coluna = 3;
        for ($i=1; $i <= 6; $i++) {
            $mes = ($i < 10) ? "0{$i}" : $i;
            $nomeMes = Util::getMes($mes);
            $nomeMes = ExcelUtil::parseValue($nomeMes);

            $sheet->setCellValueByColumnAndRow($coluna, $linha, $nomeMes);
            $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna + 1), $linha);

            $sheet->setCellValueByColumnAndRow($coluna, ($linha+1), "Quantidade");
            $sheet->setCellValueByColumnAndRow(($coluna+1), ($linha+1), "Valor");

            $coluna += 2;
        }
        $style = $sheet->getStyleByColumnAndRow(3, $linha, 14, $linha);
        $style->applyFromArray($subHeaderStyle4);

        $sheet->mergeCellsByColumnAndRow(2, $linha, 2, ($linha + 1));
        $linha++;

        $sheet->setCellValueByColumnAndRow(2, ($linha-1), "Motivo");
        $style = $sheet->getStyleByColumnAndRow(2, ($linha - 1), 2, $linha);
        $style->applyFromArray($subHeaderStyle5);

        $style = $sheet->getStyleByColumnAndRow(2, $linha, 14, $linha);
        $style->applyFromArray($subHeaderStyle5);
        $linha++;

        $linhaInicial = $linha;

        foreach ($motivos as $idMotivo => $nome) {
            $coluna = 2;

            $valor = ExcelUtil::parseValue($nome);
            $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
            $coluna++;

            for ($i=1; $i <= 6; $i++) {
                $mes = ($i < 10) ? "0{$i}" : $i;
                $qtd = $perdas[$idMotivo][$mes]['qtd'];
                $valor = $perdas[$idMotivo][$mes]['valor'];

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $qtd);

                $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                if (!isset($coordenadasQtd[$idMotivo])) {
                    $coordenadasQtd[$idMotivo] = array();
                }
                $coordenadasQtd[$idMotivo][] = "{$columnLetter}{$linha}";

                $coluna++;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                if (!isset($coordenadasVlr[$idMotivo])) {
                    $coordenadasVlr[$idMotivo] = array();
                }
                $coordenadasVlr[$idMotivo][] = "{$columnLetter}{$linha}";

                $coluna++;
            }

            $linha++;
        }

        $linhaFinal = $linha - 1;

        $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

        for ($i=3; $i <= 14 ; $i++) {
            $columnLetter = Coordinate::stringFromColumnIndex($i);
            $sheet->setCellValueByColumnAndRow($i, $linha, "=SUM({$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal})");
            if ($i%2 == 0) {
                $sheet->getStyleByColumnAndRow($i, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
            }
        }
        $sheet->getStyleByColumnAndRow(2, $linha, 14, $linha)->getFont()->setBold(true);

        $sheet->getStyleByColumnAndRow(2, $linhaInicial, 14, $linha)
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

    $linha += 2;

    // Julho a dezembro
        $coluna = 3;
        for ($i=7; $i <= 12; $i++) {
            $mes = ($i < 10) ? "0{$i}" : $i;
            $nomeMes = Util::getMes($mes);
            $nomeMes = ExcelUtil::parseValue($nomeMes);

            $sheet->setCellValueByColumnAndRow($coluna, $linha, $nomeMes);
            $sheet->mergeCellsByColumnAndRow($coluna, $linha, ($coluna + 1), $linha);

            $sheet->setCellValueByColumnAndRow($coluna, ($linha+1), "Quantidade");
            $sheet->setCellValueByColumnAndRow(($coluna+1), ($linha+1), "Valor");

            $coluna += 2;
        }
        $style = $sheet->getStyleByColumnAndRow(3, $linha, 14, $linha);
        $style->applyFromArray($subHeaderStyle4);

        $sheet->mergeCellsByColumnAndRow(2, $linha, 2, ($linha + 1));
        $linha++;

        $sheet->setCellValueByColumnAndRow(2, ($linha-1), "Motivo");
        $style = $sheet->getStyleByColumnAndRow(2, ($linha - 1), 2, $linha);
        $style->applyFromArray($subHeaderStyle5);

        $style = $sheet->getStyleByColumnAndRow(2, $linha, 14, $linha);
        $style->applyFromArray($subHeaderStyle5);
        $linha++;

        $linhaInicial = $linha;
        foreach ($motivos as $idMotivo => $nome) {
            $coluna = 2;

            $valor = ExcelUtil::parseValue($nome);
            $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
            $coluna++;

            for ($i=7; $i <= 12; $i++) {
                $mes = ($i < 10) ? "0{$i}" : $i;
                $qtd = $perdas[$idMotivo][$mes]['qtd'];
                $valor = $perdas[$idMotivo][$mes]['valor'];

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $qtd);

                $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                if (!isset($coordenadasQtd[$idMotivo])) {
                    $coordenadasQtd[$idMotivo] = array();
                }
                $coordenadasQtd[$idMotivo][] = "{$columnLetter}{$linha}";

                $coluna++;

                $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
                $sheet->getStyleByColumnAndRow($coluna, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');

                $columnLetter = Coordinate::stringFromColumnIndex($coluna);
                if (!isset($coordenadasVlr[$idMotivo])) {
                    $coordenadasVlr[$idMotivo] = array();
                }
                $coordenadasVlr[$idMotivo][] = "{$columnLetter}{$linha}";

                $coluna++;
            }

            $linha++;
        }
        $linhaFinal = $linha - 1;

        $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL");

        for ($i=3; $i <= 14 ; $i++) {
            $columnLetter = Coordinate::stringFromColumnIndex($i);
            $sheet->setCellValueByColumnAndRow($i, $linha, "=SUM({$columnLetter}{$linhaInicial}:{$columnLetter}{$linhaFinal})");
            if ($i%2 == 0) {
                $sheet->getStyleByColumnAndRow($i, $linha)
                      ->getNumberFormat()
                      ->setFormatCode('"R$ "#,##0.00_-');
            }
        }
        $sheet->getStyleByColumnAndRow(2, $linha, 14, $linha)->getFont()->setBold(true);

        $sheet->getStyleByColumnAndRow(2, $linhaInicial, 14, $linha)
              ->getBorders()
              ->getAllBorders()
              ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

    $linha += 3;

    $sheet->setCellValueByColumnAndRow(2, $linha, "PERDAS ANO {$ano}");
    $style = $sheet->getStyleByColumnAndRow(2, $linha, 4, $linha);
    $style->applyFromArray($headerStyle1);
    $sheet->mergeCellsByColumnAndRow(2, $linha, 4, $linha);
    $sheet->getRowDimension($linha)->setRowHeight(25);
    $linha++;

    $sheet->setCellValueByColumnAndRow(2, $linha, "Motivo");
    $sheet->setCellValueByColumnAndRow(3, $linha, "Quantidade");
    $sheet->setCellValueByColumnAndRow(4, $linha, "Valor");
    $style = $sheet->getStyleByColumnAndRow(2, $linha, 4, $linha);
    $style->applyFromArray($subHeaderStyle5);
    $linha++;

    $colunasMotivos = array(
        'qtd' => array(),
        'valor' => array(),
    );
    $colunasAdendosTransferencias = array(
        'qtd' => array(),
        'valor' => array(),
    );

    $linhaInicial = $linha;
    foreach ($motivos as $idMotivo => $nome) {
        $coluna = 2;

        $qtds = $coordenadasQtd[$idMotivo];
        $vlrs = $coordenadasVlr[$idMotivo];

        $valor = ExcelUtil::parseValue($nome);
        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

        $valor = "=" . implode("+", $qtds);
        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $columnLetter = Coordinate::stringFromColumnIndex($coluna);
        $coordenadaQtd = "{$columnLetter}{$linha}";

        $coluna++;

        $valor = "=" . implode("+", $vlrs);
        $sheet->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $sheet->getStyleByColumnAndRow($coluna, $linha)
              ->getNumberFormat()
              ->setFormatCode('"R$ "#,##0.00_-');
        $columnLetter = Coordinate::stringFromColumnIndex($coluna);
        $coordenadaValor = "{$columnLetter}{$linha}";

        if (is_numeric($idMotivo)) {
            $colunasMotivos['qtd'][] = $coordenadaQtd;
            $colunasMotivos['valor'][] = $coordenadaValor;
        } else {
            $colunasAdendosTransferencias['qtd'][] = $coordenadaQtd;
            $colunasAdendosTransferencias['valor'][] = $coordenadaValor;
        }

        $linha++;
    }
    $linhaFinal = $linha - 1;

    // Clientes perdidos
        $sheet->setCellValueByColumnAndRow(2, $linha, "Clientes Perdidos");

        $valor = "=" . implode("+", $colunasMotivos['qtd']);
        $sheet->setCellValueByColumnAndRow(3, $linha, $valor);

        $valor = "=" . implode("+", $colunasMotivos['valor']);
        $sheet->setCellValueByColumnAndRow(4, $linha, $valor);
        $sheet->getStyleByColumnAndRow(4, $linha)
              ->getNumberFormat()
              ->setFormatCode('"R$ "#,##0.00_-');

        $sheet->getStyleByColumnAndRow(2, $linha, 4, $linha)->getFont()->setBold(true);
        $linha++;

    // Adendos e transferências
        $sheet->setCellValueByColumnAndRow(2, $linha, "Adendos & Transferências");

        $valor = "=" . implode("+", $colunasAdendosTransferencias['qtd']);
        $sheet->setCellValueByColumnAndRow(3, $linha, $valor);

        $valor = "=" . implode("+", $colunasAdendosTransferencias['valor']);
        $sheet->setCellValueByColumnAndRow(4, $linha, $valor);
        $sheet->getStyleByColumnAndRow(4, $linha)
              ->getNumberFormat()
              ->setFormatCode('"R$ "#,##0.00_-');

        $sheet->getStyleByColumnAndRow(2, $linha, 4, $linha)->getFont()->setBold(true);
        $linha++;

    // Total perdas
        $sheet->setCellValueByColumnAndRow(2, $linha, "TOTAL - PERDAS");

        $valor = "=SUM(C{$linhaInicial}:C{$linhaFinal})";
        $sheet->setCellValueByColumnAndRow(3, $linha, $valor);

        $valor = "=SUM(D{$linhaInicial}:D{$linhaFinal})";
        $sheet->setCellValueByColumnAndRow(4, $linha, $valor);
        $sheet->getStyleByColumnAndRow(4, $linha)
              ->getNumberFormat()
              ->setFormatCode('"R$ "#,##0.00_-');

        $sheet->getStyleByColumnAndRow(2, $linha, 4, $linha)->getFont()->setBold(true);

    $sheet->getStyleByColumnAndRow(2, $linhaInicial, 4, $linha)
          ->getBorders()
          ->getAllBorders()
          ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

    $linha += 2;

$spreadsheet->removeSheetByIndex(0);
$spreadsheet->setActiveSheetIndex(0);

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
