Here I will explain how to generate excel from json.
I am using NPOI and Newtonsoft.Json third party dll for this.
Download dll from here : https://drive.google.com/file/d/0B2UW8NPznIKsdzJSWDBDX2xLSms/view
JSON For Test : [ { "ID" : "1", "NAME" : "Nikhil Sangani", "MOBILENO" : "123456789", "DESIGNATION" : "Web Developer" } ]
First of all i am create aspx page design like this.
After that put this code on your code behind file.
I am using NPOI and Newtonsoft.Json third party dll for this.
Download dll from here : https://drive.google.com/file/d/0B2UW8NPznIKsdzJSWDBDX2xLSms/view
JSON For Test : [ { "ID" : "1", "NAME" : "Nikhil Sangani", "MOBILENO" : "123456789", "DESIGNATION" : "Web Developer" } ]
First of all i am create aspx page design like this.
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td> JSON STRING :<asp:TextBox runat="server" ID="txtjsonstring"></asp:TextBox> </td> </tr> <tr> <td> <asp:Button runat="server" ID="BtnExcel" Text="Excel" OnClick="BtnExcel_Click" /> </td> </tr> </table> </div> </form> </body> </html>
After that put this code on your code behind file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.SS.UserModel;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.IO;
using Newtonsoft.Json;
namespace testProject
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public Workbook ExportToExcel(DataTable ExcelTable, string FileName)
{
DataTable dtTemp = new DataTable();
for (int i = 0; i < ExcelTable.Columns.Count; i++)
{
dtTemp.Columns.Add(ExcelTable.Columns[i].ColumnName);
}
// Create a new workbook and a sheet name
HSSFWorkbook workbook = new HSSFWorkbook();
CreationHelper createHelper = workbook.GetCreationHelper();
Sheet sheet = workbook.CreateSheet("Nikhil Sangani");
// Add header labels
int rowIndex = 0;
Row row = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.Font Headerfont = workbook.CreateFont();
Headerfont.FontName = "Arial";
Headerfont.FontHeightInPoints = 10;
Headerfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
foreach (DataColumn column in dtTemp.Columns)
{
Cell headerCell = row.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
CellStyle headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.FillForegroundColor = HSSFColor.SKY_BLUE.index;
headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headerCellStyle.Alignment = HorizontalAlignment.CENTER;
headerCellStyle.SetFont(Headerfont);
headerCellStyle.BorderBottom = CellBorderType.THIN;
headerCellStyle.BottomBorderColor = HSSFColor.BLACK.index;
headerCellStyle.BorderLeft = CellBorderType.THIN;
headerCellStyle.LeftBorderColor = HSSFColor.BLACK.index;
headerCellStyle.BorderRight = CellBorderType.THIN;
headerCellStyle.RightBorderColor = HSSFColor.BLACK.index;
headerCellStyle.BorderTop = CellBorderType.THIN;
headerCellStyle.TopBorderColor = HSSFColor.BLACK.index;
headerCell.CellStyle = headerCellStyle;
}
rowIndex++;
NPOI.SS.UserModel.Font font = workbook.CreateFont();
font.FontName = "Calibri";
font.FontHeightInPoints = 9;
CellStyle FontCellStyle = workbook.CreateCellStyle();
FontCellStyle.SetFont(font);
FontCellStyle.BorderBottom = CellBorderType.THIN;
FontCellStyle.BottomBorderColor = HSSFColor.BLACK.index;
FontCellStyle.BorderLeft = CellBorderType.THIN;
FontCellStyle.LeftBorderColor = HSSFColor.BLACK.index;
FontCellStyle.BorderRight = CellBorderType.THIN;
FontCellStyle.RightBorderColor = HSSFColor.BLACK.index;
FontCellStyle.BorderTop = CellBorderType.THIN;
FontCellStyle.TopBorderColor = HSSFColor.BLACK.index;
foreach (DataRow roww in ExcelTable.Rows)
{
row = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in ExcelTable.Columns)
{
row.CreateCell(column.Ordinal).SetCellValue(roww[column].ToString());
}
rowIndex++;
}
// Auto-size each column
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
for (int i = 1; i < sheet.LastRowNum - 5; i++)
{
for (int j = 0; j < sheet.GetRow(i).LastCellNum; j++)
{
if (j != 0 && j != 1 && j != 2 && j != 3 && j != 4 && j != 5)
{
sheet.GetRow(i).GetCell(j).CellStyle = FontCellStyle;
}
}
}
return workbook;
}
protected void BtnExcel_Click(object sender, EventArgs e)
{
Workbook workbook = new HSSFWorkbook();
string FileName = "Nikhil" + "_" + DateTime.Today.ToString("dd_MM_yy").ToString() + "_" + DateTime.Now.ToString("hh_mm_ss").ToString() + ".xls";
//Here convert JSON to DataTable.
DataTable dtValue1 = (DataTable)JsonConvert.DeserializeObject(txtjsonstring.Text, (typeof(DataTable)));
workbook = ExportToExcel(dtValue1, FileName);
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
string saveAsFileName = FileName;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
}
}
InPut:
Output :
Comments
Post a Comment