step 1 :create ExportGridToExcel.ashx for open file
public class ExportGridToExcel : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string tabData = context.Request["excelData"];
DataTable dt = ConvertCsvData(tabData);
if (dt == null)
{
return;
}
string excelFilename = context.Request["filename"];
if (File.Exists(excelFilename))
File.Delete(excelFilename);
string FileName = CreateExcel.CreateExcelDocument(dt, excelFilename, context.Response); ;
string FilePath = "ExcelFiles//" + FileName;
context.Response.Clear();
context.Response.Buffer = true;
context.Response.AddHeader("content-disposition", string.Format("attechment; filename={0}", FileName));
string strFilePath = context.Request.PhysicalApplicationPath + FilePath;
string strExtension = System.IO.Path.GetExtension(strFilePath);
if (strExtension.Equals(".xls", StringComparison.InvariantCulture) || strExtension.Equals(".csv", StringComparison.InvariantCulture) || strExtension.Equals(".xlsx", StringComparison.InvariantCulture))
{
context.Response.ContentType = "application/ms-excel";
}
else if (strExtension.Equals(".pdf", StringComparison.InvariantCulture))
{
context.Response.ContentType = "application/pdf";
}
context.Response.WriteFile(strFilePath);
context.Response.End();
}
private DataTable ConvertCsvData(string CSVdata)
{
// Convert a tab-separated set of data into a DataTable, ready for our C# CreateExcelFile libraries
// to turn into an Excel file.
//
DataTable dt = new DataTable();
try
{
System.Diagnostics.Trace.WriteLine(CSVdata);
string[] Lines = CSVdata.Split(new char[] { '\r', '\n' });
if (Lines == null)
return dt;
if (Lines.GetLength(0) == 0)
return dt;
string[] HeaderText = Lines[0].Split('\t');
int numOfColumns = HeaderText.Count();
foreach (string header in HeaderText)
dt.Columns.Add(header, typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
string[] Fields = Lines[i].Split('\t');
if (Fields.GetLength(0) == numOfColumns)
{
Row = dt.NewRow();
for (int f = 0; f < numOfColumns; f++)
{
if (Fields[f] == "0")
Row[f] = "";
else
Row[f] = Fields[f].Replace("null", "");
Row[f] = Row[f].ToString().Replace("", "");
Row[f] = Row[f].ToString().Replace("
", ",");
}
dt.Rows.Add(Row);
}
}
return dt;
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("An exception occurred: " + ex.Message);
return null;
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
public class CreateExcel
{
///
/// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
///
///
DataTable containing the data to be written to the Excel.
///
The filename (without a path) to call the new Excel file.
///
HttpResponse of the current page.
///
True if it was created succesfully, otherwise false.
public static string CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)
{
try
{
return CreateExcelDocumentAsStream(dt, filename, Response);
}
catch (Exception ex)
{
return "";
}
}
///
/// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
///
///
DataSet containing the data to be written to the Excel.
///
The filename (without a path) to call the new Excel file.
///
HttpResponse of the current page.
///
Either a MemoryStream, or NULL if something goes wrong.
public static string CreateExcelDocumentAsStream(DataTable dt, string filename, System.Web.HttpResponse Response)
{
try
{
Random rnd = new Random();
string _strFile = DateTime.Now.ToString("ddMMyyyy") + "_" + rnd.Next(0, 9999) + ".xlsx";
FileInfo newFile = new FileInfo(HttpContext.Current.Server.MapPath("~/ExcelFiles/" + _strFile));
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(_strFile);
}
ExcelPackage xlPackage = new ExcelPackage(newFile);
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add("DefaultFormat");
int _RowID = 1;
xlWorkSheet.Cells.Style.Numberformat.Format = "@";
xlWorkSheet.Cells.Style.Numberformat.Format = "General";
int _Cell = 1;
for (int i = 1; i <= dt.Columns.Count; i++)
{
xlWorkSheet.Cells[_RowID, _Cell].Value = dt.Columns[i - 1].ToString();
xlWorkSheet.Cells[_RowID, _Cell].Style.WrapText = true;
xlWorkSheet.Cells[_RowID, _Cell].Style.Font.Bold = true;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Top.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Left.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Right.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
xlWorkSheet.Cells[_RowID, _Cell].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
xlWorkSheet.Cells[_RowID, _Cell].Style.Numberformat.Format = "@";
xlWorkSheet.Cells[_RowID, _Cell].Style.Numberformat.Format = "General";
_Cell++;
}
_RowID++;
for (int i = 0; i < dt.Rows.Count; i++)
{
_Cell = 1;
for (int ji = 0; ji < dt.Columns.Count; ji++)
{
xlWorkSheet.Cells[_RowID, _Cell].Value = Convert.ToString(dt.Rows[i][ji]);
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Top.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Left.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Right.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
xlWorkSheet.Cells[_RowID, _Cell].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
xlWorkSheet.Cells[_RowID, _Cell].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
xlWorkSheet.Cells[_RowID, _Cell].Style.Numberformat.Format = "@";
xlWorkSheet.Cells[_RowID, _Cell].Style.Numberformat.Format = "General";
_Cell++;
}
_RowID++;
}
xlWorkSheet.Cells.AutoFitColumns();
xlPackage.Save();
xlPackage.Dispose();
return _strFile;
}
catch (Exception ex)
{
return "";
}
}
}
Step 2 Js file
function ExportJQGridDataToExcel(tableCtrl, excelFilename) {
// Export the data from our jqGrid into a (real !) Excel .xlsx file.
//
// We'll build up a (very large?) tab-separated string containing the data to be exported, then POST them
// off to a .ashx handler, which creates the Excel file.
debugger
var allJQGridData = $(tableCtrl).jqGrid('getGridParam', 'data');
var jqgridRowIDs = $(tableCtrl).getDataIDs(); // Fetch the RowIDs for this grid
var headerData = $(tableCtrl).getRowData(jqgridRowIDs[0]); // Fetch the list of "name" values in our colModel
// For each visible column in our jqGrid, fetch it's Name, and it's Header-Text value
var columnNames = new Array(); // The "name" values from our jqGrid colModel
var columnHeaders = new Array(); // The Header-Text, from the jqGrid "colNames" section
var inx = 0;
var allColumnNames = $(tableCtrl).jqGrid('getGridParam', 'colNames');
for (var headerValue in headerData) {
// If this column ISN'T hidden, and DOES have a column-name, then we'll export its data to Excel.
var isColumnHidden = $(tableCtrl).jqGrid("getColProp", headerValue).hidden;
if (!isColumnHidden && headerValue != null) {
columnNames.push(headerValue);
columnHeaders.push(allColumnNames[inx]);
}
inx++;
}
// We now need to build up a (potentially very long) tab-separated string containing all of the data (and a header row)
// which we'll want to export to Excel.
// First, let's append the header row...
var excelData = '';
for (var k = 0; k < columnNames.length; k++) {
excelData += columnHeaders[k] + "\t";
}
excelData = removeLastChar(excelData) + "\r\n";
// ..then each row of data to be exported.
var cellValue = '';
for (i = 0; i < allJQGridData.length; i++) {
var data = allJQGridData[i];
for (var j = 0; j < columnNames.length; j++) {
// Fetch one jqGrid cell's data, but make sure it's a string
cellValue = '' + data[columnNames[j]];
if (cellValue == null)
excelData += "\t";
else {
if (cellValue.indexOf("a href") > -1) {
// Some of my cells have a jqGrid cell with a formatter in them, making them hyperlinks.
// We don't want to export the "
" tags to our Excel file, just the cell's text.
cellValue = $(cellValue).text();
}
// Make sure we are able to POST data containing apostrophes in it
cellValue = cellValue.replace(/'/g, "'");
excelData += cellValue + "\t";
}
}
excelData = removeLastChar(excelData) + "\r\n";
}
// Now, we need to POST our Excel Data to our .ashx file *and* redirect to the .ashx file.
postAndRedirect("../../Handlers/ExportGridToExcel.ashx?filename=" + excelFilename, { excelData: excelData });
}
function removeLastChar(str) {
// Remove the last character from a string
return str.substring(0, str.length - 1);
}
function postAndRedirect(url, postData) {
// Redirect to a URL, and POST some data to it.
// Taken from:
// http://stackoverflow.com/questions/8389646/send-post-data-on-redirect-with-javascript-jquery
//
var postFormStr = "
";
var formElement = $(postFormStr);
$('body').append(formElement);
$(formElement).submit();
}
step 3 Add js code in jqgriid
$("#jqTable").navButtonAdd('#jqTablePager', {
title: "Export to Excel",
buttonicon: "ace-icon fa fa-file-excel-o",
caption: "",
onClickButton: function () {
ExportJQGridDataToExcel("#jqTable", "ExcelName.xlsx");
},
position: "last"
});
if data not display in excel please set below property in JqGrid
loadonce: true,
Thanks