public List <DTO_CUS_VANTAI_WO_NhatKyLamViec> readWorksheet(ExcelWorksheet ws, ref bool haveError) { List <DTO_CUS_VANTAI_WO_NhatKyLamViec> data = new List <DTO_CUS_VANTAI_WO_NhatKyLamViec>(); try { //var phuongTienList = BS_CUS_VANTAI_CONF_PhuongTien.get_CUS_VANTAI_CONF_PhuongTien(db, PartnerID).ToList(); //var nhanSuList = BS_CUS_HRM_STAFF_NhanSu.get_CUS_HRM_STAFF_NhanSu(db, PartnerID).ToList(); int SheetColumnsCount = 0; int SheetRowCount = 0; SheetColumnsCount = ws.Dimension.End.Column; // Find End Column SheetRowCount = ws.Dimension.End.Row; // Find End Row if (SheetColumnsCount != 21) { haveError = true; return(data); } for (int rowid = 2; rowid <= SheetRowCount; rowid++) { DTO_CUS_VANTAI_WO_NhatKyLamViec p = new DTO_CUS_VANTAI_WO_NhatKyLamViec(); #region item List <string> row = new List <string>(); for (int i = 1; i <= SheetColumnsCount; i++) { row.Add(ws.Cells[rowid, i].Value == null ? "" : ws.Cells[rowid, i].Text); } if (row[1] == "" || row[2] == "" || row[3] == "" || row[4] == "") { continue; } int iID = 0; int.TryParse(row[0], out iID); p.ID = iID; p.NgayThucHien = DateTime.Parse(row[1]); //DateTime.FromOADate(long.Parse(row[1])); p.IDPhuongTien = int.Parse(Regex.Match(row[2], @"\[(\d+)\]").Groups[1].Value); // phuongTienList.First(d => d.Code == row[2]).ID; p.IDLenhLamViec = int.Parse(Regex.Match(row[3], @"\[(\d+)\]").Groups[1].Value); p.TaiXe = int.Parse(Regex.Match(row[4], @"\[(\d+)\]").Groups[1].Value); //nhanSuList.First(d => d.Code == row[4]).ID; if (int.TryParse(Regex.Match(row[5], @"\[(\d+)\]").Groups[1].Value, out int phuXe)) { p.PhuXe = phuXe; } //p.PhuXe = row[5] == "" ? null : int.Parse(Regex.Match(row[5], @"\[(\d+)\]").Groups[1].Value); //nhanSuList.First(d => d.Code == row[5]).ID; p.SangBatDau = row[6]; p.SangKetThuc = row[7]; p.TruaBatDau = row[8]; p.TruaKetThuc = row[9]; p.ChieuBatDau = row[10]; p.ChieuKetThuc = row[11]; p.ToiBatDau = row[12]; p.ToiKetThuc = row[13]; p.GiamSatThiCong = row[15]; p.GiamSatThiCongXacNhan = row[16] == "ok"; p.GiamSatThiCongNhanXet = row[17]; p.ChiHuyTruong = row[18]; p.ChiHuyTruongXacNhan = row[19] == "ok"; p.ChiHuyTruongNhanXet = row[20]; #endregion //ID Ngày thực hiện 2Xe 3Nội dung làm việc 4Tài xế 5Phụ xe 6Sáng 8Trưa 10Chiều 12Tối 14"Tổng số giờ" //15"Giám sát thi công" "Xác nhận" 17Nhận xét //18Chỉ huy trưởng "Xác nhận" Nhận xét validateImportRow(ws, data, p, rowid, ref haveError); if (p.SoGioLamViec != 0) { data.Add(p); } } } catch (Exception ex) { haveError = true; } return(data); }
void validateImportRow(ExcelWorksheet ws, List <DTO_CUS_VANTAI_WO_NhatKyLamViec> data, DTO_CUS_VANTAI_WO_NhatKyLamViec frow, int rowid, ref bool haveError) { int inputIntTryParse = 0; bool inputBoolTryParse = false; bool isInvalid = false; string errormessage = ""; //if (frow.Categorization == 1 && string.IsNullOrEmpty(frow.DefaultValue)) //{ // isInvalid = true; // errormessage = string.Format(Resources.message.RequireMessage, frow.FieldName); //} //if (frow.ControlID == "drlProduct") //{ // var drlProductDataSource = documentCtrl.en.tbl_LSProduct.Where(d => d.tbl_LSProductCategory.Partner == PartnerCode && d.IsDeleted == false).Select(s => new // { // s.Code, // ProductName = s.tbl_LSProductDetail.Where(c => c.Language == language).FirstOrDefault() == null ? "N/A" : // s.tbl_LSProductDetail.Where(c => c.Language == language).FirstOrDefault().ProductName // }); // int importproductcode = 0; // int.TryParse(frow.DefaultValue, out importproductcode); // if (drlProductDataSource.Where(d => d.Code == importproductcode).Count() == 0) // { // isInvalid = true; // errormessage = string.Format(Resources.lang.document_inputFieldInvalid, frow.FieldName); // } //} //else if (ws.Name.Contains("4. ") && frow.ControlID == "drlBuyer" && data.Count(d => d.ControlID == "drlBuyer" && d.DefaultValue == frow.DefaultValue) > 0) //{ // isInvalid = true; // errormessage = string.Format(Resources.lang.document_importDupplicateErrorMessage, frow.FieldName); //} //else if (!(string.IsNullOrEmpty(frow.DefaultValue)) && frow.DataType == 2 && !int.TryParse(frow.DefaultValue, out inputIntTryParse)) //{ // isInvalid = true; // errormessage = string.Format(Resources.message.FieldIsNumber, frow.FieldName); //} //else if (!(string.IsNullOrEmpty(frow.DefaultValue)) && frow.DataType == 16 && !bool.TryParse(frow.DefaultValue, out inputBoolTryParse)) //{ // isInvalid = true; // errormessage = Resources.lang.document_ListValidateErrorMessage; //} //if (isInvalid) //{ // var comment = ws.Cells["E" + rowid].AddComment(errormessage, "traceverified"); // comment.AutoFit = true; // haveError = true; // ws.Cells["E" + rowid].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; // ws.Cells["E" + rowid].Style.Fill.BackgroundColor.SetColor(Color.Red); // ws.Cells["E" + rowid].Style.Font.Color.SetColor(Color.White); //} }