private void btnValidateDataNorm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
try
{
if (dtValidityDate == null)
{
return;
}
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
Range range = worksheet.GetDataRange();
Range SelectedRange = worksheet.SelectedCell;
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Validieren...");
int CCount = 11;
int rCount = range.RowCount;
DataTable dt = new DataTable();
for (int j = 0; j < CCount; j++)
{
if (Convert.ToString(worksheet.Rows[0][j].Value) == "Preis")
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(decimal));
}
else
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(string));
}
}
DataRow dr = null;
for (int i = 1; i < rCount; i++)
{
dr = dt.NewRow();
for (int j = 0; j < CCount; j++)
{
try
{
CellValue v = worksheet.Rows[i][j].Value;
dr[j] = Convert.ToString(v);
}
catch (Exception) { }
}
dt.Rows.Add(dr);
}
if (dt.Rows.Count > 0)
{
BSupplier ObjBSupplier = new BSupplier();
DataSet dsResults = ObjBSupplier.ValidateDataNorm(SupplierID, dtValidityDate, dt);
if (dsResults.Tables[0].Rows.Count > 0)
{
IWorkbook workbook = spreadsheetControl1.Document;
bool isVLExists = false;
bool isOLExists = false;
bool isILExists = false;
foreach (Worksheet ws in workbook.Sheets)
{
if (ws.Name == "Blatt im Original")
{
ws.ConditionalFormattings.Clear();
isOLExists = true;
}
else if (ws.Name == "Log mit Validierungsangaben")
{
ws.ConditionalFormattings.Clear();
isVLExists = true;
}
else if (ws.Name == "Import des Logs")
{
ws.ConditionalFormattings.Clear();
isILExists = true;
}
}
if (isOLExists)
{
workbook.Worksheets.Add("Sheet1");
workbook.Worksheets.Remove(workbook.Worksheets["Blatt im Original"]);
}
if (isVLExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Log mit Validierungsangaben"]);
}
if (isILExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Import des Logs"]);
}
if (dsResults.Tables.Count > 1)
{
if (dsResults.Tables[1].Rows.Count > 0)
{
btnSaveandConfirm.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
}
else
{
btnSaveandConfirm.Visibility = DevExpress.XtraBars.BarItemVisibility.Always;
}
}
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Blatt im Original";
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = sheet.Tables.Add(dsResults.Tables[0], 0, 0, options);
table.Columns[0].Name = "Artikel_Id";
table.Columns[1].Name = "Artikelnummer";
table.Columns[2].Name = "Kurztext1";
table.Columns[3].Name = "Kurztext2";
table.Columns[4].Name = "WG";
table.Columns[5].Name = "WA";
table.Columns[6].Name = "WI";
table.Columns[7].Name = "A";
table.Columns[8].Name = "B";
table.Columns[9].Name = "L";
table.Columns[10].Name = "Preis";
table.Columns[11].Name = "New/Update";
table.Columns[12].Name = "OldPreis";
table.Columns[13].Name = "Validity Date";
table.Columns[14].Name = "Comments";
sheet.DataValidations.Add(sheet["E:K"], DataValidationType.Custom, "=AND(ISNUMBER(E1))");
sheet.Range["N:N"].NumberFormat = "mmm/yyyy";
}
finally
{
workbook.EndUpdate();
}
if (dsResults.Tables[1].Rows.Count > 0)
{
workbook.Worksheets.Add("Log mit Validierungsangaben");
Worksheet Vsheet = workbook.Worksheets["Log mit Validierungsangaben"];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = Vsheet.Tables.Add(dsResults.Tables[1], 0, 0, options);
table.Columns[0].Name = "Art_Id";
table.Columns[1].Name = "Comments";
}
finally
{
workbook.EndUpdate();
}
sheet.Columns.AutoFit(0, 14);
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets["Blatt im Original"];
Range LogRange = Vsheet.GetDataRange();
ApplyNotExistsArticleFormatingforValidationLog(sheet, LogRange, Vsheet);
string stRange = SelectedRange.GetReferenceA1();
spreadsheetControl1.SelectedCell = sheet.Cells[stRange];
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Angaben wurden validiert. Bitte prüfen Sie die Hinweise zur Validierung.");
}
else
{
string stRange = SelectedRange.GetReferenceA1();
spreadsheetControl1.SelectedCell = sheet.Cells[stRange];
sheet.Columns.AutoFit(0, 14);
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Angaben wurden validiert. Sämtliche Angaben sind korrekt und vollständig.");
}
}
}
}
catch (Exception ex)
{
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
throw ex;
}
}