Browse excel by columns in C #

Question

Browse excel by columns in C #

Navigation

#1 by (1 votes)

0

Good day community, I'm stuck on a problem that I'm sure is something minimal, I'm making an application in .Net with MVC 2 (ASPX). and I have some excel that upload via fileupload to different tables in the database all well I go through the excel well when it is to the side I explain myself with this image

When it comes to traversing by row from left to right I can capture the data well with this code

DataTable dt = ds.Tables[0];


        String format = "yyyy/MM/dd"; // formato de fecha Año mes dia incluyendo los 0 para buscar por fecha específica
        String formatsql = "yyyy-MM-dd HH:mm:ss";
        CultureInfo chile = new CultureInfo("es-CL");

        MVELEntitiess dbCont = new MVELEntitiess();
        var fechames = DateTime.Now.AddMonths(-1);
        string mesconcero = fechames.ToString(format);
        string messolo = mesconcero.ToString().Substring(5, 2);

        var tbtasacambio = dbCont.Set<tb_tasa_cambio>();
        var fecha = dt.Rows[2];
        foreach (DataRow dnom in dt.Rows)
        {
            //creo una variable que situa en la posicion cero mi Nombre de barra
            var dia = dnom[0].ToString();
            string fechasubstring = fecha + "/" + dia;
            var fechatotal = Convert.ToDateTime(fechasubstring);
            var valorDolar = dnom[1];
            String fechasql1 = fechatotal.ToString(formatsql);
            var diasubs = fechasql1.ToString().Substring(8, 2);
            string descripcion = "Dolar";
            var selectId = dbCont.tb_tasa_cambio.Where(w => w.Fecha.ToString().ToLower() == fecha.ToString().ToLower()).AsEnumerable().Select(s => s.Id).ToList(); // Igualo el nombre del archivo excel con la descripcion de la barra
            var id2 = dbCont.tb_tasa_cambio.Select(s => s.Id).ToList();
            int idvalormax = id2.Max();
            int idsql = idvalormax + 1;
            if (selectId.Count() >= 0)//Si el nombre existe en la BBDD realiza el proceso
            {


                DataRow[] drExcel = ds.Tables[0].Select("Dia = '" + diasubs + "'");

                var count = from query5 in dbCont.tb_tasa_cambio//Query para comparar datos obtenidos de excel con la DDBB si es != de null se actualiza
                            where query5.Fecha.ToString().ToLower() == fecha.ToString().ToLower() && query5.Valor.ToString().ToLower() == valorDolar.ToString().ToLower()
                            && query5.Descripcion_moneda.ToString().ToLower() == descripcion.ToString().ToLower()
                            select query5;


                if ((count != null) && (count.Count() > 0))
                {
                    var update = from query5 in dbCont.tb_tasa_cambio//Query para comparar datos obtenidos de excel con la DDBB si es != de null se actualiza
                                 where query5.Fecha.ToString().ToLower() == fecha.ToString().ToLower() && query5.Valor.ToString().ToLower() == valorDolar.ToString().ToLower()
                                 && query5.Descripcion_moneda.ToString().ToLower() == descripcion.ToString().ToLower()
                                 select query5;


                    foreach (var campos in update)
                    {

                        campos.Valor = Convert.ToDecimal(valorDolar);

                    }
                    dbCont.SaveChanges();
                }
                else
                {
                    if ((count == null) || (count.Count() == 0))
                    {
                        tbtasacambio.Add(new tb_tasa_cambio
                        {

                            Descripcion_moneda = Convert.ToString(descripcion),
                            Id = Convert.ToInt32(idsql),
                            Valor = Convert.ToDecimal(valorDolar),
                            Fecha = Convert.ToDateTime(fechasql1),


                        });
                        dbCont.SaveChanges();
                        //}


                    }
                }
            }
            else // Si no existe envía una alerta pero carga el resto
            {
                ViewBag.ErrorMessage = "No se ha podido actualizar/insertar" + " " + valorDolar + fechasubstring;

            }
        }
    }

But when the excel template comes with data stacked by columns, I explain myself in this image.

Do not know how to do it? The first example I do with foreach and it turns out well, my problem is to go over the excel in this case companies is the name of my columns and the values are down.

Reply is welcome.

c#

asked by Joel Baez 11.06.2018 в 18:28

source

1 answer

Ionic 3, transparent sidemenu Why do I have a "java.util.InputMismatchException" in my scanner?

score 1 · Accepted Answer

It should be easier if you traversed the matrix with a 'for' instead of a 'foreach'. This is an example of how to traverse the second matrix with a 'for' per column knowing the number of rows:

static void Main(string[] args)
{
    DataTable table = new DataTable();
    table.Columns.Add("AA");
    table.Columns.Add("BB");
    table.Columns.Add("CC");
    table.Columns.Add("DD");

    var valor1 = table.NewRow();
    valor1["AA"] = 1;
    valor1["BB"] = 4;
    valor1["CC"] = 7;
    valor1["DD"] = 10;
    table.Rows.Add(valor1);
    var valor2 = table.NewRow();
    valor2["AA"] = 2;
    valor2["BB"] = 5;
    valor2["CC"] = 8;
    valor2["DD"] = 11;
    table.Rows.Add(valor2);
    var valor3 = table.NewRow();
    valor3["AA"] = 3;
    valor3["BB"] = 6;
    valor3["CC"] = 9;
    valor3["DD"] = 12;
    table.Rows.Add(valor3);

    for (int c = 0; c < table.Columns.Count; c++)
    {
        Console.WriteLine("Property {0}", table.Columns[c].ColumnName);
        Console.WriteLine("Valor1 {0}", table.Rows[0][c]);
        Console.WriteLine("Valor2 {0}", table.Rows[1][c]);
        Console.WriteLine("Valor3 {0}", table.Rows[2][c]);
    }

    Console.Read();

}

One recommendation, for representative indices is a good practice to use variables or constants instead of magic numbers. This makes it much easier to read the code.

private const int VALOR1 = 0;
private const int VALOR2 = 1;
private const int VALOR3 = 2;

Then

for (int c = 0; c < table.Columns.Count; c++)
{
    Console.WriteLine("Property {0}", table.Columns[c].ColumnName);
    Console.WriteLine("Valor1 {0}", table.Rows[VALOR1][c]);
    Console.WriteLine("Valor2 {0}", table.Rows[VALOR2][c]);
    Console.WriteLine("Valor3 {0}", table.Rows[VALOR3][c]);
}