Create TreeView in JQuery using jsTree Plugin from flat JSON Data

In my previous article Create TreeView using jsTree Plugin and nested JSON Data we have seen how to create treeview structure using jsTree plugin and a nested JSON data.

This article will explain on creating Tree-View structure using jsTree plugin and a flat JSON data. This flat JSON data structure is more easy to implement rather than the nested structure as its very easy to get the data from database and hold it inside a custom list and just deserialize it into a flat JSON with some parent id.

Follow my previous article and setup the project to use jsTree plugin, once you set it up we are all set to consume the flat JSON data structure to use in the project.

Find out all the articles on jsTree here.

Tree View Example


View Live Demo


Creating container for the jsTree


Here is the html container that will use the flat JSON structure to create the jsTree.
<div class="panel panel-primary">
       <div class="panel-heading">
           <h3 class="panel-title">Flat JSON Example Data</h3>
       </div>
       <div class="panel-body">
           <div class="m-b-10">
               <asp:TextBox ID="txtSearchAPI" runat="server" placeholder="Serch JSTree" CssClass="search-input form-control"></asp:TextBox>
           </div>

           <div id="jstree-api">
           </div>
       </div>
       <div id="footer-api" class="panel-footer">You have not selected any node</div>
   </div>

 Now lets feed the jsTree with JSON Data


The jsTree will work on only JSON data and it has two predefined structure, in which the JSON will be accepted.
  1. JSON with nested elements (parent-children structure)
  2. JSON with flat structure (with parent id structure)
The first one JSON with nested elements is a bit difficult to produce when you are using a database so I prefer using the second one as its pretty simple and easy because of its flat structure.



Below is the flat JSON format. This JSON format is easy to generate from backend; be it C# or PHP.
var jsonData = [
       { "id": "1", "parent": "#", "text": "Folder 1" },
       { "id": "2", "parent": "1", "text": "Sub Folder 1" },
       { "id": "3", "parent": "2", "text": "Sub - Sub Folder 1" },
       { "id": "4", "parent": "1", "text": "Sub Folder 2" },
       { "id": "5", "parent": "#", "text": "Folder 2" },
    ];
The jquery code for loading the jsTree is same for nested JSON and flat JSON.
function LoadJSTreeWithFlatJSONData() {

    var jsonData = [
       { "id": "1", "parent": "#", "text": "Folder 1", type: "root" },
       { "id": "2", "parent": "1", "text": "Sub Folder 1", type: "child" },
       { "id": "3", "parent": "2", "text": "Sub - Sub Folder 1", type: "child" },
       { "id": "4", "parent": "1", "text": "Sub Folder 2", type: "child" },
       { "id": "5", "parent": "#", "text": "Folder 2", type: "root" },
    ];

    $("#txtSearchAPI").keyup(function () {
        var searchString = $(this).val();
        console.log(searchString);
        $('#jstree-api').jstree('search', searchString);
    });

    $('#jstree-api').jstree({
        'core': {
            'data': jsonData
        },
        "types": {
            "child": {
                "icon": "glyphicon glyphicon-leaf"
            },
            "root": {
                "icon": "glyphicon glyphicon-folder-close"
            },
            "default": {
                "icon": "glyphicon glyphicon-folder-close"
            }
        },
        "search": {

            "case_insensitive": true,
            "show_only_matches": true


        },

        "plugins": ["search", "themes", "types"]
    });

    $('#jstree-api').on('changed.jstree', function (e, data) {
        var objNode = data.instance.get_node(data.selected);
        var note;
        note = 'Selected Node Data(Id: <strong>' + objNode.id + '</strong>, Name: <strong>' + objNode.text + '</strong>)'; e = 'Selected Category(Id: <strong>' + objNode.id + '</strong>, Name: <strong id="api-data" data-parent="' + objNode.parent + '" data-id="' + objNode.id + '">' + objNode.text + '</strong>)';

        $('#footer-api').html(note);
    });

    $('#jstree-api').on('open_node.jstree', function (e, data) {
        data.instance.set_icon(data.node, "glyphicon glyphicon-folder-open");
    }).on('close_node.jstree', function (e, data) { data.instance.set_icon(data.node, "glyphicon glyphicon-folder-close"); });
}

