public static List <Dictionary <string, string> > LeerCamposExcel(string filePath, Stream fulArchivo, byte[] test) { List <Dictionary <string, string> > lstFila = new List <Dictionary <string, string> >(); OleDbConnection ExcelConnection = null; FileMode fileMode = FileMode.Create; byte[] byteArray = test; // _file.OpenBinary(); MemoryStream dataStream = new MemoryStream(byteArray); Stream stream = dataStream; //fulArchivo; using (FileStream fs = File.Open(filePath, fileMode)) { byte[] buffer = new byte[4096]; int bytesRead; while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0) { fs.Write(buffer, 0, bytesRead); } fs.Close(); } //Create the Connection String string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" + filePath + "'; Extended Properties=Excel 5.0"; //Create the connection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString); //create a string for the query string ExcelQuery; //read columns from the Excel file ExcelQuery = "Select * from [Hoja1$]"; // from Sheet1"; //use "Select * ... " to select the entire sheet //create the command System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection); //Open the connection ExcelConnection.Open(); //Create a reader System.Data.OleDb.OleDbDataReader ExcelReader; ExcelReader = ExcelCommand.ExecuteReader(); ManejadorLogSimpleBL.WriteLog("Numero de Campos: " + ExcelReader.FieldCount + " " + DateTime.Now + DateTime.Now.Millisecond); while (ExcelReader.Read()) { Dictionary <string, string> dato = new Dictionary <string, string>(); for (int i = 0; i < ExcelReader.FieldCount; i++) { if (ExcelReader.GetName(i).ToString().Trim() == "CodSector") { if (ExcelReader.GetValue(i).ToString().Trim() != "0") { var s = ExcelReader.GetValue(i); } } dato.Add(ExcelReader.GetName(i).ToString().Trim(), ExcelReader.GetValue(i).ToString().Trim()); } lstFila.Add(dato); } ExcelConnection.Close(); return(lstFila); }
public static List <ExcelDataDTO> LeerArchivo(ArchivoDTO archivo, out int nroColumnas) { try { nroColumnas = 0; string NombreArchivo = archivo.Nombre; //"archivo del fileupload"; string filePath = Path.GetTempPath() + NombreArchivo; // @"C:\TempFiles";//ConfigurationManager.AppSettings["TempLoaction"] + "\\" + fileName; List <List <String> > lst = ReadTextExcel(filePath, archivo.FulArchivo); //List<List<String>> lst = ReadTextFile(filePath, archivo.FulArchivo); //For each row after the first List <ExcelDataDTO> lstData = new List <ExcelDataDTO>(); foreach (List <string> reg in lst) { ExcelDataDTO data = new ExcelDataDTO(); if (nroColumnas == 0) { nroColumnas = reg.Count; } if (reg.Count > 0) { data.Columna0 = reg[0]; } if (reg.Count > 1) { data.Columna1 = reg[1]; } if (reg.Count > 2) { data.Columna2 = reg[2]; } if (reg.Count > 3) { data.Columna3 = reg[3]; } if (reg.Count > 4) { data.Columna4 = reg[4]; } if (reg.Count > 5) { data.Columna5 = reg[5]; } if (reg.Count > 6) { data.Columna6 = reg[6]; } if (reg.Count > 7) { data.Columna7 = reg[7]; } if (reg.Count > 8) { data.Columna8 = reg[8]; } if (reg.Count > 9) { data.Columna9 = reg[9]; } if (reg.Count > 10) { data.Columna10 = reg[10]; } if (reg.Count > 11) { data.Columna11 = reg[11]; } if (reg.Count > 12) { data.Columna12 = reg[12]; } if (reg.Count > 13) { data.Columna13 = reg[13]; } if (reg.Count > 14) { data.Columna14 = reg[14]; } if (reg.Count > 15) { data.Columna15 = reg[15]; } if (reg.Count > 16) { data.Columna16 = reg[16]; } if (reg.Count > 17) { data.Columna17 = reg[17]; } if (reg.Count > 18) { data.Columna18 = reg[18]; } if (reg.Count > 19) { data.Columna19 = reg[19]; } if (reg.Count > 20) { data.Columna20 = reg[20]; } lstData.Add(data); } return(lstData); } catch (Exception ex) { ManejadorLogSimpleBL.WriteLog(ex.ToString()); throw new Exception(ex.Message); } }