using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Telerik.WinControls;
using MySql.Data.MySqlClient;
using System.Configuration;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System.IO;

namespace Gerencial.Formularios.Relatorios.Representantes
{
    public partial class Principal : Telerik.WinControls.UI.RadForm
    {
        #region Variáveis

        private DateTime dataInicial;
        private DateTime dataFinal;
        private string strCaminho;
        public bool ProgressStatus = true; 
        

        #endregion Variáveis

        #region Propriedades

        private MySqlConnection Connection { get; set; }

        #endregion Propriedades

        #region Connection

        public void OpenConnection()
        {
            try
            {
                //Fornece acesso ao arquivo de configuração 
                string config = ConfigurationManager.ConnectionStrings["crmConnectionString"].ConnectionString;

                //Representa uma conexão aberta a um base de dados Firbird.
                Connection = new MySqlConnection(config);

                //Abre uma conexão de base de dados com as configurações de propriedades especificadas 
                Connection.Open();

            }
            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro ao abrir a conexão ao banco de dados. Erro : " + ex.Message);
            }
        }

        public void CloseConnection()
        {
            try
            {
                if (Connection != null)
                {
                    Connection.Close();
                    Connection = null;
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Ocorreu um erro ao abrir a conexão ao banco de dados. Erro : " + ex.Message);
            }
        }

        #endregion Connection

        #region Construtor

        public Principal()
        {
            InitializeComponent();
        }

        #endregion Construtor

        #region Eventos - Formulário

        private void Principal_Load(object sender, EventArgs e)
        {
            try
            {
                DateTime data = DateTime.Now;
                DateTime mesAnterior = data.AddMonths(-1);
                DateTime primeiroDia = new DateTime(mesAnterior.Year, mesAnterior.Month, 1);
                DateTime ultimoDia = new DateTime(mesAnterior.Year, mesAnterior.Month,
                DateTime.DaysInMonth(mesAnterior.Year, mesAnterior.Month));
                radDateTimePickerDataInicial.Value = primeiroDia;
                radDateTimePickerDataFinal.Value = ultimoDia;
            }
            catch (ApplicationException ae)
            {
                RadMessageBox.Show(ae.Message, "Alerta", MessageBoxButtons.OK, RadMessageIcon.Exclamation);
            }
            catch (Exception ex)
            {
                RadMessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, RadMessageIcon.Error);
            }
        }

        #endregion Eventos - Formulário

        #region Eventos - Botões

