javascript - After clicking Export to Excel. in Excel Duration time is not Displaying correct -
here trying correct duration time not able add punch in , punch out time in duration column.
i tried not getting correct result per requirement me. when excel file download should display correct duration time.
@(html.kendo().grid(model) .name("timetrackinglogsgrid") .columns(columns => { columns.bound(c => c.id).hidden(true).htmlattributes(new { style = "text-align:center;" }); columns.bound(c => c.isdeleted).hidden(true); columns.bound(c => c.approvalrequired).visible((bool)viewbag.isadmin).clienttemplate("# if (approvalrequired == true && approved == false && '" + (viewbag.isadmin).tostring() + "'=== 'true') {# yes: <a data-ajax='true' data-ajax-complete='refreshgrid' data-ajax-method='post' href='/punch/approve/#:id#' class='grid-link' >approve</a>#} else{# no #}#"); columns.bound(c => c.employeename).filterable(ftb => ftb.multi(true).search(true)); columns.bound(c => c.month).filterable(ftb => ftb.multi(true)); columns.bound(c => c.punchdate) .clientfootertemplate("#= getformattddate(data.punchdate.min) # - #= getformattddate(data.punchdate.max) # ") .clientgroupheadertemplate("#:convetutcdatetolocaldatebyinput(value)#") .htmlattributes(new { @class = "utc-date", utc_date_value = "#:punchdate#" }); columns.bound(c => c.punchinpic).hidden(true).visible((bool)viewbag.isadmin).clienttemplate("<img class='punchpic' width='100' src='data: image / png; base64,#:punchinpic#'/>").htmlattributes(new { style = "text-align:center;" }); columns.bound(c => c.punchin).groupable(false).filterable(false).clienttemplate("#:punchin.hours#:#:punchin.minutes#").htmlattributes(new { @class = "utc-time", utc_time_value = "#:punchin.hours#:#:punchin.minutes#" }); columns.bound(c => c.punchoutpic).hidden(true).visible((bool)viewbag.isadmin).clienttemplate("<img class='punchpic' width='100' src='data: image / png; base64,#:punchoutpic#'/>").htmlattributes(new { style = "text-align:center;" }); columns.bound(c => c.punchout).groupable(false).filterable(false).clienttemplate("#:punchout == null ? 0 :punchout.hours #:#: punchout == null ? 0 : punchout.minutes #").htmlattributes(new { @class = "utc-time", utc_time_value = "#: punchout == null ? 0 : punchout.hours#:#:punchout == null ? 0 : punchout.minutes #" }); columns.bound(c => c.duration).groupable(false).filterable(false) .clientfootertemplate("#= secondstotime(data.durationinseconds.sum) # ") .clienttemplate("#:formatduration(duration.hours,duration.minutes) #").htmlattributes(new { @class = "" }); columns.bound(c => c.comments).visible((bool)viewbag.isadmin).filterable(false).groupable(false).clienttemplate("# if (approvalrequired == true && approved == false && '" + (viewbag.isadmin).tostring() + "' !== 'true') {# please time log approved #} else{#<span class='comments' (#:comments# == null ? '' : title='<ul> #:comments# </ul>')> view <span>#}#"); columns.command(command => { // command.edit().text(" "); command.destroy().text(" "); }).width(50); }) .htmlattributes(new { @class = "" }) //.scrollable() .groupable() .sortable() .filterable() .navigatable() .columnmenu() .toolbar(toolbar => { //toolbar.create(); //toolbar.save(); //toolbar.pdf(); toolbar.excel(); toolbar.custom().text("clear filter").htmlattributes(new { @class = "cleargridfilter" }); }) .excel(exl => exl.allpages(true).filterable(true).filename("timesheet.xlsx") .proxyurl(url.action("exportsave", "punch"))) .pdf(pdf => pdf .allpages() .title("time tracker report") .subject("daily activity") .avoidlinks() .papersize("a4") .scale(0.8) .margin("2cm", "1cm", "1cm", "1cm") .landscape() .repeatheaders() .templateid("page-template") .filename("officegx result") .proxyurl(url.action("exportsave", "punch")) ) .events(events => events.databound("convertutctolocal").pdfexport("onpunchreportpdfexport").excelexport("onpunchreportexcelexport")) //.editable(editable => editable.mode(grideditmode.incell)) .pageable(pageable => pageable .refresh(true) .pagesizes(new int[] { 10, 20, 50, 100, 500 }) .buttoncount(5)) .resizable(resize => resize.columns(true)) .reorderable(reorder => reorder.columns(true)) .datasource(datasource => datasource .ajax() .filter(f => { f.add(field => field.isdeleted).isequalto(false); //f.add(field => field.punchdate).isgreaterthan(datetime.now.adddays(-1)); }) .batch(true) .pagesize(20) .serveroperation(false) .events(events => events.error("griderrorhandler")) .aggregates(aggrgates => aggrgates.add(x => x.durationinseconds).sum()) .aggregates(aggrgates => aggrgates.add(x => x.punchdate).min()) .aggregates(aggrgates => aggrgates.add(x => x.punchdate).max()) .model(model => { model.id(x => x.id); model.field(c => c.id).editable(false); }) .sort(sort => { sort.add(field => field.punchdate).descending(); }) //.group(groups=> groups.add(x=>x.employeename)) .group(groups => { //groups.add(x => x.month); }) .read(read => read.action("read", "punch")) //.create(update => update.action("add", "ticket")) //.update(update => update.action("update", "punch")) .destroy(update => update.action("delete", "punch")) ) ) this .cs file
public class punch { [key] public int id { get; set; } public datetime punchdate { get; set; } public timespan punchin { get; set; } [notmapped] public string month { { var in = timezoneinfo.converttimefromutc(punchdate.date + punchin, timezoneinfo.findsystemtimezonebyid(user.registeredtimezone)); return in.tostring("mmmm"); } } public timespan? punchout { get; set; } public int userid { get; set; } public string userguid { get; set; } public bool approved { get; set; } public bool approvalrequired { get; set; } //[scaffoldcolumn()] public string comments { get; set; } public string ipaddress { get; set; } public string macaddress { get; set; } [notmapped] public string employeename => user != null ? $"{user.firstname} {user.lastname}" : string.empty; public int hours { { return punchout.hasvalue ? convert.toint32(punchout.value.subtract(punchin).totalhours) : 0; } set { } } public double durationinseconds { get; set; } [notmapped] public timespan duration { { var out = punchout.hasvalue ? timezoneinfo.converttimefromutc(punchdate.date + punchout.value, timezoneinfo.findsystemtimezonebyid(user == null ? "india standard time" : user.registeredtimezone)) : datetime.minvalue; var in = timezoneinfo.converttimefromutc(punchdate.date + punchin, timezoneinfo.findsystemtimezonebyid(user == null ? "india standard time": user.registeredtimezone)); if (punchout.hasvalue) { var duration = new timespan(out.timeofday.hours, out.timeofday.minutes, out.timeofday.seconds) - new timespan(in.timeofday.hours, in.timeofday.minutes, in.timeofday.seconds); return duration; } return datetime.minvalue.timeofday; } set { } } public int approvedhours { { if (punchout != null) return approvalrequired ? (approved ? punchout.value.subtract(punchin).seconds : 0) : punchout.value.subtract(punchin).seconds; return 0; } set { } } this .js file
function onpunchreportexcelexport(e) { var sheet = e.workbook.sheets[0]; var rows = sheet.rows; var rowidx, colidx, cells, cell; (rowidx = 0; rowidx < rows.length; rowidx++) { if (rows[rowidx].type === "data") { cells = rows[rowidx].cells; var color = "#ffffff"; if (cells[0].value === true) { color = "#ff0000"; } (colidx = sheet.freezepane.colsplit; colidx < cells.length; colidx++) { debugger; cell = cells[colidx]; cell.background = color; //cell.value = kendo.tostring(cell.value, "c"); if (colidx === 6) { cell.value = cell.value.hours + ":" + cell.value.minutes; } else if (cell.value !== undefined && cell.value !== null && cell.value.hours !== undefined) { cell.value = utctimetolocaltime(cell.value.hours + ":" + cell.value.minutes); } else if (cell.value !== undefined && cell.value !== null && cell.value === true) { cell.value = "yes"; } else if (cell.value !== undefined && cell.value !== null && cell.value === false) { cell.value = "no"; } } } } } $(function () { var exportflag = false; $("#timetrackinglogsgrid").data("kendogrid").bind("excelexport", function (e) { if (!exportflag) { var sheet = e.workbook.sheets[0]; var columns = e.sender.columns; //jquery.each(columns, // function (index) { // if (this.template) { // var template = kendo.template(this.template); // (var = 1; < sheet.rows.length; i++) { // var row = sheet.rows[i]; // var dataitem = { // unitprice: row.cells[index].value // }; // row.cells[index].value = template(dataitem); // } // } // }); //var columns = e.sender.columns; //jquery.each(columns, // function (index) { // if (this.attributes !== undefined && this.attributes.class !== undefined) { // if (this.attributes.class === "excel-export-field-hide") { // this.hidden = true; // } // if (this.attributes.class === "excel-export-field-show") { // this.hidden = false; // } // } // }); //e.sender.showcolumn(e.sender.columns.length + 1); e.preventdefault(); exportflag = true; settimeout(function () { e.sender.saveasexcel(); }, 1000); } else { // e.sender.hidecolumn(0); //e.sender.hidecolumn(e.sender.columns.length + 1); exportflag = false; } }); }); 
Comments
Post a Comment