private void btnSaveandConfirm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
if (SupplierID == null)
{
return;
}
if (dtValidityDate == null)
{
return;
}
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
Range range = worksheet.GetDataRange();
int CCount = 11;
int rCount = range.RowCount;
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Importieren...");
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();
DataTable dtResults = ObjBSupplier.ImportDataNorm(SupplierID, dtValidityDate, dt);
if (dtResults.Rows.Count > 0)
{
IWorkbook workbook = spreadsheetControl1.Document;
bool isILExists = false;
bool isVLExists = false;
foreach (Worksheet ws in workbook.Sheets)
{
if (ws.Name == "Blatt im Original")
{
ws.ConditionalFormattings.Clear();
}
else if (ws.Name == "Import des Logs")
{
ws.ConditionalFormattings.Clear();
isILExists = true;
}
else if (ws.Name == "Log mit Validierungsangaben")
{
ws.ConditionalFormattings.Clear();
isVLExists = true;
}
}
if (isILExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Import des Logs"]);
}
if (isVLExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Log mit Validierungsangaben"]);
}
workbook.Worksheets.Add("Import des Logs");
Worksheet sheet = workbook.Worksheets["Import des Logs"];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = sheet.Tables.Add(dtResults, 0, 0, options);
table.Columns[0].Name = "Art_ID";
table.Columns[1].Name = "WG";
table.Columns[2].Name = "WA";
table.Columns[3].Name = "WI";
table.Columns[4].Name = "A";
table.Columns[5].Name = "B";
table.Columns[6].Name = "L";
table.Columns[7].Name = "Error";
}
finally
{
workbook.EndUpdate();
}
Range LogRange = sheet.GetUsedRange();
ApplyNotExistsArticleFormatingforImportLog(worksheet, LogRange, sheet);
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets["Blatt im Original"];
}
}
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Daten wurden erfolgreiche importiert und gespeichert.");
}
catch (Exception ex)
{
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show(ex.Message);
}
}