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