Upload & Read Excel File

This article demonstrate

1) How to upload the file in MVC

2) Read excel file content

How to upload the file in MVC

In your MVC razor view add the below html content

@using(Html.BeginForm("Upload", "Home", FormMethod.Post, new {enctype = "multipart/form-data"}))
{
    <table>
        <tr>
            <td>File:</td>
            <td>
                <input type="file" name="UploadedFile" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input type="submit" name="Submit" value="Submit" />
            </td>
        </tr>
    </table>
}

Notice, the action name : Upload, Controller Name: Home

Now come to home controller and add action called Upload

 public ActionResult Upload(FormCollection formCollection)
        {
            if (Request != null)
            {
                HttpPostedFileBase file = Request.Files["UploadedFile"];

                if ((file != null) && (file.ContentLength &gt; 0) && !string.IsNullOrEmpty(file.FileName))
                {
                    string fileName = file.FileName;
                    string fileContentType = file.ContentType;
                    byte[] fileBytes = new byte[file.ContentLength];
                    var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));

		}
	    }
	}

Now try to run the app by putting a breakpoint and see if all is working until now

Now add dll called EPPLUS from NUGET which is used for reading and writing files.

Lets say I have file with FirstName & LastName

Now add a class called users

public class Users
{
      public string FirstName { get; set; }

       public string LastName { get; set; }
}

Now lets modify our action method to read the file stream object which we uploaded. Add using OfficeOpenXml; statement


 public ActionResult Upload(FormCollection formCollection)
        {
            if (Request != null)
            {
                HttpPostedFileBase file = Request.Files["UploadedFile"];

                if ((file != null) && (file.ContentLength &gt; 0) && !string.IsNullOrEmpty(file.FileName))
                {
                    string fileName = file.FileName;
                    string fileContentType = file.ContentType;
                    byte[] fileBytes = new byte[file.ContentLength];
                    var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));

                    var usersList = new List&lt;Users&gt;();

                    using (var package = new ExcelPackage(file.InputStream))
                    {
                        var currentSheet = package.Workbook.Worksheets;
                        var workSheet = currentSheet.First();
                        var noOfCol = workSheet.Dimension.End.Column;
                        var noOfRow = workSheet.Dimension.End.Row;

                        for (int rowIterator = 2; rowIterator &lt;= noOfRow; rowIterator++)
                        {
                            var user = new Users();
                            user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString();
                            user.LastName = workSheet.Cells[rowIterator, 2].Value.ToString();
                            usersList.Add(user);
                        }


                    }
                }
            }

            return View("Index");
        }

Finally all the users will be available in usersList object.

Happy Kooding…. Hope this helps!!

Example on Read & Write to Dictionary in JAVASCRIPT

Below sample code demonstrate on how to read/write dictionary in JS world.

Creating & Adding data to Dictionary

var workOrderDictionary = []; // declare dictionary

var companyId1 = 1;
var orders1 = [{ ProductId: 10, ProductName: Pen } , {ProductId: 20, ProductName: Eraser} ]
workOrderDictionary.push({ key: companyId1, value: orders1 });

var companyId2 = 2;
var orders2 = [{ ProductId: 101, ProductName: Lunch-Box } , {ProductId: 20, ProductName: Bottle } ]
workOrderDictionary.push({ key: companyId2, value: orders2 });

Reading the data from Dictionary

for (var key in workOrderDictionary) {
	if (workOrderDictionary.hasOwnProperty(key)) {
	    var orders =	workOrderDictionary[key];
	}
}

Happy Kooding… Hope this helps…!

LINQ LIST: Distinct method extension

While operating with collections, sometime we would like to perform Distinct on column, to achieve this we need to write the extension method.


public static class StaticDistinct
{
       public static IEnumerable<TSource> DistinctBy<TSource, TKey>
        (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
        {
              HashSet<TKey> knownKeys = new HashSet<TKey>();
              foreach (TSource element in source)
               {
                    if (knownKeys.Add(keySelector(element)))
                    {
                         yield return element;
                    }
                }
          }
 }

// USAGE
var distinctUsers = users.DistinctBy(x => x.EmailId).ToList();

This example also demostrate what are extension methods and syntax of extension method.

Extension Methods:

#1) These are static methods, so these method should be surronded by static class

#2) These method signature would start this keyword. Let’s say you want to write extension method to string then the method would be some thing like below.


// DEFINE EXTENSION METHOD
 public static string ImExtensionMethod(this string value)
 {
   return "Hurray! I am EXTENSION method";
 }

//USAGE
  string whoAreYou = "Hello";
  whoAreYou.ImExtensionMethod();

By now you should be knowing why did we define DISTINCTBY as extension method

Happy Kooding…. Hope this helps!

ORACLE: Looping through data using CURSORS

Below the syntax for creating the cursors in Oracle. This example also demostrates creating of procedures in Oracle


CREATE OR REPLACE PROCEDURE MYSCHEMA.GET_EMAILS AS

 EMAIL_ID VARCHAR2(100);
 
 CURSOR CUR IS SELECT EMAIL_ADDRESS FROM MYSCHEMA.USERS; /* DEFINE CURSOR */
 
BEGIN
 
 OPEN CUR; /* OPEN THE CURSOR */
 
 LOOP /* LOOP */
 
 FETCH CUR INTO EMAIL_ID; /* READ THE CURSOR VALUE */
 
 EXIT WHEN CUR%NOTFOUND; /* EXIT WHEN DATA NOT FOUND */
 
 DBMS_OUTPUT.PUT_LINE(EMAIL_ID); /* PRINT THE VALUE / PROCESS ACCORDING TO YOUR NEED */
 
 
 END LOOP; /* END OF LOOP */
 
 CLOSE CUR; /* FINALLY RELASE/CLOSE THE CURSOR */
END;
/

Happy Kooding.. Hope this helps!

Error: multiple actions were found that match the request

If this error is happening with Web API then below is the reason

Usually App_Start/WebApiConfig contains route as


config.Routes.MapHttpRoute("DefaultApi", "api/{controller}/{id}", new { id = RouteParameter.Optional });

So change the above configuration to


config.Routes.MapHttpRoute("DefaultApi", "api/{controller}/{action}/{id}", new { id = RouteParameter.Optional });

Happing Kooding… !

How to use Kendo Validator / Kendo Validation

For example, I have form which has Salary as input field, now I would like to perform custom validation on this field.

HTML


<div id="DataEntryDiv" style="margin-top:20px">
<div class="editor-label">Salary</div>
<div class="editor-field">
<input id="Salary" type="number" class="input" name="Salary" data-bind="value: Salary" />
</div>
</div>

JAVASCRIPT


<script type="text/javascript">
var myValidator = $("#DataEntryDiv").kendoValidator({

rules: {
SalaryRule: function (input) {
if (input.is("[name=Salary]")) {
return !(parseInt($("#Salary").val()) <= 0);
}
return true;
}
},
messages: {
SalaryRule: "Value must be greater than 0",
},
validateOnBlur: false
}).data("kendoValidator");

function onSave(e) {
if (!myValidator.validate()) {
e.preventDefault();
}
}
</script>

This works with Kendo Grid Pop as well. All you have to do is hook up the save event for kend Grid


@(Html.Kendo().Grid<MyModelClass>()
.Name("myKendoGrid")

.Events(e => e.Save("onSave"))

Happy Kooding… Hope this helps!