Blazor Export Excel based on template excel file C#

 Target:

i have mudtable contains data from model object.

i need when user press on export excel button to load object data for mudtable to ready excel file template and write data for this template then download it for end user on the browser.

Used Nuget: NPOI version 2.6.0

Create custom component for export excel ExportExcel.razor

@inherits BaseComponentCustom

<a href="" @onclick="Export" class="btn btn-white btn-sm  shadow-sm fw-bold" data-bs-toggle="modal" data-bs-target="#kt_share">

    @trans["ExportData"]

    <!--begin:: Icon-->

    <img src="assets/media/svg/icon/share-qoute.svg" class="h-25px">

    <!--end:: Icon-->

</a>

*-------------------------------------------------------------------------------

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

namespace YourNameSpace;

public partial class ExportExcel

{

    private FileStream _file;

    [Parameter] public ExcelDataObject? ExcelDataObject{ get; set; }

    private async void Export()

    {

        if (ExcelDataObject!= null)

        {

            string rootpath = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot/assets/Templates/");

            string name = "TemplateFile.xlsx";

            string FileName = Path.Combine(rootpath, name);

            IWorkbook _workbook;

            var memoryStream = new MemoryStream();

            ISheet excelSheet;

            _file?.Close();

            var bytes = new byte[0];

            _file = new FileStream(FileName, FileMode.Open, FileAccess.Read);

            {

                    _workbook = new XSSFWorkbook(_file);

                    _workbook.MissingCellPolicy = MissingCellPolicy.RETURN_NULL_AND_BLANK;

                _file.Close();

            }

            excelSheet = _workbook.GetSheetAt(0);

            IWorkbook workbook = excelSheet.Workbook;

            int i = 1;

            foreach (var member in ExcelDataObject.ListToGetColumnsData)

            {

                IRow row = excelSheet.CreateRow(i);

                row.CreateCell(0).SetCellValue(member.cell0Value);

                row.CreateCell(1).SetCellValue(member.cell2Value);

                row.CreateCell(2).SetCellValue(member.cell3Value);

               );

                i++;

            }

            using (var ms = new MemoryStream())

            {

                workbook.Write(ms, false);

                bytes = ms.ToArray();

            }

            await SaveAs(JS, FileName + ".xlsx", bytes);

        }

        async Task SaveAs(IJSRuntime js, string fileName, byte[] data)

        {

            await js.InvokeAsync<object>(

                "BlazorDownloadFile",

                fileName,

                Convert.ToBase64String(data)

            );

        }

    }

}

---------------------------------------------------------------------------------------------

Javascript Function

function BlazorDownloadFile(filename, bytesBase64) {

    var link = document.createElement('a');

    link.download = filename;

    link.href = "data:application/octet-stream;base64," + bytesBase64;

    document.body.appendChild(link); // Needed for Firefox

    link.click();

    document.body.removeChild(link);

}

C# linq Queries Tips and Tricks

 update multiple columns inside complex object

-------------------------------------------

  _policies = SharedFunctions.PoliciesFilterForTenant(policyTenants, _policies, _selectedTenant);

                foreach (TPolicy item in _policies)

                {

                    item.Roles = item.Roles.Select(r => { r.IsChecked = false; r.AssignmentId = 0; return r; }).ToList();

                }

---------------------------------------------------------------------

join two lists with multiple on condition

join table with custom list and get result using linq query.

 public bool UserTenantDelete(int TenantId, int GovId)

        {

            //get custom list to compare later

            List<UserTenant> TenantsToDelete = _ctx.UserTenants.Where(d => d.TenantId == TenantId && d.GovId == GovId).ToList();

            if (TenantsToDelete.Any())

            {

               List<SubjectAssignment> subjectAssignment = (from sa in _ctxPolicyServerDbContext.SubjectAssignments.ToList()

                                                             join ut in TenantsToDelete

                                                             on new {A=sa.TenantId, B=sa.SubjectId } equals new {A=ut.TenantId,B=ut.UserId.ToString()}

                                                             select  sa).ToList();

             //   _ctxPolicyServerDbContext.SubjectAssignments.RemoveRange(subjectAssignment);

               // _ctxPolicyServerDbContext.SaveChanges();

               // _ctx.SaveChanges();

                return true;

            }

            return false;

        }

