Create and Read Excel File in C#

Hello guys 🙂

It is very common for applications to have import or export in excel format. There are plenty options that you can use to read or write an excel file in .net. Today I am going to introduce you two simple libraries that are open source and can help you in reading and writing excel files in any type of .net applications.

 

Here is a simple code that create an excel file by using EPPluslibrary, you can download the full source code directly from my git-hub

Before you start coding you need to make sure that you have imported these libraries :

 


using Excel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

Creating Excel:


public string CreateExcelFile(DirectoryInfo outputDir)
{

FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
if (newFile.Exists)
{
newFile.Delete();  // ensures we create a new workbook
newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Student Lists");
//Here  column to  the current sheet
worksheet.Cells[1, 1].Value = "Title";
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 3].Value = "Family";
worksheet.Cells[1, 4].Value = "Student Code";

//Add value to celles

worksheet.Cells[2, 1].Value = "Mr.";
worksheet.Cells[2, 2].Value = "Mehran";
worksheet.Cells[2, 3].Value = "Janfeshan";
worksheet.Cells[2, 4].Value = "ST54516";

worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
//Save and export excel file

package.Save();

}

return newFile.FullName;
}

Reading Excel :


public DataSet ReadExcel()
{
string filePath;
filePath = "C:\\sample1.xlsx";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result;
}

Please contact me if you have any issue with the code.

What is Git and why should I use Git?

Hey Guys

I believe, If Microsoft starts working on something or if it buys a start-up company you must take it serious. We all know that Microsoft left behind in so many things same as Mobile, tablet and even Cloud that’s why it is trying to buy new companies or include new technologies inside its product . Any way I want to talk about Git as Microsoft supports it.

Let see what Git is:

I am sure most of you heard about source control if even you have not used it yet. Source control is not a new term in developing and programing world. Developers always were looking for a tool that can help them to keep track of the changes on the code and help them to control the version of application.  Demands for source control became more and more as IT industry expanded its own in every angle of our life. And development companies needed source control to let different member of a team work in one project at same time to develop it faster. Microsoft came with Source Safe and then upgrade it to TFS (team foundation server) and of course other companies came with other source control same as PVSC, Vault, and Perforce and etc.  I can say all of these products were awesome and still TFS is one the first choice for most of Microsoft based developer. But as a developer we always must look around for better options. One of the best option is Git. Git is an open source, source control which is developed by founder of Linux (Linus Torvalds) at that time he wanted to have a source control with special features for linux development team, finally he found out that there is no such product in the market so he decided to build his own source control and finally he came with Git, if you want to read more about the history of Git you can find it here.

Basically there are two types of source code control

  1. Centralized source control same TFS and source Safe.
  2. Distributed source control same Git.

In centralized source control live connection to the server is mandatory for any check in and check out and all clients must get the latest version from centralized repository. But on the other hand in distributed source control, each client has all source code plus all the histories of source code, and also each client has own repository, but it does not mean that there is no centralized repository.

I should say that the logic behind centralized and distributed is totally different, that’ why when someone like me wants to move from TFS to Git, he may feel uncomfortable and I am sure you may tell yourself that TFS is much more better. I should say that this kind of feeling when you are moving from current source control to Git is very normal. But once you understood the concept of Git, you can see that Git is much more better than your current source control and it will give you much freedom and features that you cannot have it in an application same as TFS. First I wanted to record a video and show you how to use TFS but later I found a very good recorded video and I decided to post the link here, I suggest you before start any search about Git, watch this video. It can help you a lot and you may not need any other tutorial.

Video Link :

https://www.microsoftvirtualacademy.com/en-us/training-courses/using-git-with-visual-studio-2013-jump-start-8306

If you have any question you can leave a comment here or always you can contact me via email:

janfeshan.mehran@gmail.com

bye bye

Why Node.js?

Hey Guys,

I decided to start a tutorial about node.js. I know sounds is boring because there are plenty tutorials outside which can help you to learn. but in this tutorial I want to start differently. I want to start developing a real application. but we start it slowly and I will try to post  a few times per day. before we start lets say why node.js ?

My answer to this questions is : because node.js is very cool. hahaha… but if you really want to know why node.js,  you can look at this web page, this is a really good explanation about node.js

Why node.js?

Any way, after you read Why Node.js, please do not waste your time to search about it. if you really want to know more about it, you only have one choice:

  1. Move your ass and work on it … hahahah

How we can install Node.Js?

Ok, this a first question that you may ask and it is right question. to install node.Js, Please click Node.Js  and click on the Green INSTALL button ( I hope you can see it there !!!!! 😉  ). Just follow the install instruction and install the Node.Js,

Note: if you do not know how to install this app please turn off your PC and do something else. because installing node.js is as easy as installing Adobe Acrobat Reader 5.5.

How can check whether the Node.Js is installed properly or not?

it is a good questions. to check Node.Js installed properly or not, please open command prompt and type node -v

In response you may get something like this :

Node Version Control

I think for now is enough, I will try to post new things :

Bye bye

How Use Cisco VPN Client on windows 10?

Hey Guys,

