Wednesday, 27 February 2019

JSON to Excel : How to export SharePoint list data into excel using JavaScript?


<script type="text/javascript" src="/SiteAssets/scripts/grid/jquery-2.2.4.min.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/xlsx.core.min.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/FileSaver.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/jhxlsx.js"></script>


$(document).ready(function(){
loadData();
});


var exportData=[[{"text":"Resource Name"},{"text":"Resource Email"},{"text":"Status1"},{"text":"Status2"},{"text":"Supervisor Email"},{"text":"Career Level"},{"text":"Current Work Location"},{"text":"Project"},{"text":"Enterprise ID"},{"text":"Start Date"}]];

function jsonToExcel()
{

var tabularData = [{
    "sheetName": "All Resources",
    "data": exportData
}];

var options = {
    fileName: "All Resources"
};
Jhxlsx.export(tabularData, options);

}

function loadData()
{//start loadData

$().SPServices({//sart service call
    operation: "GetListItems",
    async: false,
    listName: "Resource List",
    CAMLViewFields: "<ViewFields Properties='True' />",
    CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where><OrderBy><FieldRef Name='Column1' Ascending='True' /></OrderBy></Query>",
    CAMLRowLimit: 0,
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
 
      exportData.push(
      [
         {"text": $(this).attr("ows_Title")},
         {"text": $(this).attr("ows_emailaddress")==undefined?"-":($(this).attr("ows_emailaddress"))},
{"text": $(this).attr("ows_status1")==undefined?"-":($(this).attr("ows_status1"))},
{"text": $(this).attr("ows_Status2")==undefined?"-":($(this).attr("ows_Status2"))},
{"text": $(this).attr("ows_SupervisorEmailID")==undefined?"-":($(this).attr("ows_SupervisorEmailID"))},
{"text": $(this).attr("ows_career_x0020_Level")==undefined?"-":($(this).attr("ows_career_x0020_Level"))},
{"text": $(this).attr("ows_Curr_Loc")==undefined?"-":($(this).attr("ows_Curr_Loc"))},
{"text": $(this).attr("ows_Project")==undefined?"-":($(this).attr("ows_Project"))},
{"text": $(this).attr("ows_Employee_x0020_ID")==undefined?"-":($(this).attr("ows_Employee_x0020_ID"))},
{"text": $(this).attr("ows_Start_x0020_Date")==undefined?"-":($(this).attr("ows_Start_x0020_Date"))}
]
       );
     
      });//end loop
    }

  });//end service call

}//end loadData


Reference: https://www.jqueryscript.net/other/JavaScript-JSON-Data-Excel-XLSX.html
Posted by at No comments:
Labels: , ,

Tuesday, 26 February 2019

Clickable Div or Button : How to open a URL on click of table cell TD or DIV?


<td style="cursor:pointer;" onclick="window.open('https://url', '_blank')">

<input type="button" onclick="window.location.href='https://google.com';" value ="YOUR BUTTON TEXT"/>

<input type="button" onclick="window.open('https://google.com', '_blank')" value ="YOUR BUTTON TEXT"/>
Posted by at No comments:
Labels: , , , , , , ,

Thursday, 14 February 2019

How to convert Excel date number into JavaScript date format?



var dateVar = new Date(1900, 0, excelDate); //ignore leap year bug

var dateVar = new Date((excelDate - (25567 + 1))*86400*1000);

Posted by at No comments:

Excel to JSON : How to convert Excel data into JSON object using JavaScript?

<html>
<head>

<script type="text/javascript" src="./scripts/jquery-2.2.4.min.js"></script>
<script type="text/javascript" src="./scripts/jszip.js"></script>
<script type="text/javascript" src="./scripts/shim.min.js"></script>
<script type="text/javascript" src="./scripts/xlsx.js"></script>

<script language="javascript" type="text/javascript">
var _json_array;
$(document).ready(function(){
    $("#uploadingText").css("display", "none");
    $(function() {
        _fileIn = document.getElementById('fileToUpload');
        if(_fileIn.addEventListener) {
            _fileIn.addEventListener('change', readAllRows, false);//false: bubbling; true: capturing
        }
    });
});


function readAllRows(_event)
{
    try{
        var _file = _event.target.files[0];
        var _reader = new FileReader();
        _reader.onload = function(e) {
       
          var _data = e.target.result;
          var _workbook = XLSX.read(_data, {
            type: 'array',cellDates: true
          });
   
          _workbook.SheetNames.forEach(function(sheetName) {
            var _xl_row = XLSX.utils.sheet_to_json(_workbook.Sheets[sheetName]);
            _json_array = JSON.stringify(_xl_row); //last sheet
          })
        };
   
        _reader.onerror = function(ex) {
          alert('Something went wrong. Please try again after sometime.');
        };
       
        _reader.onloadend = function(ex) {
          uploadData();
        };
       
        _reader.readAsArrayBuffer(_file);//,{cellDates:true,cellText:false});  
    }
    catch(err)
    {
        alert('Invalid workbook.');
    }
}

function uploadData()
{
    jasonData = $.parseJSON(_json_array);
    console.log(jasonData[i]["ColumnName"])
}