        private void radButtonGerarRelatorio_Click(object sender, EventArgs e)
        {
            try
            {
                //Método para validar os campos da competência 
                if (!Validar())
                {
                    throw new ApplicationException("Não foi possivel gerar o relatório dos representantes , verifique o(s) campo(s) inválidos !");
                }

                //Definir Data Inicial e Final
                this.dataInicial = new DateTime(radDateTimePickerDataInicial.Value.Year, radDateTimePickerDataInicial.Value.Month, radDateTimePickerDataInicial.Value.Day);
                this.dataFinal   = new DateTime(radDateTimePickerDataFinal.Value.Year, radDateTimePickerDataFinal.Value.Month, radDateTimePickerDataFinal.Value.Day);

                //Selecionar Caminho
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    //Define caminho
                    strCaminho = saveFileDialog.FileName;
                    this.Cursor = Cursors.WaitCursor;
                    backgroundWorker.RunWorkerAsync();
                }
            }
            catch (ApplicationException ae)
            {
                RadMessageBox.Show(ae.Message, "Alerta", MessageBoxButtons.OK, RadMessageIcon.Exclamation);
            }
            catch (Exception ex)
            {
                RadMessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, RadMessageIcon.Error);
            }
        }

        #endregion Eventos - Botões

        #region Database

        private DataTable ConsultarQuantidadeContas()
        {
            try
            {
                //Variavel local
                MySqlCommand command;
                MySqlParameter parameter;
                MySqlDataAdapter dataAdapter;

                //Abre Conexão 
                OpenConnection();

                //Consulta
                command = new MySqlCommand(@"
                SELECT
                CONCAT(users.first_name,' ',last_name) AS Nome,
                CASE 
                    WHEN accounts.account_type IS NULL 						THEN 'Nao informado'
                    WHEN accounts.account_type = '' 					    THEN 'Nao informado'
	                WHEN accounts.account_type = 'Prospect' 				THEN 'Prospect Cliente'
                    WHEN accounts.account_type = 'Customer'					THEN 'Cliente'
                    WHEN accounts.account_type = 'ExCliente'				THEN 'Ex-Cliente'
                    WHEN accounts.account_type = 'ProspectParceiro'	        THEN 'Prospect Parceiro'
                    WHEN accounts.account_type = 'Partner'					THEN 'Parceiro'
                    WHEN accounts.account_type = 'ExParceiro'				THEN 'Ex-Parceiro'
	                WHEN accounts.account_type = 'Fornecedor'				THEN 'Fornecedor'
		                ELSE  accounts.account_type   /*Exibir nome caso não esteja traduzido */
 	                END as Tipo,
                COUNT(accounts.id)                     AS Total
                FROM
                accounts
                JOIN users
                ON accounts.assigned_user_id = users.id
                WHERE users.id NOT IN 			        (	'1',                                    /*admin*/
																													)
                AND users.`status` = 'Active'
                AND DATE(accounts.date_entered) BETWEEN @dataInicial AND @dataFinal
                GROUP BY 1,2", Connection);

                parameter = new MySqlParameter("@dataInicial", dataInicial);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);


                parameter = new MySqlParameter("@dataFinal", dataFinal);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);

                //Executa
                dataAdapter = new MySqlDataAdapter(command);

                // Carrega resultados
                DataTable DatatableQuantidadeContas = new DataTable();
                dataAdapter.Fill(DatatableQuantidadeContas);

                return DatatableQuantidadeContas;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro : " + ex.Message);
            }
        }

        private DataTable ConsultarQuantidadeLigacao()
        {
            try
            {
                //Variavel local
                MySqlCommand command;
                MySqlParameter parameter;
                MySqlDataAdapter dataAdapter;

                //Abre Conexão 
                OpenConnection();

                //Consulta
                command = new MySqlCommand(@"
                SELECT
                CONCAT(first_name,' ',last_name)  AS Nome,
                COUNT(calls.id)                   AS Total 
                FROM 
                calls
                JOIN users ON
                calls.assigned_user_id = users.id 
                WHERE users.id NOT IN 			  (	'1',                                    /*admin*/
													 
												   )
                AND users.`status` = 'Active'
                AND DATE(calls.date_entered) BETWEEN @dataInicial AND @dataFinal
                GROUP BY 1;", Connection);

                parameter = new MySqlParameter("@dataInicial", dataInicial);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);


                parameter = new MySqlParameter("@dataFinal", dataFinal);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);

                //Executa
                dataAdapter = new MySqlDataAdapter(command);

                // Carrega resultados
                DataTable DatatableQuantidadeLigacao = new DataTable();
                dataAdapter.Fill(DatatableQuantidadeLigacao);

                return DatatableQuantidadeLigacao;
            }
            catch (Exception ex)
            {
               throw new Exception("Erro : " + ex.Message);
            }
        }

        private DataTable ConsultarQuantidadeAnexos()
        {
            try
            {
                //Variavel local
                MySqlCommand command;
                MySqlParameter parameter;
                MySqlDataAdapter dataAdapter;

                //Abre Conexão 
                OpenConnection();

                //Consulta
                command = new MySqlCommand(@"
                SELECT 
                CONCAT(users.first_name,' ',last_name) AS Nome,
                COUNT(notes.id)                        AS Total
                FROM
                notes
                JOIN users
                ON notes.created_by = users.id
                WHERE users.id NOT IN 		  (	'1',                                    /*admin*/
												
											   )
                AND users.`status` = 'Active'
                AND DATE(notes.date_entered) BETWEEN @dataInicial AND @dataFinal
                GROUP BY 1 ;", Connection);

                parameter = new MySqlParameter("@dataInicial", dataInicial);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);


                parameter = new MySqlParameter("@dataFinal", dataFinal);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);

                //Executa
                dataAdapter = new MySqlDataAdapter(command);

                // Carrega resultados
                DataTable DatatableQuantidadeAnexos = new DataTable();
                dataAdapter.Fill(DatatableQuantidadeAnexos);

                return DatatableQuantidadeAnexos;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro : " + ex.Message);
            }
        }

        private DataTable ConsultarQuantidadeReunioes()
        {
            try 
            {
                //Variavel local
                MySqlCommand     command;
                MySqlParameter   parameter;
                MySqlDataAdapter dataAdapter;

                //Abre Conexão 
                OpenConnection();

                //Consulta
                command = new MySqlCommand(@"
                SELECT
                CONCAT(users.first_name,' ',last_name) AS Nome,
                COUNT(meetings.id)                     AS Total
                FROM
                meetings
                JOIN users
                ON meetings.assigned_user_id = users.id
                WHERE users.id NOT IN 			(	'1',                                    /*admin*/
												    
									             )
                AND users.`status` = 'Active'
                AND DATE(meetings.date_entered) BETWEEN @dataInicial AND @dataFinal
                GROUP BY 1;", Connection);

                parameter = new MySqlParameter("@dataInicial", dataInicial);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);


                parameter = new MySqlParameter("@dataFinal", dataFinal);
                parameter.DbType = DbType.DateTime;
                parameter.IsNullable = false;
                command.Parameters.Add(parameter);

                //Executa
                dataAdapter = new MySqlDataAdapter(command);

                // Carrega resultados
                DataTable DatatableQuantidadeReunioes = new DataTable();
                dataAdapter.Fill(DatatableQuantidadeReunioes);

                return DatatableQuantidadeReunioes;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro : " + ex.Message);
            }
        }

        #endregion Database

        #region Metodos

        private bool Validar()
        
        {
            //Variável de retorno
            bool valido = true;

            //Limpa ErroProvider 
            errorProvider.Clear();

            //Mensagens
            string msgCampoInvalido = "Campo inválido";

            if (radDateTimePickerDataInicial.Value > DateTime.Now)
            {
               errorProvider.SetError(radDateTimePickerDataInicial, msgCampoInvalido);
               valido = false;
            }

            if (radDateTimePickerDataFinal.Value > DateTime.Now)
            {
                errorProvider.SetError(radDateTimePickerDataFinal, msgCampoInvalido);
                valido = false;
            }

            if (radDateTimePickerDataInicial.Value > radDateTimePickerDataFinal.Value)
            {
                errorProvider.SetError(radDateTimePickerDataFinal, msgCampoInvalido);
                valido = false;
            }

            //Retorna
            return valido;
        }

        #endregion Metodos

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
               #region Excel

                    //Arquivo
                    FileInfo fileInfo = new FileInfo(strCaminho);
                    if (fileInfo.Exists)
                        fileInfo.Delete();

                    using (ExcelPackage package = new ExcelPackage(fileInfo))
                    {
                        #region Contas
                        DataTable dtQuantidadeContas = ConsultarQuantidadeContas();

                        if (dtQuantidadeContas.Rows.Count > 0)
                        {
                            //Total
                            int total = dtQuantidadeContas.Rows.Count;

                            //Variaveis Rows/Col
                            int ToRow = total + 1;
                            int ToCol = 3; //Total de colunas
                            int SumRow = ToRow + 1;

                            // add a new worksheet to the empty workbook
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Contas");

                            #region Add the headers row 1

                            //Add the headers

                            worksheet.Cells[1, 1].Value = "Nome";
                            worksheet.Cells[1, 2].Value = "Tipo";
                            worksheet.Cells[1, 3].Value = "Total";

                            //Format row hearder 1 style;
                            using (var range = worksheet.Cells[1, 1, 1, ToCol])
                            {
                                range.Style.Font.Bold = true;
                                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                                range.Style.Font.Color.SetColor(Color.Azure);
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            #endregion Add the headers 1

                            #region Add some items in the cells

                            //Add some items in the cells...
                            int row = 1;
                            foreach (DataRow drContas in dtQuantidadeContas.Rows)
                            {
                                row++;

                                //Campos                                       
                                worksheet.SetValue(row, 1, drContas["Nome"]);
                                worksheet.SetValue(row, 2, drContas["Tipo"]);
                                worksheet.SetValue(row, 3, drContas["Total"]);
                            }
                            #endregion Add some items in the cells

                            //Format the values
                            using (var range = worksheet.Cells[2, 1, ToRow, ToCol])
                            {
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            using (var range = worksheet.Cells[1, 1, ToRow, ToCol])
                            {
                                range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Left.Color.SetColor(Color.FromArgb(0, 0, 0));

                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Bottom.Color.SetColor(Color.FromArgb(0, 0, 0));
                            }

                            //Create an autofilter for the range
                            worksheet.Cells[1, 1, ToRow, ToCol].AutoFilter = true;

                            //Excel line freeze
                            worksheet.View.FreezePanes(2, 1);

                            #region Format type cells
                            //Format type cells
                            for (int i = 0; i < total; i++)
                            {
                                //Row
                                row = i + 1;

                                //Campos
                                worksheet.Cells[row, 1].Style.Numberformat.Format = "@";
                                worksheet.Cells[row, 2].Style.Numberformat.Format = "@";
                                worksheet.Cells[row, 3].Style.Numberformat.Format = "@";
                            }

                            #endregion Format type cells

                            //Autofit columns for all cells
                            worksheet.Cells.AutoFitColumns(0);

                            // Change the sheet view to show it in page layout mode
                            worksheet.View.PageLayoutView = false;
                        }
                        #endregion Contas

                        #region Ligações

                        DataTable dtQuantidadeLigacao = ConsultarQuantidadeLigacao();

                        if (dtQuantidadeLigacao.Rows.Count > 0)
                        {
                            //Total
                            int total = dtQuantidadeLigacao.Rows.Count;

                            //Variaveis Rows/Col
                            int ToRow = total + 1;
                            int ToCol = 2; //Total de colunas
                            int SumRow = ToRow + 1;

                            // add a new worksheet to the empty workbook
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Ligações");

                            #region Add the headers row 1

                            //Add the headers

                            worksheet.Cells[1, 1].Value = "Nome";
                            worksheet.Cells[1, 2].Value = "Total";

                            //Format row hearder 1 style;
                            using (var range = worksheet.Cells[1, 1, 1, ToCol])
                            {
                                range.Style.Font.Bold = true;
                                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                                range.Style.Font.Color.SetColor(Color.Azure);
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            #endregion Add the headers 1

                            #region Add some items in the cells

                            //Add some items in the cells...
                            int row = 1;
                            foreach (DataRow drLigacao in dtQuantidadeLigacao.Rows)
                            {
                                row++;

                                //Campos                                       
                                worksheet.SetValue(row, 1, drLigacao["Nome"]);
                                worksheet.SetValue(row, 2, drLigacao["Total"]);
                            }
                            #endregion Add some items in the cells

                            //Format the values
                            using (var range = worksheet.Cells[2, 1, ToRow, ToCol])
                            {
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            using (var range = worksheet.Cells[1, 1, ToRow, ToCol])
                            {
                                range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Left.Color.SetColor(Color.FromArgb(0, 0, 0));

                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Bottom.Color.SetColor(Color.FromArgb(0, 0, 0));
                            }

                            //Create an autofilter for the range
                            worksheet.Cells[1, 1, ToRow, ToCol].AutoFilter = true;

                            //Excel line freeze
                            worksheet.View.FreezePanes(2, 1);

                            #region Format type cells
                            //Format type cells
                            for (int i = 0; i < total; i++)
                            {
                                //Row
                                row = i + 1;

                                //Campos
                                worksheet.Cells[row, 1].Style.Numberformat.Format = "@";
                                worksheet.Cells[row, 2].Style.Numberformat.Format = "@";
                            }

                            #endregion Format type cells

                            //Autofit columns for all cells
                            worksheet.Cells.AutoFitColumns(0);

                            // Change the sheet view to show it in page layout mode
                            worksheet.View.PageLayoutView = false;
                        }

                        #endregion Ligações

                        #region Anexos

                        DataTable dtQuantidadeAnexos = ConsultarQuantidadeAnexos();

                        if (dtQuantidadeAnexos.Rows.Count > 0)
                        {
                            //Total
                            int total = dtQuantidadeAnexos.Rows.Count;

                            //Variaveis Rows/Col
                            int ToRow = total + 1;
                            int ToCol = 2; //Total de colunas
                            int SumRow = ToRow + 1;

                            // add a new worksheet to the empty workbook
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Anexos");

                            #region Add the headers row 1

                            //Add the headers

                            worksheet.Cells[1, 1].Value = "Nome";
                            worksheet.Cells[1, 2].Value = "Total";

                            //Format row hearder 1 style;
                            using (var range = worksheet.Cells[1, 1, 1, ToCol])
                            {
                                range.Style.Font.Bold = true;
                                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                                range.Style.Font.Color.SetColor(Color.Azure);
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            #endregion Add the headers 1

                            #region Add some items in the cells

                            //Add some items in the cells...
                            int row = 1;
                            foreach (DataRow drAnexos in dtQuantidadeAnexos.Rows)
                            {
                                row++;

                                //Campos                                       
                                worksheet.SetValue(row, 1, drAnexos["Nome"]);
                                worksheet.SetValue(row, 2, drAnexos["Total"]);
                            }
                            #endregion Add some items in the cells

                            //Format the values
                            using (var range = worksheet.Cells[2, 1, ToRow, ToCol])
                            {
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            using (var range = worksheet.Cells[1, 1, ToRow, ToCol])
                            {
                                range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Left.Color.SetColor(Color.FromArgb(0, 0, 0));
                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Bottom.Color.SetColor(Color.FromArgb(0, 0, 0));
                            }

                            //Create an autofilter for the range
                            worksheet.Cells[1, 1, ToRow, ToCol].AutoFilter = true;

                            //Excel line freeze
                            worksheet.View.FreezePanes(2, 1);

                            #region Format type cells
                            //Format type cells
                            for (int i = 0; i < total; i++)
                            {
                                //Row
                                row = i + 1;

                                //Campos
                                worksheet.Cells[row, 1].Style.Numberformat.Format = "@";
                                worksheet.Cells[row, 2].Style.Numberformat.Format = "@";
                            }

                            #endregion Format type cells

                            //Autofit columns for all cells
                            worksheet.Cells.AutoFitColumns(0);

                            // Change the sheet view to show it in page layout mode
                            worksheet.View.PageLayoutView = false;
                        }

                        #endregion Anexos

                        #region Reunioes

                        DataTable dtQuantidadeReunioes = ConsultarQuantidadeReunioes();

                        if (dtQuantidadeReunioes.Rows.Count > 0)
                        {
                            //Total
                            int total = dtQuantidadeReunioes.Rows.Count;

                            //Variaveis Rows/Col
                            int ToRow = total + 1;
                            int ToCol = 2; //Total de colunas
                            int SumRow = ToRow + 1;

                            // add a new worksheet to the empty workbook
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Reuniões");

                            #region Add the headers row 1

                            //Add the headers

                            worksheet.Cells[1, 1].Value = "Nome";
                            worksheet.Cells[1, 2].Value = "Total";

                            //Format row hearder 1 style;
                            using (var range = worksheet.Cells[1, 1, 1, ToCol])
                            {
                                range.Style.Font.Bold = true;
                                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                                range.Style.Font.Color.SetColor(Color.Azure);
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            #endregion Add the headers 1

                            #region Add some items in the cells

                            //Add some items in the cells...
                            int row = 1;
                            foreach (DataRow drReunioes in dtQuantidadeReunioes.Rows)
                            {
                                row++;

                                //Campos                                       
                                worksheet.SetValue(row, 1, drReunioes["Nome"]);
                                worksheet.SetValue(row, 2, drReunioes["Total"]);
                            }
                            #endregion Add some items in the cells

                            //Format the values
                            using (var range = worksheet.Cells[2, 1, ToRow, ToCol])
                            {
                                range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            }

                            using (var range = worksheet.Cells[1, 1, ToRow, ToCol])
                            {
                                range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Left.Color.SetColor(Color.FromArgb(0, 0, 0));
                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                range.Style.Border.Bottom.Color.SetColor(Color.FromArgb(0, 0, 0));
                            }

                            //Create an autofilter for the range
                            worksheet.Cells[1, 1, ToRow, ToCol].AutoFilter = true;

                            //Excel line freeze
                            worksheet.View.FreezePanes(2, 1);

                            #region Format type cells
                            //Format type cells
                            for (int i = 0; i < total; i++)
                            {
                                //Row
                                row = i + 1;

                                //Campos
                                worksheet.Cells[row, 1].Style.Numberformat.Format = "@";
                                worksheet.Cells[row, 2].Style.Numberformat.Format = "@";
                            }

                            #endregion Format type cells

                            //Autofit columns for all cells
                            worksheet.Cells.AutoFitColumns(0);

                            // Change the sheet view to show it in page layout mode
                            worksheet.View.PageLayoutView = false;
                        }

                        #endregion Reunioes

                        // set some document properties
                        package.Workbook.Properties.Title = "Relatório dos Representantes";

                        // set some extended property values
                        package.Workbook.Properties.Company = "Infofisco Serviços de Informática Ltda";

                        // save our new workbook and we are done!
                        package.Save();
                    }
                    #endregion Excel
            }
            catch (ApplicationException ae)
            {
                ProgressStatus = false; 
                RadMessageBox.Show(ae.Message,"Alerta",MessageBoxButtons.OK,RadMessageIcon.Exclamation);
            }
            catch (Exception ex)
            {
                ProgressStatus = false; 
                RadMessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, RadMessageIcon.Error);
            }
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //Volta Cursor ao normal 
            this.Cursor = Cursors.Default;

            //Verifica se existem erros 
            if (ProgressStatus)
            {
                RadMessageBox.Show("Operação Relizada com Sucesso", "Informação", MessageBoxButtons.OK, RadMessageIcon.Info);
            }
        }
    }
}