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);
}
}
}
}
}