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…!

Tips/Tricks: Javascript or JQuery

1) How to get difference between 2 days in days using Javascript


var noOfDays = (finishDate - startDate) / (1000 * 60 * 60 * 24);

2) Remove Last Comma from a string

var str = commaInLastStr.replace(/,\s*$/, "")

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!

Tips/Tricks about KENDO

1) KENDO GRID: At anygiven time, if you want to access the edited or added data (before reload of grid/save)

var editableModel = $('#myGrid').data().kendoGrid.editable.options.model;

2) KENDO GRID: If you would like to update one of the property value manually for recent edited record in grid then


var editableModel = $('#myGrid').data().kendoGrid.editable.options.model;
editableModel.set("dobDate", '05/31/1983');

3) MONTH PICKER: Implementing month picker with Kendo. This would work with Grid Edit Pop also.

<input id="dobDate" type="date" class="input" name="dobDate" data-bind="value: dobDate" />
$(document).ready(function () {
 

$("#dobDate").kendoDatePicker({
start: "year",
depth: "year",
format: "MMMM yyyy",
parseFormats: ["MMMM yyyy"]
});


});

Month Picker cannot be achieved with Model binder as there is a known bug. The only available is the above

4) KENDO GRID: Format/Parse the date column (bound column)

columns.Bound(m =&amp;gt; m.DobDate).Width(50).Title("Dob Date").ClientTemplate("#= DobDate &amp;lt; 1/1/1900 ? '' : &nbsp; kendo.format('{0:MMMM yyyy}', DobDate) #")

5) KENDO DATE PICKER: Disable the control by editing it

<input id=”dobDate” type=”date” class=”input” name=”dobDate” onkeydown=”return false;”  data-bind=”value: dobDate” />

6) KENDO FILTER OPERATORS: Below are the few filter operators

  filter.push({ field: "IsNew", operator: "eq", value: 1 });

eqgtgteltteneq

7) READONLY:

 @(Html.Kendo().DatePicker().Value(DateTime.Now)
 .Name("myDate").Min(DateTime.Now)
 .HtmlAttributes(new { style = "width: 100%", @readonly="readonly" }))

or


$("#myDate").kendoDatePicker(); 

var myDate= $("#myDate").data("kendoDatePicker"); 

myDate.readonly(true);

8) NUMERIC TEXTBOX: Don’t allow decimal value and don’t allow negative value (define min value)

a) format allows you to ignore/avoid entering decimal value

b) min allows you to set the min value to zero


$("#Hours").kendoNumericTextBox({ decimals: 0, format: "#", min: 0 });

9) HIDE/SHOW KENDO DROPDOWNLIST: Below is the syntax to hide/show kendo dropdownlist
HIDE:

 $("#ddlCountries").closest(".k-widget").hide();

SHOW:

 $("#ddlCountries").closest(".k-widget").show();

10) Kendo Grid Filter Configuration : For a Kendo Grid, if you would like to customize the filter menu below is how you can do


@(Html.Kendo().Grid<UserModel>()
    .Name("MyGrid")
    .ColumnMenu()
    .Filterable(f => f.Operators(o => o.ForString(s => s
        .Clear()
        .Contains("Contains")
        .DoesNotContain("Does not contain")
        .EndsWith("Ends with")
    )))
// other configuration goes
)

If you would like to customize to a specific column then you would do same above filter configuration to a column rather than entire grid


@(Html.Kendo().Grid<UserModel>()
    .Name("MyGrid")
    .Columns(columns =>
          {
              columns.Bound(m => m.UserName)
              .Filterable(f => f.Operators(o =>
              o.ForString(s => s.Clear().Contains("Contains")
              .DoesNotContain("Does not contain")
              .EndsWith("Ends with")
    )))

})
)

11) Kendo Grid Clear Selected Row :


            var grid = $("#myGrid").data("kendoGrid");
            grid.clearSelection();

Happy Koooding…..  Hope this helps!….