/// <summary>
/// Generate Tree View
/// </summary>
private void PopulateTreeView()
{
var xlApp = new Excel.Application();
var xlWorkbook = OpennessHelper.GetExcelFile(networkListPath, xlApp);
foreach (Excel.Worksheet xlWorksheet in xlWorkbook.Worksheets)
{
string sheetName = xlWorksheet.Name;
var matrix = OpennessHelper.ExcelToMatrix(xlWorksheet);
if (OpennessHelper.IsNetworkList(matrix))
{
Worksheets.Add(sheetName);
}
}
xlWorkbook.Close(0);
xlApp.Quit();
var projectTreeView = new TreeView();
foreach (var ws in Worksheets)
{
TreeViewItem tvi = new TreeViewItem
{
Tag = ws,
Header = new CheckBox()
{
Content = new TextBlock()
{
Text = ws
},
Tag = ws
}
};
projectTreeView.Items.Add(tvi);
}
if (projectTreeView.Items.Count == 0)
{
TreeViewItem tvi = new TreeViewItem
{
Header = "Excel does not contain a valid Worksheet"
};
projectTreeView.Items.Add(tvi);
BtnEnabled = false;
}
ProjectTree.Refresh(projectTreeView);
}
/// <summary>
/// Saves the current DataGrid values in the chosen path
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Btn_Save_Click(object sender, RoutedEventArgs e)
{
WriteSavingLabelText("Saving...");
if (ExcelManager.IsOpened(FilePath))
{
MessageBox.Show("Please close the current workbook before saving");
}
else
{
RetrieveValues();
if (FilePath.Contains(".xlsx") || FilePath.Contains(".xlsm") || FilePath.Contains(".xltx") || FilePath.Contains(".xltm"))
{
Excel.Application xlApp = new Excel.Application
{
DisplayAlerts = false
};
Workbook xlWorkbook = xlApp.Workbooks.Open(FilePath);
int row = 4;
var matrixs = new List <object[, ]>();
var sheetNames = new List <string>();
foreach (Worksheet sheet in xlWorkbook.Worksheets)
{
string sheetName = sheet.Name;
if (!sheetName.Contains("AS_") || sheetName.Equals("AS_000000"))
{
continue;
}
object[,] matrix = OpennessHelper.ExcelToMatrix(sheet);
if (sheet.Name.Contains(ComboSheet.SelectedItem.ToString()))
{
Ws = sheet;
while (Ws.Cells[row, 3].Value != null)
{
Ws.Cells[row, 3].Value = null;
Ws.Cells[row, 4].Value = null;
Ws.Cells[row, 5].Value = null;
Ws.Cells[row, 6].Value = null;
Ws.Cells[row, 7].Value = null;
Ws.Cells[row, 8].Value = null;
row += 1;
}
row = 4;
foreach (Step step in steps)
{
Ws.Cells[row, 2].Value = step.StepNumber;
Ws.Cells[row, 3].Value = step.Schritt.Replace(" ", string.Empty);
Ws.Cells[row, 4].Value = step.Beschreibung;
Ws.Cells[row, 5].Value = step.Aktion.Replace(" ", string.Empty);
Ws.Cells[row, 6].Value = step.Vorheriger_Schritt.Replace(" ", string.Empty);
Ws.Cells[row, 7].Value = step.Nächster_Schritt.Replace(" ", string.Empty);
Ws.Cells[row, 8].Value = step.Zeit_Schritt.ToLower().Replace(" ", string.Empty).Replace("ms", string.Empty);
matrix[row, 2] = step.StepNumber;
matrix[row, 3] = step.Schritt.Replace(" ", string.Empty);
matrix[row, 4] = step.Beschreibung;
matrix[row, 5] = step.Aktion.Replace(" ", string.Empty);
matrix[row, 6] = step.Vorheriger_Schritt.Replace(" ", string.Empty);
matrix[row, 7] = step.Nächster_Schritt.Replace(" ", string.Empty);
matrix[row, 8] = step.Zeit_Schritt.ToLower().Replace(" ", string.Empty).Replace("ms", string.Empty);
row += 1;
}
}
matrixs.Add(matrix);
sheetNames.Add(sheet.Name);
}
MatrixList(matrixs);
SheetNamesList(sheetNames);
xlWorkbook.Save();
xlWorkbook.Close(0);
xlApp.Quit();
sheetsStepS = new List <object[, ]>();
foreach (var m in matrixs)
{
sheetsStepS.Add(m);
}
sheetsNameS = new List <string>();
foreach (var n in sheetNames)
{
sheetsNameS.Add(n);
}
Saving.Text = "";
Saving.Update();
if (Ws == null)
{
System.Windows.MessageBox.Show("This Excel does not contain a usable worksheet", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
WriteSavingLabelText("");
}
/// <summary>
/// Add PLC Tags in PLC DB file
/// </summary>
private void AddPlcDbTags(string robName)
{
Excel.Application xlApp = new Excel.Application();
Workbook xlWorkBook = OpennessHelper.GetExcelFile(PlcDBPath, xlApp);
Worksheet xlWorksheet = null;
if (xlWorkBook == null)
{
return;
}
try
{
xlWorksheet = xlWorkBook.Sheets["PLC Tags"];
}
catch (Exception)
{
MessageBox.Show("Could not find sheet named \"PLC Tags\" in file: \"" + PlcDBPath + "\"\n\nPLC DB Tags will not be imported.", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
xlWorkBook.Close(0);
xlApp.Quit();
return;
}
int lastRow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
object[,] matrix = OpennessHelper.ExcelToMatrix(xlWorksheet);
//SEARCH TAGS IN EXCEL AND ADD TO ROBBASE
for (int row = 1; row <= lastRow; row++)
{
if (Convert.ToString(matrix[row, 1]) != null) //If Column 'A' is not Null
{
//Check if name equals Robot Name
if (Convert.ToString(matrix[row, 1]).Contains(robName))
{
string symbolic = Convert.ToString(matrix[row, 1]);
string datatype = Convert.ToString(matrix[row, 3]);
string address = Convert.ToString(matrix[row, 4]);
string comment = Convert.ToString(matrix[row, 5]);
RobotBase rb = new RobotBase(symbolic, datatype, address, comment);
if (address.Contains('A')) // If startaddress is output
{
int outputIndexRob = -1;
outputIndexRob = robBase[0].FindIndex(a => a.Address.Equals(rb.Address));
if (outputIndexRob != -1)
{
robBase[0][outputIndexRob] = rb;
}
}
else if (address.Contains('E')) // If startaddress is input
{
int inputIndexRob = -1;
inputIndexRob = robBase[1].FindIndex(a => a.Address.Equals(rb.Address));
if (inputIndexRob != -1)
{
robBase[1][inputIndexRob] = rb;
}
}
}
}
}
xlWorkBook.Close(0);
xlApp.Quit();
}
/// <summary>
///Saves the current values of the 2 grid views in the current selected item of the tree view
/// </summary>
public void SaveEngValues()
{
RetrieveValues();
var matrixs = new List <object[, ]>();
var sheetNames = new List <string>();
if (TreeViewManager.FilePath != "" && File.Exists(TreeViewManager.FilePath))
{
if (TreeViewManager.FilePath.Contains(".xlsx") || TreeViewManager.FilePath.Contains(".xlsm") || TreeViewManager.FilePath.Contains(".xltx") || TreeViewManager.FilePath.Contains(".xltm"))
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.DisplayAlerts = false;
Workbook xlWorkbook = xlApp.Workbooks.Open(TreeViewManager.FilePath);
int row = 4;
foreach (Worksheet sheet in xlWorkbook.Worksheets)
{
if (sheet.Name.Contains("EngAssist"))
{
TreeViewManager.Ws = sheet;
object[,] EngAssistSheet = OpennessHelper.ExcelToMatrix(TreeViewManager.Ws);
while (TreeViewManager.Ws.Cells[row, 2].Value != null)
{
TreeViewManager.Ws.Cells[row, 2].Value = null;
TreeViewManager.Ws.Cells[row, 3].Value = null;
TreeViewManager.Ws.Cells[row, 4].Value = null;
TreeViewManager.Ws.Cells[row, 5].Value = null;
int column = 8;
while (TreeViewManager.Ws.Cells[row, column].Value != null)
{
TreeViewManager.Ws.Cells[row, column].Value = null;
TreeViewManager.Ws.Cells[row + 1, column].Value = null;
column += 1;
}
row += 2;
}
row = 4;
foreach (EngAssist engAssist in DBMaker.newEngValues)
{
TreeViewManager.Ws.Cells[row, 2].Value = engAssist.Arbeitsgruppe_ARG;
TreeViewManager.Ws.Cells[row, 3].Value = engAssist.Schutzkreis_SK;
TreeViewManager.Ws.Cells[row, 4].Value = engAssist.Station;
TreeViewManager.Ws.Cells[row, 5].Value = engAssist.Erw_Stationsbez_SBZ;
EngAssistSheet[row, 2] = engAssist.Arbeitsgruppe_ARG;
EngAssistSheet[row, 3] = engAssist.Schutzkreis_SK;
EngAssistSheet[row, 4] = engAssist.Station;
EngAssistSheet[row, 5] = engAssist.Erw_Stationsbez_SBZ;
int column = 8;
foreach (string part in engAssist.Parts)
{
TreeViewManager.Ws.Cells[row, column].Value = part;
EngAssistSheet[row, column] = part;
column += 1;
}
column = 8;
foreach (string valve in engAssist.Valves)
{
TreeViewManager.Ws.Cells[row + 1, column].Value = valve;
EngAssistSheet[row + 1, column] = valve;
column += 1;
}
row += 2;
}
matrixs.Add(EngAssistSheet);
}
else
{
var matrix = OpennessHelper.ExcelToMatrix(sheet);
matrixs.Add(matrix);
}
sheetNames.Add(sheet.Name);
}
MatrixList(matrixs);
SheetNamesList(sheetNames);
xlWorkbook.Save();
xlWorkbook.Close(0);
xlApp.Quit();
}
}
}
