Exemple #1
0
        private static void StyleHeader(IXLWorksheet ws)
        {
            var headerRange = ws.Range(
                ws.FirstRow().FirstCell(),
                ws.FirstRow().LastCellUsed());

            headerRange.Style.Font.Bold = true;
        }
Exemple #2
0
        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);
        }
Exemple #3
0
        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);
            }
        }
Exemple #4
0
        /// <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);
        }
Exemple #5
0
        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);
        }
Exemple #6
0
        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);
        }
Exemple #7
0
        private void MontarCabecalhoDoNovoArquivo(DataTable dataTable, IXLWorksheet worksheetOriginal)
        {
            IXLRow headerRow = worksheetOriginal.FirstRow();

            foreach (var item in headerRow.CellsUsed())
            {
                dataTable.Columns.Add(item.Value.ToString());
            }
        }
Exemple #8
0
        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();
            }
        }
Exemple #9
0
        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);
        }
Exemple #10
0
        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);
        }
Exemple #11
0
        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);
        }
Exemple #12
0
        //-> 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);
            }
        }
Exemple #15
0
        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}");
        }
Exemple #16
0
        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());
        }
Exemple #17
0
 public IDataSheetRow FirstRow()
 {
     return(new DataSheetRow(_worksheet.FirstRow()));
 }
Exemple #18
0
        //-> 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");
                }
            }
        }
Exemple #19
0
 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);
 }
Exemple #20
0
 /// <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();
 }
Exemple #21
0
 public static void SetContentColor(this IXLWorksheet sheet)
 {
     sheet.RowsUsed(r => sheet.FirstRow() != r).Style.Fill.BackgroundColor = XLColor.FromArgb(242, 242, 242);
 }