public void CheckOut(int Barcode)
{
if (File.Exists(file) == false)
{
MessageBox.Show("Database can not be found");
}
if (File.Exists(file) == true)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet EquipmentSheet;
Excel.Worksheet EquipmentInUseSheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range xlRange;
Excel.Range currentFind = null;
xlWorkBook = xlApp.Workbooks.Open(file);
EquipmentSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
EquipmentInUseSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
Excel.Range barcodeRange = EquipmentSheet.get_Range("C:C");
currentFind = barcodeRange.Find(Barcode, misValue,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
misValue, misValue);
if (currentFind != null)
{
var add = currentFind.Address[true, true, Excel.XlReferenceStyle.xlA1, false, null];
int x = currentFind.Row;
if (EquipmentSheet.Cells[x, 4].value == "Available")
{
//MessageBox.Show($"Barcode found at: {add}");
xlRange = (Excel.Range)EquipmentInUseSheet.Cells[EquipmentSheet.Rows.Count, 1];
long lastRow = (long)xlRange.get_End(Excel.XlDirection.xlUp).Row;
long newRow = lastRow + 1;
//MessageBox.Show($"New Row: {newRow}");
EquipmentSheet.get_Range($"A{x}", add).Copy(EquipmentInUseSheet.get_Range($"C{newRow}", $"E{newRow}"));
EquipmentSheet.Cells[x, 4].Value = "In Use";
EquipmentInUseSheet.Cells[newRow, 1] = Details.Employee.Name;
EquipmentInUseSheet.Cells[newRow, 2] = Details.Employee.Team;
EquipmentInUseSheet.Cells[newRow, 6] = DateTime.Now;
}
else if (EquipmentSheet.Cells[x, 4].value == "In Use")
{
MessageBox.Show($"The {EquipmentSheet.Cells[x, 1].value} with serial number {EquipmentSheet.Cells[x, 3].value} is already in use.");
}
}
if (currentFind == null)
{
MessageBox.Show($"The barcode can not be found.\r\nThe barcode is {0}", Barcode.ToString());
}
xlWorkBook.Close(true);
xlApp.Quit();
Marshal.ReleaseComObject(EquipmentSheet);
Marshal.ReleaseComObject(EquipmentInUseSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
}
public void CheckIn(int Barcode)
{
if (File.Exists(file) == false)
{
MessageBox.Show("Database can not be found");
}
if (File.Exists(file) == true)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet EquipmentSheet;
Excel.Worksheet EquipmentInUseSheet;
Excel.Worksheet LogWorksheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range xlRange;
Excel.Range currentFind = null;
Excel.Range currentFind2 = null;
Excel.Range barcodeRange = null;
Excel.Range barcodeRange2 = null;
xlWorkBook = xlApp.Workbooks.Open(file);
EquipmentSheet = xlWorkBook.Worksheets.get_Item(2);
EquipmentInUseSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
LogWorksheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4);
barcodeRange = EquipmentInUseSheet.get_Range("E:E");
currentFind = barcodeRange.Find(Barcode, misValue,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
misValue, misValue);
if (currentFind != null)
{
var add = currentFind.Address[true, true, Excel.XlReferenceStyle.xlA1, false, null];
//MessageBox.Show($"Barcode found at: {add}");
xlRange = (Excel.Range)LogWorksheet.Cells[EquipmentInUseSheet.Rows.Count, 1];
long lastRow = xlRange.get_End(Excel.XlDirection.xlUp).Row;
long newRow = lastRow + 1;
//MessageBox.Show($"New Row: {newRow}");
Excel.Range row = EquipmentInUseSheet.get_Range(add, misValue).EntireRow;
Excel.Range nextRow = LogWorksheet.get_Range($"A{newRow}", misValue).EntireRow;
nextRow.Value = row.Value;
LogWorksheet.Cells[newRow, 7] = DateTime.Now;
row.EntireRow.Delete(Excel.XlDirection.xlUp);
barcodeRange2 = EquipmentSheet.get_Range("C:C");
currentFind2 = barcodeRange2.Find(Barcode, misValue,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
misValue, misValue);
if (currentFind2 != null)
{
EquipmentSheet.Cells[currentFind2.Row, 4].Value = "Available";
}
if (currentFind == null)
{
MessageBox.Show($"The barcode can not be found in Equipment List.\r\nThe barcode is {0}", Barcode.ToString());
}
}
if (currentFind == null)
{
MessageBox.Show($"The barcode can not be found in In use list.\r\nThe barcode is {0}", Barcode.ToString());
}
xlWorkBook.Close(true);
xlApp.Quit();
Marshal.ReleaseComObject(EquipmentSheet);
Marshal.ReleaseComObject(EquipmentInUseSheet);
Marshal.ReleaseComObject(LogWorksheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
}