public IEnumerator <T> GetEnumerator() { _dataReader.Reset(); EnsureCorrectSheetSelected(); EnsureColumnNamesResolved(); while (_dataReader.Read()) { var instance = Activator.CreateInstance <T>(); foreach (var propertyMap in _configuration.PropertyMaps.Where(p => !p.Ignored)) { var valueFromExcel = propertyMap.MapStrategy == ExcelIteratorPropertyMapStrategy.ByName ? _dataReader.GetValue(ResolveIndexByName(propertyMap)) : _dataReader.GetValue(propertyMap.ColumnIndex); propertyMap.Property.SetValue(instance, propertyMap.SourceValueConverter.ConvertValue(valueFromExcel)); } yield return(instance); } }
private void UpdateDataCustom(IExcelDataReader reader, int step = 0, ValidaImportDto validaImportDto = null) { // read the data of the record and gearbox if (step == 1) { for (int column = 0; column < reader.FieldCount; column++) { if (column == 0) { var value = reader.GetValue(column).ToString().Split('-'); if (value != null && value.Count() != 0) { validaImportDto.GearBoxNumber = value[0]; validaImportDto.ProfileNumber = value[1]; break; } } } } else { //read the data of the table of content for (int i = 0; i < reader.FieldCount; i++) { if (i == 0) { var value = reader.GetValue(i).ToString(); validaImportDto.TableOfContentName = !string.IsNullOrEmpty(value) ? value : string.Empty; break; } } } }
public static float GetFloat(int i) { var type = reader.GetFieldType(i); if (type == typeof(double)) { return (float)reader.GetDouble(i); } if (type == typeof(float)) { return reader.GetFloat(i); } if (type == typeof(int)) { return reader.GetInt32(i); } if (float.TryParse(reader.GetValue(i)?.ToString(), out float value)) { return value; } throw Exception("Can't parse number from column " + (i + 1)); }
/// <summary> /// Format a cell as a string before we pass to the type converter, so any formatting done in Excel /// is properly applied to the string result that is then sent to the users parser. /// </summary> /// <param name="index">Column in the excel file</param> /// <param name="capitalBoolean">True to use capital boolean format, false to use C# style</param> /// <returns>Cell value formatted as a string, or the original value if not</returns> private string FormatValueAsString( int index, bool capitalBoolean) { var value = _reader.GetValue(index); if (value != null) { // For compatibility with old PHP code, format TRUE and FALSE for boolean values var type = _reader.GetFieldType(index); if (type == typeof(bool)) { if ((bool)value) { return(capitalBoolean ? "TRUE" : "True"); } return(capitalBoolean ? "FALSE" : "False"); } // To ensure DateTime values make sense in any culture, we render them in ISO 8601 format if (type == typeof(DateTime)) { return(((DateTime)value).ToString("o")); } // TODO: We currently do not have a way to get real formatting out of the Excel file cell, // so to do this correctly we really need to find a way to get that, and then format it. return(value.ToString().Trim()); } // Always return empty strings, not nulls return(string.Empty); }
private static Product ImportProductRow(IExcelDataReader reader) { var p = new Product(); p.code = GetStringOrNull(reader.GetValue(1)); p.name = GetStringOrNull(reader.GetValue(2)); p.description = GetStringOrNull(reader.GetValue(3)); return(p); }
public void Excel(string path) { try { FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); // DataSet result = excelReader.AsDataSet(); // var n = result.Tables; //var listss = new List<Listingss>(); while (excelReader.Read()) { if (excelReader.GetValue(0) == null) { break; } else { var temp = new Listingss() { Title = excelReader?.GetString(0), Price = excelReader?.GetValue(1)?.ToString(), Description = excelReader?.GetString(2), Brand = excelReader?.GetString(3), Model = excelReader?.GetString(4), Internal = excelReader?.GetValue(5)?.ToString(), Image_1 = excelReader?.GetString(6), Image_2 = excelReader?.GetString(7), Image_3 = excelReader?.GetString(8), Image_4 = excelReader?.GetString(9), InternalStorage = 0 }; if (temp.Title.ToLower() != "title") { DataBasee.Lister.Add(temp); } else { } } } stream.Dispose(); Console.WriteLine($"{DataBasee.Lister[0].Title} Read"); TestArea(); } catch (IOException) { MessageBox.Show("The Sheet you're trying to view is already in use", "File Already Open", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); Clear(); } catch (Exception ex) { MessageBox.Show("Unknown Error", "Error 0", MessageBoxButtons.OK, MessageBoxIcon.Error); Clear(); } }
private void getAllSections(bool boo, IExcelDataReader reader) { if (boo) { Section newSection = new Section(); string class_description = reader.GetString(5); Class c = Class.Where(s => s.Description.Equals(class_description)).FirstOrDefault <Class>(); newSection.ClassId = c.ClassId; newSection.Type = reader.GetString(0); newSection.StatusId = 1; // 'A' newSection.Capacity = Convert.ToInt32((reader.GetDouble(22))); newSection.RemainingSeats = Convert.ToInt32((reader.GetDouble(22))) - Convert.ToInt32((reader.GetDouble(8))); string times = ""; if (reader.GetValue(12) != null) { if (reader.GetString(12).Equals("Y")) { times = times + "Mon "; } if (reader.GetString(13).Equals("Y")) { times = times + "Tue "; } if (reader.GetString(14).Equals("Y")) { times = times + "Wed "; } if (reader.GetString(15).Equals("Y")) { times = times + "Thu "; } if (reader.GetString(16).Equals("Y")) { times = times + "Fri "; } } // if (reader.GetValue(23) != null || reader.GetValue(24) != null) { times = times + reader.GetDateTime(23).Hour + ":" + reader.GetDateTime(23).Minute + "-" + reader.GetDateTime(24).Hour + ":" + reader.GetDateTime(24).Minute; } newSection.Times = times; newSection.Room = reader.GetString(20); Section.Add(newSection); SaveChanges(); } }
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e) { #region Variables List <in_Ing_Egr_Inven_det_Info> Lista_IngresoInventarioDet = new List <in_Ing_Egr_Inven_det_Info>(); in_Ing_Egr_Inven_det_List ListaIngresoInventario = new in_Ing_Egr_Inven_det_List(); in_Producto_Bus bus_producto = new in_Producto_Bus(); int cont = 0; decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual); int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa); tb_sucursal_Bus bus_sucursal = new tb_sucursal_Bus(); #endregion Stream stream = new MemoryStream(e.UploadedFile.FileBytes); if (stream.Length > 0) { IExcelDataReader reader = null; reader = ExcelReaderFactory.CreateOpenXmlReader(stream); #region Ingreso Inventario var lst_producto = bus_producto.get_list(IdEmpresa, false); while (reader.Read()) { if (!reader.IsDBNull(0) && cont > 0) { var pr_codigo_producto = Convert.ToString(reader.GetValue(3)); var IdUnidadMedida = Convert.ToString(reader.GetValue(2)); var costo_total = Convert.ToDouble(reader.GetValue(6)); var cantidad = Convert.ToDouble(reader.GetValue(5)); var info_producto = lst_producto.Where(q => q.pr_codigo == pr_codigo_producto).FirstOrDefault(); if ((info_producto != null && info_producto.IdProducto != 0) && (costo_total > 0 && cantidad > 0)) { in_Ing_Egr_Inven_det_Info info_detalle = new in_Ing_Egr_Inven_det_Info { Secuencia = cont++, IdEmpresa = IdEmpresa, IdProducto = info_producto.IdProducto, pr_descripcion = info_producto.pr_descripcion, IdUnidadMedida_sinConversion = string.IsNullOrEmpty(IdUnidadMedida) ? info_producto.IdUnidadMedida_Consumo : IdUnidadMedida, dm_cantidad_sinConversion = cantidad, mv_costo_sinConversion = costo_total / cantidad, }; Lista_IngresoInventarioDet.Add(info_detalle); } } else { cont++; } } ListaIngresoInventario.set_list(Lista_IngresoInventarioDet, IdTransaccionSession); #endregion } }
public static string getValue(IExcelDataReader reader, int column) { string val = null; if (reader.GetValue(column) != null) { val = reader.GetValue(column).ToString(); } return(val); }
/// <summary> /// Reads a cell from the Excel file. /// </summary> /// <typeparam name="T">The type of the field.</typeparam> /// <param name="index">Column index to reead the value from.</param> /// <returns>The value from the column converted to the specific type</returns> public T GetColumn <T>( int index) { var type = typeof(T); var converter = TypeConverterFactory.GetConverter(type); var typeConverterOptions = TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo); var value = _reader.GetValue(index); return((T)converter.ConvertFromExcel(typeConverterOptions, value)); }
public static FxOperation MapForwardContract(this IExcelDataReader reader) { return(new FxForwardContract( reader.GetDateTime(TradeDate), Enum.Parse <CurrencyPair>(reader.GetValue(Symbol).ToString()), reader.GetDouble(Notional), reader.GetDouble(Price), Enum.Parse <Direction>(reader.GetValue(BuyOrSell).ToString()), reader.GetDateTime(WithdrawDate), reader.GetDateTime(SettlementDate))); }
public static Person MapPerson(this IExcelDataReader reader, Address address) { return(new Person { FirstName = reader.GetValue(2) as string, Surname = reader.GetValue(3) as string, Address = address, NationalIdentificationNumber = reader.GetValue(4).ToString(), PhoneNumber = reader.GetValue(15).ToString(), PhoneNumber2 = reader.GetValue(16).ToString() }); }
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e) { #region Variables ct_plancta_Bus bus_ctacble = new ct_plancta_Bus(); ct_cbtecble_det_List ListaDet = new ct_cbtecble_det_List(); List <ct_cbtecble_det_Info> Lista_Det = new List <ct_cbtecble_det_Info>(); int cont = 0; decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual); int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa); #endregion Stream stream = new MemoryStream(e.UploadedFile.FileBytes); if (stream.Length > 0) { IExcelDataReader reader = null; reader = ExcelReaderFactory.CreateOpenXmlReader(stream); var SecDet = 1; #region Presupuesto while (reader.Read()) { if (!reader.IsDBNull(0) && cont > 0) { var IdCtaCble = Convert.ToString(reader.GetValue(1)); ct_plancta_Info infoCtaCble = bus_ctacble.get_info(IdEmpresa, IdCtaCble); if (infoCtaCble != null) { ct_cbtecble_det_Info info = new ct_cbtecble_det_Info { IdEmpresa = IdEmpresa, secuencia = SecDet++, IdCtaCble = IdCtaCble, Descripcion = infoCtaCble.pc_Cuenta, dc_Valor = Convert.ToDouble(reader.GetValue(2)) > 0 ? Convert.ToDouble(reader.GetValue(2)) : (Convert.ToDouble(reader.GetValue(3)) * -1), dc_Valor_debe = Convert.ToDouble(reader.GetValue(2)), dc_Valor_haber = Convert.ToDouble(reader.GetValue(3)) }; Lista_Det.Add(info); } } else { cont++; } } ListaDet.set_list(Lista_Det, IdTransaccionSession); #endregion } }
public Trade[] ReadXlsx(IExcelDataReader reader) { List <Trade> result = new List <Trade>(); do { while (reader.Read()) { try { var time = reader.GetValue(0); var price = reader.GetValue(1); var volume = reader.GetValue(2); var value = reader.GetValue(3); var reasonString = reader.GetValue(4); if (time != null && price != null && volume != null && value != null && reasonString != null) { Reason reason; switch (reasonString) { case "ASK": reason = Reason.ASK; break; case "BID": reason = Reason.BID; break; case "MATCH": reason = Reason.MATCH; break; default: reason = Reason.ASK; break; } result.Add(new Trade(time: Convert.ToDateTime(time), price: Convert.ToInt32(price), volume: Convert.ToInt32(volume), value: Convert.ToInt32(value), reason: reason)); } } catch (Exception ex) { Console.WriteLine(ex.Message.ToString()); continue; // Try to read the next row } } } while (reader.NextResult()); return(result.ToArray()); }
public static FxOperation MapVanillaOption(this IExcelDataReader reader) { return(new FxVanillaOption( reader.GetDateTime(TradeDate), Enum.Parse <CurrencyPair>(reader.GetValue(Symbol).ToString()), reader.GetDouble(Notional), reader.GetDouble(Premium), reader.GetDouble(Spot), reader.GetDouble(Strike), Enum.Parse <OptionType>(reader.GetValue(CallOrPut).ToString()), Enum.Parse <ExecutionType>(reader.GetValue(AmericanOrEuropean).ToString()), Enum.Parse <Direction>(reader.GetValue(BuyOrSell).ToString()), reader.GetDateTime(WithdrawDate), reader.GetDateTime(SettlementDate))); }
public void UsingExcelDataReader() { System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); using (FileStream xlsFile = File.Open("D:\\repository\\SDET\\javaSDET\\ExcelFiles\\excelDriven.xlsx", FileMode.Open, FileAccess.Read)) { using (IExcelDataReader sheet = ExcelReaderFactory.CreateReader(xlsFile)) { do { while (sheet.Read()) //reads Each ROW // At end of Rows will Return False // and End While loop. { for (int column = 0; column < sheet.FieldCount; column++) { //Console.WriteLine(reader.GetString(column));//Will blow up if the value is decimal etc. Console.WriteLine(sheet.GetValue(column));//Get Value returns object } } } while (sheet.NextResult()); //Move to NEXT SHEET // When ALL the ROWS in sheet are done // and End While loop. } } }
public static Address MapAddress(this IExcelDataReader reader) { return(new Address { StreetName = reader.GetValue(5) as string, StreetNumber = reader.GetValue(6) as string, FlatNumber = reader.GetValue(7) as string, PostCode = reader.GetValue(8) as string, PostOfficeCity = reader.GetValue(9) as string, CorrespondenceStreetName = reader.GetValue(10) as string, CorrespondenceStreetnumber = reader.GetValue(11) as string, CorrespondenceFlatNumber = reader.GetValue(12) as string, CorrespondencePostCode = reader.GetValue(13) as string, CorrespondencePostOfficeCity = reader.GetValue(14) as string }); }
private Sheet ExtractSheetData(Sheet sheet, IExcelDataReader reader) { int rowIndex = 0; while (reader.Read()) { if (rowIndex >= sheet.Rows - 1) { break; } for (int i = 0; i < sheet.Columns; i++) //i<reader.FieldCount - ali ima onda hrpa praznih polja { Cell cell = new Cell { Value = reader.GetValue(i) }; sheet.Table[rowIndex, i] = cell; //if (cell.Value != null) // Console.WriteLine(String.Format("{0},{1}:{2}", rowIndex, i, cell.Value.ToString())); } rowIndex++; } return(sheet); }
static Dictionary <string, List <string[]> > ReadExcelData(TextAsset excelAsset, bool readExtraSheet) { IExcelDataReader reader = ExcelReaderFactory.CreateReader(new MemoryStream(excelAsset.bytes)); Dictionary <string, List <string[]> > result = new Dictionary <string, List <string[]> >(); do { List <string[]> properties = new List <string[]>(); while (reader.Read()) { string[] row = new string[reader.FieldCount]; for (int i = 0; i < row.Length; i++) { row[i] = ""; object obj = reader.GetValue(i); if (obj != null) { row[i] = obj.ToString(); } } if (row[0] != "") { properties.Add(row); } } result.Add(reader.Name, properties); } while (readExtraSheet && reader.NextResult()); reader.Close(); return(result); }
//[FUNCTION - GetCourseLevel] //Returns the level of course in currently selected excel row string GetCourseLevel() { object value = excelReader.GetValue(8); string stringValue = value.ToString(); return((stringValue != "NA") ? stringValue : "000"); }
internal SheetData(IExcelDataReader reader) { ColumnCount = reader.FieldCount; Name = reader.Name; while (reader.Read()) { List <string> row = new List <string>(ColumnCount); bool isNullRow = true; for (int i = 0; i < ColumnCount; ++i) { var val = reader.GetValue(i); string strVal = ""; if (val is DateTime) { var fmt = reader.GetNumberFormatString(i); strVal = ((DateTime)val).ToString(fmt.Replace('h', 'H')); } else { strVal = val.ToString(); } row.Add(strVal); } if (!isNullRow) { data.Add(row); } } }
private async Task ReadValues(IExcelDataReader reader, string country) { try { for (int i = 3; i < reader.FieldCount; ++i) { try { var a = reader.GetValue(i); if (a == null || a.ToString() == "-") { continue; } float co = float.Parse(a.ToString()); elements.Add( new ElementOfSectionSignificate { SignificativeId = currentSignificative, Count = co, ElementOfSectionId = i + 7, // костыль CountryId = countries[country] }); } catch (Exception e) { } } } catch (Exception e) { } }
public bool ReadRow(IDataRow row, Dictionary <int, int> charactersLength = null) { row.Clear(); m_lineNbr++; if (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { int?itemLength = null; if (charactersLength != null && charactersLength.ContainsKey(i + 1)) { itemLength = charactersLength[i + 1]; } var value = Convert.ToString(reader.GetValue(i)); if (value != null && itemLength.HasValue && itemLength.Value != 0 && value.Length > itemLength.Value) { value = value.Substring(0, itemLength.Value - 1); } row.Add(new DataRowItem(value, m_lineNbr)); } return(true); } else { return(false); } }
public Cell(int index, IExcelDataReader reader) { Type = reader.GetFieldType(index)?.Name; Value = reader.GetValue(index); NumberFormatIndex = reader.GetNumberFormatIndex(index); NumberFormatString = reader.GetNumberFormatString(index); }
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e) { int cont = 0; ro_rubro_tipo_Bus bus_rubro = new ro_rubro_tipo_Bus(); ro_empleado_info_list empleado_info_list = new ro_empleado_info_list(); ro_PrestamoMasivo_Det_List ListaDetalle_PrestamoMasivo = new ro_PrestamoMasivo_Det_List(); List <ro_PrestamoMasivo_Det_Info> PrestamoMasivo_Det = new List <ro_PrestamoMasivo_Det_Info>(); var IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSession); int Secuencia = 1; Stream stream = new MemoryStream(e.UploadedFile.FileBytes); if (stream.Length > 0) { IExcelDataReader reader = null; reader = ExcelReaderFactory.CreateOpenXmlReader(stream); while (reader.Read()) { if (!reader.IsDBNull(0)) { if (cont != 0) { int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa); string cedula = reader.GetString(0); var empleado = empleado_info_list.get_list(IdTransaccionSession).Where(v => v.pe_cedulaRuc == cedula).FirstOrDefault(); var CodRubro = Convert.ToString(reader.GetValue(2)); var info_rubro = bus_rubro.get_info_x_codigo(IdEmpresa, CodRubro); if (empleado != null) { ro_PrestamoMasivo_Det_Info info = new ro_PrestamoMasivo_Det_Info { Secuencia = Secuencia++, IdEmpleado = (empleado == null ? 0 : empleado.IdEmpleado), IdRubro = (info_rubro == null ? null : info_rubro.IdRubro), Monto = Convert.ToDouble(reader.GetValue(3)), NumCuotas = Convert.ToInt32(reader.GetValue(4)), ru_descripcion = (info_rubro == null ? "" : info_rubro.ru_descripcion), pe_nombreCompleto = (empleado == null ? "" : empleado.Empleado) }; PrestamoMasivo_Det.Add(info); } } cont++; } } ListaDetalle_PrestamoMasivo.set_list(PrestamoMasivo_Det, IdTransaccionSession); } }
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e) { #region Variables pre_rubro_Bus bus_rubro = new pre_rubro_Bus(); pre_PresupuestoDet_List ListaDet = new pre_PresupuestoDet_List(); List <pre_PresupuestoDet_Info> Lista_Det = new List <pre_PresupuestoDet_Info>(); int cont = 0; decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual); int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa); #endregion Stream stream = new MemoryStream(e.UploadedFile.FileBytes); if (stream.Length > 0) { IExcelDataReader reader = null; reader = ExcelReaderFactory.CreateOpenXmlReader(stream); var SecDet = 1; #region Presupuesto while (reader.Read()) { if (!reader.IsDBNull(0) && cont > 0) { var IdRubroDet = Convert.ToInt32(reader.GetValue(0)); pre_rubro_Info infoRubro = bus_rubro.GetInfo(IdEmpresa, IdRubroDet); pre_PresupuestoDet_Info info = new pre_PresupuestoDet_Info { IdEmpresa = IdEmpresa, Secuencia = SecDet++, IdRubro = IdRubroDet, Descripcion = infoRubro.Descripcion, Monto = Convert.ToDouble(reader.GetValue(1)), }; Lista_Det.Add(info); } else { cont++; } } ListaDet.set_list(Lista_Det, IdTransaccionSession); #endregion } }
public static void HandleDescriptionFactor(IExcelDataReader reader) { var descriptionModel = new Description(); //loop through each row. while (reader.Read()) { //Loop through each column of the the current row. for (int i = 0; i < reader.FieldCount; i += 2) { //gets the current value. var currentCellValue = reader.IsDBNull(i) ? reader.GetValue(i) : reader.GetValue(i).ToString().Trim(); Console.WriteLine(currentCellValue); } } }
private object GetValue(Type propertyType, int columnIndex) { var value = _excelDataReader.GetValue(columnIndex); return(value == null ? (propertyType.IsValueType ? Activator.CreateInstance(propertyType) : null) : Convert.ChangeType(value, propertyType)); }
public IEnumerable <ShippingLabel> Map(IExcelDataReader dataReader) { _logger.LogInformation($"Map using ShippingLabelMapper."); yield return(new ShippingLabel { Tmstmp = DateTime.Parse(dataReader.GetString(0)), Name = dataReader.GetString(1), Recipient = dataReader.GetString(2), StreetName = dataReader.GetString(3), StreetNo = dataReader.GetValue(4) == null ? string.Empty : dataReader.GetString(4), AreaCode = dataReader.GetDouble(5).ToString(), AreaName = dataReader.GetString(6), Units = dataReader.GetDouble(7).ToString(), Email = dataReader.GetString(8), Phone = dataReader.GetValue(9).ToString() }); }
public static Agreement MapAgreement(this IExcelDataReader reader, Person person, FinancialState financialState) { return(new Agreement { Number = reader.GetValue(1).ToString(), Person = person, FinancialState = financialState }); }