Showing posts with label C#. Show all posts
Showing posts with label C#. Show all posts

Wednesday, March 23, 2016

Working with Microsoft Excel using C#

Library

To programmatically access MS Excel, you will need Microsoft Excel Extension library usually comes with Visual Studio: Microsoft.Office.Interop.Excel. I usually include it in header like this:

    using Excel = Microsoft.Office.Interop.Excel;

Open an Excel file
 
You can code like this:
  
  var app = new Excel.Application();
  var book = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

Or simply like this:

     var book = app.Workbooks.Open(@"c:\test\data.xlsx");

Access a sheet, Range and cell

To access a sheet, say the first sheet (please note sheet index is 1-based rather than 0-based):

  var sheets = book.Worksheets as Excel.Sheets;
  var sheet = (Excel.Worksheet)sheets.get_Item(1);

To reach to a cell, you will do it through excel object Range, like this:

  var range = sheet.UsedRange;

Or for multiple cells:

  var range  = (Excel.Range) sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[3,3]);

Or for single cell:

  var range = (Excel.Range) sheet.Cells[1, 1];

You got it, they are all 1-based. To access a cell:

 var range = sheet.UsedRange;
 for (int i = 1; i <= range.Rows.Count; i++)
 {
    for(int j=1; j <= range.Columns.Count; j++)
    var s = (string) (range.Cells[i, j] as Excel.Range).Value2;
               // ProcessString(s);
 }

Clean up


Make sure to clean up after use. Here's how and don't forget to add try catch around it.


 book.Close(false, Type.Missing, Type.Missing);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(book);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);

Tuesday, November 4, 2014

Simple way to compress Web API Response

There are many ways to do Web API Response compression. I particularly like Radenko Zec's solution mostly because it's simple and efficient.

A few steps I did in my project:
  •  Pick your favorite compression tool. I used DotNetZip as Radenko recommended. It gives me close to 85% compression rate for JSON. A CompressionHelper is used as well so that I can easily change to a different compression tool if I want.
  • Implement DeflateCompressionAttribute. Mostly Radenko's code:
    public class DeflateCompressionAttribute : ActionFilterAttribute
    {
        public override void OnActionExecuted(HttpActionExecutedContext actContext)
        {
            var content = actContext.Response.Content;
            var bytes = content == null ? null : content.ReadAsByteArrayAsync().Result;
            var compressedContent = bytes == null ? new byte[0] : CompressionHelper.Compress(bytes);
            actContext.Response.Content = new ByteArrayContent(compressedContent);
            actContext.Response.Content.Headers.Remove("Content-Type");
            actContext.Response.Content.Headers.Add("Content-encoding", "deflate");
            actContext.Response.Content.Headers.Add("Content-Type", "application/json");
            base.OnActionExecuted(actContext);
        }
    }
  • Add [DeflateCompression] attribute to any WebAPI method needs compression
  • Decompress using standard HttpClientHandler. As our Web API client is expecting objects, we configure HttpClientHandler to decompress response:
            using (var client = new HttpClient(new HttpClientHandler(){AutomaticDecompression = DecompressionMethods.Deflate|DecompressionMethods.GZip}))
            {
                var para = new ServiceParams();
                client.BaseAddress = new Uri(_serviceUrl);
                client.DefaultRequestHeaders.AcceptEncoding.Add(new StringWithQualityHeaderValue("gzip"));
                client.DefaultRequestHeaders.AcceptEncoding.Add(new StringWithQualityHeaderValue("deflate"));
                HttpResponseMessage response =
                    client.PostAsync(_servicePath, para, new JsonMediaTypeFormatter()).Result;
                r = response.Content.ReadAsAsync<ServiceResponse>().Result;
            }

Tuesday, July 10, 2012

Claim-Based Authorization for ASP.NET MVC

As WIF becoming an official part of .NET 4.5, more attentions have been paid to integrate it with other .NET technologies, like MVC or Azure. Many consider WIF as a Claim-based authentication standard, actually it also comes with a well-rounded Claim-based user authorization mechanism. In this post, I'll discuss the following topics to show how easy to apply Claim-based authentication to ASP.NET MVC, and how powerfully and flexible it can be compare to standard Role-based authentication:
  • Claims Authorization Manager
  • Apply to ASP.NET MVC
Claims Authorization Manager

WIF provides a claims authorization manager for 'an extensibility point from which you can authorize access to a resource based on the claims presented in a token, before your RP application is called (from msdn)'.  I personally like it because the following reasons:
  1. It makes more factors available for consideration as to doing access control logic, like Resources, Operations, Principle Claims(including Roles), all of which enable more detailed access control than regular Role-based access control.
  2. When used with .NET Attributes, it provides a clean separation between the code to implement a feature and the code to implement access control. See sample code below.
To start, I usually list all the resources and any operation supported by my application, and define them as constants which can be used later by Attributes. To build custom ClaimAuthorizationManager, simply extend Microsoft.IdentityModel.Claims.ClaimsAuthorizationManager, and override CheckAccess Method. It is there where you can implement your own access logic basing on resources, operations and claims.

 public class  MyClaimAuthorizationManager : ClaimsAuthorizationManager
    {
        public override bool CheckAccess(AuthorizationContext context)
        {
            var claimsId = context.Principal.Identity as IClaimsIdentity;
            if(claimsId==null || !claimsId.IsAuthenticated)
            {
                return false;
            }
            var resource = context.Resource.First().Value;
            var operation = context.Action.First().Value;
            
            // Do your access control logic here
            // ...
            return false;
         }
    }

To use custom authorization manager, just define it in claimsAuthorizationManager section in web.config. Something like this:

 <microsoft.identityModel>
    <service>
      <claimsAuthorizationManager type="MyNameSpace.MyClaimAuthorizationManager">
            <policy resource="http://localhost/MyService.svc" action="GET">
                   <claim claimType="http://schemas.xmlsoap.org/ws/2005/05/identity/claims/dateofbirth" minAge="21" />
             </policy>
     </claimsAuthorizationManager>
      ...
    </service>
</ microsoft.identityModel>

Please note, configuring claim authorization manager policies is another way to define your access control logic. It's good to use in a way that it requires less code changes when access logic changed. But it's relatively rigid, so not my favorite. 

Apply to ASP.NET MVC

Two things need to be handled before you can fully enjoy the claim-based access control for MVC. First, make sure your controller responsive to System.Security.SecurityException. This can be achieved by applying HandleError Attribute to controller, so MVC knows which view to go in the case of an access attempt is denied. Of course, the same attribute can be applied at Controller Action level for more fine-grained access-deny handling. Secondly, make sure your application supports custom Error handling. This can be achieved by setting customError mode to ‘On’ in web.config.

<system.web>
    <customErrors mode="On" defaultRedirect="Home/Logout"></customErrors>
    …
</system.web>

Now you can apply ClaimsPrincipalPermission attribute to any Controller Actions you plan to do access control. Just make sure to provide correct resource(s) and operation as parameters, because they will be used by claim authorization manager to make access control decision.

    [HandleError(ExceptionType = typeof(SecurityException), View = "Home/Login")]
    public class MyController : Controller
    {
       [ClaimsPrincipalPermission(SecurityAction.Demand, Resource = MyResources.REPORTS_ACCESS, Operation = MyOperations.GET)]
        public ActionResult ViewReport()
        { 
           ...
            return View();
        }
         ...
    }

Similar authorization control can also be used for AJAX Web Services. In this case, simply apply ClaimsPrincipalPermission Attribute to Web Method. Make sure AJAX client to handle the security exception once an access attempt is denied.