//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); }