I need help with a project in C #

0

Good, I'm doing a project in windows form and what it does is upload an excel file pass the data to a dataGridView and save them in a MySQL database, the problem is that this excel shows data of a cycle of 4 months and this is the last month therefore shows FEBRUARY, MARCH, APRIL AND MAY next month will show JUNE, JULY, AUGUST AND SEPTEMBER I do not know how to change the name of the months as it corresponds to each period, I thought about creating the base of data with 12 months and go filling according to the header of the dataGridView but I do not know how I can save it in a variable or something.

I leave the code so you can help me.

THIS IS THE IMPORTAEXCEL CLASS

class importarExcel
{
    OleDbConnection conn;
    OleDbDataAdapter MyDataAdapter;
    DataTable dt;

    public void importarExcel(DataGridView dgv, String nombreHoja, string ruta)
    {
        try
        {

            conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + ruta + ";Extended Properties='Excel 12.0 Xml;HDR=Yes'");
            MyDataAdapter = new OleDbDataAdapter("Select * from [" + nombreHoja + "$]", conn);
            dt = new DataTable();
            MyDataAdapter.Fill(dt);
            dgv.DataSource = dt;

        }
        catch (Exception ex)
        {
            MessageBox.Show("La ruta y/o el Nombre de la hoja son incorrectos");
        }
    }

THIS IS THE BUTTON CODE TO FIND THE FILE

        string ruta = "";

        OpenFileDialog openfile1 = new OpenFileDialog();
        openfile1.Filter = "Excel Files |*.xlsx";
        openfile1.Title = "Seleccione el archivo de Excel";
        if (openfile1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            if (openfile1.FileName.Equals("") == false)
            {
                ruta = openfile1.FileName;
                textBox1.Text = ruta;
                textBox2.Text = "";
                textBox2.Focus();
                textBox1.Enabled = false;
            }
        }

THIS BUTTON CODE TO IMPORT DATA TO DATAGRIDVIEW

string nom = textBox2.Text;
string ruta = textBox1.Text;
new importar().importarExcel(dataGridView1, nom, ruta);

AND THIS BUTTON CODE TO RAISE TO THE BD

        foreach (DataGridViewRow row in dataGridView1.Rows)//CICLO PARA RECORRER LA TABLA
        {
            a1 = Convert.ToString(row.Cells["Sold-to party"].Value);
            a2 = Convert.ToString(row.Cells["Ship-to-party"].Value);
            a3 = Convert.ToString(row.Cells["Cliente"].Value);
            a4 = Convert.ToString(row.Cells["Cotización"].Value);
            a5 = Convert.ToString(row.Cells["Material"].Value);
            a6 = Convert.ToString(row.Cells["Descripción"].Value);
            a7 = Convert.ToString(row.Cells["Febrero"].Value);
            a8 = Convert.ToString(row.Cells["Marzo"].Value);
            a9 = Convert.ToString(row.Cells["Abril"].Value);
            a10 = Convert.ToString(row.Cells["Mayo"].Value);
            a11 = Convert.ToString(row.Cells["Total Cotizado"].Value);
            a12 = Convert.ToString(row.Cells["PO Number"].Value);
            a13 = Convert.ToString(row.Cells["Cantidad Cotización"].Value);
            a14 = Convert.ToString(row.Cells["Cantidad OC"].Value);
            a15 = Convert.ToString(row.Cells["Valor OC"].Value);
            a16 = Convert.ToString(row.Cells["Éxito"].Value);
            a17 = Convert.ToString(row.Cells["SBU"].Value);

            string estado="";//VARIBLE HAY QUE PROGRAMAR EL ESTADO

            con.ingresarcotizaciones(a1, a2, a3, a4, a5, a6, int.Parse(a7), int.Parse(a8), int.Parse(a9), int.Parse(a10), int.Parse(a11), a12, int.Parse(a13), int.Parse(a14), int.Parse(a15), int.Parse(a16), int.Parse(a17), estado);

I hope you can help me.

    
asked by marcelo c 10.05.2017 в 02:35
source

1 answer

1

The issue is that you are designing the persistence incorrectly, the months should not be defined as columns, but as rows in a related table

Then you will have the table of quotes and then for each month the value in a related table, it would be necessary to determine which would be the key of that table, some way to identify the header of the data.

Cotizaciones  (tabla)
CotizacionId  PK
Cliente
Material
Descripción
//resto columnas

CotizacionMeses (tabla)
CotizacionMesesId  PK
CotizacionId  
Mes  (este campo tomara los valores del 1 al 12)
Monto

Then insert the common data in "Quotes" and then for each month that you have data inserted in "CotizacióncionMeses", in this way you will initially insert the first 4 months and then when you have the other iras completing with new inserts

    
answered by 10.05.2017 / 05:11
source