using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;

namespace CreateExcelEpplus
{
    class Program
    {
        static void Main(string[] args)
        {
            string strWorkDir = string.Empty,
                   strBuffer = string.Empty,
                   strFileToWork = string.Empty,
                   strFileToCreate = string.Empty;

            bool bRet = false;

            strWorkDir = ConfigurationManager.AppSettings["WorkDir"].ToString();

            strFileToWork = strWorkDir + ConfigurationManager.AppSettings["FileToWork"].ToString();

            strFileToCreate = strWorkDir + ConfigurationManager.AppSettings["FileToCreate"].ToString();

            strBuffer = File.ReadAllText(strFileToWork, Encoding.Default);

            List<CodigosPostales> lstCp = null;

            lstCp = GetListCp(strBuffer);

            bRet = CreateReporteExcel(lstCp, strFileToCreate);

            if (bRet == true)
            {
                Console.WriteLine("Excel Creado Existosamente");
            }
        }


        static bool CreateReporteExcel(List<CodigosPostales> lstCp, string strFileToCreate)
        {
            bool bRet = false;

            FileInfo newFile = new FileInfo(strFileToCreate);
            newFile.Delete();

            try
            {
                using (ExcelPackage xlsPackage = new ExcelPackage(newFile))
                {
                    ExcelWorksheet wsReporteCodigosPostales = xlsPackage.Workbook.Worksheets.Add("CodigosPostales");
                    HojaReporteCodigosPostales(wsReporteCodigosPostales, lstCp);


                    xlsPackage.Workbook.Properties.Title = "Reporte";
                    xlsPackage.Workbook.Properties.Author = "La Web de Dan";
                    xlsPackage.Save();
                    bRet = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + " " + ex.StackTrace);
            }

            return bRet;
        }

        private static void HojaReporteCodigosPostales(ExcelWorksheet wsReporteCodigosPostales, List<CodigosPostales> lstCp)
        {
            CommonExcel.WorkSheetStyles(wsReporteCodigosPostales);

            int nRow = 1;

            nRow = EncabezadosGeneral(wsReporteCodigosPostales, nRow, false);

            foreach (CodigosPostales item in lstCp)
            {
                CommonExcel.cellNumber(wsReporteCodigosPostales, nRow, 1, item.DCodigo, false, true, CommonExcel.colorBlanco, CommonExcel.colorNegro, CommonExcel.colorBlanco, 8, CommonExcel.horizontalAligmentCenter);
                CommonExcel.cellString(wsReporteCodigosPostales, nRow, 2, nRow, 2, item.DCiudad, false, CommonExcel.colorBlanco, CommonExcel.colorNegro, CommonExcel.colorBlanco, 8);
                CommonExcel.cellString(wsReporteCodigosPostales, nRow, 3, nRow, 3, item.DMnpio, false, CommonExcel.colorBlanco, CommonExcel.colorNegro, CommonExcel.colorBlanco, 8);
                CommonExcel.cellString(wsReporteCodigosPostales, nRow, 4, nRow, 4, item.DAsenta, false, CommonExcel.colorBlanco, CommonExcel.colorNegro, CommonExcel.colorBlanco, 8);
                nRow++;
            }

        }

        #region EncabezadosGeneral

        private static int EncabezadosGeneral(ExcelWorksheet ws, int nRow, bool bShowLastUpdate)
        {
            #region titles Grls
            CommonExcel.cellString(ws, nRow, 1, nRow, 1, "Codigo Postal", false, CommonExcel.colorAzulRey, CommonExcel.colorBlanco, CommonExcel.colorBlanco, 10);
            CommonExcel.cellString(ws, nRow, 2, nRow, 2, "Ciudad", false, CommonExcel.colorAzulRey, CommonExcel.colorBlanco, CommonExcel.colorBlanco, 10);
            CommonExcel.cellString(ws, nRow, 3, nRow, 3, "Municipio", false, CommonExcel.colorAzulRey, CommonExcel.colorBlanco, CommonExcel.colorBlanco, 10);
            CommonExcel.cellString(ws, nRow, 4, nRow, 4, "Asenta", false, CommonExcel.colorAzulRey, CommonExcel.colorBlanco, CommonExcel.colorBlanco, 10);
            #endregion //titlesGrls
            nRow++;

            return nRow;
        }

        #endregion //EncabezadosGeneral


        static List<CodigosPostales> GetListCp(string strBuffer)
        {
            List<CodigosPostales> lstCp = new List<CodigosPostales>();

            foreach (string strLine in strBuffer.Split(new[] { "\r\n" }, StringSplitOptions.None))
            {
                string[] aryFields = strLine.Split(new[] { "|" }, StringSplitOptions.None);

                CodigosPostales cp = new CodigosPostales();

                for (int i = 0; i < aryFields.Count(); i++)
                {
                    switch (i)
                    {
                        case 0:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.DCodigo = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 1:
                            cp.DAsenta = aryFields[i];
                            break;
                        case 2:
                            cp.DTipoAsenta = aryFields[i];
                            break;
                        case 3:
                            cp.DMnpio = aryFields[i];
                            break;
                        case 4:
                            cp.DEstado = aryFields[i];
                            break;
                        case 5:
                            cp.DCiudad = aryFields[i];
                            break;
                        case 6:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.DCP = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 7:
                            cp.CEstado = aryFields[i];
                            break;
                        case 8:
                            cp.COficina = aryFields[i];
                            break;
                        case 9:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.CCP = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 10:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.CTipoAsenta = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 11:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.CMnpio = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 12:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.IdAsentaCPCons = ConvertToInt(aryFields[i]);
                            }
                            break;
                        case 13:
                            cp.DZona = aryFields[i];
                            break;
                        case 14:
                            if (!String.IsNullOrEmpty(aryFields[i]))
                            {
                                cp.CCveCiudad = ConvertToInt(aryFields[i]);
                            }
                            break;
                    }
                }

                lstCp.Add(cp);
            }


            return lstCp;
        }

        static int ConvertToInt(string str)
        {
            int nNum = 0;

            nNum = Convert.ToInt32(str);

            return nNum;
        }
    }
}