private void sum(ExcelLine excelLine) { if (this.InvokeRequired) { this.Invoke( new MethodInvoker( delegate() { sum(excelLine); })); } else { double x = 0; if (excelLine.FactorDePutere < 0.65) { x = excelLine.PutereReactiva * 0.1926; double sum = x + double.Parse(sum1Text.Text); sum1Text.Text = sum.ToString("0.00"); } if (excelLine.FactorDePutere > 0.65 && excelLine.FactorDePutere < 0.9) { x = excelLine.PutereReactiva * 0.0642; double sum = x + double.Parse(sum2Text.Text); sum2Text.Text = sum.ToString("0.00"); } else if (excelLine.FactorDePutere > 0.9) { x = 0; textBox2.Text = "" + x; } } }
/// <summary> /// Load Excel file from Vendor /// </summary> public string ParseEngine(string FilePath, string UserName) { string[] delimiter = new string[] { "\t" }; String ExcelLine; String LineProblems = ""; LocationCode = ""; WorkFormsData("ClearExcel", ""); // Now scan the contents using (StreamReader sr = new StreamReader(FilePath)) { while ((ExcelLine = sr.ReadLine()) != null) { string[] Cols = ExcelLine.Split(delimiter, StringSplitOptions.RemoveEmptyEntries); LocationValue = ""; PartValue = ""; BinValue = ""; CapacityValue = "0"; RecStatus = ""; string[] LineResults = ValidateInputColumns(Cols); LineProblems = LineResults[1]; RecStatus = LineResults[0]; if (RecStatus != "EM") { try { WorkFormsData("InsertExcel" , "" , LocationValue , PartValue , BinValue , CapacityValue , "Excel:" + RecStatus , "0" , "" , UserName); } catch (Exception ex) { LineProblems += "Write; "; RecStatus = "99"; WorkFormsData("InsertRTS" , "" , LocationValue , PartValue , BinValue , "0" , "Excel:" + RecStatus , "0" , "" , UserName); } } recctr += 1; } } return("Recs " + recctr.ToString()); }
private double applyAlgorithm(ExcelLine line) { double x = 0; if (line.FactorDePutere < 0.65) { x = line.PutereReactiva * 0.1926; } else if (line.FactorDePutere < 0.9) { x = line.PutereReactiva * 0.0642; } return(x); }
private void appendLine(ExcelLine line) { if (this.InvokeRequired) { this.Invoke( new MethodInvoker( delegate() { appendLine(line); })); } else { textBox1.AppendText("Date: " + line.DateAdded + " -----------> Val F: " + line.FactorDePutere.ToString("0.00") + " R: " + line.PutereReactiva.ToString("0.00") + "----------------------------------------------------->" + "||" + " Result: " + this.applyAlgorithm(line).ToString("0.00") + Environment.NewLine); } }
private void initFile(string path) { Microsoft.Office.Interop.Excel.Application excelApplication = null; Workbook excelWorkbook = null; try { excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelWorkbook = excelApplication.Workbooks.Open(path); for (int i = 1; i <= excelWorkbook.Worksheets.Count; i++) { dynamic dynamicWorksheet = excelWorkbook.Worksheets[i]; Worksheet worksheet = excelWorkbook.Worksheets[i]; ExcelWorksheet excelWorksheet = new ExcelWorksheet(dynamicWorksheet.Name); Range lastRow = dynamicWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing); Range workingRange = worksheet.get_Range("A1", lastRow); int numberOfRows = workingRange.Rows.Count; for (int j = 2; j <= numberOfRows; j++) { dynamic currentRow = workingRange.Rows[j]; dynamic cell1 = currentRow.Cells[1]; dynamic val1 = cell1.Value; dynamic cell2 = currentRow.Cells[2]; dynamic val2 = cell2.Value; dynamic cell5 = currentRow.Cells[5]; dynamic val5 = cell5.Value; dynamic cell8 = currentRow.Cells[8]; dynamic val8 = cell8.Value; try { int noCrt = Convert.ChangeType(val1, typeof(int)); DateTime date = val2 == null ? DateTime.MinValue : this.getDateTime(val2); double factorDePutere = val5 == null ? 0 : Convert.ChangeType(val5, typeof(double)); double putereReactiva = val8 == null ? 0 : Convert.ChangeType(val8, typeof(double)); if (date >= this.startInterval && date <= this.endInterval) { ExcelLine excelLine = new ExcelLine(noCrt, date, factorDePutere, putereReactiva); excelWorksheet.AddLine(excelLine); this.appendLine(excelLine); this.sum(excelLine); } } catch (Exception ex) { Console.WriteLine(ex); } processWorker.ReportProgress(j * 100 / numberOfRows); } myWorkbook.AddWorkshets(excelWorksheet); if (i == 1) { break; } } } catch (Exception ex) { Console.WriteLine(ex); } finally { try { excelWorkbook.Close(SaveChanges: true); } catch { } excelApplication.Quit(); if (excelWorkbook != null) { Marshal.ReleaseComObject(excelWorkbook); } if (excelApplication != null) { Marshal.ReleaseComObject(excelApplication); } excelWorkbook = null; excelApplication = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
private void MappingFrm_Load(object sender, EventArgs e) { ShowWaitForm(); dataGridView.Rows.Clear(); DataGridViewComboBoxColumn cmb = new DataGridViewComboBoxColumn(); cmb.HeaderText = "From Field"; cmb.Name = "cmb"; cmb.MinimumWidth = 250; if (setup.type == Library.Type.SQL) { string connetionString = "Data Source=" + setup.server + ";Initial Catalog=" + setup.database + ";Integrated Security=true;"; using (SqlConnection connection = new SqlConnection(connetionString)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name = '" + setup.sqlTable + "'"; connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { cmb.Items.Add(reader.GetString(0)); } } } } if (setup.type == Library.Type.CSV) { csvFieldList = new List <CsvField>(); csvLines = new List <CsvLine>(); int j = 0; using (var reader = new StreamReader(setup.csvPath)) { List <string> csvfields = new List <string>(); bool first = true; while (!reader.EndOfStream) { CsvLine csvLine = new CsvLine(); csvLine.csvFields = new List <CsvField>(); var line = reader.ReadLine(); string[] values = line.Split(';'); if (first) { csvfields = line.Split(';').ToList(); first = false; } else { for (int i = 0; i < csvfields.Count; i++) { csvLine.csvFields.Add(new CsvField { key = csvfields[i], index = i, value = values[i] }); } csvLines.Add(csvLine); _waitForm.updateLbl(j++); _waitForm.Refresh(); } } foreach (string s in csvfields) { cmb.Items.Add(s); } } } if (setup.type == Library.Type.Excel) { excelLines = new List <ExcelLine>(); ExcelLine excelLine = new ExcelLine(); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(setup.excelPath); Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; List <string> headers = new List <string>(); for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { if (i == 1) { cmb.Items.Add(xlRange.Cells[i, j].Value2.ToString()); headers.Add(xlRange.Cells[i, j].Value2.ToString()); } else { if (j == 1) { excelLine = new ExcelLine(); excelLine.excelFields = new List <ExcelField>(); } if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) { excelLine.excelFields.Add(new ExcelField { key = headers[j - 1], index = i, value = xlRange.Cells[i, j].Value2.ToString() }); } } } if (i != 1) { excelLines.Add(excelLine); _waitForm.updateLbl(i); _waitForm.Refresh(); } } GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); } dataGridView.Columns.Add(cmb); try { var client = new DataToBCService.DataToBc(); client.Url = setup.webServiceUrl; client.Credentials = new NetworkCredential(setup.userName, setup.password); string fields = client.GetFields(setup.function, setup.direct); List <string> result = fields.Split(',').ToList(); foreach (string s in result) { this.dataGridView.Rows.Add(s); } } catch (Exception) { MessageBox.Show("Not vaild setup"); Close(); } }