-------------------------------------------------------------------------------------

Filter List using another List

   List<int> GovsNotAuthorized = _ctx.GovAgencyTenants.Where(a => a.TenantId != TenantId).Select(a=>a.GovId).ToList();
                List<int> GovsAuthorized = _ctx.GovAgencyTenants.Where(a => a.TenantId == TenantId).Select(a => a.GovId).ToList();
                List<int> result = GovsNotAuthorized.Where(x => !GovsAuthorized.Any(a => a == x)).Distinct().ToList(); 

-------------------------------------------------------------------------------------------------

.netCore Global Filter with injected services inside.

 problem:

when using global filter to authorize user with custom service function inside the IActionFilter

i have object reference Error for  for the service instance.

    public class GlobalActionFilter : IActionFilter

    {

        private readonly IUserAuthorizationService userAuthorizationService;

        public GlobalActionFilter(IUserAuthorizationService userAuthorizationService)

        {

            this.userAuthorizationService = userAuthorizationService;

        }

       

        public void OnActionExecuted(ActionExecutedContext context)

        {

        }

        public void OnActionExecuting(ActionExecutingContext context)

        {

            // our code before action executes

            try

            {

                string user = context.HttpContext.User?.Claims?.FirstOrDefault(i => i.Type == "name")?.Value;

                string tenantId = context.HttpContext.User?.Claims?.FirstOrDefault(i => i.Type == "tid")?.Value;

                tenantId ??= context.HttpContext.User?.Claims?.FirstOrDefault(i => i.Type == "http://schemas.microsoft.com/identity/claims/tenantid")?.Value;

                string subjectId = context.HttpContext.User.Claims?.FirstOrDefault(i => i.Type == "sub").Value;

                Audit.Core.IAuditScope auditScope = context.HttpContext.GetCurrentAuditScope();

                auditScope.SetCustomField("Username", user);

                auditScope.SetCustomField("TenantName", tenantId);

                var param = context.ActionArguments.SingleOrDefault(p => p.Key.ToLower()=="tenantid");

                context.ActionArguments.TryGetValue("TenantId", out object actionTenantId);

                if (actionTenantId != null)

                {

                    if(int.TryParse(tenantId, out int loggedInUserTenantId))

                    {

                        if (!userAuthorizationService.CheckTenantAdminPolicyForLoggedInUser(subjectId, loggedInUserTenantId, (int)actionTenantId))

                            context.Result = new UnauthorizedObjectResult("user is unauthorized");

                    }

                    

                }

            }

            catch (System.Exception)

            {

            }

        }

    }

Startup configuration for MyGlobal Custom filter.

 services.AddControllers(configure =>

            {

                AuditConfiguration.ConfigureAudit(services, Configuration);

                AuditConfiguration.AddAudit(configure);

                configure.Filters.Add(new GlobalActionFilter());

            });

---------------------------------------------------------------------------

solution:

implement new class inherits from IFilterFactory and inject service for my global action filter inside.

public class AuthorizationFilterFactory : IFilterFactory

    {

        public bool IsReusable => false;

        public IFilterMetadata CreateInstance(IServiceProvider serviceProvider)

        {

            // manually find and inject necessary dependencies.

            var context = (IUserAuthorizationService)serviceProvider.GetService(typeof(IUserAuthorizationService));

            return new GlobalActionFilter(context);

        }

    }

}

then Edit startup configuration for as follow

  services.AddControllers(configure =>

            {

                AuditConfiguration.ConfigureAudit(services, Configuration);

                AuditConfiguration.AddAudit(configure);

                //configure.Filters.Add(new GlobalActionFilter());

                configure.Filters.Add(new AuthorizationFilterFactory());

            });

check sql server database for bulk data exist or not

 problem:

i have role with multiple policies and every policy have multiple permissions relates.

so i need to validate end user to insert new role with restriction to prevent repeate roles with the same policies and permissions.

solution:

create stored procedure for sql database to check if role (policies and related permissions) exists befor 

if exist the procedure will return the name for rule and application will show error message that the role is with the same permissions exists before you can use it.

