private void btn_Import_Click(object sender, EventArgs e) { try { OpenFileDialog OPFD = new OpenFileDialog(); if (OPFD.ShowDialog() == DialogResult.OK) { FileStream fs = File.Open(OPFD.FileName, FileMode.Open, FileAccess.Read); IExcelDataReader ExcelReader; var file = new FileInfo(OPFD.FileName); if (file.Extension.Equals(".xls")) { ExcelReader = ExcelReaderFactory.CreateBinaryReader(fs); } else if (file.Extension.Equals(".xlsx")) { ExcelReader = ExcelReaderFactory.CreateOpenXmlReader(fs); } else { throw new Exception("Invalid File Name"); } var Config = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; DataSet ds = ExcelReader.AsDataSet(Config); ExcelReader.Close(); ExcelReader.Dispose(); fs.Close(); fs.Dispose(); OPFD.Dispose(); foreach (DataTable ThisTable in ds.Tables) { foreach (DataRow row in ThisTable.Rows) { // here we create the student object becaue this is where we will object[] StudentInfo_Excel = row.ItemArray; SortDataFromExcelForStudent(StudentInfo_Excel); // then we will add the students to the database. } } string Caption = "Success"; MessageBoxButtons Button = MessageBoxButtons.OK; MessageBoxIcon icon = MessageBoxIcon.None; MessageBox.Show("Students Successfully Added To Database!", Caption, Button, icon); } } catch (Exception ex) { string caption = "File Not Found"; MessageBoxButtons Button = MessageBoxButtons.OK; MessageBoxIcon icon = MessageBoxIcon.Error; MessageBox.Show(ex.Message, caption, Button, icon); } }
/*private void Import_To_Grid(string FilePath, string Extension, string isHDR) * * { * string conStr = ""; * switch (Extension) * { * case "xls": //Excel 97-03 * * conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; * break; * * case "xlsx": //Excel 07 * * conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString; * break; * } * conStr = String.Format(conStr, FilePath, isHDR); * * OleDbConnection connExcel = new OleDbConnection(conStr); * OleDbCommand cmdExcel = new OleDbCommand(); * OleDbDataAdapter oda = new OleDbDataAdapter(); * DataTable DT = new DataTable(); * cmdExcel.Connection = connExcel; * connExcel.Open(); * * DataTable dtExcelSchema; * * dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); * * string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); * connExcel.Close(); * connExcel.Open(); * * cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; * * oda.SelectCommand = cmdExcel; * oda.Fill(DT); * if (DT.Rows.Count > 0) * { * Grdprueba.DataSource = DT; * } * else { Grdprueba.DataSource = null; } * * Grdprueba.DataBind(); * connExcel.Close(); * * }*/ protected void Import(string FilePath, string Extension) { try { FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read); IExcelDataReader ExcelReader; /* * //1. Reading Excel file * if (Path.GetExtension(FilePath).ToUpper() == ".XLS") * { ExcelReader = ExcelReaderFactory.CreateBinaryReader(stream); } * else * { ExcelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); }// XLSX */ //2. DataSet - The result of each spreadsheet will be created in the result.Tables // DataSet result = ExcelReader.AsDataSet(); ExcelReader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream); //// reader.IsFirstRowAsColumnNames var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; //3. DataSet - Create column names from first row var dataSet = ExcelReader.AsDataSet(conf); DataTable DT = dataSet.Tables[0]; if (DT.Rows.Count > 0) { Grdprueba.DataSource = DT; } else { Grdprueba.DataSource = null; } Grdprueba.DataBind(); } catch (Exception ex) { } }
protected void Import(string FilePath, string Extension) { string ip = Request.ServerVariables["REMOTE_ADDR"]; string VbPC = System.Net.Dns.GetHostEntry(Request.ServerVariables["remote_addr"]).HostName; DataRow[] Result; FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read); IExcelDataReader ExcelReader; ExcelReader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream); //// para que tome la primera fila como titulo de campos var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; var dataSet = ExcelReader.AsDataSet(conf); DataTable DT = dataSet.Tables[0]; if (DT.Rows.Count > 0) { Cnx.SelecBD(); using (SqlConnection sqlCon = new SqlConnection(Cnx.GetConex())) { sqlCon.Open(); string VBQuery = "AjusteSP"; using (SqlCommand SC = new SqlCommand(VBQuery, sqlCon)) { try { string PMensj = ""; SC.CommandType = CommandType.StoredProcedure; SqlParameter Prmtrs1 = SC.Parameters.AddWithValue("@Ajuste", DT); SqlParameter Prmtrs2 = SC.Parameters.AddWithValue("@Usu", Session["C77U"].ToString()); SqlParameter Prmtrs3 = SC.Parameters.AddWithValue("@IdCia", Session["!dC!@"]); SqlParameter Prmtrs4 = SC.Parameters.AddWithValue("@NIT", Session["Nit77Cia"]); SqlParameter Prmtrs5 = SC.Parameters.AddWithValue("@NomMaquina", VbPC.Trim()); SqlParameter Prmtrs6 = SC.Parameters.AddWithValue("@FecAjuste", Convert.ToDateTime(TxtFech.Text)); SqlParameter Prmtrs7 = SC.Parameters.AddWithValue("@CodMvto", DdlMvto.Text.Trim()); SqlParameter Prmtrs8 = SC.Parameters.AddWithValue("@CodAlma", DdlAlmac.SelectedItem.Text.Trim()); SqlParameter Prmtrs9 = SC.Parameters.AddWithValue("@CCost", DdlCcost.Text.Trim()); SqlParameter Prmtrs10 = SC.Parameters.AddWithValue("@Mtv", TxtMotvo.Text.Trim()); Prmtrs1.SqlDbType = SqlDbType.Structured; using (SqlDataAdapter SDA = new SqlDataAdapter()) { using (DataSet DST = new DataSet()) { SDA.SelectCommand = SC; SDA.Fill(DST); ViewState["DST"] = DST; PMensj = DST.Tables[1].Rows[0]["Mensj"].ToString().Trim(); if (!PMensj.Trim().Equals("")) { Result = Idioma.Select("Objeto= '" + PMensj.ToString().Trim() + "'"); foreach (DataRow row in Result) { PMensj = row["Texto"].ToString().Trim(); } //LblTitIncosistnc.Text = PMensj; ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + PMensj + "');", true); LblTitInconsist.Text = PMensj; GrdInconsist.DataSource = DST.Tables[0]; GrdInconsist.DataBind(); BtnCargarInvIni.OnClientClick = ""; return; } PMensj = DST.Tables[0].Rows[0]["Mensj"].ToString().Trim(); Result = Idioma.Select("Objeto= '" + PMensj.ToString().Trim() + "'"); foreach (DataRow row in Result) { LblTitInconsist.Text = row["Texto"].ToString().Trim(); } //LblTitIncosistnc.Text = PMensj; GrdInconsist.DataSource = null; GrdInconsist.DataBind(); BtnCargarInvIni.Visible = false; BtnSubirInventario.Visible = true; } } } catch (Exception Ex) { BtnCargarInvIni.OnClientClick = ""; Result = Idioma.Select("Objeto= 'MensAjt03'");// Inconvenientes con el archivos, verifique si tiene la estructura requerida. foreach (DataRow row in Result) { ScriptManager.RegisterClientScriptBlock(this.Page, this.Page.GetType(), "alert", "alert('" + row["Texto"].ToString() + "');", true); } // string VbcatUs = Session["C77U"].ToString(), VbcatNArc = ViewState["PFileName"].ToString(), VbcatVer = Session["77Version"].ToString(), VbcatAct = Session["77Act"].ToString(); Cnx.UpdateErrorV2(VbcatUs, VbcatNArc, "Import Detalle Sol Pedido", Ex.StackTrace.Substring(Ex.StackTrace.Length > 300 ? Ex.StackTrace.Length - 300 : 0, 300), Ex.Message, VbcatVer, VbcatAct); } } } } }