View Live Demo

That's it you have created a jsTree with nested JSON data. Check out my next article on creating jsTree with flat JSON data. And all articles on JsTree here.

Happy Codding...

Create Tree-view in JQuery using jsTree Plugin From Nested JSON Data

Treeview is a structure we mostly use to represent parent-children data for better observation.

But when it comes to ASP.NET development there is a server side control named treeview for it. But I am sure it's not that efficient now-a-days when everything is being done in front-end side by JavaScript frameworks we never gonna use this one.
There are many Jquery plugins available in the internet to create TreeView but I find jsTree very intuitive and easy to use.

There are few articles a good documentation on it in their site but I still found those not sufficient enough to get started with jsTree when you are using it for the first time. So I came up with an idea to create a series of blogs on jsTree that can help people who are trying to figure it out and want to use jsTree in their ASP.NET applications.

Find out all the articles on jsTree here.

Tree View Example
Before we get into the details of jsTree and its implementations you need to download the js and css files for it. Download jsTree plugin from here.


View Live Demo


Setting up a Project and adding jsTree resources


Now create a project in visual studio and add the jstree-src folder inside it, that you got from the download.

Contents of jsTree folder



The folder has two themes one is default and one is a darker one. You can use those according to your need. Now lets add the js and css references to your web page.

<script src="js/jquery.min.js"></script>
<script src="jstree-src/jstree.min.js"></script>

<link href="jstree-src/themes/default/style.css" rel="stylesheet" />
You also need to add a jquery reference in your page in order to use the jsTree plugin.

Creating container for the jsTree


Below is the html I have used in my demo project, but you might not want to use the entire html. Just use the container to populate the jsTree.
<div role="tabpanel" class="tab-pane active p-20-0" id="preview" aria-labelledby="preview-tab">
        <div class="panel panel-primary">
            <div class="panel-heading">
                <h3 class="panel-title">Nested JSON Example Data</h3>
            </div>
            <div class="panel-body">
                <div class="m-b-10">
                    <asp:TextBox ID="txtSearchXML" runat="server" placeholder="Serch JSTree" CssClass="search-input form-control"></asp:TextBox>
                </div>

                <div id="jstree-xml">
                </div>
            </div>
            <div id="footer-xml" class="panel-footer">You have not selected any node</div>
        </div>
    </div>
You just need the below container for jsTree.
<div id="jstree-xml"> </div>

 Now lets feed the jsTree with JSON Data


The jsTree will work on only JSON data and it has two predefined structure, in which the JSON will be accepted.
  1. JSON with nested elements (parent-children structure)
  2. JSON with flat structure (with parent id structure)
The first one JSON with nested elements is a bit difficult to produce when you are using a database so I prefer using the second one as its pretty simple and easy because of its flat structure.



But in this article we will use the first json format i.e JSON with nested element to populate the jsTree. Below is the JSON format.
var jsonData = [
      {
          id: 1,
          text: "Folder 1",
          state: {
              selected: false
          },
          children: [
            {
                id: 2,
                text: "Sub Folder 1",
                state: {
                    selected: false
                },
                children: [
                     {
                         id: 3,
                         text: "Sub - Sub Folder 1",
                         state: {
                             selected: true
                         },
                         children: []
                     }
                ],
            },
            {
                id: 4,
                text: "Sub Folder 2",
                state: {
                    selected: false
                },
            }
          ]
      },
      {
          id: 5,
          text: "Folder 2",
          state: {
              selected: false
          },
          children: []
      }
    ];
Now we will initiate the jsTree with required attributes.
$('#jstree-xml').jstree({
        'core': {
            'data': jsonData
        },
        "types": {
            "child": {
                "icon": "glyphicon glyphicon-leaf"
            },
            "root": {
                "icon": "glyphicon glyphicon-folder-close"
            },
            "default": {
                "icon": "glyphicon glyphicon-folder-close"
            }
        },
        "search": {

            "case_insensitive": true,
            "show_only_matches": true


        },

        "plugins": ["search", "themes", "types"]
    });
If you look into the above code you can find below attributes

