private List <double> GetVariableValues(string selectedFilterVariable, ExcelDataSet excelDataSet)
{
if (string.IsNullOrEmpty(selectedFilterVariable))
{
return(null);
}
var result = new List <double>();
var indexOfColumn = excelDataSet.TrialDataRows[0].Values.ToList().IndexOf(selectedFilterVariable);
for (int i = 1; i < excelDataSet.TrialDataRows.Count; i++)
{
var value = excelDataSet.TrialDataRows[i].Values[indexOfColumn] ?? string.Empty;
if (value == string.Empty)
{
result.Add(double.NaN);
}
else
{
double doubleValue;
if (double.TryParse(value, out doubleValue))
{
result.Add(doubleValue);
}
else
{
throw new InvalidOperationException(
"The provided value cannot be converted to double precision number.");
}
}
}
return(result);
}
public async Task <IActionResult> AddCheck(IFormFile checkExcel)
{
if (checkExcel != null)
{
var filePath = _hostingEnvironment.WebRootPath;
filePath = Path.Combine(filePath, "Files");
filePath = Path.Combine(filePath, checkExcel.FileName);
using (var fileStream = new FileStream(filePath, FileMode.Create))
{
await checkExcel.CopyToAsync(fileStream);
}
var _tatneftReports = ExcelDataSet.Read(filePath);
var _checks = from item in _tatneftReports
select new RefuelingCheck()
{
GasStation = Models.Enums.GasStation.Tatneft,
Liters = Convert.ToDecimal((double)item.Litres),
Truck = _truckRepository.GetTruckByCardNumber(((string)item.CardNumber).
Replace("держатель", string.Empty).
Replace("№", string.Empty).Replace(" ", string.Empty).Replace(":", string.Empty)),
RefuelDate = Convert.ToDateTime(((string)item.Date + " " + ((DateTime)item.Time).TimeOfDay).ToString()),
Price = Convert.ToDecimal((double)item.Price)
};
await _refuelingRepository.AddRangeRefuelingCheck(_checks.ToList());
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
}
return(Redirect(Request.Headers["Referer"].ToString()));
}
private List <double> GetFilterVariableValues(CohensSettings cohensSettings, ExcelDataSet excelDataSet)
{
List <double> filterVariableValues = null;
if (!string.IsNullOrEmpty(cohensSettings.SelectedFilterVariable))
{
filterVariableValues = GetVariableValues(cohensSettings.SelectedFilterVariable, excelDataSet);
}
return(filterVariableValues);
}
public HedgesSetupVM(ExcelDataSet excelDataSet, HedgesSettings settings)
{
_excelDataSet = excelDataSet;
_settings = settings;
var availableVariableNames = GetAvailableVariableNames().ToList();
AvailableVariables = new ObservableCollection <string>(availableVariableNames);
AvailableGroups = new ObservableCollection <string>(availableVariableNames);
AvailableFirstValues = new ObservableCollection <string>();
AvailableSecondValues = new ObservableCollection <string>();
}
public List <object> GenerateTrialResult(ExcelDataSet dataSet)
{
var result = new List <dynamic>();
var generetedType = GenerateType(dataSet.TrialDataRows[0].Values);
for (var i = 1; i < dataSet.TrialDataRows.Count; i++)
{
var generatedObject = GenerateObject(generetedType, dataSet.TrialDataRows[i].Values);
result.Add(generatedObject);
}
return(result);
}
public CohensSetupVM(ExcelDataSet excelDataSet, CohensSettings cohensSettings)
{
_cohensSettings = cohensSettings;
AvailableFirstVariables =
new ObservableCollection <string>(excelDataSet.TrialDataRows[0].Values.Select(p => p.ToString()));
AvailableFirstVariables.Insert(0, string.Empty);
AvailableSecondVariables =
new ObservableCollection <string>(excelDataSet.TrialDataRows[0].Values.Select(p => p.ToString()));
AvailableSecondVariables.Insert(0, string.Empty);
AvailableFilterVariables =
new ObservableCollection <string>(excelDataSet.TrialDataRows[0].Values.Select(p => p.ToString()));
AvailableFilterVariables.Insert(0, string.Empty);
}
public ExpandoObject CreateDynamicData(ExcelDataSet excelDataSet)
{
var data = _trialResultGenerator.GenerateTrialResult(excelDataSet);
var t = data[0].GetType();
var list = (IList)Activator.CreateInstance((typeof(List <>).MakeGenericType(t)));
foreach (var o in data)
{
list.Add(o);
}
dynamic result = new ExpandoObject();
result.Data = list;
return(result);
}
private DataTable FillDatatableFromExcel(string ExcelPath, int PrimaryKeyColumnIndex)
{
DataSet ExcelDataSet;
DataRow[] ExcelFilteredRows;
DataTable ExcelFilteredDataTable;
FileStream stream = File.Open(ExcelPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
ExcelDataSet = excelReader.AsDataSet();
ExcelFilteredRows = ExcelDataSet.Tables[0].Select("[" + ExcelDataSet.Tables[0].Columns[PrimaryKeyColumnIndex].ColumnName + "] IS NOT NULL");
ExcelFilteredDataTable = ExcelFilteredRows.CopyToDataTable();
ExcelDataSet.Clear();
ExcelDataSet.Dispose();
excelReader.Close();
return(ExcelFilteredDataTable);
}
public static ExcelDataSet ReadExcel(string excelPath)
{
ExcelDataSet set = new ExcelDataSet();
if (!File.Exists(excelPath))
{
throw new Exception("path not exists");
}
try
{
FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
do
{
ExcelDataTable table = new ExcelDataTable();
// sheet name
while (excelReader.Read())
{
ExcelDataRow row = new ExcelDataRow();
for (int i = 0; i < excelReader.FieldCount; i++)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
row.Cells.Add(value);
}
table.Rows.Add(row);
}
if (table.Rows.Count > 0)
{
set.Tables.Add(table);
}
} while (excelReader.NextResult());
excelReader.Dispose();
stream.Dispose();
}
catch (Exception e)
{
DebugLogWrapper.LogError(e);
}
return(set);
}
public HedgesCalculator(ExcelDataSet excelDataSet, HedgesSettings hedgesSettings)
{
_excelDataSet = excelDataSet;
_hedgesSettings = hedgesSettings;
}
private void OpenExcelSheet(string filename)
{
_excelDataSet = _excelImporter.GetCellValues(filename);
_columnReducer.ReduceToColumnsWithDoubleValues(_excelDataSet);
Expando = _dynamicListGenerator.CreateDynamicData(_excelDataSet);
}
public CohensCalculator(ExcelDataSet excelDataSet, CohensSettings cohensSettings)
{
_excelDataSet = excelDataSet;
_cohensSettings = cohensSettings;
}
