Archive for February, 2016

How to display larger numbers on Excel export in C#

Introduction:

In this article I am going to explain a solution to the problem with displaying large numbers (more than 15 numbers) in excel while exporting data in C#.

Issue Description:

By default, excel normally display larger numbers like 123456E+17 when the number overcomes Excel’s precision limit for numbers which is 8 digits. When we export the data from Gridivew to excel the same problem exists.

Excel

Drawback of existing Solution:

One solution to resolve this is to make the column type of excel to text instead of numbers. To this we need to add a single quote (‘) to the numbers which we are exporting. This will resolve the issue of displaying the complete number, however this solution has a drawback as it will display single quote along with the number like ‘123456789011.

Solution to the Problem

To show the number without using single quote we can use a Third Party Excel library called EPPlus. EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). This library offers several features and methods to help the user to generate Excel sheet. You can read the complete details from the link I shared above.

Here in this fix we will use the method LoadFromDataTable to load the data from DataTable to Excel. The same case applies when you Export data from gridview and other controls.

Code

private void ExcelExport(DataTable tbl)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
                //Load the datatable into the sheet, starting from cell A1.
                ws.Cells["A1"].LoadFromDataTable(tbl, true);
                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=file.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
            }
        }

Before using above code add the below references in your page

using OfficeOpenXml;
using OfficeOpenXml.Style;

Sample usage to above method

DataTable dt = new DataTable();
            dt.Columns.Add("PhoneNo");
            dt.Columns.Add("Name");
            DataRow Sample = dt.NewRow();
            Sample["PhoneNo"] = "12345678911121314";
            Sample["Name"] = "SampleName";
            dt.Rows.Add(Sample);
            ExcelExport(dt);

With above code the exported excel file will look like below

Excel2

You can use Nugget to add the EPPlus in your solution.

 

Advertisements

, , , , , , ,

1 Comment

How to read JSON with object name using Json.Net in C#

Introduction:
In this article I am going to explain how to read the values from Json using Json.Net library when it is having Object Name.

Description:

In this article we are using Json.Net library to parse and read values in Json. We will be using JSON serializer methods available in this dictionary.

If you don’t have Json Framework in your machine you can get it from Nugget for Json.

Sample JSon file which we use in this article

{"employees":[
{"firstName":"Test1FN", "lastName":"Test1LN"},
{"firstName":"Test2FN", "lastName":"Test2LN"}
]}

Implementation:

After adding library to your project ensure that you have added the namespace reference in your page

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

Next add a class to hold the values, note that I have used JsonProperty to map the JSON property to a .NET member or constructor parameter. This is very important as the object name format is not following key: value combination so if you don’t set this then while reading you will get exception.

[JsonObject(MemberSerialization.OptIn)]
public class User
{
   [JsonProperty("firstName", Required = Required.Always)]
   public string FirstName { get; set; }

   [JsonProperty("lastName", Required = Required.Always)]
   public string LastName { get; set; }
}

Now you can use the below code to read the values

//Load your Json string here
String jsonString = "{\"user\":[{\"firstName\": \"Test1FN\", \"lastName\": \"Doe\"},{\"firstName\": \"Test2FN\", \"lastName\": \"Test2LN\"}]}";
JToken root = JObject.Parse(jsonString);
JToken user = root["user"];
//Deserialize the Json to list
var deserializedUser = JsonConvert.DeserializeObject<List<User>>(user.ToString());

//Loop through values 
foreach (User p in deserializedUser)
{
//Your code to process the values
}

, , , ,

Leave a comment