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();
}
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);
}