public static DataTable ToDataTable(this ExcelDataSource excelDataSource)
{
IList list = ((IListSource)excelDataSource).GetList();
DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list;
List <PropertyDescriptor> properties = dataView.Columns.ToList <PropertyDescriptor>();
DataTable table = new DataTable();
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor prop = properties[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[properties.Count];
foreach (DevExpress.DataAccess.Native.Excel.ViewRow item in list)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = properties[i].GetValue(item);
}
table.Rows.Add(values);
}
return(table);
}
void ImportData(string ImportFileName)
{
if (string.IsNullOrWhiteSpace(ImportFileName))
{
Alit.WinformControls.MessageBox.Show("Please select excel file.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!System.IO.File.Exists(ImportFileName))
{
Alit.WinformControls.MessageBox.Show("Selected file doesn't exists.", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string extension = System.IO.Path.GetExtension(ImportFileName).ToUpper();
if (extension != ".XLS" && extension != ".XLSX")
{
if (Alit.WinformControls.MessageBox.Show(this, "Selected file is not seems to be valid excel file. Do you want to continue ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
//--
tAApprovalImportViewModelBindingSource.Clear();
if (extension.ToUpper() != ".XLS" && extension.ToUpper() != ".XLSX")
{
if (Alit.WinformControls.MessageBox.Show(this, "Selected file is seems to be Not valid excel file. Do you want to continue ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
{
return;
}
}
string conStr = string.Empty;
if (extension == ".xlsx")
{
conStr = string.Format(Excel07ConString, ImportFileName, "Yes");
}
else
{
conStr = string.Format(Excel03ConString, ImportFileName, "Yes");
}
ShowWaitForm();
try
{
DevExpress.DataAccess.Excel.ExcelDataSource myExcelSource = new DevExpress.DataAccess.Excel.ExcelDataSource();
myExcelSource.FileName = ImportFileName;
DevExpress.DataAccess.Excel.ExcelWorksheetSettings worksheetSettings = new DevExpress.DataAccess.Excel.ExcelWorksheetSettings(GetWorkSheetNameByIndex(ImportFileName, 0));
myExcelSource.SourceOptions = new DevExpress.DataAccess.Excel.ExcelSourceOptions(worksheetSettings);
myExcelSource.SourceOptions.SkipEmptyRows = true;
myExcelSource.SourceOptions.UseFirstRowAsHeader = true;
myExcelSource.Fill();
System.Collections.IList list = ((IListSource)myExcelSource).GetList();
DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list;
try
{
#region Decoding Data
for (int ri = 0; ri < dataView.Count; ri++)
{
DevExpress.DataAccess.Native.Excel.ViewRow dsRow = (DevExpress.DataAccess.Native.Excel.ViewRow)dataView[ri];
TAApprovalImportViewModel NewRecord = new TAApprovalImportViewModel()
{
RowNo = ri + 1
};
//--
object importvalue = null;
// Transaction Date (Approval Date)
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionDate].GetValue(dsRow);
if (importvalue != null)
{
DateTime dtvalue;
if (DateTime.TryParse(importvalue.ToString(), out dtvalue))
{
NewRecord.ApprovalDate = dtvalue;
}
else
{
NewRecord.ApprovalDateError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalDateError = "No value";
}
// TransactionNoPrefix
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionNoPrefix].GetValue(dsRow);
if (importvalue != null)
{
NewRecord.ApprovalNoPrefixName = importvalue.ToString();
string prefixToCheck = NewRecord.ApprovalNoPrefixName.Trim().ToUpper();
var Prefix = dsApprovalNoPrefix.FirstOrDefault(r => r.TAApprovalNoPrefixName.ToUpper() == prefixToCheck);
if (Prefix != null)
{
NewRecord.ApprovalNoPrefixID = Prefix.TAApprovalNoPrefixID;
}
else
{
NewRecord.ApprovalNoPrefixNameError = importvalue.ToString() + " : not found.";
}
}
else
{
NewRecord.ApprovalNoPrefixNameError = "No value.";
}
// TransactionNo
importvalue = dataView.Columns[ImportFileColumnIndex_TransactionNo].GetValue(dsRow);
if (importvalue != null)
{
int value = 0;
if (int.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovalNo = value;
//--
if (NewRecord.ApprovalNoPrefixID != null)
{
if (DALObj.IsDuplicateRecord(NewRecord.ApprovalNoPrefixID.Value, NewRecord.ApprovalNo.Value, 0))
{
NewRecord.ApprovalNo = null;
NewRecord.ApprovalNoError = importvalue.ToString() + " : duplicate value can not accepted.";
}
}
}
else
{
NewRecord.ApprovalNoError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalNoError = "No value";
}
// EmployeeNo
importvalue = dataView.Columns[ImportFileColumnIndex_EmployeeNo].GetValue(dsRow);
if (importvalue != null)
{
int value = 0;
if (int.TryParse(importvalue.ToString(), out value))
{
NewRecord.EmployeeNo = value;
var Employee = dsEmployee.FirstOrDefault(r => r.EmployeeNo == NewRecord.EmployeeNo);
if (Employee != null)
{
NewRecord.EmployeeID = Employee.EmployeeID;
NewRecord.EmployeeNoPrefixName = Employee.EmployeeNoPrefix;
NewRecord.EmployeeName = Employee.EmployeeName;
}
else
{
NewRecord.EmployeeNameError = importvalue.ToString() + " : not found";
}
}
else
{
NewRecord.EmployeeNameError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.EmployeeNameError = "No value";
}
// Approved Date
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedDate].GetValue(dsRow);
if (importvalue != null)
{
DateTime value;
if (DateTime.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovedDate = value;
}
else
{
NewRecord.ApprovedDateError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovedDateError = "No value";
}
// ApprovedHours
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedHours].GetValue(dsRow);
if (importvalue != null)
{
decimal value;
if (decimal.TryParse(importvalue.ToString(), out value))
{
NewRecord.ApprovedHours = value;
}
else
{
NewRecord.ApprovedHoursError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovedHoursError = "No value";
}
// ApprovedFor
importvalue = dataView.Columns[ImportFileColumnIndex_ApprovedFor].GetValue(dsRow);
if (importvalue != null)
{
int value;
if (int.TryParse(importvalue.ToString(), out value) && value >= 1 && value <= 4)
{
NewRecord.ApprovalTypeID = (eTAApprovalType)(value - 1);
}
else
{
NewRecord.ApprovalTypeIDError = importvalue.ToString() + " : invalid value";
}
}
else
{
NewRecord.ApprovalTypeIDError = "No value";
}
// Remark
importvalue = dataView.Columns[ImportFileColumnIndex_Remark].GetValue(dsRow);
if (importvalue != null)
{
NewRecord.Remark = importvalue.ToString();
}
//// DocumandLocation
//importvalue = dataView.Columns[ImportFileColumnIndex_DocumandLocation].GetValue(dsRow);
//if (importvalue != null)
//{
// NewRecord.DocumentLocation = importvalue.ToString();
//}
//// Check - Valid for Import
//if (NewRecord.ApprovalDate != null && NewRecord.ApprovalNoPrefixID != null && NewRecord.ApprovalNo != null &&
// NewRecord.ApprovalTypeID != null && NewRecord.EmployeeID != null &&
// NewRecord.ApprovedDate != null && NewRecord.ApprovedHours != null)
//{
// NewRecord.ValidForImport = true;
//}
//else
//{
// NewRecord.ValidForImport = false;
//}
//--
tAApprovalImportViewModelBindingSource.Add(NewRecord);
}
#endregion
}
catch (Exception ex)
{
ex = DAL.CommonFunctions.GetFinalError(ex);
CloseWaitForm();
Alit.WinformControls.MessageBox.Show("Error while trying to decode excel file.\r\n" + (ex != null ? ex.Message : ""), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
ex = DAL.CommonFunctions.GetFinalError(ex);
CloseWaitForm();
Alit.WinformControls.MessageBox.Show("Error while trying to connect excel file.\r\n" + (ex != null ? ex.Message : ""), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
CloseWaitForm();
}
private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
//XtraReport1 xtraReport1 = new XtraReport1();
Report2_Sayfa report2_Sayfa = new Report2_Sayfa();
excelDataSource2_Sayfa.Fill();
//excelDataSource1.SourceOptions.SkipEmptyRows = false;
////excelDataSource1.SourceOptions.SkipHiddenColumns = false;
////excelDataSource1.SourceOptions.SkipHiddenRows = false;
excelDataSource2_Sayfa.SourceOptions.SkipEmptyRows = false;
excelDataSource2_Sayfa.SourceOptions.UseFirstRowAsHeader = false;
//List<string> values1 = new List<string>();
//List<string> values2 = new List<string>();
//int j;
DevExpress.DataAccess.Native.Excel.DataView dv = ((IListSource)excelDataSource2_Sayfa).GetList() as DevExpress.DataAccess.Native.Excel.DataView;
//for (int i = 0; i < dv.Count; i++)
//{
// j = 0;
// DevExpress.DataAccess.Native.Excel.ViewRow row = dv[i] as DevExpress.DataAccess.Native.Excel.ViewRow;
// foreach (DevExpress.DataAccess.Native.Excel.ViewColumn col in dv.Columns)
// {
// object val = col.GetValue(row);
// if (val != null)
// {
// values2.Add($"col[{i}] row[{j}] value :{ val.ToString()}");
// //values1.Add($"rowindex : {row.Index }] / {col.Name}: dv[{i}] : {j} : {val.ToString()}");
// }
// else
// {
// //values2.Add($"col[{j}] row[{i}] value : empty");
// // col.SetValue();
// }
// j++;
// }
//}
// var value1 = dv.Columns[2].GetValue(dv[5]);
//xtraReport1.PaperKind = System.Drawing.Printing.PaperKind.A4;
//(xtraReport1.FindControl("xrTableCellAciklama", false) as XRTableCell ).Text = dv.Columns[1].GetValue(dv[38]).ToString();
//xtraReport1.AllControls<XRLabel>().First(x => x.Name == "xrLabel1").Text = dv.Columns[16].GetValue(dv[0]).ToString(); ;//.Text = "deneme";
//xtraReport1.AllControls<XRLabel>().First(x => x.Name == "xrBaslik").Text = dv.Columns[0].Name.ToString(); ;//.Text = "deneme";
//xtraReport1.ShowPreview();
(report2_Sayfa.FindControl("xrLabelBaslık", false) as XRLabel).Text = dv.Columns[0].GetValue(dv[0]).ToString();
(report2_Sayfa.FindControl("xrLabelUygulamaYili", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[13]).ToString();
(report2_Sayfa.FindControl("xrLabelYukleniciAdi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[14]).ToString();
(report2_Sayfa.FindControl("xrLabelSozlesmeBedeli", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[15]).ToString();
(report2_Sayfa.FindControl("xrLabelİhaleTarihi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[16]).ToString();
(report2_Sayfa.FindControl("xrLabelSozlesmeTarihi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[17]).ToString();
(report2_Sayfa.FindControl("xrLabelYerTeslimTarihi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[18]).ToString();
(report2_Sayfa.FindControl("xrLabelİsinSuresi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[19]).ToString();
(report2_Sayfa.FindControl("xrLabelSozlesmeİsinBitis", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[20]).ToString();
(report2_Sayfa.FindControl("xrLabelSureUzatimİsBitim", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[21]).ToString();
(report2_Sayfa.FindControl("xrYiliOdenegi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[22]).ToString();
(report2_Sayfa.FindControl("xrLabelYiliOdenegi", false) as XRLabel).Text = dv.Columns[1].GetValue(dv[22]).ToString();
(report2_Sayfa.FindControl("xrLabelKontrolMuh", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[23]).ToString();
(report2_Sayfa.FindControl("xrLabelSantiyeSefi", false) as XRLabel).Text = dv.Columns[7].GetValue(dv[24]).ToString();
report2_Sayfa.ShowPreview();
}