core: this is taking the json data in the variable data. this is where we will attach ajax functions in our next articles to feed the jstree with json data.




type: this is the attribute that is responsible for styling nodes. If a node is of type root then the defined style will be applied to it and if of child then the specified style for child type will be added to that node.

search: this says about the search feature options if its case sensitive or not and all.

In the next section we will set up a search feature in the jsTree and the code is below. I have taken a textbox and will search the jsTree on keyUp function. You can customize it according to your need. You may take a textbox and a button and on click of it you can perform a search or whatever you want.
$("#txtSearchXML").keyup(function () {
        var searchString = $(this).val();
        console.log(searchString);
        $('#jstree-xml').jstree('search', searchString);
    });
And  now we will write another function to get the selected node information. Below is the code to get the selected node information of the jsTree.
$('#jstree-xml').on('changed.jstree', function (e, data) {
        var objNode = data.instance.get_node(data.selected);
        var note;

        note = 'Selected Node Data(Id: <strong>' + objNode.id + '</strong>, Name: <strong>' + objNode.text + '</strong>)';

        $('#footer-xml').html(note);
    });
Here I am getting the node information and printing it on the panel footer.

If you are looking for the whole jquery function to load the jsTree with nested JSON data then here it is.
function LoadJSTreeWithNestedJSONData() {

    $('#jstree-xml').jstree({
        'core': {
            'data': jsonData
        },
        "types": {
            "child": {
                "icon": "glyphicon glyphicon-leaf"
            },
            "root": {
                "icon": "glyphicon glyphicon-folder-close"
            },
            "default": {
                "icon": "glyphicon glyphicon-folder-close"
            }
        },
        "search": {

            "case_insensitive": true,
            "show_only_matches": true


        },

        "plugins": ["search", "themes", "types"]
    });

    $("#txtSearchXML").keyup(function () {
        var searchString = $(this).val();
        console.log(searchString);
        $('#jstree-xml').jstree('search', searchString);
    });

    $('#jstree-xml').on('changed.jstree', function (e, data) {
        var objNode = data.instance.get_node(data.selected);
        var note;

        note = 'Selected Node Data(Id: <strong>' + objNode.id + '</strong>, Name: <strong>' + objNode.text + '</strong>)';

        $('#footer-xml').html(note);
    });
}

View Live Demo

That's it you have created a jsTree with nested JSON data. Check out my next article on creating jsTree with flat JSON data. And all articles on JsTree here.

Happy Codding...

Deserialize n-level(multi level) JSON data to C# custom object

Ever wondered how to deserialize a multi level(nested) JSON data where you are not sure of the level. It my be upto level 5 or upto n level.

I faced a similar situation where I have to fetch the product information from an API, exposed by an eCommerce site. And the JSON I got is of a multi level nested JSON.

I searched a lot in Google on how to hold that JSON data into a custom C# List of objects, but found no help. After a long research and trying my hands here and there I found the best solution.



Here is the JSO format I was getting from API.
{
   "Categories":[
      {
         "Code":"2984",
         "Name":"Baby",
         "Children":[
            {
               "Code":"100978",
               "Name":"Christening & Gifts",
               "Children":[
                  {
                     "Code":"100980",
                     "Name":"Baby Jewellery"
                  },
                  {
                     "Code":"100981",
                     "Name":"Ornaments"
                  },
                  {
                     "Code":"121628",
                     "Name":"Gift Baskets"
                  },
                  {
                     "Code":"139760",
                     "Name":"Christening",
                     "Children":[
                        {
                           "Code":"100979",
                           "Name":"Gifts"
                        },
                        {
                           "Code":"139764",
                           "Name":"Silverware"
                        },
                        {
                           "Code":"139765",
                           "Name":"Other Christening"
                        }
                     ]
                  },
                  {
                     "Code":"32871",
                     "Name":"Other Gifts"
                  }
               ]
            }
         ]
      },
      {
         "Code":"100982",
         "Name":"Baby Carriers/Backpacks"
      },
      {
         "Code":"1261",
         "Name":"Other Baby"
      },
      {
         "Code":"134282",
         "Name":"Walkers"
      }
   ]
}
Here you can see there is 3 level of children but this can go upto n-level. So the question is how your C# object structure should be to hold this type of JSO data.