1- create user defined type to pass to procedure which will be passed from application and checked by procedure.

Create Type RolePermissionType 

as Table (

policyid int,

TenantId int,

PermissionId int

)

------------------------------------------------------------------------------------------------------------------- 

2-create the procedure based on input with type RolePermissionType.

 

Create Procedure [dbo].[sp_Check_RoleExist](@Mypermission RolePermissionType readonly  , @RoleNameOut nvarchar(200) OUTPUT) as 

begin

declare @itemsCount int =(select count(*) from @Mypermission);

declare @QueryCount int ;

declare @RoleId int;

declare @RoleName nvarchar(200)

PRINT '@itemsCount = ' + CONVERT(varchar(10), @itemsCount)

DECLARE db_cursor CURSOR FOR 

SELECT roleId ,a.Name

FROM [PolicyServerDb].[dbo].[PermissionAssignments] ps

inner join [PolicyServerDb].dbo.Roles a on a.id  =ps.RoleId and a.PolicyId=32

group by RoleId ,Name

having count(roleId) =@itemsCount

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @RoleId ,@RoleName

WHILE @@FETCH_STATUS = 0  

BEGIN

set @QueryCount =(

select count(*) from

(

     select  PolicyId,TenantId,PermissionId from [PolicyServerDb].[dbo].[PermissionAssignments]

where RoleId=@RoleId

  intersect select PolicyId,TenantId,PermissionId from @Mypermission

)as x )

if(@QueryCount = @itemsCount)

set @RoleNameOut = @RoleName

else

set @RoleNameOut=''

FETCH NEXT FROM db_cursor INTO @RoleId ,@RoleName

END 

CLOSE db_cursor  

DEALLOCATE db_cursor 

end

-------------------------------------------------------------------

3-C# function which returns rule name in case roleExists

   public string GetRoleNameByPermissionAssignments(PackageViewModel model)

        {

            string rolenameout = string.Empty;

            DataTable dt = new();

            dt.Clear();

            _ = dt.Columns.Add("policyid");

            _ = dt.Columns.Add("TenantId");

            _ = dt.Columns.Add("PermissionId");

            foreach (PolicyPermissions item in model.PolicyPermissions)

            {

                foreach (SimplePermission permission in item.permissions)

                {

                    _ = dt.Rows.Add(item.PolicyId, model.TenantId, permission.PermissionId);

                }

            }

            using (PolicyServerDbContext context = new())

            {

                SqlParameter Par1 = new("@Mypermission", dt)

                {

                    TypeName = "dbo.RolePermissionType",

                    Direction = ParameterDirection.Input

                };

                SqlParameter Par2 = new("@RoleNameOut", rolenameout)

                {

                    SqlDbType = SqlDbType.NVarChar,

                    Size = 200,

                    Direction = ParameterDirection.Output

                };

                _ = context.Database.ExecuteSqlRaw("exec dbo.sp_Check_RoleExist @Mypermission={0}, @RoleNameOut={1} out", Par1, Par2);

                if (Par2.Value != DBNull.Value)

                {

                    rolenameout = (string)Par2.Value;

                }

            }

            return rolenameout;

        }

if return empty string this meaning that role with passed permissions not exists.

DTO Passed To Function:

 public class PackageViewModel

    {

            public int RoleId { get; set; }

            public string RoleName { get; set; }

            public string PolicyId { get; set; }

            public string Tenant { get; set; }

            public string TenantId { get; set; }

            public string TenantProtected { get; set; }

            public string Description { get; set; }

            public bool AssignForChildTenant { get; set; }

            public int SelectedPolicyId { get; set; }

            public List<PolicyPermissions> PolicyPermissions { get; set; }

    }

 public class PolicyPermissions

    {

        public int PolicyId { get; set; }

        public int ParentId { get; set; }

        public string PolicyName { get; set; }

        public List<SimplePermission> permissions { get; set; }

    }

    public class SimplePermission {

        public int PermissionId { get; set; }

        public string PermissionName { get; set; }

    } 

Git Update Local Repositories.bat get remote branches to visual studio

  to update local repositories for Git Branches

create batch file in solution root folder then run the following command.

git remote update origin --prune