private static void StyleHeader(IXLWorksheet ws)
{
var headerRange = ws.Range(
ws.FirstRow().FirstCell(),
ws.FirstRow().LastCellUsed());
headerRange.Style.Font.Bold = true;
}
public void WriteXlsx(string filepath, List <ResourceItem> resourceList)
{
foreach (var resource in resourceList)
{
var currentRow = _sheet.LastRowUsed().RowBelow();
WriteKeyValues(currentRow, resource);
WriteDefaultValue(currentRow, resource);
WriteLocaleValues(currentRow, resource);
}
_sheet.FirstRow().CellsUsed().Style.Fill.BackgroundColor = XLColor.Yellow;
_workBook.SaveAs(filepath);
}
private void ReadSheet(IXLWorksheet worksheet, CremaDataSet dataSet)
{
var dataTable = dataSet.Tables.FirstOrDefault(item => item.Name == worksheet.Name);
if (dataTable == null)
{
if (worksheet.Name.IndexOf('~') >= 0)
{
dataTable = dataSet.Tables.FirstOrDefault(item => SpreadsheetUtility.Ellipsis(item.Name) == worksheet.Name);
}
}
if (dataTable == null)
{
return;
}
var columns = this.ReadColumns(dataTable, worksheet.FirstRow());
foreach (var item in worksheet.Rows().Skip(1))
{
if (item.Cells(true).Any() == false)
{
continue;
}
this.ReadRow(dataTable, columns, item);
}
}
/// <summary>
/// Parse xlsx sheet data into a list of dynamic objects
/// it will list for given columnNames ( if not null ) or for all columns ( if null )
/// if columnNamesIgnoreCase result object will contains lowercase properties
/// </summary>
public static List <dynamic> ParseXlsxData(this IXLWorksheet ws, HashSet <string> _columnNames = null, bool columnNamesIgnoreCase = true)
{
HashSet <string> columnNames = null;
if (columnNamesIgnoreCase && _columnNames != null)
{
columnNames = _columnNames.Select(w => w.ToLower()).ToHashSet();
}
else
{
columnNames = _columnNames;
}
var res = new List <dynamic>();
var columnDict = new Dictionary <string, int>();
var row = ws.FirstRow();
var lastCol = row.LastCellUsed().Address.ColumnNumber;
for (int ci = 1; ci <= lastCol; ++ci)
{
var cname = (string)row.Cell(ci).Value;
if (string.IsNullOrEmpty((string)cname))
{
continue;
}
if (columnNamesIgnoreCase)
{
cname = cname.ToLower();
}
if (columnNames == null || columnNames.Contains(cname))
{
columnDict.Add(cname, ci);
}
}
var lastRow = ws.LastRowUsed().RowNumber();
for (int ri = 2; ri <= lastRow; ++ri)
{
row = ws.Row(ri);
IDictionary <string, object> eo = new ExpandoObject();
foreach (var c in columnDict)
{
var cell = row.Cell(c.Value);
eo.Add(c.Key, cell.Value);
}
res.Add(eo);
}
return(res);
}
public object ExcelToJson(string excel_path, string isHeader, string importStartRow)
{
List <Dictionary <string, object> > resultJson = new List <Dictionary <string, object> >();
List <string> header = new List <string>();
XLWorkbook workbook = new XLWorkbook(excel_path);
IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);
var firstCell = worksheet.FirstCellUsed();
var lastCell = worksheet.LastCellUsed();
var firstRow = worksheet.FirstRow();
int hearderColumnsCount = lastCell.Address.ColumnNumber;
if (isHeader == "checked")
{
for (int col = 1; col <= hearderColumnsCount; col++)
{
header.Add(worksheet.Cell(1, col).Value.ToString());
}
}
else
{
Dictionary <string, object> jo = new Dictionary <string, object>();
for (int col = 1; col <= hearderColumnsCount; col++)
{
string id = "col_" + col.ToString();
header.Add(id);
jo.Add(id, id);
}
resultJson.Add(jo);
}
int row = 1;
foreach (IXLRow dr in worksheet.Rows())
{
if (row != 1)
{
if (int.Parse(importStartRow) > row)
{
row += 1;
continue;
}
}
Dictionary <string, object> jo = new Dictionary <string, object>();
jo.Add("sort_order", row);
var i = 1;
foreach (var head in header)
{
jo.Add(head, dr.Cell(i).Value.ToString());
i += 1;
}
resultJson.Add(jo);
row += 1;
}
return(resultJson);
}
private static void SetupHeaderRow(IXLWorksheet worksheet, XLColor color)
{
var headerRow = worksheet.FirstRow();
headerRow.Style.Font.Bold = true;
headerRow.Style.Fill.BackgroundColor = color;
worksheet.SheetView.FreezeRows(1);
}
private void MontarCabecalhoDoNovoArquivo(DataTable dataTable, IXLWorksheet worksheetOriginal)
{
IXLRow headerRow = worksheetOriginal.FirstRow();
foreach (var item in headerRow.CellsUsed())
{
dataTable.Columns.Add(item.Value.ToString());
}
}
private static void FillTitle(IXLWorksheet ws, DataTable dt)
{
var cell = ws.FirstRow().FirstCell();
foreach (DataColumn column in dt.Columns)
{
cell.Value = column.ColumnName;
cell = cell.CellRight();
}
}
public void CopyRow()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Test").Style.Font.SetBold();
ws.FirstRow().CopyTo(ws.Row(2));
Assert.IsTrue(ws.Cell("A2").Style.Font.Bold);
}
private static IXLColumn GetColumn(string columnName, IXLWorksheet sheet)
{
var projectColumn =
sheet.FirstRow().Cells().FirstOrDefault(x => x.Value.ToString() == columnName)?.WorksheetColumn();
if (projectColumn == null)
{
throw new Exception(columnName + " column not found");
}
return(projectColumn);
}
public void Validation_3()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Cell("A1").SetValue("A");
ws.Cell("B1").SetDataValidation().Custom("A1");
ws.FirstRow().InsertRowsAbove(1);
Assert.AreEqual("A2", ws.Cell("B2").DataValidation.Value);
}
//-> UploadCustomer
public async Task <int> UploadCustomer(UploadCustomer uploadExcel)
{
if (uploadExcel.ExcelFile.ContentLength < 0)
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Not a valid file");
}
if (!uploadExcel.ExcelFile.FileName.EndsWith(".xlsx"))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Only .xlsx is allowed");
}
using (var transaction = db.Database.BeginTransaction())
{
try
{
XLWorkbook Workbook = new XLWorkbook(uploadExcel.ExcelFile.InputStream);
IXLWorksheet WorkSheet = null;
WorkSheet = Workbook.Worksheet("sheet1");
if (!DocumentHelper.SaveExcelFile(uploadExcel.ExcelFile))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Error saving file.");
}
WorkSheet.FirstRow().Delete();//delete header column
int countUpdateRecord = 0;
foreach (var row in WorkSheet.RowsUsed())
{
//var soNumber = row.Cell(1).Value.ToString().Trim().Replace(" ", string.Empty);//Get ist cell. 1 represent column number
//var status = row.Cell(2).Value.ToString().Trim().Replace(" ", string.Empty); ;
var ex = await db.tblCustomers.FirstOrDefaultAsync(x => x.code.ToLower() == row.Cell(1).Value.ToString().Trim().ToLower() && x.deleted == null);
if (ex == null)
{
var record = new tblCustomer();
record.code = row.Cell(1).Value.ToString().Trim();
record.firstName = row.Cell(2).Value.ToString().Trim();
db.tblCustomers.Add(record);
await db.SaveChangesAsync();
countUpdateRecord++;
}
}
transaction.Commit();
return(countUpdateRecord);
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception(ex.Message);
}
}
}
private void BtnExport_Click(object sender, EventArgs e)
{
if (DtgPersonList.DataSource == null)
{
return;
}
if (string.IsNullOrEmpty(fullPath))
{
return;
}
IXLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.AddWorksheet("Veriler");
IXLRow row = worksheet.Row(1);
row.Cell(1).Value = "No";
row.Cell(2).Value = "Ad Soyad";
row.Cell(3).Value = "Doğum Tarihi";
row = row.RowBelow();
foreach (DataGridViewRow item in DtgPersonList.Rows)
{
row.Cell("A").Value = item.Cells["Id"].Value;
row.Cell("B").Value = item.Cells["Name"].Value;
row.Cell("C").Value = item.Cells["BirthDate"].Value;
row = row.RowBelow();
}
/*foreach (Person item in people)
* {
* row.Cell("A").Value = item.Id;
* row.Cell("B").Value = item.Name;
* row.Cell("C").Value = item.BirthDate;
* row = row.RowBelow();
* }*/
var firstRow = worksheet.FirstRow();
firstRow.RowUsed().SetAutoFilter(true);
firstRow.Cells().Style.Font.Bold = true;
firstRow.Height = firstRow.Height * 2;
var column = worksheet.Column("C");
column.Width = 11.5;
worksheet.Protect("12345");
workbook.SaveAs(fullPath);
System.Diagnostics.Process.Start(fullPath);
}
private void ReadSheet(IXLWorksheet worksheet, CremaDataTable dataTable)
{
if (dataTable.Rows.Any() == true)
{
throw new InvalidOperationException("비어있는 테이블이 아닙니다.");
}
var columns = this.ReadColumns(dataTable, worksheet.FirstRow());
foreach (var item in worksheet.Rows().Skip(1))
{
if (item.Cells(true).Any() == false)
{
continue;
}
this.ReadRow(dataTable, columns, item);
}
}
public void Preview()
{
const int columnsMaxCount = 50;
const int rowsMaxCount = 50;
var firstColumn = _sheet.FirstColumn();
var lastColumn = _sheet.LastColumnUsed().ColumnNumber() < columnsMaxCount?
_sheet.LastColumnUsed() :
_sheet.Column(columnsMaxCount);
var firstRow = _sheet.FirstRow();
var lastRow = _sheet.LastRowUsed().RowNumber() < rowsMaxCount?
_sheet.LastRowUsed() :
_sheet.Row(rowsMaxCount);
var previewData = new string[lastColumn.ColumnNumber(), lastRow.RowNumber()];
for (var row = firstRow.RowNumber(); row <= lastRow.RowNumber(); row++)
{
for (int col = firstColumn.ColumnNumber(); col <= lastColumn.ColumnNumber(); col++)
{
previewData[col - 1, row - 1] = _sheet.Row(row).Cell(col).GetString();
Console.Write(previewData[col - 1, row - 1] + " \t");
}
Console.WriteLine();
}
Console.WriteLine();
var rowsUsed = _sheet.LastRowUsed().RowNumber();
int rowsRoundedCount = rowsUsed < 100 ?
lastRow.RowNumber() :
(int)Math.Round((double)(rowsUsed - rowsUsed / 20) / 10) * 10;
Console.WriteLine("\n=== SUMMARY ===\n");
Console.WriteLine($"Примерное кол-во строк: {rowsRoundedCount}");
}
public ActionResult Index(UploadFile UploadFile)
{
if (ModelState.IsValid)
{
if (UploadFile.ExcelFile.ContentLength > 0)
{
if (UploadFile.ExcelFile.FileName.EndsWith(".xlsx") || UploadFile.ExcelFile.FileName.EndsWith(".xls"))
{
XLWorkbook Workbook;
try
{
Workbook = new XLWorkbook(UploadFile.ExcelFile.InputStream);
}
catch (Exception ex)
{
ModelState.AddModelError(String.Empty, $"Check your file. {ex.Message}");
return(View());
}
IXLWorksheet WorkSheet = null;
try
{
WorkSheet = Workbook.Worksheet(1);
}
catch (Exception ex)
{
ex.StackTrace.ToArray();
ModelState.AddModelError(String.Empty, "sheet1 not found!");
return(View());
}
try
{
var count = WorkSheet.Columns().Count();
if (count < 37)
{
ModelState.AddModelError(String.Empty, "File data not same as sample file shown in above image");
return(View());
}
}
catch (Exception e)
{
}
WorkSheet.FirstRow().Delete();//if you want to remove ist row
var x = 1;
foreach (var row in WorkSheet.RowsUsed())
{
Debug.Print(row.Cell(1).Value.ToString() + "------");
try
{
int empId = Convert.ToInt32(row.Cell(1).Value.ToString());
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
if (context.Users.FirstOrDefault(u =>
u.EmployeeId == empId) == null)
{
ApplicationUser user = new ApplicationUser();
user.EmployeeId = Convert.ToInt32(row.Cell(1).Value.ToString());
user.FirstName = row.Cell(2).Value.ToString();
user.MiddleName = row.Cell(3).IsEmpty() ? null : row.Cell(3).Value.ToString();
user.LastName = row.Cell(4).Value.ToString();
user.UserName = "******" + user.EmployeeId + "*****@*****.**";
var departmentname = row.Cell(5).Value.ToString().Contains("Operations")
? "Operations"
: row.Cell(5).Value.ToString();
Department department = context.Departments.Where(m => m.name == departmentname)
.SingleOrDefault();
if (department == null)
{
Department dp = new Department();
dp.name = departmentname;
//dp.departmntNumber = departmentId;
context.Departments.Add(dp);
context.SaveChanges();
user.DepartmentId = dp.Id;
//context.SaveChanges();
}
else
{
user.DepartmentId = department.Id;
// context.SaveChanges();
}
var costcentercode = row.Cell(6).IsEmpty() ? null : row.Cell(6).Value.ToString();
var costcentername = row.Cell(7).Value.ToString();
CostCenter costCenter = context.CostCenters.Where(m => m.name == costcentername)
.SingleOrDefault();
if (costCenter == null)
{
CostCenter cs = new CostCenter();
cs.name = costcentername;
cs.CostCenterCode = costcentercode;
context.CostCenters.Add(cs);
context.SaveChanges();
user.CostCenterId = cs.Id;
}
else
{
user.CostCenterId = costCenter.Id;
}
// user.EmployeeHireDate = row.Cell(8).GetDateTime();
var nationalityname = row.Cell(9).Value.ToString();
Nationality nationality = context.Nationalities.Where(m => m.name == nationalityname)
.SingleOrDefault();
if (nationality == null)
{
Nationality ps = new Nationality();
ps.name = nationalityname;
context.Nationalities.Add(ps);
context.SaveChanges();
user.NationalityId = ps.Id;
//context.SaveChanges();
}
else
{
user.NationalityId = nationality.Id;
//context.SaveChanges();
}
// user.EmployeeFireDate = row.Cell(10).GetDateTime();
user.EmployeeStatus = row.Cell(11).IsEmpty() ? null : row.Cell(11).Value.ToString();
var eventreason = row.Cell(12).Value.ToString();
EventReason eventReason = context.EventReasons.Where(m => m.name == eventreason)
.SingleOrDefault();
if (eventReason == null)
{
EventReason ev = new EventReason();
ev.name = eventreason;
context.EventReasons.Add(ev);
context.SaveChanges();
user.EventReasonId = ev.Id;
//context.SaveChanges();
}
else
{
user.EventReasonId = eventReason.Id;
//context.SaveChanges();
}
var locationId = row.Cell(13).Value.ToString();
Location location = context.Locations.Where(m => m.name == locationId)
.SingleOrDefault();
if (location == null)
{
Location lc = new Location();
lc.name = locationId;
context.Locations.Add(lc);
context.SaveChanges();
user.LocationId = lc.Id;
//context.SaveChanges();
}
else
{
user.LocationId = location.Id;
// context.SaveChanges();
}
var locationgroupId = row.Cell(14).Value.ToString();
LocationGroup locationgrp = context.LocationGroups.Where(m => m.name == locationgroupId)
.SingleOrDefault();
if (locationgrp == null)
{
LocationGroup lc = new LocationGroup();
lc.name = locationgroupId;
context.LocationGroups.Add(lc);
context.SaveChanges();
user.LocationGroupId = lc.Id;
//context.SaveChanges();
}
else
{
user.LocationGroupId = locationgrp.Id;
// context.SaveChanges();
}
var jobtitlename = row.Cell(15).Value.ToString();
JobTitle jobTitle = context.JobTitles.Where(m => m.name == jobtitlename)
.SingleOrDefault();
if (jobTitle == null)
{
JobTitle jb = new JobTitle();
jb.name = jobtitlename;
context.JobTitles.Add(jb);
context.SaveChanges();
user.JobTitleId = jb.Id;
//context.SaveChanges();
}
else
{
user.JobTitleId = jobTitle.Id;
// context.SaveChanges();
}
user.GOSINumber = row.Cell(16).IsEmpty() ? null : row.Cell(16).Value.ToString();
user.saudiNationalId = row.Cell(17).IsEmpty() ? null : row.Cell(17).Value.ToString();
user.saudiResidentialId = row.Cell(18).IsEmpty() ? null : row.Cell(18).Value.ToString();
var employeeclass = row.Cell(19).Value.ToString();
EmployeeClass employeeClass = context.employeeClasses
.Where(m => m.name == employeeclass).SingleOrDefault();
if (employeeClass == null)
{
EmployeeClass ec = new EmployeeClass();
ec.name = employeeclass;
context.employeeClasses.Add(ec);
context.SaveChanges();
user.EmployeeClassId = ec.Id;
//context.SaveChanges();
}
else
{
user.EmployeeClassId = employeeClass.Id;
// context.SaveChanges();
}
var ethincity = row.Cell(20).Value.ToString();
Ethnicity ethnicity = context.Ethnicities.Where(m => m.name == ethincity)
.SingleOrDefault();
if (ethnicity == null)
{
Ethnicity ec = new Ethnicity();
ec.name = ethincity;
context.Ethnicities.Add(ec);
context.SaveChanges();
user.EthincityId = ec.Id;
//context.SaveChanges();
}
else
{
user.EthincityId = ethnicity.Id;
// context.SaveChanges();
}
user.HasSwipeAccess = row.Cell(21).Value.ToString().Equals("No Swipe Access")
? false
: true;
user.bussinessPhoneNumber =
row.Cell(22).IsEmpty() ? null : row.Cell(22).Value.ToString();
user.homePhoneNumber = row.Cell(23).IsEmpty() ? null : row.Cell(23).Value.ToString();
user.cellPhoneNumber = row.Cell(24).IsEmpty() ? null : row.Cell(24).Value.ToString();
;
user.otherPhoneNumber = row.Cell(25).IsEmpty() ? null : row.Cell(25).Value.ToString();
var payscalename = row.Cell(26).Value.ToString();
PayScaleType payscale = context.PayScaleTypes.Where(m => m.name == payscalename)
.SingleOrDefault();
if (payscale == null)
{
PayScaleType ps = new PayScaleType();
ps.name = payscalename;
context.PayScaleTypes.Add(ps);
context.SaveChanges();
user.PayScaleTypeId = ps.Id;
//context.SaveChanges();
}
else
{
user.PayScaleTypeId = payscale.Id;
//context.SaveChanges();
}
var religionname = row.Cell(27).Value.ToString();
Religion religion = context.Religions.Where(m => m.name == religionname)
.SingleOrDefault();
if (religion == null)
{
Religion ps = new Religion();
ps.name = religionname.Equals(null) ? "Others" : religionname;
context.Religions.Add(ps);
context.SaveChanges();
user.ReligionId = ps.Id;
//context.SaveChanges();
}
else
{
user.ReligionId = religion.Id;
//context.SaveChanges();
}
user.personalInfoString = row.Cell(28).IsEmpty() ? null : row.Cell(28).Value.ToString();
;
//user.DOB = row.Cell(29).GetDateTime();
var gendername = row.Cell(30).Value.ToString();
Gender gender = context.Genders.Where(m => m.name == gendername).SingleOrDefault();
if (gender == null)
{
Gender gn = new Gender();
gn.name = gendername;
context.Genders.Add(gn);
context.SaveChanges();
user.GenderId = gn.Id;
//context.SaveChanges();
}
else
{
user.GenderId = gender.Id;
//context.SaveChanges();
}
user.otherEmail = row.Cell(31).IsEmpty() ? null : row.Cell(31).Value.ToString();
user.bussinessEmail = row.Cell(32).IsEmpty() ? null : row.Cell(32).Value.ToString();
user.personalEmail = row.Cell(33).IsEmpty() ? null : row.Cell(33).Value.ToString();
var employertypename = row.Cell(34).Value.ToString();
EmployerType employerType = context.employerTypes.Where(m => m.name == employertypename)
.SingleOrDefault();
if (employerType == null)
{
EmployerType ty = new EmployerType();
ty.name = employertypename;
context.employerTypes.Add(ty);
context.SaveChanges();
user.EmployerTypeId = ty.Id;
//context.SaveChanges();
}
else
{
user.EmployerTypeId = employerType.Id;
//context.SaveChanges();
}
var vendorname = row.Cell(35).Value.ToString();
Vendor vendor = context.vendors.Where(m => m.name == vendorname).SingleOrDefault();
if (vendor == null)
{
Vendor vn = new Vendor();
vn.name = vendorname;
context.vendors.Add(vn);
context.SaveChanges();
user.VendorId = vn.Id;
//context.SaveChanges();
}
else
{
user.VendorId = vendor.Id;
//context.SaveChanges();
}
user.outSourcedId = row.Cell(36).IsEmpty() ? null : row.Cell(36).Value.ToString();
var bandname = row.Cell(37).Value.ToString();
Band band = context.bands.Where(m => m.name == bandname).SingleOrDefault();
if (band == null)
{
Band bnd = new Band();
bnd.name = bandname;
context.bands.Add(bnd);
context.SaveChanges();
user.BandId = bnd.Id;
//context.SaveChanges();
}
else
{
user.BandId = band.Id;
//context.SaveChanges();
}
context.Users.Add(user);
try
{
context.SaveChanges();
}
catch (DbEntityValidationException ex)
{
Debug.WriteLine(ex.Message);
}
}
else
{
Debug.Print("Employee exists " + row.Cell(1).Value.ToString());
}
}
catch (Exception e)
{
Debug.Print(e.InnerException + "File row issue" + e.StackTrace);
ModelState.AddModelError(String.Empty, "Something went wrong while processing row " + (Convert.ToInt32(x) + 1) + ".Employees data till that row has been stored.");
return(View());
}
x++;
}
}
else
{
ModelState.AddModelError(String.Empty, "Only .xlsx and .xls files are allowed");
return(View());
}
}
else
{
ModelState.AddModelError(String.Empty, "Not a valid file");
return(View());
}
}
TempData["success"] = "File uploaded successfully";
return(View());
}
public IDataSheetRow FirstRow()
{
return(new DataSheetRow(_worksheet.FirstRow()));
}
//-> UploadRetailer
public async Task <int> UploadRetailer(UploadRetailer uploadExcel)
{
if (uploadExcel.ExcelFile.ContentLength < 0)
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Not a valid file");
}
if (!uploadExcel.ExcelFile.FileName.EndsWith(".xlsx"))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Only .xlsx is allowed");
}
string code = string.Empty;
using (var transaction = db.Database.BeginTransaction())
{
try
{
XLWorkbook Workbook = new XLWorkbook(uploadExcel.ExcelFile.InputStream);
IXLWorksheet WorkSheet = null;
WorkSheet = Workbook.Worksheet("sheet1");
if (!DocumentHelper.SaveExcelFile(uploadExcel.ExcelFile))
{
throw new HttpException((int)HttpStatusCode.BadRequest, "Error saving file.");
}
WorkSheet.FirstRow().Delete();//delete header column
int countUpdateRecord = 0;
foreach (var row in WorkSheet.RowsUsed())
{
//var soNumber = row.Cell(1).Value.ToString().Trim().Replace(" ", string.Empty);//Get ist cell. 1 represent column number
//var status = row.Cell(2).Value.ToString().Trim().Replace(" ", string.Empty); ;
int?custID = null;
var custCode = row.Cell(1).Value.ToString().Trim();
var cust = await db.tblCustomers.FirstOrDefaultAsync(x => x.deleted == null& x.code == custCode);
if (cust != null)
{
custID = cust.id;
}
code = row.Cell(4).Value.ToString().ToLower().Trim();
var _record = await db.tblRetailers.FirstOrDefaultAsync(x => x.retl_Deleted == null &&
x.retl_Code.ToLower().Trim() == code &&
x.retl_CustomerID == cust.id);
if (_record == null)
{
var record = new tblRetailer();
record.retl_Name = row.Cell(5).Value.ToString().Trim();
record.retl_Code = row.Cell(4).Value.ToString().Trim();
record.retl_District = row.Cell(7).Value.ToString().Trim();
record.retl_Province = row.Cell(9).Value.ToString().Trim();
record.retl_Phone = row.Cell(10).Value.ToString().Trim();
record.retl_Phone2 = row.Cell(11).Value.ToString().Trim();
record.retl_CustomerID = custID;
db.tblRetailers.Add(record);
await db.SaveChangesAsync();
countUpdateRecord++;
}
}
transaction.Commit();
return(countUpdateRecord);
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception($"Customer Number: {code} not found");
}
}
}
public static void SetHeaderColor(this IXLWorksheet sheet)
{
sheet.FirstRow().Style.Font.Bold = true;
sheet.FirstRow().Style.Font.FontColor = XLColor.White;
sheet.FirstRow().Style.Fill.BackgroundColor = XLColor.FromArgb(83, 141, 213);
}
/// <summary>
/// Initializes a new instance of the <see cref="WorksheetReader"/> class.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
public WorksheetReader(IXLWorksheet worksheet)
{
current = worksheet.FirstRow().FirstCell();
}
public static void SetContentColor(this IXLWorksheet sheet)
{
sheet.RowsUsed(r => sheet.FirstRow() != r).Style.Fill.BackgroundColor = XLColor.FromArgb(242, 242, 242);
}