I created a process to make a Insert in a worksheet in EXCEL but the data is not being inserted. It returns no error.
Code:
private void AtualizarPerformanceEntrega()
{
try
{
DataTable dt = ExportPerformanceEntrega();
//string sFileXLSX = @"C:\Entrega\Performance.xlsx"; //ConfigurationManager.AppSettings["ExportPerformanceEntrega"]
if (File.Exists(@"C:\Entrega\Performance.xlsx"))
{
string strConnXLSX = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Entrega\Performance.xlsx; Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False';");
foreach (DataRow row in dt.Rows)
{
using (OleDbConnection connection = new OleDbConnection(strConnXLSX))
{
string strSQL;
OleDbCommand cmd;
//OleDbDataReader dts;
if ((string)row["StatusEntrega"] == "No Prazo" || (string)row["StatusEntrega"] == "Fora do Prazo" || (string)row["StatusEntrega"] == "Antes do Prazo")
{
if (dt.Rows.Count > 0)
{
strSQL = "INSERT INTO [BaseEntregue$] " +
" (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
" VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
" @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
cmd = new OleDbCommand(strSQL, connection);
cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
connection.Open();
//Executando o INSERT
cmd.ExecuteNonQuery();
//Fechando a conexão
connection.Close();
}
}
if ((string)row["StatusEntrega"] == "Não Entregue")
{
if (dt.Rows.Count > 0)
{
strSQL = "INSERT INTO [BaseNaoEntregue$] " +
" (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
" VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
" @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
cmd = new OleDbCommand(strSQL, connection);
cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));
connection.Open();
//Executando o INSERT
cmd.ExecuteNonQuery();
//Fechando a conexão
connection.Close();
}
}
connection.Close();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}