コード例 #1
0
        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);
            }
        }
コード例 #2
0
        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);
            }
        }