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