//loads data from .xls files. prices. data for plots is stored in Pricings.xls
public void LoadPriceFiles()
{
bool doesExist = File.Exists(DependencyService.Get <ISave>().GetFileName() + "/Pricings.xls");
if (doesExist)
{
ExcelEngine excelEngine = new ExcelEngine();
FileStream inputStream = new FileStream(DependencyService.Get <ISave>().GetFileName() + "/Pricings.xls", FileMode.Open);
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(inputStream);
for (int x = 0; x < workbook.Worksheets.Count; x++)
{
SortedList <double, double> bracket = new SortedList <double, double>();
IWorksheet sheet = workbook.Worksheets[x];
if (sheet.GetValueRowCol(1, 1).ToString() == "Name")
{
string name = sheet.GetValueRowCol(1, 2).ToString();
double loglen = double.Parse(sheet.GetValueRowCol(2, 2).ToString());
for (int y = 0; y < int.Parse(sheet.GetValueRowCol(3, 3).ToString()); y++)
{
bracket.Add(double.Parse(sheet.GetValueRowCol(4 + y, 1).ToString()), double.Parse(sheet.GetValueRowCol(4 + y, 2).ToString()));
}
((List <PriceRange>)Application.Current.Properties["Prices"]).Add(new PriceRange(name, "yew", bracket, loglen));
}
}
inputStream.Dispose();
}
}
public static DataTable ExcelToDataTable(string fileName)
{
DataTable dt = new DataTable();
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(fileName, ExcelParseOptions.Default, true, "", ExcelVersion.Xlsx); //可以访问打开的excel
IWorksheet sheet = workbook.Worksheets[0]; //第一 个sheet
for (int col = 1; col <= sheet.Columns.Length; col++)
{
if (sheet.GetText(1, col) == null)
{
continue;
}
dt.Columns.Add(sheet.GetText(1, col), sheet.GetValueRowCol(1, col).GetType());
}
for (int i = 2; i <= sheet.Rows.Length; i++)
{
DataRow dataRow = dt.NewRow();
for (int j = 1; j <= sheet.Columns.Length; j++)
{
dataRow[j - 1] = sheet.GetValueRowCol(i, j);
}
dt.Rows.Add(dataRow);
}
}
return(dt);
}
// Кнопка загружаем из Excel
private void mK_load_Excel_ToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.CheckPathExists = true;
openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx|Excel (*.xls)|*.xls";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
{
try
{
FileInfo fi = new FileInfo(openFileDialog1.FileName);
ExcelEngine excelEngine = new ExcelEngine();
IApplication _excel = excelEngine.Excel;
// Тип файла
_excel.DefaultVersion = ExcelVersion.Excel97to2003; // .xls
if (fi.Extension == ".xlsx")
{
_excel.DefaultVersion = ExcelVersion.Excel2010; // .xlsx
}
IWorkbook wBook = _excel.Workbooks.Open(openFileDialog1.FileName);
IWorksheet wSheet = wBook.Worksheets[0];
list.Clear();
for (int i = 2; i <= wSheet.Columns.Count(); i++)
{
list.Add(new mul()
{
name = string.IsNullOrEmpty(wSheet.GetValueRowCol(1, i).ToString().Replace("-", "").Trim()) ? (i - 1).ToString() : wSheet.GetValueRowCol(1, i).ToString().Replace("-", "").Trim(),
P_E = string.IsNullOrEmpty(wSheet.GetValueRowCol(2, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(2, i),
E_P = string.IsNullOrEmpty(wSheet.GetValueRowCol(3, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(3, i),
P_S = string.IsNullOrEmpty(wSheet.GetValueRowCol(4, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(4, i),
P_BV = string.IsNullOrEmpty(wSheet.GetValueRowCol(5, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(5, i),
P_CF = string.IsNullOrEmpty(wSheet.GetValueRowCol(6, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(6, i),
CF_P = string.IsNullOrEmpty(wSheet.GetValueRowCol(7, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(7, i),
P_FCE = string.IsNullOrEmpty(wSheet.GetValueRowCol(8, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(8, i),
FCE_P = string.IsNullOrEmpty(wSheet.GetValueRowCol(9, i).ToString().Replace("-", "").Trim()) ? 0 : (decimal)wSheet.GetNumber(9, i)
});
}
filename_load = openFileDialog1.FileName;
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString(), "Ошибка открытия файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (filename_load != "")
{
mk_saveExcel_ToolStripMenuItem.Enabled = true;
}
showTable();
}
}
//loads data from .xls files plots. data for plots is stored in Plots.xls
public void LoadPlotFiles()
{
bool doesExist = File.Exists(DependencyService.Get <ISave>().GetFileName() + "/Plots.xls");
if (doesExist)
{
ExcelEngine excelEngine = new ExcelEngine();
FileStream inputStream = new FileStream(DependencyService.Get <ISave>().GetFileName() + "/Plots.xls", FileMode.Open);
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(inputStream);
for (int x = 0; x < workbook.Worksheets.Count; x++)
{
SortedList <double, double> bracket = new SortedList <double, double>();
IWorksheet sheet = workbook.Worksheets[x];
if (sheet.GetValueRowCol(1, 1).ToString() == "Name")
{
string name = sheet.GetValueRowCol(1, 2).ToString();
double[] geotag = { double.Parse(sheet.GetValueRowCol(2, 2).ToString()), double.Parse(sheet.GetValueRowCol(2, 3).ToString()) };
Plot newPlot = new Plot(name);
newPlot.SetTag(geotag);
newPlot.Owner = sheet.GetValueRowCol(1, 5).ToString();
try { newPlot.YearPlanted = int.Parse(sheet.GetValueRowCol(3, 5).ToString()); } catch { };
newPlot.NearestTown = sheet.GetValueRowCol(2, 5).ToString();
newPlot.Owner = sheet.GetValueRowCol(1, 5).ToString();
newPlot.Describe = sheet.GetValueRowCol(4, 5).ToString();
List <Position> PolyPlot = new List <Position>();
for (int y = 0; y < int.Parse(sheet.GetValueRowCol(3, 3).ToString()); y++)
{
PolyPlot.Add(new Position(double.Parse(sheet.GetValueRowCol(5 + y, 1).ToString()), double.Parse(sheet.GetValueRowCol(5 + y, 2).ToString())));
}
newPlot.AddPolygon(PolyPlot);
((List <Plot>)Application.Current.Properties["Plots"]).Add(newPlot);
}
}
inputStream.Dispose();
}
}
public static async Task ReloadOntology(string filename)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
OpenFileDialog dlg = new OpenFileDialog();
IWorkbook workbook = application.Workbooks.Open(filename);
//Sets workbook version.
workbook.Version = ExcelVersion.Excel2016;
IWorksheet worksheet = workbook.Worksheets[0];
Data.TestData.OntologyUP = new DataFromOntology();
Data.TestData.OntologyUP.Ss = new List <string>();
Data.TestData.OntologyUP.Pr = new List <string>();
for (int i = 2; i < worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1).ToString() != "")
{
Data.TestData.OntologyUP.Ss.Add(worksheet.GetValueRowCol(i, 1).ToString());
}
else
{
break;
}
}
worksheet = workbook.Worksheets[1];
for (int i = 2; i < worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1).ToString() != "")
{
Data.TestData.OntologyUP.Pr.Add(worksheet.GetValueRowCol(i, 1).ToString());
}
else
{
break;
}
}
FileUtils.JsonSerialization(@"Data\OntologyData.json", Data.TestData.OntologyUP);
}
//loads data from .xls files populates plots with trees. data for trees is stored in <PlotName>.xls
public void LoadTreeFiles2()
{
List <(string, double)> currency = new List <(string, double)>();
int shift = 0;
int treecounter = -1;
bool doesExist = File.Exists(DependencyService.Get <ISave>().GetFileName() + "/trees.xls");
if (doesExist)
{
ExcelEngine excelEngine = new ExcelEngine();
FileStream inputStream = new FileStream(DependencyService.Get <ISave>().GetFileName() + "/trees.xls", FileMode.Open);
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
Plot Thisplot;
if (sheet.GetValueRowCol(1, 1).ToString() == "Tree ID")
{
//if (sheet.GetValueRowCol(1, 14).ToString() == "Currency") {
int p = 0;
while (sheet.GetValueRowCol(2 + p, 14).ToString() != null && sheet.GetValueRowCol(2 + p, 14).ToString() != "")
{
currency.Add((sheet.GetValueRowCol(2 + p, 14).ToString(), double.Parse(sheet.GetValueRowCol(2 + p, 15).ToString())));
p++;
}
Application.Current.Properties["Currenlist"] = currency;
//}
if (sheet.GetValueRowCol(1, 6).ToString() == "Merchantable Height")
{
shift = 1;
}
shift = 1;
for (int y = 0; y < int.Parse(sheet.GetValueRowCol(1, 10 + shift).ToString()); y++)
{
for (int x = 0; x < ((List <Plot>)Application.Current.Properties["Plots"]).Count; x++)
{
if (((List <Plot>)Application.Current.Properties["Plots"]).ElementAt(x).GetName() == sheet.GetValueRowCol(2 + y, 2).ToString())
{
Thisplot = ((List <Plot>)Application.Current.Properties["Plots"]).ElementAt(x);
for (int z = 0; z < Thisplot.getTrees().Count; z++)
{
if (Thisplot.getTrees().ElementAt(z).Id.ToString() == sheet.GetValueRowCol(2 + y, 1).ToString())
{
treecounter = z;
}
}
if (treecounter > -1)
{
if (shift == 1)
{
Thisplot.getTrees().ElementAt(treecounter).AddToHistory(double.Parse(sheet.GetValueRowCol(2 + y, 4).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 5).ToString()), DateTime.Parse(sheet.GetValueRowCol(2 + y, 3).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 6).ToString()));
}
else
{
Thisplot.getTrees().ElementAt(treecounter).AddToHistory(double.Parse(sheet.GetValueRowCol(2 + y, 4).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 5).ToString()), DateTime.Parse(sheet.GetValueRowCol(2 + y, 3).ToString()));
}
}
else
{
if (shift == 1)
{
Thisplot.AddTree(new Tree(double.Parse(sheet.GetValueRowCol(2 + y, 4).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 5).ToString()), int.Parse(sheet.GetValueRowCol(2 + y, 1).ToString()), DateTime.Parse(sheet.GetValueRowCol(2 + y, 3).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 6).ToString())));
}
else
{
Thisplot.AddTree(new Tree(double.Parse(sheet.GetValueRowCol(2 + y, 4).ToString()), double.Parse(sheet.GetValueRowCol(2 + y, 5).ToString()), int.Parse(sheet.GetValueRowCol(2 + y, 1).ToString()), DateTime.Parse(sheet.GetValueRowCol(2 + y, 3).ToString())));
}
}
treecounter = -1;
x = ((List <Plot>)Application.Current.Properties["Plots"]).Count + 1;
}
}
}
}
inputStream.Dispose();
}
}
public static async Task LoadExcel(string filename)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
OpenFileDialog dlg = new OpenFileDialog();
IWorkbook workbook = application.Workbooks.Open(filename);
//Sets workbook version.
workbook.Version = ExcelVersion.Excel2016;
IWorksheet worksheet = workbook.Worksheets[0];
ProgectData.CrossSystemsLimitation_data = new BindingList <CrossSystemsLimitation>();
ProgectData.UniClassGroup_data = new BindingList <UniClassGroup>();
ProgectData.GlobalGroup_data = new BindingList <GlobalGroup>();
ProgectData.Limitation_functions_data = new BindingList <Limitation_functions>();
ProgectData.GroupsSs = new BindingList <GroupsUC>();
ProgectData.GroupsPr = new BindingList <GroupsUC>();
ProgectData.OntologyGroups = new BindingList <OntologyGroups>();
for (int i = 2; i < worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 9) != null && worksheet.GetValueRowCol(i, 9) != "")
{
CrossSystemsLimitation newLimit = new CrossSystemsLimitation();
newLimit.N = i;
newLimit.SsSource = worksheet.GetValueRowCol(i, 1).ToString();
newLimit.PrSource = worksheet.GetValueRowCol(i, 2).ToString();
newLimit.GroupSource = worksheet.GetValueRowCol(i, 3).ToString();
newLimit.SsTarget = worksheet.GetValueRowCol(i, 4).ToString();
newLimit.PrTarget = worksheet.GetValueRowCol(i, 5).ToString();
newLimit.GroupTarget = worksheet.GetValueRowCol(i, 6).ToString();
newLimit.Description = worksheet.GetValueRowCol(i, 7).ToString();
//string descr = ""/*Utils.CreateDescrLimitation(newLimit, ProgectData.uniClassGroups_key, ProgectData.UniSystemTable, ProgectData.UniProductTable)*/;
//newLimit.Description = descr;
newLimit.PointSource = worksheet.GetValueRowCol(i, 8).ToString();
newLimit.IDLimitation = worksheet.GetValueRowCol(i, 9).ToString();
newLimit.ConnectionWeight = worksheet.GetValueRowCol(i, 10).ToString();
newLimit.StrSource = worksheet.GetValueRowCol(i, 11).ToString();
newLimit.Category = worksheet.GetValueRowCol(i, 12).ToString();
ProgectData.CrossSystemsLimitation_data.Add(newLimit);
//colLimitation.Add(newLimit);
}
else
{
break;
}
}
ProgectData.UniclassGroupDescription = new Dictionary <int, string>();
worksheet = workbook.Worksheets[1];
for (int i = 2; i <= worksheet.Rows.Count <IRange>(); i++)
{
try {
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1) != "")
{
int key = Convert.ToInt32(worksheet.GetValueRowCol(i, 1).ToString());
if (ProgectData.UniclassGroupDescription.ContainsKey(key) == false)
{
ProgectData.UniclassGroupDescription.Add(key, worksheet.GetValueRowCol(i, 8).ToString());
}
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1) != "")
{
UniClassGroup newData = new UniClassGroup();
newData.GroupID = Convert.ToInt32(worksheet.GetValueRowCol(i, 1).ToString());
newData.Exclude = worksheet.GetValueRowCol(i, 2).ToString();
newData.SystemsID = worksheet.GetValueRowCol(i, 3).ToString();
newData.ProductID = worksheet.GetValueRowCol(i, 4).ToString();
newData.GroupDescription = Utils.CreateDescrGroups(newData.SystemsID, newData.ProductID);
newData.GroupDescription_Note = worksheet.GetValueRowCol(i, 8).ToString();
newData.Function_layer = worksheet.GetValueRowCol(i, 7).ToString();
newData.AnalyticalModel = worksheet.GetValueRowCol(i, 6).ToString();
ProgectData.UniClassGroup_data.Add(newData);
}
}
else
{
break;
}
}
catch {
MessageBox.Show("Ошибка при чтении UniClassGroups");
}
}
worksheet = workbook.Worksheets[2];
for (int i = 1; i < worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1) != "")
{
Limitation_functions newLimitFunc = new Limitation_functions();
newLimitFunc.IDLimitation = worksheet.GetValueRowCol(i, 1).ToString();
newLimitFunc.IDLimitDescription = worksheet.GetValueRowCol(i, 2).ToString();
ProgectData.Limitation_functions_data.Add(newLimitFunc);
}
}
worksheet = workbook.Worksheets[3];
for (int i = 2; i < worksheet.Rows.Count <IRange>(); i++)
{
var IDGlobalGroup = worksheet.GetValueRowCol(i, 1).ToString();
if (IDGlobalGroup != null && IDGlobalGroup != "")
{
GlobalGroup newGroup = new GlobalGroup();
newGroup.IDGlobalGroup = Convert.ToInt32(worksheet.GetValueRowCol(i, 1).ToString());
newGroup.SystemID = worksheet.GetValueRowCol(i, 2).ToString();
newGroup.ProductID = worksheet.GetValueRowCol(i, 3).ToString();
newGroup.LocGroups = worksheet.GetValueRowCol(i, 4).ToString();
newGroup.ZCoord = worksheet.GetValueRowCol(i, 5).ToString();
newGroup.GlobalGroupsDescription = worksheet.GetValueRowCol(i, 6).ToString();
ProgectData.GlobalGroup_data.Add(newGroup);
}
}
worksheet = workbook.Worksheets[6];
for (int i = 2; i <= worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1).ToString() != "")
{
var uc = new GroupsUC();
uc.Group = worksheet.GetValueRowCol(i, 1).ToString();
uc.UC_Id = worksheet.GetValueRowCol(i, 3).ToString();
uc.UC_Description = worksheet.GetValueRowCol(i, 4).ToString();
uc.GroupDescription = worksheet.GetValueRowCol(i, 2).ToString();
ProgectData.GroupsSs.Add(uc);
}
}
worksheet = workbook.Worksheets[7];
for (int i = 2; i <= worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1).ToString() != "")
{
var up = new GroupsUC();
up.Group = worksheet.GetValueRowCol(i, 1).ToString();
up.UC_Id = worksheet.GetValueRowCol(i, 3).ToString();
up.UC_Description = worksheet.GetValueRowCol(i, 4).ToString();
up.GroupDescription = worksheet.GetValueRowCol(i, 2).ToString();
ProgectData.GroupsPr.Add(up);
}
}
OntologyGroups og = new OntologyGroups();
og.keyValuePairs = new Dictionary <string, string>();
worksheet = workbook.Worksheets[8];
for (int i = 2; i <= worksheet.Rows.Count <IRange>(); i++)
{
if (worksheet.GetValueRowCol(i, 1) != null && worksheet.GetValueRowCol(i, 1).ToString() != "")
{
string group = worksheet.GetValueRowCol(i, 2).ToString();
string ontology = worksheet.GetValueRowCol(i, 1).ToString();
og.keyValuePairs.Add(group, ontology);
}
}
ProgectData.OntologyGroups.Add(og);
}
private async Task <bool> ExcelProcess()
{
var excelPath = config.sourceOptions?.FirstOrDefault(p => p.Key == "excelPath").Value;
var excelSheet = config.sourceOptions?.FirstOrDefault(p => p.Key == "excelSheet").Value;
if (string.IsNullOrWhiteSpace(excelPath) || string.IsNullOrWhiteSpace(excelSheet))
{
Logger.ErrorFormat("Excel params from config is invalid.");
return(false);
}
if (!Directory.Exists(excelPath))
{
Logger.ErrorFormat("Failed open directory in: {0}", excelPath);
}
else
{
Logger.Info("Starting proccess excel");
DirectoryInfo di = new DirectoryInfo(excelPath);
FileInfo[] files = di.GetFiles("*.bak");
if (files.Count() == 0)
{
Logger.InfoFormat("No excel files found!!!");
return(false);
}
if (string.IsNullOrEmpty(ApiTicket))
{
return(false);
}
foreach (var file in files)
{
Stream excelStream = file.OpenRead();
using (excelStream)
{
if (excelStream != null)
{
Logger.InfoFormat("{0} Excel file loaded", file.Name.ToString());
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(excelStream);
IWorksheet sheet = workbook.Worksheets.FirstOrDefault(p => p.Name == excelSheet);
if (sheet == null)
{
Logger.ErrorFormat("{0} sheet not found in {1} excel file. File Skiped", excelSheet, file.Name);
continue;
}
Logger.Info("Reading Excel content.");
if (sheet.Rows.Count() > 1)
{
for (int i = 1; i < sheet.Rows.Count(); i++)
{
List <DataInfoField> dataInfoFields = new List <DataInfoField>();
var dataId = string.Empty;
for (int j = 0; j < sheet.Columns.Count(); j++)
{
DataInfoField dataInfoField = new DataInfoField();
var columnName = sheet.GetValueRowCol(1, j + 1).ToString();
if (columnName == columnId)
{
dataId = sheet.GetValueRowCol(i + 1, j + 1).ToString();
}
else
{
dataInfoField.fieldname = columnName;
dataInfoField.value = sheet.GetValueRowCol(i + 1, j + 1).ToString();
dataInfoField.type = "";
dataInfoFields.Add(dataInfoField);
}
}
DataInfo dataInfoItem = new DataInfo();
dataInfoItem.dataInfoFields = dataInfoFields;
if (!historyStorage.Exits(dataId))
{
if (await StartProcess(dataInfoItem, processId, communityId, config.initWF, config.superAdmin, dataId))
{
historyStorage.InsertItem(dataId, 0, 1);
}
else
{
historyStorage.InsertItem(dataId, 1, 0);
}
}
else
{
var faildAttems = historyStorage.IsFailed(dataId);
if (faildAttems > 0 && faildAttems <= maxAttemps)
{
// retry and updateElement
if (await StartProcess(dataInfoItem, processId, communityId, config.initWF, config.superAdmin, dataId))
{
historyStorage.UpdateItem(dataId, faildAttems, 1);
}
else
{
historyStorage.UpdateItem(dataId, faildAttems + 1, 0);
}
}
}
}
}
else
{
Logger.Warn("This sheet of excel not contain informations.");
}
Logger.InfoFormat("{0} Excel file process complete.", file.Name.ToString());
}
}
}
}
return(true);
}
