Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
            }
        }