コード例 #1
0
        private void Click_EmailLastAssignedOrder(object sender, EventArgs e)
        {
            string            lastExcelOpenPathAssignedOrder = lastAssginedOrder.ExcelPath.Replace(".xlsx", Defs.EXCEL_FILE_POSTFIX);
            EmailConfirmation windows = new EmailConfirmation(lastExcelOpenPathAssignedOrder);

            windows.Show();
        }
コード例 #2
0
        public static void UpdateBOMExcelFile(string filePath, List <MaterialOrder> materialList)
        {
            string copyFilePath = filePath.ToLower().Replace(".xlsx", Defs.EXCEL_FILE_POSTFIX);


            bool retry = true;

            while (retry)
            {
                try
                {
                    Util.DeleteFileIfExist(copyFilePath);
                    File.Copy(filePath, copyFilePath, true);
                    retry = false;
                }
                catch (Exception exc)
                {
                    if (!(MessageBox.Show($"Verifique que el archivo no este siendo usado por otro usuario ({copyFilePath})", "Archivo siento utilizado",
                                          MessageBoxButtons.YesNo, MessageBoxIcon.Question,
                                          MessageBoxDefaultButton.Button1) == System.Windows.Forms.DialogResult.Yes))
                    {
                        retry = false;
                    }
                }
            }

            CurrentExcelOpenPath = copyFilePath;

            Excel.Application app = new Excel.Application();
            app.WorkbookBeforeClose += CloseUpdatedExcel;
            Excel.Workbook workbook  = app.Workbooks.Open(copyFilePath, UpdateLinks: 3);
            List <string>  errorList = new List <string>();

            foreach (Excel._Worksheet sheet in workbook.Sheets)
            {
                if (sheet.Visible == Excel.XlSheetVisibility.xlSheetHidden)
                {
                    continue;
                }
                Excel.Range   range         = sheet.UsedRange;
                int           rowCount      = range.Rows.Count;
                int           colCount      = range.Columns.Count;
                List <Column> headercolumns = new List <Column>(colCount);
                string        sheetName     = sheet.Name.ToUpper();
                if (sheetName == Defs.EXCLUDED_SHEET_COSTOS || sheetName == Defs.EXCLUDED_SHEET_LEYENDA)
                {
                    continue;
                }
                Excel.Range rows = range.Rows;
                int         rowToleranceIndex = 0;
                for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
                {
                    if (headercolumns.Count < MIN_COLUMNS_BOM_AMOUNT) //Get Columns names
                    {
                        colCount = colCount > NORMAL_COLUMN_AMOUNT ? NORMAL_COLUMN_AMOUNT : colCount;
                        for (int colIndex = 1; colIndex <= colCount; colIndex++)
                        {
                            if (range.Cells[rowIndex, colIndex] != null && range.Cells[rowIndex, colIndex].Value2 != null)
                            {
                                var    colNameTemp = range.Cells[rowIndex, colIndex].Value2.ToString();
                                string columnName  = Util.ConvertDynamicToString(colNameTemp);
                                Column column      = new Column(columnName, colIndex);
                                headercolumns.Add(column);
                            }
                        }
                        if (rowIndex == HEADER_COLUMN_TOLERANCE)
                        {
                            break;
                        }
                    }
                    else
                    {
                        Column colMaterialCode = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_MATERIAL_CODE));
                        Column colAmount       = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_AMOUNT));

                        if (colMaterialCode == null)
                        {
                            MessageBox.Show($"Formato no reconocido en {sheetName} en la columna de: Clave del material");
                            break;
                        }

                        var dynamicCode   = range.Cells[rowIndex, colMaterialCode.Index].Value;
                        var dynamicAmount = range.Cells[rowIndex, colAmount.Index].Value;

                        string materialCode = Util.ConvertDynamicToString(dynamicCode);
                        materialCode = Util.NormalizeString(materialCode);
                        if (Util.IsEmptyString(materialCode))
                        {
                            rowToleranceIndex++;
                            if (EMPTINESS_ROW_TOLERANCE < rowToleranceIndex)
                            {
                                break;
                            }
                        }
                        MaterialOrder material = materialList.Find(item => item.Code == materialCode);
                        if (material == null)
                        {
                            continue;
                        }
                        double desiredAmount = Util.ConvertDynamicToDouble(dynamicAmount);;

                        //Add Valid material to list
                        if (!Util.IsEmptyString(materialCode) && desiredAmount != 0)
                        {
                            Column colStatus = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_STATUS));
                            Column colUnit   = headercolumns.Find(col => Util.IsLike(col.Name, Defs.COL_UNIT));
                            DeleteOverRowFromExcel(rows);

                            double leftAmount = Math.Abs(desiredAmount - material.ChosenAmount);

                            int currentRowIndex  = rowIndex;
                            int previousRowIndex = currentRowIndex - 1;
                            int nextRowIndex     = currentRowIndex + 1;

                            //Add row upon
                            try
                            {
                                rows[currentRowIndex].Insert(Excel.XlInsertShiftDirection.xlShiftDown, rows[currentRowIndex]);
                            }
                            catch (Exception e)
                            {
                                errorList.Add($"En la hoja: {sheetName} hubo un problema, posiblemente quedó incompleta, por favor revisarla");
                                workbook.Save();
                                continue;
                            }

                            Excel.Range currentLine = (Excel.Range)rows[nextRowIndex];
                            Excel.Range newLine     = (Excel.Range)rows[currentRowIndex];
                            currentLine.Copy(newLine);
                            range.Cells[currentRowIndex, colStatus.Index].Value = Defs.STATE_ORIGINAL;

                            currentRowIndex++;
                            previousRowIndex = currentRowIndex - 1;
                            nextRowIndex     = currentRowIndex + 1;

                            if (desiredAmount > material.ChosenAmount)
                            {
                                range.Cells[currentRowIndex, colAmount.Index].Value = material.ChosenAmount;


                                Excel.Range currentLineTemp = (Excel.Range)rows[currentRowIndex];
                                //Add row below
                                try
                                {
                                    rows[nextRowIndex].Insert(Excel.XlInsertShiftDirection.xlShiftDown, rows[currentRowIndex]);
                                }
                                catch (Exception e)
                                {
                                    errorList.Add($"En la hoja: {sheetName} hubo un problema, posiblemente quedó incompleta, por favor revisarla");
                                    workbook.Save();
                                    continue;
                                }


                                Excel.Range newLineTemp = (Excel.Range)rows[nextRowIndex];
                                currentLineTemp.Copy(newLineTemp);


                                range.Cells[nextRowIndex, colAmount.Index].Value = leftAmount;
                                range.Cells[nextRowIndex, colStatus.Index].Value = Defs.STATE_PENDING;
                                rowIndex++;
                            }
                            else
                            {
                                range.Cells[currentRowIndex, colAmount.Index].Value = material.ChosenAmount;
                            }
                            rows[currentRowIndex].Interior.Color = Color.Green;
                            range.Cells[currentRowIndex, colStatus.Index].Value        = Defs.STATE_RESERVED;
                            range.Cells[currentRowIndex, colUnit.Index].Interior.Color = Color.Red;
                            rows[previousRowIndex].Interior.Color             = Color.White;
                            range.Cells[currentRowIndex, colUnit.Index].Value = material.Unit;

                            rowIndex++;
                            materialList.Remove(material);
                        }
                    }
                }
                Marshal.ReleaseComObject(range);
                Marshal.ReleaseComObject(sheet);
            }

            if (errorList.Count != 0)
            {
                Util.ShowMessage(AlarmType.WARNING, errorList);
            }
            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();


            //close and release
            workbook.Save();
            app.Visible = true;

            string excelName = workbook.Name;
            bool   condition = !("Sheet1" == excelName);

            if (condition)
            {
                EmailConfirmation windows = new EmailConfirmation(CurrentExcelOpenPath);
                windows.Show();
            }

            Marshal.ReleaseComObject(workbook);

            //quit and release
            //app.Quit();
            Marshal.ReleaseComObject(app);
        }