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