public bool Importar(ImportacaoPlanilha importacao) { string sheetName = "importar"; //var connectionString = ""; var pathArquivoPendente = string.Format("{0}{1}", importacao._diretorioPendente, importacao.nomeArquivoProcessado); var pathArquivoProcessado = string.Format("{0}{1}", importacao._diretorioProcessado, importacao.nomeArquivoProcessado); var pathArquivoLog = string.Format("{0}{1}", importacao._diretorioLog, importacao.nomeArquivoErro); try { FileInfo file = new FileInfo(pathArquivoPendente); using (var package = new ExcelPackage(file)) { var currentSheet = package.Workbook.Worksheets; var workSheet = currentSheet.First(p => p.Name.ToLower() == sheetName.ToLower()); //abre a planilha chamada "importar" var totalColuna = workSheet.Dimension.End.Column; var totalLinha = workSheet.Dimension.End.Row; //Importar conteúdo da planilha int totalErros = 0; int totalSucesso = 0; int numeroLinha = 1; //ignora primeira linha cabeçalho for (int rowIterator = 2; rowIterator <= totalLinha; rowIterator++) { //user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString(); numeroLinha++; int columnIterator = 0; try { //Prossegue apenas se existir numero do terminal if (workSheet.Cells[rowIterator, 3].Value != null) { if (!string.IsNullOrEmpty(workSheet.Cells[rowIterator, 3].Value.ToString())) { VendasViva obj = new VendasViva(); #region Mapeamento das colunas excel columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataVenda = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroPDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroLinha = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeVendedor = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePlano = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePacoteDados = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeTipo = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeTitular = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeDependente = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dadosDependente = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroOs = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroICCD = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroContrato = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valorVenda = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } #endregion //Atualizar importacao obj.importacaoPlanilha = importacao; obj.idImportacaoPlanilha = importacao.id; //Inserir no banco this.Salvar(obj); totalSucesso++; } else { totalErros++; var log = string.Format("Linha: {0} - {1}", numeroLinha, "LINHA/TERMINAL não pode ser vazio"); this.GerarArquivoLogErro(pathArquivoLog, log); } } else { totalErros++; var log = string.Format("Linha: {0} - {1}", numeroLinha, "LINHA/TERMINAL não pode ser vazio"); this.GerarArquivoLogErro(pathArquivoLog, log); } } catch (Exception ex) { totalErros++; var log = string.Format("Linha: {0} - {1}", numeroLinha, ex.Message); this.GerarArquivoLogErro(pathArquivoLog, log); } } importacao.dataFimProcessamento = DateTime.Now; importacao.qtdImportacaoSucesso = totalSucesso; importacao.qtdImportacaoIgnorada = totalErros; importacao.idStatus = 1; ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL(); _dalImportacaoPlanilhaDAL.Salvar(importacao); //remover os arquivos temporarios if ((File.Exists(pathArquivoPendente))) { File.Move(pathArquivoPendente, pathArquivoProcessado); } } return(true); } catch (Exception ex) { var mensagemErro = ex.Message; if (mensagemErro.ToUpper().Contains("SEQUENCE CONTAINS NO MATCHING ELEMENT")) { mensagemErro = "Não foi encontrada nenhuma planilha com nome 'importar', favor verificar no EXCEL"; } var log = string.Format("Arquivo: {0} - Erro: {1}", pathArquivoPendente, mensagemErro); this.GerarArquivoLogErro(pathArquivoLog, log); //Cancelar processamento importacao.dataFimProcessamento = DateTime.Now; importacao.idStatus = 3; ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL(); _dalImportacaoPlanilhaDAL.Salvar(importacao); //remover os arquivos temporarios if ((File.Exists(pathArquivoPendente))) { File.Move(pathArquivoPendente, pathArquivoProcessado); } return(false); } }
public bool Importar(ImportacaoPlanilha importacao) { string sheetName = "importar"; //var connectionString = ""; var pathArquivoPendente = string.Format("{0}{1}", importacao._diretorioPendente, importacao.nomeArquivoProcessado); var pathArquivoProcessado = string.Format("{0}{1}", importacao._diretorioProcessado, importacao.nomeArquivoProcessado); var pathArquivoLog = string.Format("{0}{1}", importacao._diretorioLog, importacao.nomeArquivoErro); try { FileInfo file = new FileInfo(pathArquivoPendente); using (var package = new ExcelPackage(file)) { var currentSheet = package.Workbook.Worksheets; var workSheet = currentSheet.First(p => p.Name.ToLower() == sheetName.ToLower()); //abre a planilha chamada "importar" var totalColuna = workSheet.Dimension.End.Column; var totalLinha = workSheet.Dimension.End.Row; //Importar conteúdo da planilha int totalErros = 0; int totalSucesso = 0; int numeroLinha = 1; //ignora primeira linha cabeçalho for (int rowIterator = 2; rowIterator <= totalLinha; rowIterator++) { //user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString(); numeroLinha++; int columnIterator = 0; try { var numeroMSISDN = string.Empty; var descricao = string.Empty; if (workSheet.Cells[rowIterator, 16].Value != null) { numeroMSISDN = workSheet.Cells[rowIterator, 16].Value.ToString(); } if (workSheet.Cells[rowIterator, 4].Value != null) { descricao = workSheet.Cells[rowIterator, 4].Value.ToString(); //caso no arquivo nao venha preenchido o MSISDN, pega o numero do telefone da coluna de Descrição if (string.IsNullOrEmpty(numeroMSISDN)) { //Se no campo descrição contem a palavra MSISDN, então captura o numero telefone if (descricao.Contains("MSISDN")) { //Captura as ultimas 11 posicoes, que correspondem ao telefone numeroMSISDN = descricao.Trim().Substring(descricao.Trim().IndexOf("MSISDN") + 7, 11); } } } //Prossegue apenas se existir (coluna P) numero do terminal ou se a (coluna D) Descrição esta preenchida if (!string.IsNullOrEmpty(descricao) || !string.IsNullOrEmpty(numeroMSISDN)) { PagamentoOperadora obj = new PagamentoOperadora(); #region Mapeamento das colunas excel columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeCicloPagamento = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.tipoIncentivo = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.tipoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; obj.descricao = descricao; //if (workSheet.Cells[rowIterator, columnIterator].Value != null) // obj.descricao = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.categoria = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroPDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroSFIDPDVPagador = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePDVPagador = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataAtivacao = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataDesativacao = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataDocumento = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.motivo = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroContrato = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; obj.numeroMSISDN = numeroMSISDN; //if (workSheet.Cells[rowIterator, columnIterator].Value != null) // obj.numeroMSISDN = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroIMEI = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroICCID = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeAparelho = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeCampanha = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeCampanhaAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeOferta = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.numeroIDBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.dataBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeRelacionamento = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeRelacionamentoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.tipoOiTV = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.isCliente_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.tipoMigracao = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePlanoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePlanoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomePlanoGrupo = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeGrupoPlanoContabil = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeServicoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeServicoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeServicoGrupo = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeGrupoServicoContabil = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.totalVoiceTerminal = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.totalVoiceDias = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.nomeCartaoCredito = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.isPortabilidade_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.isOCT_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.quantidade = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.tipoPex = workSheet.Cells[rowIterator, columnIterator].Value.ToString(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valor = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valorAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valorEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valorCalculado = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } columnIterator++; if (workSheet.Cells[rowIterator, columnIterator].Value != null) { obj.valorPago = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal(); } #endregion //Atualizar importacao obj.importacaoPlanilha = importacao; obj.idImportacaoPlanilha = importacao.id; //Inserir no banco this.Salvar(obj); totalSucesso++; } else { totalErros++; var log = string.Format("Linha: {0} - {1}", numeroLinha, "Campo 'Descrição' ou 'MSISDN/Terminal' não podem ser vazios"); this.GerarArquivoLogErro(pathArquivoLog, log); } } catch (Exception ex) { totalErros++; var log = string.Format("Linha: {0} - {1}", numeroLinha, ex.Message); this.GerarArquivoLogErro(pathArquivoLog, log); } } importacao.dataFimProcessamento = DateTime.Now; importacao.qtdImportacaoSucesso = totalSucesso; importacao.qtdImportacaoIgnorada = totalErros; importacao.idStatus = 1; ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL(); _dalImportacaoPlanilhaDAL.Salvar(importacao); //remover os arquivos temporarios if ((File.Exists(pathArquivoPendente))) { File.Move(pathArquivoPendente, pathArquivoProcessado); } } return(true); } catch (Exception ex) { var mensagemErro = ex.Message; if (mensagemErro.ToUpper().Contains("SEQUENCE CONTAINS NO MATCHING ELEMENT")) { mensagemErro = "Não foi encontrada nenhuma planilha com nome 'importar', favor verificar no EXCEL"; } var log = string.Format("Arquivo: {0} - Erro: {1}", pathArquivoPendente, mensagemErro); this.GerarArquivoLogErro(pathArquivoLog, log); //Cancelar processamento importacao.dataFimProcessamento = DateTime.Now; importacao.idStatus = 3; ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL(); _dalImportacaoPlanilhaDAL.Salvar(importacao); //remover os arquivos temporarios if ((File.Exists(pathArquivoPendente))) { File.Move(pathArquivoPendente, pathArquivoProcessado); } return(false); } }