355 lines
13 KiB
C#
355 lines
13 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Drawing;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Diagnostics;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Windows.Forms;
|
|
using Microsoft.Office.Interop.Excel; // zusätzlich ist der Projektverweis auf COM -> Microsoft Excel xx-Objectlibrary
|
|
using NXOpen;
|
|
using NXOpen.UF;
|
|
|
|
namespace UpdateStandPartFamMemUsStat
|
|
{
|
|
public partial class Form1 : Form
|
|
{
|
|
#region Version und Copyright
|
|
// Version und Copyright
|
|
string programName = Path.GetFileNameWithoutExtension(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase); //Den Programmnamen auslesen
|
|
string programVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
|
|
static object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCopyrightAttribute), false);
|
|
string copyright = ((AssemblyCopyrightAttribute)attributes[0]).Copyright;
|
|
string icon = System.Windows.Forms.Application.StartupPath + "\\Info.bmp";
|
|
//Assembly Datum und Zeit
|
|
static DateTime value = AssemblyDateTime();
|
|
string date = value.ToShortDateString();
|
|
string time = value.ToLongTimeString();
|
|
private static DateTime AssemblyDateTime()
|
|
{
|
|
var version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
|
|
var buildDateTime = new DateTime(2000, 1, 1).Add(new TimeSpan(TimeSpan.TicksPerDay * version.Build + TimeSpan.TicksPerSecond * 2 * version.Revision));
|
|
//Tage seit dem 1. Januar 2000 und Sekunden seit Mitternacht, (multiplizier mit 2 ergibt das Original)
|
|
return buildDateTime;
|
|
}
|
|
#endregion
|
|
|
|
#region Programm-Info
|
|
/// <summary>
|
|
/// Contains the name of the program file
|
|
/// </summary>
|
|
public static string ProgramName { set; get; }
|
|
|
|
/// <summary>
|
|
/// Contains the version of the program file
|
|
/// </summary>
|
|
public static string ProgramVersion { set; get; }
|
|
|
|
/// <summary>
|
|
/// Contains the copyright notice
|
|
/// </summary>
|
|
public static string Copyright { set; get; }
|
|
|
|
private void SetProgramInfo()
|
|
{
|
|
//Name, Version und Copyright setzen
|
|
ProgramName = programName;
|
|
ProgramVersion = programVersion;
|
|
Copyright = copyright;
|
|
}
|
|
#endregion
|
|
|
|
//Spalten Namen
|
|
const string colObject = "Objekt";
|
|
const string colVStatus = "VStat";
|
|
const string colItemNumber = "ItemNumber";
|
|
const string colStatus = "Status";
|
|
|
|
//int colomnStatus = 0;
|
|
|
|
int oRangeSap;
|
|
//int oRangeReuse;
|
|
|
|
//string fileNameReuse = "";
|
|
|
|
public static List<string> SapObject { set; get; }
|
|
public static List<string> SapVStat { set; get; }
|
|
public static List<string> PfItem { set; get; }
|
|
public static List<string> PfStatusOld { set; get; }
|
|
public static List<string> PfStatusNew { set; get; }
|
|
|
|
////oExcelApp benötigt den kompletten Namensraum, da andernfalls der Compiler den Unterschied zwischen
|
|
////"Microsoft.Office.Interop.Excel.Application" und "System.Windows.Forms.Application" nicht erkennen kann
|
|
//private Microsoft.Office.Interop.Excel.Application oExcelApp = new Microsoft.Office.Interop.Excel.Application();
|
|
//private Workbook oWorkbook = null;
|
|
//private Worksheet oWorksheet = null;
|
|
|
|
|
|
public Form1()
|
|
{
|
|
SetProgramInfo(); //Name, Version und Copyright setzen
|
|
InitializeComponent();
|
|
|
|
Session theSession = Session.GetSession();
|
|
Part dispPart = theSession.Parts.Display;
|
|
Part workPart = theSession.Parts.Work;
|
|
UFSession theUFSession = UFSession.GetUFSession();
|
|
lw = theSession.ListingWindow;
|
|
}
|
|
|
|
private void Form1_Load(object sender, EventArgs e)
|
|
{
|
|
this.MaximumSize = new Size(808, Screen.PrimaryScreen.Bounds.Height - 30); //Um 30 kleiner, weil sonst der untere Rand hinter der Start-Leiste verschwindet
|
|
|
|
this.Text = String.Concat("::::: ", ProgramName, " ::::: -- V", ProgramVersion, " - ", Copyright.Replace("Copyright ", ""), " --");
|
|
|
|
lwWriteLine("Selection Window opened.");
|
|
}
|
|
|
|
private void buttonReadSapColumn_Click(object sender, EventArgs e)
|
|
{
|
|
buttonReadSapColumn.Enabled = false;
|
|
|
|
if (!ReadSapColumns())
|
|
{
|
|
buttonReadSapColumn.Enabled = true;
|
|
return;
|
|
}
|
|
|
|
buttonReadSapColumn.Enabled = false;
|
|
|
|
ReadReuseColumns();
|
|
|
|
buttonSaveChanges.Enabled = true;
|
|
}
|
|
|
|
private void buttonSaveChanges_Click(object sender, EventArgs e)
|
|
{
|
|
List<object> columnItem = new List<object>();
|
|
List<object> columnStatusOld = new List<object>();
|
|
List<object> columnStatusNew = new List<object>();
|
|
|
|
buttonSaveChanges.Enabled = false;
|
|
|
|
//Alle Änderungen in eine Liste schreiben
|
|
int range = dataGridViewExcelReuse.Rows.Count;
|
|
while (dataGridViewExcelReuse.Rows[range - 1].Cells[0].Value == null)
|
|
{
|
|
range--; //Leerzeilen am Listenende entfernen
|
|
}
|
|
|
|
columnItem.Clear();
|
|
columnStatusOld.Clear();
|
|
columnStatusNew.Clear();
|
|
|
|
for (int i = 0; i < range; i++)
|
|
{
|
|
if ((bool)dataGridViewExcelReuse.Rows[i].Cells[3].Value & !String.IsNullOrEmpty(dataGridViewExcelReuse.Rows[i].Cells[2].Value.ToString()))
|
|
{
|
|
columnItem.Add(dataGridViewExcelReuse.Rows[i].Cells[0].Value);
|
|
columnStatusOld.Add(dataGridViewExcelReuse.Rows[i].Cells[1].Value);
|
|
columnStatusNew.Add(dataGridViewExcelReuse.Rows[i].Cells[2].Value);
|
|
}
|
|
}
|
|
|
|
Program.PfItem = columnItem;
|
|
Program.PfStatusOld = columnStatusOld;
|
|
Program.PfStatusNew = columnStatusNew;
|
|
|
|
CleanUpView();
|
|
|
|
this.Close();
|
|
}
|
|
|
|
private bool ReadSapColumns()
|
|
{
|
|
List<object> columnObject = new List<object>();
|
|
List<object> columnVstat = new List<object>();
|
|
List<object> columnStat = new List<object>();
|
|
|
|
//Datei öffnen
|
|
string fileName = ReadFileName("Browse SAP Excel File");
|
|
if (String.IsNullOrEmpty(fileName))
|
|
{
|
|
return false;
|
|
}
|
|
lwWriteLine($"SAP Excel sheet {fileName} selected.");
|
|
|
|
lwWriteLine("Determining 'usage status' values from the SAP Excel sheet.");
|
|
var excel = new Excel(fileName);
|
|
columnObject = excel.ReadColumn(colObject);
|
|
columnVstat = excel.ReadColumn(colVStatus);
|
|
columnStat = TranslateColumnValue(excel.RangeColumns, columnVstat);
|
|
oRangeSap = excel.RangeColumns;
|
|
|
|
dataGridViewExcelSAP.Rows.Add(excel.RangeColumns - 2); //Feld anlegen
|
|
|
|
for (int i = 0; i < excel.RangeColumns - 1; i++)
|
|
{
|
|
dataGridViewExcelSAP.Rows[i].Cells[0].Value = columnObject[i];
|
|
dataGridViewExcelSAP.Rows[i].Cells[1].Value = columnVstat[i];
|
|
dataGridViewExcelSAP.Rows[i].Cells[2].Value = columnStat[i];
|
|
}
|
|
|
|
excel.CloseSheet();
|
|
|
|
lwWriteLine("Showing determined and calculated values.");
|
|
|
|
return true;
|
|
}
|
|
|
|
private string ReadFileName(string title)
|
|
{
|
|
openFileDialogExcel = new OpenFileDialog();
|
|
openFileDialogExcel.Title = title;
|
|
openFileDialogExcel.DefaultExt = "xlsx";
|
|
openFileDialogExcel.Filter = "Excel file (*.xlsx)|*.xlsx|All files (*.*)|*.*";
|
|
openFileDialogExcel.FilterIndex = 1;
|
|
openFileDialogExcel.CheckFileExists = true;
|
|
openFileDialogExcel.CheckPathExists = true;
|
|
openFileDialogExcel.ShowDialog();
|
|
|
|
return openFileDialogExcel.FileName;
|
|
}
|
|
|
|
private void ReadReuseColumns()
|
|
{
|
|
List<object> columnItemNumber = new List<object>();
|
|
List<object> columnStatusOld = new List<object>();
|
|
List<object> columnStatusNew = new List<object>();
|
|
List<bool> changeStatus = new List<bool>();
|
|
|
|
lwWriteLine("Determining child members.");
|
|
|
|
//Datei holen
|
|
columnItemNumber = Program.PfItem;
|
|
columnStatusOld = Program.PfStatusOld;
|
|
columnStatusNew = MatchValues(columnItemNumber.Count, columnItemNumber, columnStatusOld);
|
|
changeStatus = MatchChangeStatus(columnStatusOld, columnStatusNew);
|
|
|
|
lwWriteLine("Determining old and new values and mark changes.");
|
|
dataGridViewExcelReuse.Rows.Add(columnItemNumber.Count - 1); //Feld anlegen
|
|
|
|
for (int i = 0; i < columnItemNumber.Count; i++)
|
|
{
|
|
dataGridViewExcelReuse.Rows[i].Cells[0].Value = columnItemNumber[i];
|
|
dataGridViewExcelReuse.Rows[i].Cells[1].Value = columnStatusOld[i];
|
|
dataGridViewExcelReuse.Rows[i].Cells[2].Value = columnStatusNew[i];
|
|
dataGridViewExcelReuse.Rows[i].Cells[3].Value = changeStatus[i];
|
|
}
|
|
|
|
lwWriteLine("Showing determined and calculated values.");
|
|
|
|
buttonSaveChanges.Enabled = true;
|
|
}
|
|
|
|
private List<object> TranslateColumnValue(int rangeColumns, List<object> columnVstat)
|
|
{
|
|
string temp = "";
|
|
List<object> newValues = new List<object>();
|
|
|
|
for (int i = 0; i < rangeColumns - 1; i++)
|
|
{
|
|
temp = columnVstat[i].ToString();
|
|
temp = temp.Remove(temp.LastIndexOf(")"));
|
|
temp = temp.Remove(0, temp.IndexOf("(") + 1);
|
|
newValues.Add(temp);
|
|
}
|
|
|
|
return newValues;
|
|
}
|
|
|
|
private List<object> MatchValues(int range, List<object> columnItemNumber, List<object> columnStatusOld)
|
|
{
|
|
List<object> columnStatusNew = new List<object>();
|
|
|
|
string value = "";
|
|
|
|
for (int i = 0; i < range; i++)
|
|
{
|
|
//Zeilenweise die 'ItemNumber' einlesen. Wenn der Wert nicht '<Kein Wert>' ist, dann in der SAP Liste Spalte 1 durchsuchen ob der Wert existiert. Existiert der Wert, dann den Wert aus 'Status' in 'StatusNew' schreiben und 'ChangeStatus' setzen.
|
|
if (columnItemNumber[i] != null)
|
|
{
|
|
if (columnItemNumber[i].ToString() != "<Kein Wert>")
|
|
{
|
|
value = DetermineValue(oRangeSap, columnItemNumber[i].ToString());
|
|
columnStatusNew.Add(String.Concat("\"", value, "\""));
|
|
if (columnStatusNew[i].ToString() == "\"\"")
|
|
{
|
|
columnStatusNew[i] = "";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
columnStatusNew.Add(null);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
columnStatusNew.Add(columnItemNumber[i]);
|
|
}
|
|
}
|
|
|
|
return columnStatusNew;
|
|
}
|
|
|
|
private List<bool> MatchChangeStatus(List<object> columnStatusOld, List<object> columnStatusNew)
|
|
{
|
|
List<bool> changeStatus = new List<bool>();
|
|
|
|
int range = columnStatusOld.Count;
|
|
|
|
for (int i = 0; i < range; i++)
|
|
{
|
|
if (columnStatusNew[i] == null || columnStatusNew[i].ToString() == "" || columnStatusOld[i].ToString() == columnStatusNew[i].ToString())
|
|
{
|
|
changeStatus.Add(false);
|
|
}
|
|
else
|
|
{
|
|
changeStatus.Add(true);
|
|
}
|
|
|
|
}
|
|
|
|
return changeStatus;
|
|
}
|
|
|
|
private string DetermineValue(int range, string value)
|
|
{
|
|
string newValue = "";
|
|
|
|
for (int j = 0; j < range - 1; j++)
|
|
{
|
|
if (dataGridViewExcelSAP.Rows[j].Cells[0].Value != null)
|
|
{
|
|
if (dataGridViewExcelSAP.Rows[j].Cells[0].Value.ToString() == value)
|
|
{
|
|
newValue = dataGridViewExcelSAP.Rows[j].Cells[2].Value.ToString();
|
|
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
return newValue;
|
|
}
|
|
|
|
private void CleanUpView()
|
|
{
|
|
dataGridViewExcelSAP.Rows.Clear();
|
|
dataGridViewExcelReuse.Rows.Clear();
|
|
}
|
|
|
|
ListingWindow lw;
|
|
private void lwWriteLine(string line)
|
|
{
|
|
lw.Open(); lw.WriteLine(line); lw.Close();
|
|
}
|
|
}
|
|
}
|