upload a flat file (excel) and using asp.net c # a web form load it and save the data in SQL

Question

upload a flat file (excel) and using asp.net c # a web form load it and save the data in SQL

Navigation

#1 by (1 votes)

0

I would like to know how I could upload a flat file (excel) and using asp.net c # a web form to load it and save the data in sql, here I upload the file, I keep it in a local folder and now what I need is to save the content of the excel file in SQL Server in a table

protected void btn_guardar_Archivo_Click(object sender, EventArgs e)
{
    try
    {


        if (generarError(FUpL_Arch, "Debe seleccionar el archivo que va adjuntar", (!FUpL_Arch.HasFile))) { return; }

        else
            {
                if (Request.Files.Count > 0)
                {
                    int vLenght = 0;
                    string Ruta = Server.MapPath("~/Archivos/DOCUMENTOS_ADJUNTOS/PRUEBAS/" + "Prueba_" + IdUsuario.ToString()); 
                    foreach (string item in Request.Files)
                    {
                        HttpPostedFile ofile = Request.Files[item];
                        if (ofile.ContentLength > 0)
                        {
                            if (!System.IO.Directory.Exists(Ruta))
                            {
                                System.IO.Directory.CreateDirectory(Ruta);
                            }
                            string NombreArch = "Prueba_" + IdUsuario.ToString() + DateTime.Now.ToShortDateString();
                            Ruta = System.IO.Path.Combine(Ruta, NombreArch + System.IO.Path.GetExtension(ofile.FileName));
                            ofile.SaveAs(Ruta);
                            vLenght += ofile.ContentLength;

                        }
                    }
                MostrarMensaje("El Archivo se ha Subido Correctamente");
                }
            }


    }
    catch (Exception ex)
    {
        ControlarExcepcion(ex);
    }
}

c# asp.net webforms

asked by Camilo Gomez 03.09.2018 в 18:54

source

1 answer

Bash autocomplete Problem when pressing a floating button

score 1 · Answer 1

I was able to solve it, here is the answer, in case it helps someone else

protected void btn_guarding_Click_File (object sender, EventArgs e) { DataTable dt_PG;

        if (FUpL_Arch.HasFile)
        {
            string FileName = Path.GetFileName(FUpL_Arch.PostedFile.FileName);
            string path = string.Concat((Server.MapPath("~/Archivos/DOCUMENTOS_ADJUNTOS/PRUEBAS/" + DateTime.Now.ToString("dd_MM_yyyy") + "_User_ " + IdUsuario + "_" + FUpL_Arch.FileName)));
            FUpL_Arch.PostedFile.SaveAs(path);

            dt_PG = ExcelToDataTable(path, true);

            if (dt_PG.Rows.Count > 0)
            {
                foreach (DataRow dr in dt_PG.Rows)
                {
                    try
                    {

                        BIO_Programacion_Detalle_BRL.InsertOrUpdate(new BIO_Programacion_Detalle()
                        {
                            Id_Programacion = txt_BPG.Text.ToInt32(),
                            Id_IE = dr[0].ToInt32(),
                            FechaInicio = dr[1].ToString(),
                            FechaFin = dr[2].ToString(),
                            Id_Turno = dr[3].ToInt32(),
                            Estado = 1,
                            UsuarioCreacion = IdUsuario
                        }, 1);
                    }
                    catch (Exception ex)
                    {
                        MostrarAlerta("Error: " + ex.Message);
                        return;
                    }
                }
            }
        }
        else
        {
            MostrarAlerta("Seleccione un archivo");
        }
    }
    public static DataTable ExcelToDataTable(string filePath, bool hasHeaders)
    {
        DataTable dtexcel = new DataTable();
        string HDR = hasHeaders ? "Yes" : "No";
        string strConn;
        if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
        else
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        DataRow schemaRow = schemaTable.Rows[0];
        string sheet = schemaRow["TABLE_NAME"].ToString();
        if (!sheet.EndsWith("_"))
        {
            string query = "SELECT  * FROM [" + sheet + "]";
            OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
            dtexcel.Locale = CultureInfo.CurrentCulture;
            daexcel.Fill(dtexcel);
        }

        conn.Close();
        return dtexcel;
    }