static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx"); DataTable dataTable = new DataTable(); // Depending on the format of the input file, you need to change this: dataTable.Columns.Add("FirstColumn", typeof(string)); dataTable.Columns.Add("SecondColumn", typeof(string)); // Select the first worksheet from the file. ExcelWorksheet ws = ef.Worksheets[0]; ExtractToDataTableOptions options = new ExtractToDataTableOptions(0, 0, 10); options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow; options.ExcelCellToDataTableCellConverting += (sender, e) => { if (!e.IsDataTableValueValid) { // GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; // someone would expect the number 12.4 as "12.4" and someone else as "12,4". e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString(); e.Action = ExtractDataEventAction.Continue; } }; // Extract the data from an Excel worksheet to the DataTable. // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears. ws.ExtractToDataTable(dataTable, options); // Write DataTable content StringBuilder sb = new StringBuilder(); sb.AppendLine("DataTable content:"); foreach (DataRow row in dataTable.Rows) { sb.AppendFormat("{0} {1}", row[0], row[1]); sb.AppendLine(); } Console.WriteLine(sb.ToString()); }
public CriteriaValidator() { InitializeComponent(); // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook1 = ExcelFile.Load("NAVIS OUT.csv"); var dataTable1 = new DataTable(); // Depending on the format of the input file, you need to change this: dataTable1.Columns.Add("Material", typeof(string)); dataTable1.Columns.Add("GUID", typeof(string)); dataTable1.Columns.Add("ELEMENT ID", typeof(string)); dataTable1.Columns.Add("NAME", typeof(string)); dataTable1.Columns.Add("GLOABALID", typeof(string)); dataTable1.Columns.Add("START DATE", typeof(string)); dataTable1.Columns.Add("END DATE", typeof(string)); dataTable1.Columns.Add("DURATION", typeof(string)); // Select the first worksheet from the file. var worksheet1 = workbook1.Worksheets[0]; var options = new ExtractToDataTableOptions(0, 0, 10000); options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow; options.ExcelCellToDataTableCellConverting += (sender, e) => { if (!e.IsDataTableValueValid) { // GemBox.Spreadsheet doesn't automatically convert numbers to strings in ExtractToDataTable() method because of culture issues; // someone would expect the number 12.4 as "12.4" and someone else as "12,4". e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString(); e.Action = ExtractDataEventAction.Continue; } }; // Extract the data from an Excel worksheet to the DataTable. // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears. worksheet1.ExtractToDataTable(dataTable1, options); //-----------------Again var workbook2 = ExcelFile.Load("REVIT output.xlsx"); var dataTable2 = new DataTable(); // Depending on the format of the input file, you need to change this: dataTable2.Columns.Add("Count", typeof(string)); dataTable2.Columns.Add("ELEMENT ID", typeof(string)); dataTable2.Columns.Add("Description", typeof(string)); dataTable2.Columns.Add("Family", typeof(string)); dataTable2.Columns.Add("Family and Type", typeof(string)); dataTable2.Columns.Add("Material: Area", typeof(string)); dataTable2.Columns.Add("Material: Volume", typeof(string)); dataTable2.Columns.Add("Material: Name", typeof(string)); // Select the first worksheet from the file. var worksheet2 = workbook2.Worksheets[0]; // Extract the data from an Excel worksheet to the DataTable. // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears. worksheet2.ExtractToDataTable(dataTable2, options); dataGridView1.DataSource = dataTable2; int i = 0; foreach (DataRow dt1_Row in dataTable1.Rows) { string dt1_EID = dt1_Row["ELEMENT ID"].ToString(); if (dt1_EID == "ELEMENT ID") { continue; } foreach (DataRow dt2_Row in dataTable2.Rows) { string dt2_EID = dt2_Row["ELEMENT ID"].ToString(); if (dt2_EID == "ELEMENT ID") { continue; } if (dt1_EID == dt2_EID) { dgv_DataRecords.Rows.Add(); dgv_DataRecords.Rows[i].Cells["ElementID"].Value = dt1_Row["ELEMENT ID"].ToString(); dgv_DataRecords.Rows[i].Cells["Name"].Value = dt1_Row["NAME"].ToString(); dgv_DataRecords.Rows[i].Cells["FamilyandType"].Value = dt2_Row["Family and Type"].ToString(); dgv_DataRecords.Rows[i].Cells["Area"].Value = dt2_Row["Material: Area"].ToString(); dgv_DataRecords.Rows[i].Cells["Volume"].Value = dt2_Row["Material: Volume"].ToString(); dgv_DataRecords.Rows[i].Cells["StartDate"].Value = dt1_Row["START DATE"].ToString(); dgv_DataRecords.Rows[i].Cells["EndDate"].Value = dt1_Row["END DATE"].ToString(); i++; } } } }
public void getExcelData() { dt.Columns.Clear(); dt.Clear(); SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = ExcelFile.Load("data/" + sheetName + ".xls"); dt.Columns.Add("ID", typeof(string)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("MinLvl", typeof(string)); dt.Columns.Add("MinDmg", typeof(string)); dt.Columns.Add("MaxDmg", typeof(string)); dt.Columns.Add("Tier", typeof(string)); dt.Columns.Add("Cost", typeof(string)); dt.Columns.Add("HealthRegen", typeof(string)); dt.Columns.Add("ManaRegen", typeof(string)); dt.Columns.Add("SpellDamage", typeof(string)); dt.Columns.Add("LifeSteal", typeof(string)); dt.Columns.Add("Mana Steal", typeof(string)); dt.Columns.Add("XPBonus", typeof(string)); dt.Columns.Add("LootBonus", typeof(string)); dt.Columns.Add("Location", typeof(string)); ExcelWorksheet ws = ef.Worksheets[0]; ExtractToDataTableOptions options = new ExtractToDataTableOptions(0, 0, 50); options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow; options.ExcelCellToDataTableCellConverting += (sender, e) => { if (!e.IsDataTableValueValid) { e.DataTableValue = e.ExcelCellValue == null ? null : e.ExcelCellValue.ToString(); e.Action = ExtractDataEventAction.Continue; } }; ws.ExtractToDataTable(dt, options); }
public async Task <string> importToExcel(string currentTime, string path) { var hoten = ""; var CMND = ""; try { string date = DateTime.Today.ToString("dd-MM-yyyy"); SpreadsheetInfo.SetLicense("ELAP-G41W-CZA2-XNNC"); var path1 = Path.Combine(_env.WebRootPath + "\\" + date + "\\" + currentTime + "\\" + path); var workbook = ExcelFile.Load(path1); var dataTable = new DataTable(); dataTable.Columns.Add("Mã ứng viên", typeof(string)); dataTable.Columns.Add("Họ và tên", typeof(string)); dataTable.Columns.Add("Vị trí ứng tuyển", typeof(string)); dataTable.Columns.Add("Kênh tuyển dụng", typeof(string)); dataTable.Columns.Add("Giới tính", typeof(string)); dataTable.Columns.Add("Ngày sinh", typeof(string)); dataTable.Columns.Add("Số CMND", typeof(string)); dataTable.Columns.Add("Ngày cấp", typeof(string)); dataTable.Columns.Add("Nơi cấp", typeof(string)); dataTable.Columns.Add("Tỉnh/Thành phố", typeof(string)); dataTable.Columns.Add("Tình trạng hôn nhân", typeof(string)); dataTable.Columns.Add("Trình độ đào tạo", typeof(string)); dataTable.Columns.Add("Trình độ văn hóa", typeof(string)); dataTable.Columns.Add("Nơi đào tạo", typeof(string)); dataTable.Columns.Add("Khoa", typeof(string)); dataTable.Columns.Add("Chuyên ngành", typeof(string)); dataTable.Columns.Add("Xếp loại", typeof(string)); dataTable.Columns.Add("Năm tốt nghiệp", typeof(string)); dataTable.Columns.Add("Trạng thái", typeof(string)); dataTable.Columns.Add("Tiến độ tuyển dụng", typeof(string)); dataTable.Columns.Add("Tệp đính kèm", typeof(string)); dataTable.Columns.Add("Record status", typeof(string)); dataTable.Columns.Add("Marker id", typeof(string)); dataTable.Columns.Add("Auth status", typeof(string)); dataTable.Columns.Add("Checker id", typeof(string)); dataTable.Columns.Add("Approve dt", typeof(string)); dataTable.Columns.Add("Điện thoại", typeof(string)); dataTable.Columns.Add("E-mail", typeof(string)); dataTable.Columns.Add("Địa chỉ", typeof(string)); dataTable.Columns.Add("Time1", typeof(string)); dataTable.Columns.Add("Day1", typeof(string)); dataTable.Columns.Add("Time2", typeof(string)); dataTable.Columns.Add("Day2", typeof(string)); dataTable.Columns.Add("Time3", typeof(string)); dataTable.Columns.Add("Day3", typeof(string)); dataTable.Columns.Add("Note", typeof(string)); dataTable.Columns.Add("TenCTY", typeof(string)); // Select the first worksheet from the file. var worksheet = workbook.Worksheets[0]; var options = new ExtractToDataTableOptions(2, 0, 10000); options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow; options.ExcelCellToDataTableCellConverting += (sender, e) => { if (!e.IsDataTableValueValid) { e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString(); e.Action = ExtractDataEventAction.Continue; } }; worksheet.ExtractToDataTable(dataTable, options); List <string> list = new List <string>(); foreach (DataRow row in dataTable.Rows) { list.Add(row[6].ToString()); } List <string> duplicates = list.GroupBy(x => x) .Where(g => g.Count() > 1) .Select(x => x.Key).ToList(); if (duplicates.Count > 0) { foreach (var dup in duplicates) { CMND += dup + ", "; } Logger.Error("CMND:" + CMND); throw new UserFriendlyException(L("Số CMND đã bị trùng" + CMND)); } foreach (DataRow row in dataTable.Rows) { string NS, NC, NTN, AP, TP, NoiDT, Time1, Time2, Time3, ViTri, KenhTD, GioiTinh; DateTime Ngay_Sinh, Ngay_Cap, NamTN, APPROVEDT, Day1, Day2, Day3; var ungVien = new CreateOrEditUngVienDto(); var TGD = _truongGiaoDichRepository.GetAll(); if (row[1].ToString().IsNullOrEmpty()) { throw new UserFriendlyException(L(name: "Họ tên không được để trống")); } else { ungVien.HoVaTen = row[1].ToString(); } hoten = ungVien.HoVaTen; //ungVien.ViTriUngTuyenCode = row[2].ToString().IsNullOrEmpty() ? null : TGD.FirstOrDefault(x => x.Code == "VTUT" && x.Value == row[2].ToString()).CDName; ViTri = row[2].ToString(); if (ViTri != null) { // ungVien.ViTriUngTuyenCode = TGD.FirstOrDefault(x => x.Code == "VTUT" && x.Value == ViTri).CDName; var customerQuery = from tgd in TGD where tgd.Code == "VTUT" && tgd.Value == ViTri select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.ViTriUngTuyenCode = Customer; } } else { ungVien.ViTriUngTuyenCode = null; } KenhTD = row[3].ToString(); if (KenhTD != null) { // ungVien.KenhTuyenDungCode = TGD.FirstOrDefault(x => x.Code == "KTD" && x.Value == KenhTD).CDName; var customerQuery = from tgd in TGD where tgd.Code == "KTD" && tgd.Value == KenhTD select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.KenhTuyenDungCode = Customer; } } else { ungVien.KenhTuyenDungCode = null; } GioiTinh = row[4].ToString(); if (GioiTinh != null) { // ungVien.GioiTinhCode = TGD.FirstOrDefault(x => x.Code == "GT" && x.Value == GioiTinh).CDName; var customerQuery = from tgd in TGD where tgd.Code == "GT" && tgd.Value == GioiTinh select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.GioiTinhCode = Customer; } } else { ungVien.GioiTinhCode = null; } NS = row[5].ToString(); if (NS != "") { try { Ngay_Sinh = Convert.ToDateTime(NS); ungVien.NgaySinh = Ngay_Sinh; } catch (Exception ex) { ungVien.NgaySinh = DateTime.MinValue; } } else { ungVien.NgaySinh = null; } ungVien.SoCMND = row[6].ToString(); //if (CheckCMND(cmnd) && !cmnd.IsNullOrWhiteSpace()) //{ // throw new UserFriendlyException(L("Số CMND đã bị trùng")); //} //else //{ // List<string> AuthorList = new List<string>(); // //AuthorList.Add(cmnd); //if(AuthorList.Count> 0 ) // { // foreach (var author in AuthorList) // { // if (author == cmnd) // { // throw new UserFriendlyException(L("Số CMND đã bị trùng")); // } // else // { // ungVien.SoCMND = cmnd; // } // } // AuthorList.Add(cmnd); // } //else // { // } NC = row[7].ToString(); if (NC != "") { Ngay_Cap = Convert.ToDateTime(NC); ungVien.NgayCap = Ngay_Cap; } else { ungVien.NgayCap = null; } ungVien.NoiCap = row[8].ToString(); TP = row[9].ToString(); if (TP == "") { ungVien.TinhThanhID = null; } else { int id = await GetMaTinhThanh(TP); ungVien.TinhThanhID = id; } string TinhTrangHonNhanCode = row[10].ToString(); if (TinhTrangHonNhanCode != null) { // ungVien.TinhTrangHonNhanCode = TGD.FirstOrDefault(x => x.Code == "TTHN" && x.Value == TinhTrangHonNhanCode).CDName; var customerQuery = from tgd in TGD where tgd.Code == "TTHN" && tgd.Value == TinhTrangHonNhanCode select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.TinhTrangHonNhanCode = Customer; } } else { ungVien.TinhTrangHonNhanCode = null; } string TrinhDoDaoTaoCode = row[11].ToString(); if (TrinhDoDaoTaoCode != null) { // ungVien.TrinhDoDaoTaoCode = TGD.FirstOrDefault(x => x.Code == "TDDT" && x.Value == TrinhDoDaoTaoCode).CDName; var customerQuery = from tgd in TGD where tgd.Code == "TDDT" && tgd.Value == TrinhDoDaoTaoCode select tgd.Code; foreach (var Customer in customerQuery) { ungVien.TrinhDoDaoTaoCode = Customer; } } else { ungVien.TrinhDoDaoTaoCode = null; } ungVien.TrinhDoVanHoa = row[12].ToString(); NoiDT = row[13].ToString(); if (NoiDT != "") { int id = await GetMaNoiDaotao(NoiDT); ungVien.NoiDaoTaoID = id; } else { ungVien.NoiDaoTaoID = null; } ungVien.Khoa = row[14].ToString(); ungVien.ChuyenNganh = row[15].ToString(); string XepLoaiCode = row[16].ToString(); if (XepLoaiCode != null) { // ungVien.XepLoaiCode = TGD.FirstOrDefault(x => x.Code == "XLHL" && x.Value == XepLoaiCode).CDName; var customerQuery = from tgd in TGD where tgd.Code == "XLHL" && tgd.Value == XepLoaiCode select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.XepLoaiCode = Customer; } } else { ungVien.XepLoaiCode = null; } NTN = row[17].ToString(); if (NTN != "") { ungVien.NamTotNghiep = int.Parse((NTN).ToString()); } else { ungVien.NamTotNghiep = null; } string TienDoTuyenDungCode = row[19].ToString(); if (TienDoTuyenDungCode != null) { // ungVien.TienDoTuyenDungCode = TGD.FirstOrDefault(x => x.Code == "TDTD" && x.Value == TienDoTuyenDungCode).CDName; var customerQuery = from tgd in TGD where tgd.Code == "TDTD" && tgd.Value == TienDoTuyenDungCode select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.TienDoTuyenDungCode = Customer; } } else { ungVien.TienDoTuyenDungCode = null; } string TrangThaiCode = row[18].ToString(); if (TrangThaiCode != null) { // ungVien.TrangThaiCode = TGD.FirstOrDefault(x => x.Code == "TRTH" && x.Value == TrangThaiCode).CDName; var customerQuery = from tgd in TGD where tgd.Code == "TRTH" && tgd.Value == TrangThaiCode select tgd.CDName; foreach (var Customer in customerQuery) { ungVien.TrangThaiCode = Customer; } } else { ungVien.TrangThaiCode = null; } ungVien.TepDinhKem = row[20].ToString(); ungVien.RECORD_STATUS = row[21].ToString(); if (row[22].ToString() != "") { ungVien.MARKER_ID = int.Parse(row[22].ToString()); } else { ungVien.MARKER_ID = null; } ungVien.AUTH_STATUS = row[23].ToString(); if (row[24].ToString() != "") { ungVien.CHECKER_ID = int.Parse(row[24].ToString()); } else { ungVien.CHECKER_ID = null; } AP = row[25].ToString(); if (AP != "") { APPROVEDT = Convert.ToDateTime(AP); ungVien.APPROVE_DT = APPROVEDT; } else { ungVien.APPROVE_DT = null; } ungVien.DienThoai = row[26].ToString(); ungVien.Email = row[27].ToString(); ungVien.DiaChi = row[28].ToString(); ungVien.Time1 = row[29].ToString(); string day1 = row[30].ToString(); if (day1 != "") { Day1 = Convert.ToDateTime(day1); ungVien.Day1 = Day1; } else { ungVien.Day1 = null; } ungVien.Time2 = row[31].ToString(); string day2 = row[32].ToString(); if (day2 != "") { Day2 = Convert.ToDateTime(day2); ungVien.Day2 = Day2; } else { ungVien.Day2 = null; } ungVien.Time3 = row[33].ToString(); string day3 = row[34].ToString(); if (day3 != "") { Day3 = Convert.ToDateTime(day3); ungVien.Day3 = Day3; } else { ungVien.Day3 = null; } ungVien.Note = row[35].ToString(); ungVien.TenCTY = row[36].ToString(); // var UngViens = ObjectMapper.Map<UngVien>(ungVien); await CreateOrEdit(ungVien); // await _ungVienRepository.InsertAsync(UngViens); } return(mes); } catch (Exception ex) { //throw new UserFriendlyException(L("Số CMND đã bị trùng" + CMND)); Logger.Error("CMND:" + CMND); Logger.Error(ex.StackTrace); return(""); } }