</script>
</head>
<body>
    <input style="font-family:'Segoe UI';" type="file" id="fileToUpload" />
</body>
</html>


Note: Based on the timezone you may have to add a day to the date.

Posted by at No comments:
Labels: , , , ,

Thursday, 7 February 2019

SharePoint: How to prepare group-by data for tree like w2ui grid?


var listData=[];

function loadTable()
{//start loadTable
var val,index,cval,cindex,TypeOne,TypeTwo,Unknown,TypeOneTotal=0,TypeTwoTotal=0,UnknownTotal=0,firstItem=true,rid=0;
$().SPServices({//sart service call
    operation: "GetListItems",
    async: false,
    listName: "List Name",
    CAMLViewFields: "<ViewFields Properties='True' />",
    CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Conter'>0</Value></Neq></Where></Query>",
    CAMLRowLimit: 0,
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
   
        if($(this).attr("ows_Item_x0020_Type")==undefined)
        {
        TypeOne=0; TypeTwo=0; Unknown=1; UnknownTotal++;
        }
        else if($(this).attr("ows_Item_x0020_Type")=='TypeOne')
        {
        TypeOne=1; TypeTwo=0; Unknown=0; TypeOneTotal++;
        }
else if($(this).attr("ows_Item_x0020_Type")=='TypeTwo')
        {
        TypeOne=0; TypeTwo=1; Unknown=0; TypeTwoTotal++;
        }
        //First record
      if(firstItem==true)
      {
      listData.push({
      recid: ++rid,
      'Column 1': $(this).attr("ows_Column 1"),
      'Column 2': '...',
      'Unknown' : Unknown,
      'TypeOne' : TypeOne,
      'TypeTwo' : TypeTwo,
      'Total':1,
      w2ui: {
                    children: [
                        {
                        recid: ++rid,
                        'Column 1': '',
'Column 2': $(this).attr("ows_Column 2"),
'Unknown' : Unknown,
'TypeOne' : TypeOne,
'TypeTwo' : TypeTwo,
'Total':1,
      }
                    ]
                    }
      });
    firstItem=false;
    rid++;
      }
      else
      {

//Update Column 1 data
val = $(this).attr("ows_Column 1");
index=-1;
for (var i=0; i<listData.length; i++){
     if(listData[i].Column 1==val){
       index = i;
       break;
     }
   
  }

if(index==-1)
{
listData.push({
      recid: rid,
      'Column 1': $(this).attr("ows_Column 1"),
      'Column 2': '...',
      'Unknown' : Unknown,
      'TypeOne' : TypeOne,
      'TypeTwo' : TypeTwo,
      'Total':1,
      w2ui: {
                    children: [
                    {
                    recid: ++rid,
                        'Column 1': '',
      'Column 2': $(this).attr("ows_Column 2"),
      'Unknown' : Unknown,
      'TypeOne' : TypeOne,
      'TypeTwo' : TypeTwo,
      'Total':1,
      }
                    ]
                    }
      });
      rid++;
}
else
{
listData[index]['Unknown'] += Unknown;
listData[index]['TypeOne'] += TypeOne;
listData[index]['TypeTwo'] += TypeTwo;
listData[index]['Total'] += 1;


//Update Column 2 data
cval = $(this).attr("ows_Column 2");
cindex=-1;
for (var i=0; i<listData[index].w2ui.children.length; i++){
     if(listData[index].w2ui.children[i].Column 2==cval){
       cindex = i;
       break;
     }
   
  }

if(cindex==-1)
{
listData[index].w2ui.children.push({
      recid: rid,
      'Column 1': '',
      'Column 2': $(this).attr("ows_Column 2"),
      'Unknown' : Unknown,
      'TypeOne' : TypeOne,
      'TypeTwo' : TypeTwo,
      'Total':1,
      });
      rid++;
}
else
{
listData[index].w2ui.children[cindex]['Unknown'] += Unknown;
listData[index].w2ui.children[cindex]['TypeOne'] += TypeOne;
listData[index].w2ui.children[cindex]['TypeTwo'] += TypeTwo;
listData[index].w2ui.children[cindex]['Total'] += 1;
}

}
     
      }
     
      });
    }
  });//end service call

//summary row
listData.push({
      recid: rid,
      'Column 1': 'All',
      'Column 2': 'All',
      'Unknown' : UnknownTotal,
      'TypeOne' : TypeOneTotal,
      'TypeTwo' : TypeTwoTotal,
      'Total':UnknownTotal+TypeOneTotal+TypeTwoTotal,
      w2ui: { summary: true },
      });

  //someFunction();

}//end loadTable

Posted by at No comments:
Labels: , ,

Tuesday, 5 February 2019

w2ui Grid: How to set custom icon on toolbar buttons?


.custom-save
{
background-image: url("https://SharePoint/../SiteAssets/save-16.png");
    background-repeat: no-repeat;
    width: 12px;
    height: 12px;
    background-position: center;
}


toolbar: {
        items: [
        { type: 'break' },
            { type: 'button', id: 'savechanges', caption: 'Save Changes', img: 'custom-save', style:'color:#0062af;font-weight:bold;'}
            
        ],

Posted by at No comments:
Labels: ,
Subscribe to: Comments (Atom)