I am sure there are plenty users outside that are still using Cisco VPN Client. I should say that by default Cisco VPN Client does not work on windows 10. you may get different errors after upgrade to windows 10. regardless the error that you are getting, please follow the below instruction to run your Cisco VPN Client without any problem.

  1. Remove Cisco VPN Client.
  2. Install sonic64
  3. Restart your Machine
  4. .Install Cisco VPN Client
  5. You need to change some values in registry:
    HKEY-LOCAL-MACHINE/SYSTEM/ CurrentControlSet/ Services/CVirtA
    please look for Displayname ( it is a key in right side panel)
  6. Open the DisplayName and change the value to :
    Cisco Systems VPN Adapter for 64-bit Windows
    by default value maybe be like this :
     %@12393^0TCisco Systems VPN Adapter for 64-bit Windows
    it means you need to remove all extra characters in front of “Cisco Systems VPN Adapter for 64-bit Windows”
    after that you can use the Cisco VPN Client happily.

Note :

  1. Just remove those funny characters in Displayanme not other things.
  2. This solution works for most of the errors that you may get after upgrading to windows 10 but still I cannot guaranty that it works for all.
  3. If you do not have sonic64, please send email to me : janfeshan.mehran@gmail.com, I will try to send it to you ASAP
  4. I suggest you to uninstall Cisco VPN Client before you upgrade to windows 10. it can cause problem in some Machines same as HP Omen.

I hope this can help you.

How can merge two datatables?

Here I will teach you how can merge two datatables :
You can whatch the video here

 Private Sub MergeDatatable()

        Dim dt1 As New DataTable
        Dim dt2 As New DataTable

        dt1.Columns.Add("Id")
        dt1.Columns.Add("Name")
        Dim dtrow1 As DataRow
        dtrow1 = dt1.NewRow
        dtrow1("Id") = 12
        dtrow1("Name") = "Mehran"
        dt1.Rows.Add(dtrow1)



        dt2.Columns.Add("Id")
        dt2.Columns.Add("Name")
        Dim dtrow2 As DataRow
        dtrow2 = dt2.NewRow
        dtrow2("Id") = 12
        dtrow2("Name") = "Nush"
        dt2.Rows.Add(dtrow2)


        dt1.Merge(dt2)

    End Sub

Click here to convert this Vb.net code to C#

How can retrieve data from Json source and show them on the page?

Maybe it is very difficult for you to connect to the Json file and retrieve data, today I want to make it easy for you 🙂 Lets start.

Follow these steps:

  1. We need a Json file for the source that produces Json data for us. To make a Json file please follow these steps :
    1. Make a new  text file and copy this information to the file
"one": "Singular sensation",

"two": "Beady little eyes",

"three": "Little birds pitch by my doorstep"
    1. Change the file extension to the Json
    2. Now you have Json file that contains data , please copy the file in your project ( I assumed you are working in Visual studio )
    3. There two types of instructions that you can follow to read a Json file , Today I want to use $.getJSON (This a JQuery function)
<script>

    $(document).ready(function () {

        $.getJSON("Data/info.Json.", function (data) {
        var items = [];
        $.each(data, function (key, val) {
            items.push("<li id='" + key + "'>" + val + "</li>");
        });

        $("<H1>", {
            "class": "my-new-list",
            html: items.join("")
        }).appendTo("body");
    });
    })

</script>

GetJSON hast two parameters

  • Source of JSON , it could be URL or the address of JSON file
  • The function that gets data from JSON source

        $.getJSON("Data/info.Json.", function (data) {

4.  Then we should add data to the array


        $.each(data, function (key, val) {
            items.push("<li id='" + key + "'>" + val + "</li>");
        });

</script>

5.  And in the last step we should add the HTLM codes that we generated in the previous step to the body tag.


        $("<H1>", {
            "class": "my-new-list",
            html: items.join("")
        }).appendTo("body");
    });

And do not forget to add JQuery reference to your code , it coule be like this :

<script src="~/Scripts/jquery-ui-1.8.20.js"></script>
<script src="~/Scripts/jquery-1.7.1.js"></script>

What is JSON ?

JSON (JavaScript Object Notation) is a lightweight data-structure format for data interchange, JSON could be read by human and it is easy for machine to generate and pars it.

When should we use JSON?

Assume you want create a service that retrieve data from DataBase and send them to the client. What will happen if your client use different type of machines and platform same as Smart phone, PHP website, ASP.net Website, different type of operating system. There are two solutions:

  1. Create different services for different machines and platforms (is it economic?)
  2. Create a service that produces a global output that is readable by different machines and platforms.

I am sure you are agree with me that the second solution is more logical.

In my opinion WebAPI could be a good choice for our scenario because, it can produce JSON output that is readable by all machines and platform and also it uses HTTP to transfer data that, also this protocol is well known for all.

Today I am just going to show you how you can create a simple JSON data structure but, In the future I will show you how create a WebAPI and call it by JQuery.

This is simple JSON sample, I declare an employee array that can carry different employees’ data.

{
"employees": [
{ "firstName":"Mehran" , "lastName":"JN" }, 
{ "firstName":"Arman" , "lastName":"Nas" }, 
{ "firstName":"Farzam" , "lastName":"ABS" }
]
}

I am going to do it in a JavaScript code:

<!DOCTYPE html>
<html>
<body>
<h2>JSON Object Creation in JavaScript</h2>

<p>
Name: <span id="Name"></span><br>  
Family: <span id="Family"></span><br> 

</p>  

<script>
var JSONObject = {
  "Name":"Mehran",
  "Family":"Jan"};
document.getElementById("Name").innerHTML=JSONObject.Name  
document.getElementById("Family").innerHTML=JSONObject.Family 

</script>

</body>
</html>