437 lines
17 KiB
C#
437 lines
17 KiB
C#
|
using ClosedXML.Excel;
|
|||
|
|
using System.Diagnostics;
|
|||
|
|
|
|||
|
|
namespace AiQ_GUI
|
|||
|
|
{
|
|||
|
|
internal class Excel
|
|||
|
|
{
|
|||
|
|
public static void WriteTo(string FilePath)
|
|||
|
|
{
|
|||
|
|
if (File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
XLWorkbook workbook = new(FilePath); // Open existing file
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
worksheet.Cell("A1").Value = "Hello World!";
|
|||
|
|
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
|
|||
|
|
workbook.SaveAs(FilePath);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string ReadFrom(string FilePath)
|
|||
|
|
{
|
|||
|
|
if (File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
XLWorkbook workbook = new(FilePath); // Open existing file
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
return worksheet.Cell("A1").GetString();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int GetNextBlankRow(string FilePath)
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(FilePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
// Start from row 1 and check downwards
|
|||
|
|
int row = 1;
|
|||
|
|
while (!worksheet.Cell(row, 1).IsEmpty())
|
|||
|
|
{
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
return row;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string UpdateSpreadSheetPreTest(string FilePath, Versions Vers, string CamDesc, string ModelOnTest)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return "Spreadsheet not found";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(FilePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
// Start from row 1 and check downwards to find next empty row in column B
|
|||
|
|
int row = 1;
|
|||
|
|
while (!worksheet.Cell(row, 2).IsEmpty()) // Column B = Serial numbers
|
|||
|
|
{
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Safety check to avoid invalid index
|
|||
|
|
if (row <= 1)
|
|||
|
|
{
|
|||
|
|
return "Last serial number not found";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Generate new serial number from previous
|
|||
|
|
string lastSerialNumber = worksheet.Cell(row - 1, 2).GetString(); // Column B
|
|||
|
|
if (string.IsNullOrWhiteSpace(lastSerialNumber) || !lastSerialNumber.StartsWith("K"))
|
|||
|
|
{
|
|||
|
|
return "Invalid last serial number format";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
int NewSerialNumberInt = Convert.ToInt32(lastSerialNumber.Substring(1)) + 1;
|
|||
|
|
string newSerialNumber = "K" + NewSerialNumberInt;
|
|||
|
|
|
|||
|
|
// Write values to the corresponding columns
|
|||
|
|
worksheet.Cell(row, 1).Value = ModelOnTest; // Column A
|
|||
|
|
worksheet.Cell(row, 2).Value = newSerialNumber; // Column B
|
|||
|
|
worksheet.Cell(row, 3).Value = CamDesc; // Column C
|
|||
|
|
worksheet.Cell(row, 4).Value = "Pre Test: " + DateTime.Now.ToString("dd/MM/yyyy"); // Column D
|
|||
|
|
worksheet.Cell(row, 5).Value = Vers.version + " - " + Vers.revision + Environment.NewLine +
|
|||
|
|
Vers.buildtime + Environment.NewLine + Vers.proquint; // Column E
|
|||
|
|
worksheet.Cell(row, 8).Value = Vers.MAC; // Column H
|
|||
|
|
worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}"; // Column N
|
|||
|
|
worksheet.Cell(row, 19).Value = "TRUE"; // Column S
|
|||
|
|
|
|||
|
|
workbook.SaveAs(FilePath);
|
|||
|
|
|
|||
|
|
return newSerialNumber;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
|
|||
|
|
return $"ERROR: {ex.Message}";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string UpdateSpreadSheetRePreTest(string filePath, Versions Vers)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(filePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return "Spreadsheet not found";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(filePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
// Find the row with the matching serial number in column B
|
|||
|
|
int row = 1;
|
|||
|
|
while (!worksheet.Cell(row, 2).IsEmpty())
|
|||
|
|
{
|
|||
|
|
string cellSerial = worksheet.Cell(row, 2).GetString();
|
|||
|
|
if (cellSerial.Contains(Vers.Serial))
|
|||
|
|
{
|
|||
|
|
// Update columns D-E
|
|||
|
|
worksheet.Cell(row, 4).Value = "Pre Test: " + DateTime.Now.ToString("dd/MM/yyyy");
|
|||
|
|
worksheet.Cell(row, 5).Value = Vers.version + " - " + Vers.revision + Environment.NewLine +
|
|||
|
|
Vers.buildtime + Environment.NewLine + Vers.proquint;
|
|||
|
|
// Update MAC to column H
|
|||
|
|
worksheet.Cell(row, 8).Value = Vers.MAC;
|
|||
|
|
|
|||
|
|
// Update GUI Version to column N
|
|||
|
|
worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}";
|
|||
|
|
|
|||
|
|
// Write TRUE to WIP checkbox in column S
|
|||
|
|
worksheet.Cell(row, 19).Value = "TRUE";
|
|||
|
|
|
|||
|
|
workbook.SaveAs(filePath);
|
|||
|
|
return "OK";
|
|||
|
|
}
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return "Serial number not found";
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
|
|||
|
|
return $"ERROR: {ex.Message}";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string UpdateSpreadSheetFinalTest(string FilePath, Diags DiagsAPI, SSHData sshData, int RMANum)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return "Spreadsheet not found";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(FilePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
// Find the row with the matching serial number in column B
|
|||
|
|
int row = 1;
|
|||
|
|
while (!worksheet.Cell(row, 2).IsEmpty())
|
|||
|
|
{
|
|||
|
|
string serial = worksheet.Cell(row, 2).GetString();
|
|||
|
|
if (serial == DiagsAPI.serialNumber)
|
|||
|
|
{
|
|||
|
|
// Update column D with test date
|
|||
|
|
string existingDate = worksheet.Cell(row, 4).GetString(); // Column D
|
|||
|
|
string newDate = (RMANum != 0 ? "RMA Test: " : "Final Test: ") + DateTime.Now.ToString("dd/MM/yyyy");
|
|||
|
|
worksheet.Cell(row, 4).Value = existingDate + Environment.NewLine + newDate;
|
|||
|
|
|
|||
|
|
// Update columns F-G
|
|||
|
|
worksheet.Cell(row, 6).Value = DiagsAPI.licenses.raptorKeyID; // Column F
|
|||
|
|
worksheet.Cell(row, 7).Value = sshData.packages; // Column G
|
|||
|
|
|
|||
|
|
// Update columns N-S
|
|||
|
|
worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}"; // Column N
|
|||
|
|
worksheet.Cell(row, 15).Value = DiagsAPI.licenses.saf1; // Column O
|
|||
|
|
worksheet.Cell(row, 16).Value = DiagsAPI.licenses.audit; // Column P
|
|||
|
|
worksheet.Cell(row, 17).Value = DiagsAPI.licenses.stream; // Column Q
|
|||
|
|
worksheet.Cell(row, 18).Value = sshData.tailscale; // Column R
|
|||
|
|
worksheet.Cell(row, 19).Value = "FALSE"; // Column S (WIP checkbox)
|
|||
|
|
|
|||
|
|
workbook.SaveAs(FilePath);
|
|||
|
|
return "OK";
|
|||
|
|
}
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return "Serial number not found";
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
|
|||
|
|
return "Failed to update spreadsheet data, please check manually: " + ex.Message;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string UpdateSpreadSheetVaxtor(string FilePath, VaxtorLic VaxtorLicResp, string serial, string model)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(FilePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return "Spreadsheet not found";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(FilePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
// Find next free row by checking column C
|
|||
|
|
int row = 2;
|
|||
|
|
while (!worksheet.Cell(row, 3).IsEmpty()) // Column C
|
|||
|
|
{
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// Write model, serial, date, and protectionKeyId to columns C–F
|
|||
|
|
worksheet.Cell(row, 3).Value = model; // Column C
|
|||
|
|
worksheet.Cell(row, 4).Value = serial; // Column D
|
|||
|
|
worksheet.Cell(row, 5).Value = DateTime.Now.ToString("dd/MM/yyyy"); // Column E
|
|||
|
|
worksheet.Cell(row, 6).Value = VaxtorLicResp.protectionKeyId; // Column F
|
|||
|
|
|
|||
|
|
// Write "PROD" to column H
|
|||
|
|
worksheet.Cell(row, 8).Value = "PROD"; // Column H
|
|||
|
|
|
|||
|
|
workbook.SaveAs(FilePath);
|
|||
|
|
return string.Empty;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error updating Vaxtor spreadsheet: " + ex.Message);
|
|||
|
|
return "Failed to update spreadsheet data, please check manually: " + ex.Message;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int CheckRMANum(string filePath, string serial, string model)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(filePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find RMA Control spreadsheet :(");
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(filePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
int row = 2; // Start from row 2
|
|||
|
|
while (!worksheet.Cell(row, 8).IsEmpty() || !worksheet.Cell(row, 9).IsEmpty()) // Columns H (8) and I (9)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sheetSerial = worksheet.Cell(row, 8).GetString();
|
|||
|
|
string sheetModel = worksheet.Cell(row, 9).GetString();
|
|||
|
|
|
|||
|
|
if (sheetSerial.Contains(serial) && sheetModel.Contains(model))
|
|||
|
|
{
|
|||
|
|
string rmaStr = worksheet.Cell(row, 1).GetString(); // Column A
|
|||
|
|
if (int.TryParse(rmaStr, out int rmaNumber))
|
|||
|
|
{
|
|||
|
|
return rmaNumber;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch { /* Safe to ignore bad row */ }
|
|||
|
|
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error reading RMA Control spreadsheet: " + ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return 0; // Default if not found
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int CheckSerialNumRow(string filePath, string serial)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(filePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(filePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
int row = 1;
|
|||
|
|
while (!worksheet.Cell(row, 2).IsEmpty()) // Column B = 2
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string cellSerial = worksheet.Cell(row, 2).GetString();
|
|||
|
|
if (cellSerial.Contains(serial))
|
|||
|
|
{
|
|||
|
|
return row;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch { /* Ignore malformed rows */ }
|
|||
|
|
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error checking serial number row: " + ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static int CheckNextFree(string filePath)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!File.Exists(filePath))
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using XLWorkbook workbook = new XLWorkbook(filePath);
|
|||
|
|
IXLWorksheet worksheet = workbook.Worksheets.First();
|
|||
|
|
|
|||
|
|
int row = 2; // Start from C2
|
|||
|
|
while (!worksheet.Cell(row, 3).IsEmpty()) // Column C = 3
|
|||
|
|
{
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return row;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
MainForm.Instance.AddToActionsList("Error checking next free row: " + ex.Message);
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//***** TESTING *****\\
|
|||
|
|
public static void TestAllExcelFunctions()
|
|||
|
|
{
|
|||
|
|
string filePath = @"C:\Users\BradleyRelyea\OneDrive - MAV Systems Ltd\MAV R&D - General\ModelsInfo.xlsx";
|
|||
|
|
// 1. Ensure spreadsheet exists and has valid starting data
|
|||
|
|
if (!File.Exists(filePath))
|
|||
|
|
{
|
|||
|
|
XLWorkbook workbook = new XLWorkbook();
|
|||
|
|
IXLWorksheet ws = workbook.Worksheets.Add("Sheet1");
|
|||
|
|
|
|||
|
|
// Pre-fill first row with dummy data for serial
|
|||
|
|
ws.Cell("A1").Value = "InitialModel";
|
|||
|
|
ws.Cell("B1").Value = "K1000"; // Starting serial
|
|||
|
|
ws.Cell("C2").Value = "Existing"; // Simulate used row in Vaxtor (for CheckNextFree)
|
|||
|
|
workbook.SaveAs(filePath);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 2. Run Pre-Test update
|
|||
|
|
Versions fakeVersion = new Versions
|
|||
|
|
{
|
|||
|
|
version = "1.6.4",
|
|||
|
|
revision = "bf16134",
|
|||
|
|
buildtime = "2025-07-21 10:00",
|
|||
|
|
proquint = "bexog-ludeg-zokud-huqer",
|
|||
|
|
MAC = "00:1A:2B:3C:4D:5E"
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
string preTestResult = Excel.UpdateSpreadSheetPreTest(filePath, fakeVersion, "Fake Cam", "TestModel");
|
|||
|
|
Debug.WriteLine("Pre-Test Result: " + preTestResult);
|
|||
|
|
|
|||
|
|
// 3. Run Final Test update
|
|||
|
|
Diags fakeDiags = new Diags
|
|||
|
|
{
|
|||
|
|
serialNumber = preTestResult, // Newly generated serial
|
|||
|
|
licenses = new Licenses
|
|||
|
|
{
|
|||
|
|
raptorKeyID = "9999999999",
|
|||
|
|
saf1 = true,
|
|||
|
|
audit = true,
|
|||
|
|
stream = true
|
|||
|
|
}
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
SSHData fakeSSH = new SSHData
|
|||
|
|
{
|
|||
|
|
packages = "\r\nlibvaxtorocr10 - 8.4.20-1\r\nvaxtorocrdatacpu3 - 8.4.20-1",
|
|||
|
|
tailscale = true
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
string finalTestResult = Excel.UpdateSpreadSheetFinalTest(filePath, fakeDiags, fakeSSH, 0);
|
|||
|
|
Debug.WriteLine("Final-Test Result: " + finalTestResult);
|
|||
|
|
|
|||
|
|
// // 4. Run Vaxtor update
|
|||
|
|
// var fakeVaxtorLic = new VaxtorLic
|
|||
|
|
// {
|
|||
|
|
// protectionKeyId = "VKID-555-ALPHA"
|
|||
|
|
// };
|
|||
|
|
|
|||
|
|
// string vaxtorResult = Excel.UpdateSpreadSheetVaxtor(filePath, fakeVaxtorLic, "VX-999", "VXModel");
|
|||
|
|
// Debug.WriteLine("Vaxtor Result: " + vaxtorResult);
|
|||
|
|
|
|||
|
|
// // 5. Check Serial Row
|
|||
|
|
// int serialRow = Excel.CheckSerialNumRow(filePath, preTestResult);
|
|||
|
|
// Debug.WriteLine("Serial row found at: " + serialRow);
|
|||
|
|
|
|||
|
|
// // 6. Check Next Free Vaxtor Row
|
|||
|
|
// int nextFree = Excel.CheckNextFree(filePath);
|
|||
|
|
// Debug.WriteLine("Next free row in Vaxtor sheet (Column C): " + nextFree);
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|