There is a free tool available that will take your JSON and give the respective C# object structure. Here it is. http://json2csharp.com/

This tool give me the below class structure.
public class Child3
{
    public string Code { get; set; }
    public string Name { get; set; }
}

public class Child2
{
    public string Code { get; set; }
    public string Name { get; set; }
    public List<Child3> Children { get; set; }
}

public class Child
{
    public string Code { get; set; }
    public string Name { get; set; }
    public List<Child2> Children { get; set; }
}

public class Category
{
    public string Code { get; set; }
    public string Name { get; set; }
    public List<Child> Children { get; set; }
}

public class RootObject
{
    public List<Category> Categories { get; set; }
}
I am sure this is not the optimum solution to the problem because it can hold data only up to 3 level. If the hierarchy goes on increasing then this will fail to perform.



So if you are looking for an optimum solution and here it is.
public class MyRootCategory
  {
      public Node[] Categories { get; set; }
  }
  
  public class Node
  {
      public string Code { get; set; }
      public string Name { get; set; }
      public Node[] Children { get; set; }
  }
This class structure can handle n-level hierarchy. And is the optimum solution we are looking for.

The code below will deserialize the json string to an object of type MyRootCategory
  var objJSON = JObject.Parse(result);
  var objResponse = objJSON["Response"].ToString();

  MyRootCategory categoryTree = JsonConvert.DeserializeObject<MyRootCategory>(objResponse);
If you are looking for, how to save this n-level list to database by iterating then stay tuned to my next article

Till then Happy Codding....

