private static string CreateUpdateQueryFromRow(IExcelDataReader reader, List <string> ListOfHeaders)
{
string startOfQuery = SQL_UPDATE_BEFORE + reader.Name + SQL_UPDATE_MIDDLE;
string columnsToUpdate = "";
string endOfQuery = "";
for (int i = 0; i < reader.FieldCount; i++)
{
string currentHeader = ListOfHeaders.ElementAt(i);
if (currentHeader.Equals(ID_IDENTIFIER))
{
endOfQuery = SQL_UPDATE_END + reader.GetDouble(i) + ";";
}
if (LIST_OF_DIRECTION_COLUMNS.Contains(currentHeader))
{
int cellValue = (int)reader.GetDouble(i);
if (cellValue != -1)
{
string columnNameInDatabase = currentHeader.Split(' ')[0];
columnsToUpdate += columnNameInDatabase + " = " + cellValue + ", ";
}
}
}
if (!string.IsNullOrEmpty(columnsToUpdate))
{
columnsToUpdate = columnsToUpdate.Substring(0, columnsToUpdate.Length - 2);
var completeUpdateQuery = startOfQuery + columnsToUpdate + endOfQuery + "\n";
return(completeUpdateQuery);
}
return("");
}
private void button2_Click(object sender, EventArgs e)
{
try
{
DataSet ds;
bool control = false;
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Dosyası |*.xlsx| Excel Dosyası|*.xls",
Title = "Excel Dosyası Seçiniz..",
ValidateNames = true,
RestoreDirectory = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
int sinir = 22;
for (int i = 0; i < sinir; i++)
{
List <double> list = new List <double>();
clustering.dataTest.Add(list);
}
FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
IExcelDataReader okuyucu = ExcelReaderFactory.CreateOpenXmlReader(fs);
ds = okuyucu.AsDataSet();
while (okuyucu.Read())
{
if (okuyucu.GetDouble(0) != 0)
{
for (int j = 0; j < sinir; j++)
{
clustering.dataTest.ElementAt(j).Add(okuyucu.GetDouble(j));
}
}
else
{
break;
}
}
okuyucu.Close();
control = true;
}
}
if (control)
{
label8.Text = clustering.dataTest.ElementAt(0).Count.ToString();
label9.Text = (clustering.dataTest.Count - 1).ToString();
MessageBox.Show("Test verisi yüklendi");
}
}
catch
{
clustering.dataTest.Clear();
MessageBox.Show("Excel dökümanı şu an başka bir uygulama tarafından kullanılıyor.\nLütfen Excel uygulamasını kapatınız.");
}
}
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();
}
}
private Transaction FromBuy(Dictionary <string, int> header, IExcelDataReader reader)
{
return(new Transaction
{
Timestamp = DateTime.SpecifyKind(reader.GetDateTime(header["Time (UTC)"]), DateTimeKind.Utc),
Debit = new Money("USD", reader.GetDouble(header["USD Amount"])),
Credit = new Money("ETH", reader.GetDouble(header["ETH Amount"])),
Fee = new Money("USD", reader.GetDouble(header["Trading Fee (USD)"]))
});
}
private void button1_Click(object sender, EventArgs e)
{
const string FilePath = @"C:\mongodb\bin\TrafficStopsSample.xls";
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
//...
//////2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
////IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//////3. DataSet - The result of each spreadsheet will be created in the result.Tables
////DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
////excelReader.IsFirstRowAsColumnNames = true;
////DataSet result = excelReader.AsDataSet();
//this.textBox1.Text = string.Format("Rows imported: {0}", result.Tables[0].Rows.Count);
int i = 0;
//////5. Data Reader methods
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
excelReader.Read(); //to get past header row
while (excelReader.Read())
{
var data = new CaryData
{
Id = i++,
CAD_Call = excelReader.GetString(0),
Call_Type = excelReader.GetString(1),
Address = excelReader.GetString(2),
Dt = new DateTime(2012, excelReader.GetInt32(5), excelReader.GetInt32(6)),
Time = excelReader.GetString(4),
Month = Int32.Parse(excelReader.GetString(5)),
Day = Int32.Parse(excelReader.GetString(6)),
Disposition = excelReader.GetString(7),
Streetno = excelReader.GetString(8),
Streetonly = excelReader.GetString(9),
Location = new[] { excelReader.GetDouble(10), excelReader.GetDouble(11) }
};
var col = database.GetCollection <CaryData>("trafficstops");
col.Save(data);
}
stopwatch.Stop();
this.textBox1.Text = "Done! Operation took " + stopwatch.Elapsed.TotalSeconds + " seconds.";
}
//6. Free resources (IExcelDataReader is IDisposable)
//excelReader.Close();
}
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 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 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));
}
public TestClass2(IExcelDataReader reader)
{
Sku = reader.IsDBNull(0) ? default : reader.GetString(0);
SupplierCategory = reader.IsDBNull(6) ? default : reader.GetString(1);
Store = reader.IsDBNull(2) ? default : reader.GetString(2);
SupplierDescription = reader.IsDBNull(3) ? default : reader.GetString(3);
Price = reader.GetDouble(4);
VAT = reader.IsDBNull(5) ? default : reader.GetString(5);
CaseQty = reader.IsDBNull(6) ? default(double?) : reader.GetDouble(6);
PackQty = reader.IsDBNull(7) ? default(double?) : reader.GetDouble(7);
Unit = reader.GetDouble(8);
Measure = reader.IsDBNull(9) ? default : reader.GetString(9);
UomDescription = reader.IsDBNull(10) ? default : reader.GetString(10);
Origin = reader.IsDBNull(11) ? default : reader.GetString(11);
Brand = reader.IsDBNull(12) ? default : reader.GetString(12);
LastPeriodVolume = reader.IsDBNull(13) ? default(double?) : reader.GetDouble(13);
}
private int GetInt(IExcelDataReader reader, int index)
{
if (typeof(string) == reader.GetFieldType(index))
{
string str = reader.GetString(index);
bool isNumeric = int.TryParse(str, out int pin);
if (!isNumeric && str != null && str != "")
{
throw new Exception("Excel файлын тоо агуулах ёстой баганад өөр төрлийн мэдээлэл орсон байна. Мөрийн дугаар: " + index.ToString());
}
else if (str == null)
{
return(-1);
}
return(pin);
}
else if (typeof(double) == reader.GetFieldType(index))
{
return((int)reader.GetDouble(index));
}
else if (reader.GetFieldType(index) == null)
{
return(-1);
}
else
{
throw new Exception("Excel файлын тоо агуулах ёстой баганад өөр төрлийн мэдээлэл орсон байна. Мөрийн дугаар: " + index.ToString());
}
}
private int Integerize(IExcelDataReader reader, int position)
{
int result = 0;
var fileType = reader.GetFieldType(position);
if (fileType == null)
{
return(0);
}
if (fileType.Equals(typeof(System.Int32)))
{
return(reader.GetInt32(position));
}
if (fileType.Equals(typeof(System.Double)))
{
return(Convert.ToInt32(reader.GetDouble(position)));
}
if (fileType.Equals(typeof(System.String)))
{
int.TryParse(reader.GetString(position), out result);
return(result);
}
return(0);
}
public static double GetSafeDouble(this IExcelDataReader reader, int i)
{
double value;
try
{
value = reader.GetDouble(i);
}
catch
{
value = 0;
}
if (value == double.MinValue || value == double.MaxValue)
{
return(0);
}
if (value < 0)
{
return(-value);
}
return(value);
}
public Data GetNextData()
{
//Gets next Data vector and skips zeros
if (reader.Read())
{
int shift = 0;
HashSet <int> zeros = new HashSet <int>()
{
57, 116, 117, 118, 119, 124, 125, 126, 127
};
Data data = new Data(reader.FieldCount - skips);
for (int i = 0; i < reader.FieldCount; i++)
{
if (zeros.Contains(i))
{
shift++;
continue;
}
data[i - shift] = reader.GetDouble(i);
}
return(data);
}
else
{
return(new Data());
}
}
private void ReadColumn(IExcelDataReader reader, int colPosition, out object value, out Type valueType)
{
switch (Type.GetTypeCode(reader.GetFieldType(colPosition)))
{
case TypeCode.String:
{
valueType = typeof(string);
value = reader.GetString(colPosition) as string;
}
break;
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Double:
{
valueType = typeof(double);
value = reader.GetDouble(colPosition);
}
break;
default:
{
valueType = typeof(object);
value = null;
}
break;
}
}
public void Handle(StartBatchUpload message)
{
Log.Info("Received StartBatchUpload with batch id: {0}, path: {1}", message.BatchId, message.BatchSourcePath);
FileInfo info = new FileInfo(message.BatchSourcePath);
if (info == null)
{
return;
}
using (FileStream stream = new FileStream(info.FullName, FileMode.Open, FileAccess.Read))
{
//ES - ResultsCount isn't reliable on ExcelDataReader, so do a
//pass through the results to get the count:
int registrationCount = 0;
using (IExcelDataReader reader = GetDataReader(info, stream))
{
if (!IsValid(reader))
{
SendErrorNotification(message);
return;
}
while (reader.Read())
{
registrationCount++;
}
}
//index is 1-based as may be published:
int registrationIndex = 1;
using (IExcelDataReader reader = GetDataReader(info, stream))
{
while (reader.Read())
{
//specify the queue so we can use BizTalk as a replacement subscriber:
Bus.Send <AddProduct>("ExcelUpload.AddProductService.1.InputQueue", m =>
{
m.BatchId = message.BatchId;
m.BatchSourcePath = message.BatchSourcePath;
m.RegistrationsInBatch = registrationCount;
m.RegistrationIndex = registrationIndex++;
m.OriginatorDestination = Bus.SourceOfMessageBeingHandled;
m.Name = (string)reader.GetString(0);
m.ProductNumber = (string)reader.GetString(1);
m.SafetyStockLevel = (int)reader.GetInt32(2);
m.ReorderPoint = (int)reader.GetInt32(3);
m.StandardCost = (decimal)reader.GetDecimal(4);
m.ListPrice = (decimal)reader.GetDecimal(5);
m.DaysToManufacture = (int)reader.GetInt32(6);
m.SellStartDate = DateTime.FromOADate(reader.GetDouble(7));
});
}
}
stream.Close();
}
}
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 void ReadPrices()
{
string startupPath = Environment.CurrentDirectory;
IExcelDataReader excelReader = ReadExcel(@startupPath + @"\PVD.xlsx");
//excelReader.IsFirstRowAsColumnNames = true;
excelReader.Read();
while (excelReader.Read())
{
Price price = new Price();
price.ToVolume = excelReader.GetDouble(0);
price.PriceCM3 = excelReader.GetDouble(1);
Prices.Add(price);
}
excelReader.Close();
}
public static Parcel GetParcel(IExcelDataReader reader)
{
string getaddress()
{
return(reader.GetString((int)FarEast.Address));
}
string getcategory() => reader.GetString((int)FarEast.Category);
string GetTrackID() => reader.GetValue((int)FarEast.TrackID).ToString();
DateTime getRegistrationTime() => reader.GetDateTime((int)FarEast.RegistrationTime);
int?getDestinationIndex() => Convert.ToInt32(reader.GetValue((int)FarEast.DestinationIndex));
string getType() => reader.GetString((int)FarEast.Type);
int getIndex() => (int)reader.GetDouble((int)FarEast.Index);
DateTime getPlannedDate() => Convert.ToDateTime(reader.GetValue((int)FarEast.PlannedDate));
int getUnsuccessfulDeliveryCount() => (int)reader.GetDouble((int)FarEast.UnsuccessfulDeliveryCount);
string getName() => reader.GetValue((int)FarEast.Name) == null ? string.Empty : reader.GetValue((int)FarEast.Name).ToString();
string getTelephoneNumber() => reader.GetValue((int)FarEast.Telephone) == null ? string.Empty : reader.GetValue((int)FarEast.Telephone).ToString();
IsPayneedResult getIsPayNeed()
{
return((int)reader.GetDouble((int)FarEast.IsNeedPay) == 1 ? IsPayneedResult.Need: IsPayneedResult.NotNeed);
}
Parcel _p = new Parcel
{
Address = getaddress(),
Category = getcategory(),
TrackID = GetTrackID(),
RegistrationTime = getRegistrationTime(),
DestinationIndex = getDestinationIndex(),
Type = getType(),
Index = getIndex(),
PlannedDate = getPlannedDate(),
UnsuccessfulDeliveryCount = getUnsuccessfulDeliveryCount(),
Name = getName(),
TelephoneNumber = getTelephoneNumber(),
IsPayNeed = getIsPayNeed()
};
_p.LastOperation = (reader.GetString((int)FarEast.LastOperation));
_p.LastZone = (reader.GetString((int)FarEast.LastZone));
return(_p);
}
public void ReadDryTreatments()
{
string startupPath = Environment.CurrentDirectory;
IExcelDataReader excelReader = ReadExcel(startupPath + @"\toerstraaling.xlsx");
excelReader.Read();
while (excelReader.Read())
{
DryTreatment DT = new DryTreatment();
DT.ToVolume = excelReader.GetDouble(0);
DT.QuantityLow = excelReader.GetDouble(1);
DT.QuantityMid = excelReader.GetDouble(2);
DT.QuantityHigh = excelReader.GetDouble(3);
DryTreatments.Add(DT);
}
excelReader.Close();
}
private string ParseCell(IExcelDataReader reader, int columnToParse)
{
//GetFieldType() returns the type of a value in the current row.Always one of the types supported by Excel:
//double, int, bool, DateTime, TimeSpan, string, or null if there is no value.
var fieldType = reader.GetFieldType(columnToParse);
if (fieldType == null)
{
return(null);
}
string result = null;
if (fieldType == typeof(double))
{
var value = reader.GetDouble(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(int))
{
var value = reader.GetInt32(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(bool))
{
var value = reader.GetBoolean(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(DateTime))
{
var value = reader.GetDateTime(columnToParse);
result = value.ToString();
}
else if (fieldType == typeof(TimeSpan))
{
Console.WriteLine("TimeSpan parsing is not implemented");
//var value = reader.GetTimeS(0);
//data[key] = value;
}
else if (fieldType == typeof(string))
{
var value = reader.GetString(columnToParse);
result = value;
}
else
{
Console.WriteLine($"unknown type from Excel field: {0}", fieldType);
}
return(result);
}
public List <Threat> ParseExcelToThreatsList(string pathToFile)
{
var list = new List <Threat>();
FileStream stream = null;
IExcelDataReader excelReader = null;
try
{
stream = File.Open(pathToFile, FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.Read(); //название таблицы
excelReader.Read(); //заголовки
while (excelReader.Read())
{
var obj = new Threat()
{
Id = (int)excelReader.GetDouble(0),
Name = excelReader.GetString(1),
Description = excelReader.GetString(2),
Source = excelReader.GetString(3),
ImpactObject = excelReader.GetString(4),
isBrokenConfidentiality = excelReader.GetDouble(5) == 1 ? true : false,
isBrokenIntegrity = excelReader.GetDouble(6) == 1 ? true : false,
isBrokenAvailobility = excelReader.GetDouble(7) == 1 ? true : false
};
list.Add(obj);
}
excelReader.Close();
return(list);
}
catch (Exception ex)
{
excelReader.Close();
throw new Exception("Не удалось пропарсить эксель файл!");
}
}
public void ReadToolTypes()
{
string startupPath = Environment.CurrentDirectory;
IExcelDataReader excelReader = ReadExcel(startupPath + @"\vaerktoejstyper.xlsx");
excelReader.Read();
while (excelReader.Read())
{
ToolType TP = new ToolType();
TP.Name = excelReader.GetString(0);
TP.AddFactor = excelReader.GetDouble(1);
ToolTypes.Add(TP);
}
excelReader.Close();
}
public void ReadCoatings()
{
string startupPath = Environment.CurrentDirectory;
IExcelDataReader excelReader = ReadExcel(startupPath + @"\BelaegningsTyper.xlsx");
//excelReader.IsFirstRowAsColumnNames = true;
excelReader.Read();
while (excelReader.Read())
{
Coating coating = new Coating();
coating.name = excelReader.GetString(0);
coating.factor = excelReader.GetDouble(1);
Coatings.Add(coating);
}
excelReader.Close();
}
public void Issue4031NullColumn()
{
using (IExcelDataReader excelReader = OpenReader("Test_Issue_4031_NullColumn"))
{
// DataSet dataSet = excelReader.AsDataSet(true);
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.AreEqual("a", excelReader.GetString(1));
Assert.AreEqual("b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual("d", excelReader.GetString(4));
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.IsNull(excelReader.GetValue(1));
Assert.AreEqual("Test", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual(1, excelReader.GetDouble(4));
}
}
public void Issue_4031_NullColumn()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_Issue_4031_NullColumn")))
{
// DataSet dataSet = excelReader.AsDataSet(true);
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.AreEqual("a", excelReader.GetString(1));
Assert.AreEqual("b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual("d", excelReader.GetString(4));
excelReader.Read();
Assert.IsNull(excelReader.GetValue(0));
Assert.IsNull(excelReader.GetValue(1));
Assert.AreEqual("Test", excelReader.GetString(2));
Assert.IsNull(excelReader.GetValue(3));
Assert.AreEqual(1, excelReader.GetDouble(4));
}
}
public void DataReader_Read_Test()
{
IExcelDataReader r =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_num_double_date_bool_string"));
var table = new DataTable();
table.Columns.Add(new DataColumn("num_col", typeof(int)));
table.Columns.Add(new DataColumn("double_col", typeof(double)));
table.Columns.Add(new DataColumn("date_col", typeof(DateTime)));
table.Columns.Add(new DataColumn("boo_col", typeof(bool)));
int fieldCount = -1;
while (r.Read())
{
fieldCount = r.FieldCount;
table.Rows.Add(r.GetInt32(0), r.GetDouble(1), r.GetDateTime(2), r.IsDBNull(4));
}
r.Close();
Assert.AreEqual(6, fieldCount);
Assert.AreEqual(30, table.Rows.Count);
Assert.AreEqual(1, int.Parse(table.Rows[0][0].ToString()));
Assert.AreEqual(1346269, int.Parse(table.Rows[29][0].ToString()));
//double + Formula
Assert.AreEqual(1.02, double.Parse(table.Rows[0][1].ToString()));
Assert.AreEqual(4.08, double.Parse(table.Rows[2][1].ToString()));
Assert.AreEqual(547608330.24, double.Parse(table.Rows[29][1].ToString()));
//Date + Formula
Assert.AreEqual(new DateTime(2009, 5, 11).ToShortDateString(),
DateTime.Parse(table.Rows[0][2].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 11, 30).ToShortDateString(),
DateTime.Parse(table.Rows[29][2].ToString()).ToShortDateString());
}
private static string CreateInsertQueryFromRow(IExcelDataReader reader, List <string> ListOfHeaders)
{
var values = "";
//reads through each columns
for (int i = 0; i < reader.FieldCount; i++)
{
string value = "";
if (ListOfHeaders.ElementAt(i).Contains(STRING_IDENTIFIER))
{
value = reader.GetString(i);
value = value.Replace("'", "\"");
value = "'" + value + "'";
}
else if (ListOfHeaders.ElementAt(i).Contains(BOOL_IDENTIFIER))
{
value = reader.GetBoolean(i).ToString();
}
else if (ListOfHeaders.ElementAt(i).Contains(INT_IDENTIFIER))
{
value = reader.GetDouble(i).ToString();
if (value.Equals("-1") || LIST_OF_DIRECTION_COLUMNS.Contains(ListOfHeaders.ElementAt(i)))
{
value = "null";
}
}
values += value + ",";
}
if (!string.IsNullOrEmpty(values))
{
values = values.Substring(0, values.Length - 1);
string SqlInsert = SQL_INSERT_BEFORE + reader.Name + SQL_INSERT_MIDDLE + values + SQL_INSERT_END;
return(SqlInsert);
}
return("");
}
private void FillPropertyFromParsedField(IExcelDataReader reader, object newObject, int col, PropertyInfo targetProperty)
{
var fieldType = reader.GetFieldType(col);
if (fieldType == typeof(string))
{
ParseString(reader.GetString(col), newObject, targetProperty);
}
else if (fieldType == typeof(double))
{
ParseDouble(reader.GetDouble(col), newObject, targetProperty);
}
else if (fieldType == typeof(int))
{
ParseInt(reader.GetInt32(col), newObject, targetProperty);
}
else if (fieldType == typeof(bool))
{
ParseBool(reader.GetBoolean(col), newObject, targetProperty);
}
// Only other possibility is that type is null. Do nothing in this case.
}
public IEnumerable <Order> Map(IExcelDataReader dataReader, string delimiter)
{
_logger.LogInformation($"Map using OrderMapper.");
var quantity = dataReader.GetValue(70) == null ? 1 : (int)dataReader.GetDouble(70);
_logger.LogInformation("Order, quantity: {0}", quantity);
if (quantity > 1)
{
var productNameColumnContent = dataReader.GetString(57);
var hasDelimiters = productNameColumnContent.IndexOf(delimiter) > 0;
_logger.LogInformation("Split productname: {0}", hasDelimiters);
if (hasDelimiters)
{
var productNames = productNameColumnContent.Split(delimiter);
var prices = (dataReader.GetFieldType(62) == typeof(string) ? dataReader.GetString(62) : dataReader.GetDouble(62).ToString()).Split(delimiter);
for (int i = 0; i < quantity; i++)
{
var result = Read(dataReader);
result.ProductName = productNames[i];
result.ProductSalesPrice = prices[i];
yield return(result);
}
}
else
{
yield return(Read(dataReader));
}
}
else
{
yield return(Read(dataReader));
}
}
public IEnumerable <EnterpriseIndex> GetEnterpriseIndices(IExcelDataReader reader)
{
reader.Read();
while (reader.Read())
{
var enterprise = new Enterprise {
Id = reader.GetString(7), Name = reader.GetString(8)
};
yield return
(new EnterpriseIndex
{
Y = reader.GetDouble(0),
X1 = reader.GetDouble(1),
X2 = reader.GetDouble(2),
X3 = reader.GetDouble(3),
X4 = reader.GetDouble(4),
X5 = reader.GetDouble(5),
X6 = reader.GetDouble(6),
Enterprise = _context.Enterprises.Find(enterprise.Id) ?? enterprise
});
}
}