hi,
i dont know anything on how to export json data to excel sheet.my data is display in jqxgrid then i want user click button export to excel finish.please someone can help me on this.there is not much tutorial on this json format.
my jquery is using 2.2 version.so i cannot use the demo code in the http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/dataexport.htm
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim SqlStr As String
Dim Result As DataTable
Dim DbCon As New Conn
Dim Temp As String
Dim ParamTmp As New Dictionary(Of String, Object)
Dim Enstr As New KtmbFunc.Crypt
Dim FromDate As Date = Request("FromDate")
Dim ToFrom As Date = Request("ToFrom")
If Request("Jenis") = "1" Then
SqlStr = "select a.nosiri,a.sendby,a.datepass,a.passedby " _
& " from Butiran_Dtl as a " _
& " where a.dateapprove between @FromDate and @ToFrom " _
& " And a.status = 1 "
ParamTmp.Add("@FromDate", Enstr.EnStr(FromDate))
ParamTmp.Add("@ToFrom", Enstr.EnStr(ToFrom))
ElseIf Request("Jenis") = "2" Then
SqlStr = "select a.nosiri,a.sendby,a.datepass,a.passedby " _
& " from Butiran_Dtl as a " _
& " where a.dateapprove between @FromDate and @ToFrom " _
& " And a.status = 5 "
ParamTmp.Add("@FromDate", Enstr.EnStr(FromDate))
ParamTmp.Add("@ToFrom", Enstr.EnStr(ToFrom))
ElseIf Request("Jenis") = "3" Then
If Request("Pilih") = "0" Then
SqlStr = "select b.NoSiri,b.NoSiriSMH,d.nama as tindakan,b.saizroda,b.axleno,e.nama as jenisroda,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l + ' ' + " _
& " CASE y1 when '1' then 'South' " _
& " when '0' then 'North' " _
& " Else 'undefined' " _
& " END as yearns_l, " _
& " b.yearns_r + ' ' + " _
& " CASE y2 when '1' then 'South' " _
& " when '0' then 'North' " _
& " else 'undefined' " _
& " END as yearns_r, " _
& " f.nama as bearingbrand, " _
& " CASE flawtest when '1' then 'Ok' " _
& " when '0' then 'No' " _
& " else 'undefined' " _
& " END as flawtest," _
& " a.datepass as 'Tarikh Terima',c.nama as 'DiLuluskan Oleh'" _
& " from butiran_dtl as a, wheel_prod_dtl as b, login_staff as c, login_staff as d, login_staff as e, login_staff as f " _
& " where a.RefNumber = b.RefNumber " _
& " And b.tindakan = 0 " _
& " And a.status = 3 " _
& " And a.passedby = c.staffid " _
& " And c.jenis = 0 " _
& " and b.tindakan = d.staffid " _
& " and d.jenis = 2 " _
& " and a.jenisroda = e.staffid " _
& " and e.jenis = 3 " _
& " and b.bearingbrand = f.staffid " _
& " and f.jenis = 5 " _
& " group by a.datepass,b.NoSiriSMH,c.nama,d.nama,b.saizroda,b.axleno,e.nama,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l,b.yearns_r,f.nama,y1,y2,flawtest,b.NoSiri"
ParamTmp.Add("@FromDate", Enstr.EnStr(FromDate))
ParamTmp.Add("@ToFrom", Enstr.EnStr(ToFrom))
ElseIf Request("Pilih") = "1" Then
SqlStr = "select b.NoSiri,b.NoSiriSMH,d.nama as tindakan,b.saizroda,b.axleno,e.nama as jenisroda,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l + ' ' + " _
& " CASE y1 when '1' then 'South' " _
& " when '0' then 'North' " _
& " Else 'undefined' " _
& " END as yearns_l, " _
& " b.yearns_r + ' ' + " _
& " CASE y2 when '1' then 'South' " _
& " when '0' then 'North' " _
& " else 'undefined' " _
& " END as yearns_r, " _
& " f.nama as bearingbrand, " _
& " CASE flawtest when '1' then 'Ok' " _
& " when '0' then 'No' " _
& " else 'undefined' " _
& " END as flawtest," _
& " a.datepass as 'Tarikh Terima',c.nama as 'DiLuluskan Oleh'" _
& " from butiran_dtl as a, wheel_prod_dtl as b, login_staff as c, login_staff as d, login_staff as e, login_staff as f " _
& " where a.RefNumber = b.RefNumber " _
& " And b.tindakan = 1 " _
& " And a.status = 3 " _
& " And a.passedby = c.staffid " _
& " And c.jenis = 0 " _
& " and b.tindakan = d.staffid " _
& " and d.jenis = 2 " _
& " and a.jenisroda = e.staffid " _
& " and e.jenis = 3 " _
& " and b.bearingbrand = f.staffid " _
& " and f.jenis = 5 " _
& " group by a.datepass,b.NoSiriSMH,c.nama,d.nama,b.saizroda,b.axleno,e.nama,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l,b.yearns_r,f.nama,y1,y2,flawtest,b.NoSiri"
ParamTmp.Add("@FromDate", Enstr.EnStr(FromDate))
ParamTmp.Add("@ToFrom", Enstr.EnStr(ToFrom))
ElseIf Request("Pilih") = "2" Then
SqlStr = "select b.NoSiri,b.NoSiriSMH,d.nama as tindakan,b.saizroda,b.axleno,e.nama as jenisroda,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l + ' ' + " _
& " CASE y1 when '1' then 'South' " _
& " when '0' then 'North' " _
& " Else 'undefined' " _
& " END as yearns_l, " _
& " b.yearns_r + ' ' + " _
& " CASE y2 when '1' then 'South' " _
& " when '0' then 'North' " _
& " else 'undefined' " _
& " END as yearns_r, " _
& " f.nama as bearingbrand, " _
& " CASE flawtest when '1' then 'Ok' " _
& " when '0' then 'No' " _
& " else 'undefined' " _
& " END as flawtest," _
& " a.datepass as 'Tarikh Terima',c.nama as 'DiLuluskan Oleh'" _
& " from butiran_dtl as a, wheel_prod_dtl as b, login_staff as c, login_staff as d, login_staff as e, login_staff as f " _
& " where a.RefNumber = b.RefNumber " _
& " And b.tindakan = 2 " _
& " And a.status = 3 " _
& " And a.passedby = c.staffid " _
& " And c.jenis = 0 " _
& " and b.tindakan = d.staffid " _
& " and d.jenis = 2 " _
& " and a.jenisroda = e.staffid " _
& " and e.jenis = 3 " _
& " and b.bearingbrand = f.staffid " _
& " and f.jenis = 5 " _
& " group by a.datepass,b.NoSiriSMH,c.nama,d.nama,b.saizroda,b.axleno,e.nama,b.years,b.bearingno1, b.bearingno2, " _
& " b.yearns_l,b.yearns_r,f.nama,y1,y2,flawtest,b.NoSiri"
ParamTmp.Add("@FromDate", Enstr.EnStr(FromDate))
ParamTmp.Add("@ToFrom", Enstr.EnStr(ToFrom))
End If
End If
Result = DbCon.ExecuteReader(SqlStr, ParamTmp)
If Result.Rows.Count <> 0 Then
Temp = "["
For Each row As DataRow In Result.Rows
Temp = Temp & "{"
For Each col As DataColumn In Result.Columns
Temp = Temp & Chr(34) & col.ToString & Chr(34) & ":" & Chr(34) & row(col).ToString() & Chr(34) & ","
Next
Temp = Temp & "},"
Temp = Replace(Temp, ",}", "}")
Next
Temp = Temp & "]"
Temp = Replace(Temp, ",]", "]")
Response.Write(Temp)
Else
Response.Write("[{}]")
End If
Result.Clear()
End Sub
$("#RepGrid").jqxGrid({
width: 800,
theme: theme,
pageable: true,
sortable: true,
setcolumnfilterable: true,
altrows: true,
selectionmode: 'multiplecellsextended',
autoheight: true,
columnsmenu: true,
pagesizeoptions: ['10', '20', '50']
});
$("#RepSearchBtn").jqxButton({ width: '110', height: '25', theme: theme });
$("#excelExport").jqxButton({ width: '110', height: '25', theme: theme });
$("#RepSearchBtn").bind('click', function () {
var Jenis = $("#PilihRep").val();
var Pilih = $("#tindakan").val();
var FromDate = $("#FromDate").val();
var ToFrom = $("#ToDate").val();
var URL = "Ajax/report.aspx?Jenis=" + Jenis + "&FromDate=" + FromDate + "&ToFrom=" + ToFrom + "&Pilih=" + Pilih + "¶m=" + getDateTimeFunction();
var source;
if (Jenis == "1") {
source = {
datatype: "json",
datafields: [
{name: 'tarikh' },
{ name: 'depoh' },
{ name: 'kuantiti' }
],
url: URL,
id: 'RefNumber'
};
var columns = [
{text: 'Tarikh', datafield: 'tarikh', width: 250 },
{ text: "Depoh", datafield: 'depoh', width: 200, cellsalign: 'right' },
{ text: "Kuantiti", datafield: 'kuantiti', width: 200, cellsalign: 'right' }
]
$("#GridPanel").show("fast");
$("#RepGrid").jqxGrid({
rowdetails: false,
source: source,
columns: columns
});
}
else if (Jenis == "2") {
source = {
datatype: "json",
datafields: [
{name: 'tarikh' },
{ name: 'depoh' },
{ name: 'kuantiti' }
],
url: URL,
id: 'RefNumber'
};
var columns = [
{text: 'Tarikh', datafield: 'tarikh', width: 250 },
{ text: "Depoh", datafield: 'depoh', width: 200, cellsalign: 'right' },
{ text: "Kuantiti", datafield: 'kuantiti', width: 200, cellsalign: 'right' }
]
$("#GridPanel").show("fast");
$("#RepGrid").jqxGrid({
rowdetails: false,
source: source,
columns: columns
});
}
else if (Jenis == "3") {
if (Pilih == "0") {
source = {
datatype: "json",
datafields: [
{ name: 'NoSiri' },
{ name: 'NoSiriSMH' },
{ name: 'tindakan' },
{ name: 'saizroda' },
{ name: 'axleno' },
{ name: 'years' },
{ name: 'bearingno1' },
{ name: 'bearingno2' },
{ name: 'yearns_l' },
{ name: 'yearns_r' },
{ name: 'bearingbrand' },
{name: 'flawtest' },
{ name: 'Tarikh Terima' },
{name: 'DiLuluskan Oleh' }
],
url: URL,
id: 'RefNumber'
};
var columns = [
{ text: "No Siri Lama", datafield: 'NoSiri', width: 200 },
{ text: "No Siri Baru", datafield: 'NoSiriSMH', width: 200 },
{ text: "Tindakan", datafield: 'tindakan', width: 200 },
{ text: "Saiz Roda", datafield: 'saizroda', width: 200 },
{ text: 'No Gandar', datafield: 'axleno', width: 70, sortable: true },
{ text: 'Tahun Gandar', columntype: 'textbox', datafield: 'years', width: 100, sortable: true },
{ text: 'No Bearing Kiri', columntype: 'textbox', datafield: 'bearingno1', width: 100, sortable: true },
{ text: 'No Bearing Kanan', columntype: 'textbox', datafield: 'bearingno2', width: 100, sortable: true },
{ text: 'Tahun Bearing Kiri', datafield: 'yearns_l', width: 100, sortable: true },
{ text: 'Tahun Bearing Kanan', datafield: 'yearns_r', width: 120, sortable: true },
{ text: 'Jenama Bearing', datafield: 'bearingbrand', width: 100, sortable: true },
{text: 'Ujian Kecacatan', datafield: 'flawtest', width: 120, sortable: true },
{ text: 'Tarikh Terima', datafield: 'Tarikh Terima', width: 250 },
{text: "DiLuluskan Oleh", datafield: 'DiLuluskan Oleh', width: 200, cellsalign: 'right' }
]
$("#GridPanel").show("fast");
$("#RepGrid").jqxGrid({
rowdetails: false,
source: source,
columns: columns
});
}
else if (Pilih == "1") {
source = {
datatype: "json",
datafields: [
{ name: 'NoSiri' },
{ name: 'NoSiriSMH' },
{ name: 'tindakan' },
{ name: 'saizroda' },
{ name: 'axleno' },
{ name: 'years' },
{ name: 'bearingno1' },
{ name: 'bearingno2' },
{ name: 'yearns_l' },
{ name: 'yearns_r' },
{ name: 'bearingbrand' },
{name: 'flawtest' },
{ name: 'Tarikh Terima' },
{name: 'DiLuluskan Oleh' }
],
url: URL,
id: 'RefNumber'
};
var columns = [
{ text: "No Siri Lama", datafield: 'NoSiri', width: 200 },
{ text: "No Siri Baru", datafield: 'NoSiriSMH', width: 200 },
{ text: "Tindakan", datafield: 'tindakan', width: 200 },
{ text: "Saiz Roda", datafield: 'saizroda', width: 200 },
{ text: 'No Gandar', datafield: 'axleno', width: 70, sortable: true },
{ text: 'Tahun Gandar', columntype: 'textbox', datafield: 'years', width: 100, sortable: true },
{ text: 'No Bearing Kiri', columntype: 'textbox', datafield: 'bearingno1', width: 100, sortable: true },
{ text: 'No Bearing Kanan', columntype: 'textbox', datafield: 'bearingno2', width: 100, sortable: true },
{ text: 'Tahun Bearing Kiri', datafield: 'yearns_l', width: 100, sortable: true },
{ text: 'Tahun Bearing Kanan', datafield: 'yearns_r', width: 120, sortable: true },
{ text: 'Jenama Bearing', datafield: 'bearingbrand', width: 100, sortable: true },
{text: 'Ujian Kecacatan', datafield: 'flawtest', width: 120, sortable: true },
{ text: 'Tarikh Terima', datafield: 'Tarikh Terima', width: 250 },
{text: "DiLuluskan Oleh", datafield: 'DiLuluskan Oleh', width: 200, cellsalign: 'right' }
]
$("#GridPanel").show("fast");
$("#RepGrid").jqxGrid({
rowdetails: false,
source: source,
columns: columns
});
}
else if (Pilih == "2") {
source = {
datatype: "json",
datafields: [
{ name: 'NoSiri' },
{ name: 'NoSiriSMH' },
{ name: 'tindakan' },
{ name: 'saizroda' },
{ name: 'axleno' },
{ name: 'years' },
{ name: 'bearingno1' },
{ name: 'bearingno2' },
{ name: 'yearns_l' },
{ name: 'yearns_r' },
{ name: 'bearingbrand' },
{name: 'flawtest' },
{ name: 'Tarikh Terima' },
{name: 'DiLuluskan Oleh' }
],
url: URL,
id: 'RefNumber'
};
var columns = [
{ text: "No Siri Lama", datafield: 'NoSiri', width: 200 },
{ text: "No Siri Baru", datafield: 'NoSiriSMH', width: 200 },
{ text: "Tindakan", datafield: 'tindakan', width: 200 },
{ text: "Saiz Roda", datafield: 'saizroda', width: 200 },
{ text: 'No Gandar', datafield: 'axleno', width: 70, sortable: true },
{ text: 'Tahun Gandar', columntype: 'textbox', datafield: 'years', width: 100, sortable: true },
{ text: 'No Bearing Kiri', columntype: 'textbox', datafield: 'bearingno1', width: 100, sortable: true },
{ text: 'No Bearing Kanan', columntype: 'textbox', datafield: 'bearingno2', width: 100, sortable: true },
{ text: 'Tahun Bearing Kiri', datafield: 'yearns_l', width: 100, sortable: true },
{ text: 'Tahun Bearing Kanan', datafield: 'yearns_r', width: 120, sortable: true },
{ text: 'Jenama Bearing', datafield: 'bearingbrand', width: 100, sortable: true },
{text: 'Ujian Kecacatan', datafield: 'flawtest', width: 120, sortable: true },
{ text: 'Tarikh Terima', datafield: 'Tarikh Terima', width: 250 },
{text: "DiLuluskan Oleh", datafield: 'DiLuluskan Oleh', width: 200, cellsalign: 'right' }
]
$("#GridPanel").show("fast");
$("#RepGrid").jqxGrid({
rowdetails: false,
source: source,
columns: columns
});
}
}
});
$("#excelExport").on('click', function (e) {
window.open('data:application/vnd.ms-excel,' + $('#RepGrid').html());
e.preventDefault();
});