Read XML file in ASP.NET(C#)

This is very often when we face such situations to read XML file and create custom objects of it. There might be many articles in the internet but as I came across this thing recently, I thought of sharing my experience with people who might find it helpful.

Alright, in this article I will read a XML file from C#(ASP.NET) and create a custom object from it and then use it according to my need.



Here is the XML file, that is generated from an eCommerce site and has some product information.
<?xml version="1.0"?>
<Products>
  <Product>
    <ProductId>25303611</ProductId>
    <Barcode>171-H007/1</Barcode>
    <CategoryCode>548</CategoryCode>
    <Brand></Brand>
    <Image>https://img.epttavm.com/prodotti/592/025/303/25303611_0.jpg</Image>
    <Price>10.8227</Price>
    <ListPrice>10.8227</ListPrice>
    <BuyingPrice>10.8227</BuyingPrice>
    <TryPrice>10.8227</TryPrice>
    <TaxedPrice>10.8227</TaxedPrice>
    <EftPrice>10.8227</EftPrice>
    <SpecialPrice>10.8227</SpecialPrice>
    <Quantity>100</Quantity>
    <Variant1></Variant1>
    <Variant2></Variant2>
    <Variant3></Variant3>
    <Variant4></Variant4>
    <PriceDiff1></PriceDiff1>
    <PriceDiff2></PriceDiff2>
    <PriceDiff3></PriceDiff3>
    <PriceDiff4></PriceDiff4>
    <Manufacture></Manufacture>
    <TaxRate>8</TaxRate>
    <ModelNumber></ModelNumber>
    <Status>0</Status>
    <Attributes/>
    <CategoryPath>Ev Dekorasyon &gt;&gt; Banyo Tekstili test 1</CategoryPath>
    <GetTranslation></GetTranslation>
    <Culture>tr</Culture>
    <Name><![CDATA[Apolena Dekoratif Havlu Set 171-h007-1]]></Name>
    <Description><![CDATA[<p><font face="Arial"><strong>SIZES:</strong> Large 70 x 140 cm Towel+ Small Towel 50X 90 cm </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>FABRIC:</strong> 100% cotton </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Material information:</strong> 2-Piece decorative Towel. Specialized manufacture of our fabrics and soft. Products used in textile dyes that are Oeko-Tex® Standard 100 certification standards because it does not contain carcinogenic substances harmful to human and animal health. </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Care information:</strong> wash at 40 with water by hand or in a washing machine. Waiting and wet do not use bleach. During washing light and dark colors to keep separate. At low rpm you can dry in the dryer drum. </font></p>]]></Description>
  </Product>
  <Product>
    <ProductId>25303635</ProductId>
    <Barcode>171-H081/1</Barcode>
    <CategoryCode>548</CategoryCode>
    <Brand></Brand>
    <Image>https://img.epttavm.com/prodotti/592/025/303/25303635_0.jpg</Image>
    <Price>10.8227</Price>
    <ListPrice>10.8227</ListPrice>
    <BuyingPrice>10.8227</BuyingPrice>
    <TryPrice>10.8227</TryPrice>
    <TaxedPrice>10.8227</TaxedPrice>
    <EftPrice>10.8227</EftPrice>
    <SpecialPrice>10.8227</SpecialPrice>
    <Quantity>100</Quantity>
    <Variant1></Variant1>
    <Variant2></Variant2>
    <Variant3></Variant3>
    <Variant4></Variant4>
    <PriceDiff1></PriceDiff1>
    <PriceDiff2></PriceDiff2>
    <PriceDiff3></PriceDiff3>
    <PriceDiff4></PriceDiff4>
    <Manufacture></Manufacture>
    <TaxRate>8</TaxRate>
    <ModelNumber></ModelNumber>
    <Status>0</Status>
    <Attributes/>
    <CategoryPath>Ev Dekorasyon &gt;&gt; Banyo Tekstili</CategoryPath>
    <GetTranslation></GetTranslation>
    <Culture>tr</Culture>
    <Name><![CDATA[Apolena Dekoratif Havlu Set 171-h081-1]]></Name>
    <Description><![CDATA[<p><font face="Arial"><strong>SIZES:</strong> Large 70 x 140 cm Towel+ Small Towel 50X 90 cm </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>FABRIC:</strong> 100% cotton </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Material information:</strong> 2-Piece decorative Towel. Specialized manufacture of our fabrics and soft. Products used in textile dyes that are Oeko-Tex® Standard 100 certification standards because it does not contain carcinogenic substances harmful to human and animal health. </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Care information:</strong> wash at 40 with water by hand or in a washing machine. Waiting and wet do not use bleach. During washing light and dark colors to keep separate. At low rpm you can dry in the dryer drum. </font></p>]]></Description>
  </Product>
  <Product>
    <ProductId>25303647</ProductId>
    <Barcode>171-H060/1</Barcode>
    <CategoryCode>548</CategoryCode>
    <Brand></Brand>
    <Image>https://img.epttavm.com/prodotti/592/025/303/25303647_0.jpg</Image>
    <Price>10.8227</Price>
    <ListPrice>10.8227</ListPrice>
    <BuyingPrice>10.8227</BuyingPrice>
    <TryPrice>10.8227</TryPrice>
    <TaxedPrice>10.8227</TaxedPrice>
    <EftPrice>10.8227</EftPrice>
    <SpecialPrice>10.8227</SpecialPrice>
    <Quantity>100</Quantity>
    <Variant1></Variant1>
    <Variant2></Variant2>
    <Variant3></Variant3>
    <Variant4></Variant4>
    <PriceDiff1></PriceDiff1>
    <PriceDiff2></PriceDiff2>
    <PriceDiff3></PriceDiff3>
    <PriceDiff4></PriceDiff4>
    <Manufacture></Manufacture>
    <TaxRate>8</TaxRate>
    <ModelNumber></ModelNumber>
    <Status>0</Status>
    <Attributes/>
    <CategoryPath>Ev Dekorasyon &gt;&gt; Banyo Tekstili</CategoryPath>
    <GetTranslation></GetTranslation>
    <Culture>tr</Culture>
    <Name><![CDATA[Apolena Dekoratif Havlu Set 171-h060-1]]></Name>
    <Description><![CDATA[<p><font face="Arial"><strong>SIZES:</strong> Large 70 x 140 cm Towel+ Small Towel 50X 90 cm </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>FABRIC:</strong> 100% cotton </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Material information:</strong> 2-Piece decorative Towel. Specialized manufacture of our fabrics and soft. Products used in textile dyes that are Oeko-Tex® Standard 100 certification standards because it does not contain carcinogenic substances harmful to human and animal health. </font></p><p><font face="Arial"> </font></p><p><font face="Arial"><strong>Care information:</strong> wash at 40 with water by hand or in a washing machine. Waiting and wet do not use bleach. During washing light and dark colors to keep separate. At low rpm you can dry in the dryer drum. </font></p>]]></Description>
  </Product>
 <Products>
My Job is to extract the category and sub category details from that xml and save those to database for further usage.

I have created one class where I need the category and sub category to be stored. Below is the Category class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Mpper.Entity
{
    public class Category
    {
        public int? Id { get; set; }

        public string Code { get; set; }

        public string Name { get; set; }

        public string SubCategoryName { get; set; }

        public string CategoryPath { get; set; }

        public int? ParentId { get; set; } 
    }
}



Now as the XML is having multiple nodes of products, I am expecting a list of Categories and here is how I get the data into the C# list from the XML file.
public List<Category> GenerateCategoryListFromProductFeedXML()
  {
      string path = System.Web.HttpContext.Current.Server.MapPath("~/Product Feed/product-feed.xml");

      XDocument xDoc = XDocument.Load(path);

      XElement xElement = XElement.Parse(xDoc.ToString());

      List<Category> lstCategory = xElement.Elements("Product").Select(d => new Category
      {
          Code = Convert.ToString(d.Element("CategoryCode").Value),
          CategoryPath = d.Element("CategoryPath").Value,
          Name = GetCateOrSubCategory(d.Element("CategoryPath").Value, 0), // Category
          SubCategoryName = GetCateOrSubCategory(d.Element("CategoryPath").Value, 1) // Sub Category
      }).GroupBy(x => new { x.Code, x.SubCategoryName }).Select(x => x.First()).ToList();

      return lstCategory;
  }
Here I am using the XDocument and XElement classes to parse the XML for which you need to add reference of "System.Xml.Linq" namespace in your project.

Now if you look into the above code you can see I am creating a XDocument object first, which will hold the XML data from the location. (I am using System.Web.HttpContext as my code was inside a class library. If you are going to use this method in a web project then you might not need that line to get the relative path).

Then XElement was responsible for parsing the string object we got from XDocument.



Once we parse the string xml data, we are ready to generate our custom C# object from the XElement. Here I am running a LINQ query on the xElement.Elements("Product"), where Product is the nested element inside the root element Products.

Then the LINQ code was simple enough, just create a new Category object and assign values from the XElement object to it.

That's it now return the List<Category> and do whatever you want to do with it.

Hope you like this article on how to read a XML file from C# and create a custom object from it.

Happy Codding....

Insert and Update Google Sheet from ASP.NET(C#) using Google.Apis.Sheets.V4 API

Few days back I got a requirement where I need to push some data into Google Sheet, that the client was maintaining for attendance of his employees.

I searched a lot in the internet and found few things but those are obsolete ones as Google has discontinued those Service APIs such as Google.GData.Client, Google.GData.Spreadsheets and replaced those APIs with Google.Apis.Sheets.v4 services.

Though Google has a very illustrative example on how to use and consume Google.Apis.Sheets.v4 services api, I found it a bit not helpful for beginners to understand and consume it. So I thought of writing a blog on this “How to consume Google.Apis.Sheets.v4 services for data insert and fetch in C#” to help others who are trying their hands on the same.

Here is the Google Developer’s link you can read the article and try your hands.

https://developers.google.com/sheets/api/quickstart/dotnet

Alright, lets get into the code. I will be explaining the process in some steps to make it easy to understand and use.



Step: 1
First you need to create a project in Google Console to get your client id. Go to the below link and create your project

https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com

Once you are done creating the project download the client id, that will be in JSON format.

Step 2:
Create a console or web or windows form application according to your need.
Refer the below libraries in your project.
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
Declare the below two line globally.
static string[] Scopes = { SheetsService.Scope.Spreadsheets };
static string ApplicationName = "TimeSheetUpdation By Cybria Technology";
static string SheetId = "Your sheet id ";

Step:3
Here we will write a method to authorize our request to access Goolge’s API.
private static SheetsService AuthorizeGoogleApp()
 {
     UserCredential credential;

     using (var stream =
         new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
     {
         string credPath = System.Environment.GetFolderPath(
             System.Environment.SpecialFolder.Personal);
         credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

         credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
             GoogleClientSecrets.Load(stream).Secrets,
             Scopes,
             "user",
             CancellationToken.None,
             new FileDataStore(credPath, true)).Result;
         Console.WriteLine("Credential file saved to: " + credPath);
     }

     // Create Google Sheets API service.
     var service = new SheetsService(new BaseClientService.Initializer()
     {
         HttpClientInitializer = credential,
         ApplicationName = ApplicationName,
     });

     return service;
 }
This method will return a sheet service SheetsService object

Step: 4
Now we need to decide the range, like from which row we have to start inserting. There are few acceptable values, below are the details.
  • A:A – this means the entire column A
  • A8:A – this means A8 to all A
// Define request parameters.
String spreadsheetId = txtGoogleSheet.Text;
String range = "K:K";

SpreadsheetsResource.ValuesResource.GetRequest getRequest =
           service.Spreadsheets.Values.Get(spreadsheetId, range);

ValueRange getResponse = getRequest.Execute();
IList<IList<Object>> getValues = getResponse.Values;
Now this code block will get you the column values according to your range. If you have selected A:A then it will give you the values of the entire A column, if you have selected A8:A then it will give you the columns values starting from A8, i.e 8th row.



Below method will return the range
protected static string GetRange(SheetsService service)
 {
     // Define request parameters.
     String spreadsheetId = SheetId;
     String range = "A:A";

     SpreadsheetsResource.ValuesResource.GetRequest getRequest =
                service.Spreadsheets.Values.Get(spreadsheetId, range);

     ValueRange getResponse = getRequest.Execute();
     IList<IList<Object>> getValues = getResponse.Values;

     int currentCount = getValues.Count() + 2;

     String newRange = "A" + currentCount + ":A";

     return newRange;
 }

Step:5
Now its time to generate some dummy data
private static IList<IList<Object>> GenerateData()
 {
     List<IList<Object>> objNewRecords = new List<IList<Object>>();

     IList<Object> obj = new List<Object>();

     obj.Add("Column - 1");
     obj.Add("Column - 2");
     obj.Add("Column - 3");

     objNewRecords.Add(obj);

     return objNewRecords;
 }

Step:6
Here is the method that will update the google sheet accordingly.
private static void UpdatGoogleSheetinBatch(IList<IList<Object>> values, string spreadsheetId, string newRange, SheetsService service)
 {
     SpreadsheetsResource.ValuesResource.AppendRequest request =
        service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange);
     request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
     request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
     var response = request.Execute();
 }
The above code will insert all the things as string if you want to insert the data in proper format then change the ValueInputOptionEnum.RAW to ValueInputOptionEnum.USERENTERED.



If you want the whole code, here it is.
using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace testGoogleSheets
{
    public class Attendance
    {
        public string AttendanceId { get; set; }
    }

    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        static string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static string ApplicationName = "TimeSheetUpdation By Cybria Technology";
        static string SheetId = "Your sheet id";

       static void Main(string[] args)
        {
            var service = AuthorizeGoogleApp();

            string newRange = GetRange(service);

            IList<IList<Object>> objNeRecords = GenerateData();

            UpdatGoogleSheetinBatch(objNeRecords, SheetId, newRange, service);

            Console.WriteLine("Inserted");
            Console.Read();
        }

       private static SheetsService AuthorizeGoogleApp()
        {
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                string credPath = System.Environment.GetFolderPath(
                    System.Environment.SpecialFolder.Personal);
                credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            return service;
        }

       protected static string GetRange(SheetsService service)
        {
            // Define request parameters.
            String spreadsheetId = SheetId;
            String range = "A:A";

            SpreadsheetsResource.ValuesResource.GetRequest getRequest =
                       service.Spreadsheets.Values.Get(spreadsheetId, range);

            ValueRange getResponse = getRequest.Execute();
            IList<IList<Object>> getValues = getResponse.Values;

            int currentCount = getValues.Count() + 2;

            String newRange = "A" + currentCount + ":A";

            return newRange;
        }

       private static IList<IList<Object>> GenerateData()
        {
            List<IList<Object>> objNewRecords = new List<IList<Object>>();

            IList<Object> obj = new List<Object>();

            obj.Add("Column - 1");
            obj.Add("Column - 2");
            obj.Add("Column - 3");

            objNewRecords.Add(obj);

            return objNewRecords;
        }

       private static void UpdatGoogleSheetinBatch(IList<IList<Object>> values, string spreadsheetId, string newRange, SheetsService service)
        {
            SpreadsheetsResource.ValuesResource.AppendRequest request =
               service.Spreadsheets.Values.Append(new ValueRange() { Values = values }, spreadsheetId, newRange);
            request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
            request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var response = request.Execute();
        }

    }
}

