Excel Import

Excel Import can be handled using the SheetJS function. After downloading Excel, edit the Excel file and import it.

For information about SheetJS, see the link below. https://github.com/SheetJS/js-xlsx (opens in a new tab)

Example 1

How to use the provider's field name and Excel's column name by matching them

Tip

When exporting to Excel, exclude the footer of RealGrid. (When importing Excel, even the footer part is imported, so code to exclude the footer area is required.)
When importing Excel, the Excel header name becomes the property name of the JSON object, so it is recommended to set the RealGrid field name to Korean.

<input type="file" name="excelFile" id="xlf">

$("#xlf").bind("change", handleXlsFile);
 
function excelExport() {
   gridView.exportGrid({
     type: "excel",
     target: "local",
     fileName: "gridExportSample.xlsx",
     footer: "hidden",
     done: function () { //Function to be executed after export is complete
         alert("done excel export")
     }
   });
}
 
function fixdata(data) {
   var o = "", l = 0, w = 10240;
   for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
   o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
   return o;
}
 
function handleXlsFile(e) {
   var files = e.target.files;
   var i, f;
   for (i = 0, f = files[i]; i != files.length; ++i) {
       var reader = new FileReader();
       var name = f.name;
       reader.onload = function (e) {
           var data = e.target.result;
 
           //var workbook = XLSX.read(data, { type: 'binary' });
           var arr = fixdata(data);
           workbook = XLSX.read(btoa(arr), { type: "base64", cellText: true, cellDates: true });
 
           process_wb(workbook);
           /* DO SOMETHING WITH workbook HERE */
       };
       //reader.readAsBinaryString(f);
       reader.readAsArrayBuffer(f);
 
   }
}
 
function process_wb(wb) {
   var output = "";
 
   output = to_json(wb);
 
   var sheetNames = Object.keys(output);
 
   if (sheetNames.length > 0) {
       var colsObj = output[sheetNames][0];
 
       if (colsObj) {
           dataProvider.fillJsonData(output, { rows: sheetNames[0] })
       }
   }
}
 
function to_json(workbook) {
   var result = {};
   workbook.SheetNames.forEach(function (sheetName) {
       var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName], {rawNumbers: true});
 
       if (roa.length > 0) {
           result[sheetName] = roa;
       }
   });
   return result;
}

Example 2

How to keep the provider's field name in English and use Excel's column name in Korean.

Tip

How to convert an Excel file to JSON data using SheetJS and then change the key of the JSON data to match the Provider field. In the example, the map function provided in ES6 was used.

<input type="file" name="excelFile" id="xlf">

$("#xlf").bind("change", handleXlsFile);
 
function excelExport() {
   gridView.exportGrid({
     type: "excel",
     target: "local",
     fileName: "gridExportSample.xlsx",
     footer: "hidden",
     done: function () { //Function to be executed after export is complete
         alert("done excel export")
     }
   });
}
 
function fixdata(data) {
   var o = "", l = 0, w = 10240;
   for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
   o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
   return o;
}
 
function handleXlsFile(e) {
   var files = e.target.files;
   var i, f;
   for (i = 0, f = files[i]; i != files.length; ++i) {
       var reader = new FileReader();
       var name = f.name;
       reader.onload = function (e) {
           var data = e.target.result;
 
           //var workbook = XLSX.read(data, { type: 'binary' });
           var arr = fixdata(data);
           workbook = XLSX.read(btoa(arr), { type: "base64", cellText: true, cellDates: true });
 
           process_wb(workbook);
           /* DO SOMETHING WITH workbook HERE */
       };
       //reader.readAsBinaryString(f);
       reader.readAsArrayBuffer(f);
 
   }
}
 
function process_wb(wb) {
   var output = "";
   output = to_json(wb);
   var sheetNames = Object.keys(output);
 
   if (sheetNames.length > 0) {
       var colsObj = output[sheetNames][0];
 
       if (colsObj) {
           var data = output[sheetNames];
 
           var mappedData = data.map(item => {
             return {
               KorName: item.name,
               Gender: item.gender,
               Age: item.age,
               Phone: item.Phone number,
               ProductId: item.product number,
               KorCountry: item.investment country,
               OrderDate: item.order date,
               CardNumber: item.card number,
               Monetary: item.currency,
               StartDate: item.First payment date,
               EndDate: item.EndDate;
               ToMonth: item['Number of payments'],
               Month: item['Numbers remaining'],
               InterestRate: item.interest rate;
               SaveCost: item.payment,
               SaveMaturity: item.MaturityAmount
             };
           });
 
           //Set with Javascript for statement without using the map function
           /*
           var mappedData = [];
 
           for(var i = 0; i < data.length; i++){
             mappedData.push({
               KorName: data[i].name,
               Gender: data[i].gender,
               Age: data[i].age,
               Phone: data[i].Phone number,
               ProductId: data[i].product number,
               KorCountry: data[i].investment country,
               OrderDate: data[i].order date,
               CardNumber: data[i].card number,
               Monetary: data[i].currency,
               StartDate: data[i].First payment date,
               EndDate: data[i].EndDate,
               ToMonth: data[i]['Number of payments'],
               Month: data[i]['Numbers remaining'],
               InterestRate: data[i].interest rate,
               SaveCost: data[i].Payment,
               SaveMaturity: data[i].MaturityAmount
             })
           }
           */
 
 
           dataProvider.fillJsonData(mappedData, { })
       }
   }
}
 
function to_json(workbook) {
   var result = {};
   workbook.SheetNames.forEach(function (sheetName) {
       var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName], {rawNumbers: true});
 
       if (roa.length > 0) {
           result[sheetName] = roa;
       }
   });
   return result;
}

Check by linking child nodes in CheckBar Merge Callback