I hope now you are on your own to play around with the Google sheet service on how to insert into google sheet from C#.

Happy Codding...

Exporting Data To MS Word in ASP.NET

Previously I have written 4 articles on exporting data from ASP.NET. Chek out all the Export Data Articles I have written.

Download source code here.

Below is the code you can have a look and then I will discuss every bit of it for your understanding.
protected void ExportToWord(object sender, EventArgs e)
    {
        DataTable dt = GetData();
    
        //Create a dummy GridView
        GridView GridView1 = new GridView();
        GridView1.AllowPaging = false;
        GridView1.DataSource = dt;
        GridView1.DataBind();
    
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.doc");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-word ";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/vnd.ms-word". Also give name to the file you are downloading.
  4. Fourth, Get a string writer and Html writer and render the grid view inside it. Now the string writer will have the exact html of  the grid and we are all set to throw it into an Word File
  5. Fifth, Now write the string writer value into response object and you are all set to download the word file.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET




Exporting Data To PDF in ASP.NET

In previous two articles we have seen how we can Export Data Into Excel Sheet and Export Data Into CSV File.

Here is the tag where you can find all the Export Data Articles.

Now we will see how we can export data into a PDF file in asp.net.

Download source code here.

Here is the method which will help exporting data into a PDF. 
protected void ExportToPDF(object sender, EventArgs e)
    {
        DataTable dt = GetData();
    
        //Create a dummy GridView
        GridView GridView1 = new GridView();
        GridView1.AllowPaging = false;
        GridView1.DataSource = dt;
        GridView1.DataBind();
    
        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;filename=MyReport.pdf");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        StringReader sr = new StringReader(sw.ToString());
        Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        pdfDoc.Open();
        htmlparser.Parse(sr);
        pdfDoc.Close();
        Response.Write(pdfDoc);
        Response.End();
    }
I guess this must be very clear to everyone what I have written inside. Anyway let's discuss it here.

  1. First, I get the data source from the method GetData(), which is nothing but just a method that returns a datatable with some data inside it. You can have your own dataset here from DB or wherever you want.
  2. Second, I have taken a grid view so that I can render that data in it. If you have a grid view at your page level then no need to take one and bind it here, even you need not to get the data again if you have already that data rendered in any gridview or what in your page.
  3. Third, Add Response headers, content types for Excel download. Remember the Content Type is very important, this is code which decides the file type you are downloading. So here for Excel sheet download the content Type is "application/pdf". Also give name to the file you are downloading.
  4. Fourth, I have used iTextSharp for creating PDF file.
  5. Fifth, Now write the string writer value into response object and you are all set to download the excel sheet.
If you are looking for the GetData() method to test out the feasibility of this process of exporting data into excel sheet in ASP.NET and C# then here is it.
     private DataTable GetData()
     {
         // Here we create a DataTable with four columns.
         DataTable dtSample = new DataTable();
         dtSample.Columns.Add("Dosage", typeof(int));
         dtSample.Columns.Add("Drug", typeof(string));
         dtSample.Columns.Add("Patient", typeof(string));
         dtSample.Columns.Add("Date", typeof(DateTime));

         // Here we add five DataRows.
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         dtSample.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
         dtSample.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
         dtSample.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
         dtSample.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
         dtSample.Rows.Add(25, "Indocin", "David", DateTime.Now);
         
         return dtSample;

     }
Hope this article helps you, as this method helped me a lot to overcome the unnecessary usage of external dll for exporting data into excel sheets.

Demo:

Export Data From ASP.NET