
# 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

## Example 1

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

### Tip

1. 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.)
2. 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.

```js
const fields = [
  { fieldName: 'Name', dataType: 'text' },
  { fieldName: 'Gender', dataType: 'text' },
  { fieldName: 'Age', dataType: 'number' },
  { fieldName: 'Phone', dataType: 'text' },
  { fieldName: 'Product ID', dataType: 'text' },
  { fieldName: 'Investment Country', dataType: 'text' },
  { fieldName: 'Order Date', dataType: 'datetime', datetimeFormat: 'yyyy-MM-dd', amText: 'AM', pmText: 'PM' },
  { fieldName: 'Card Number', dataType: 'text' },
  { fieldName: 'Currency', dataType: 'text' }
];

const columns = [
  { name: 'Name', fieldName: 'Name', width: '60', header: { text: 'Name' } },
  { name: 'Gender', fieldName: 'Gender', width: '40', header: { text: 'Gender' } },
  { name: 'Age', fieldName: 'Age', width: '40', header: { text: 'Age' }, styleName: 'right-column' },
  { name: 'Phone', fieldName: 'Phone', width: '100', header: { text: 'Phone' } },
  { name: 'Product ID', fieldName: 'Product ID', width: '120', header: { text: 'Product ID' } },
  { name: 'Investment Country', fieldName: 'Investment Country', width: '100', header: { text: 'Investment Country' } },
  { name: 'Order Date', fieldName: 'Order Date', width: '100', header: { text: 'Order Date' } },
  { name: 'Card Number', fieldName: 'Card Number', width: '140', header: { text: 'Card Number' } },
  { name: 'Currency', fieldName: 'Currency', width: '40', header: { text: 'Currency' } }
];

const { gridView, dataProvider } = RealGrid.createGrid('realgrid', {
  columns,
  header: {
    height: 40
  },
  display: {
    rowHeight: 36,
    emptyMessage: 'No data to display.'
  },
  edit: {
    insertable: true,
    appendable: true
  },
  footer: {
    height: 40
  },
  stateBar: {
    width: 16
  },
  onInitialized: (grid) => {
    fetch('/data/en/simple_data_han.json')
      .then(response => response.json())
      .then(data => {
        grid.getDataSource().setRows(data);
      });
  }
}, {
  fields
});

window.gridView = gridView;
window.dataProvider = dataProvider;

window.excelExport = function() {
  gridView.exportGrid({
    type: 'excel',
    target: 'local',
    fileName: 'gridExportSample.xlsx',
    footer: 'hidden',
    done: function () {
      alert('done excel export');
    }
  });
};

window.gridClear = function() {
  dataProvider.clearRows();
};

window.fixdata = function(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;
};

window.handleXlsFile = function(e) {
  const files = e.target.files;
  if (files.length === 0) return;

  const file = files[0];
  const reader = new FileReader();

  reader.onload = function (e) {
    const data = e.target.result;
    const arr = window.fixdata(data);
    const workbook = XLSX.read(btoa(arr), { type: 'base64', cellText: true, cellDates: true });
    window.processWorkbook(workbook);
  };

  reader.readAsArrayBuffer(file);
};

window.processWorkbook = function(wb) {
  const output = window.toJson(wb);
  const sheetNames = Object.keys(output);

  if (sheetNames.length > 0) {
    const colsObj = output[sheetNames[0]][0];
    if (colsObj) {
      dataProvider.fillJsonData(output, { rows: sheetNames[0] });
    }
  }
};


window.toJson = function(workbook) {
  const result = {};
  workbook.SheetNames.forEach(function (sheetName) {
    const roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName], { rawNumbers: true, raw: false });
    if (roa.length > 0) {
      result[sheetName] = roa;
    }
  });
  return result;
};

// DOM is already ready when code runs in runner.html, so setTimeout is used
setTimeout(function() {
  console.log(JSZip.version)
  const fileInput = document.getElementById('xlf');
  if (fileInput) {
    fileInput.addEventListener('change', window.handleXlsFile);
    console.log('Excel import event listener registered');
  } else {
    console.error('File input not found');
  }
}, 100);
```

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

```js
$("#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, raw: false});

       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

1. 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.

```js
const fields = [
  { fieldName: 'KorName', dataType: 'text' },
  { fieldName: 'Gender', dataType: 'text' },
  { fieldName: 'Age', dataType: 'number' },
  { fieldName: 'Phone', dataType: 'text' },
  { fieldName: 'ProductId', dataType: 'text' },
  { fieldName: 'KorCountry', dataType: 'text' },
  { fieldName: 'OrderDate', dataType: 'datetime', datetimeFormat: 'yyyy-MM-dd', amText: 'AM', pmText: 'PM' },
  { fieldName: 'CardNumber', dataType: 'text' },
  { fieldName: 'Monetary', dataType: 'text' },
  { fieldName: 'StartDate', dataType: 'datetime', datetimeFormat: 'yyyy-MM-dd', amText: 'AM', pmText: 'PM' },
  { fieldName: 'EndDate', dataType: 'datetime', datetimeFormat: 'yyyy-MM-dd', amText: 'AM', pmText: 'PM' },
  { fieldName: 'ToMonth', dataType: 'number' },
  { fieldName: 'Month', dataType: 'number' },
  { fieldName: 'Year', dataType: 'number' },
  { fieldName: 'InterestRate', dataType: 'number' },
  { fieldName: 'SaveCost', dataType: 'number' },
  { fieldName: 'SaveMaturity', dataType: 'number' },
  { fieldName: 'CurrentSave', dataType: 'number' }
];

const columns = [
  { name: 'KorName', fieldName: 'KorName', width: '60', header: { text: 'Name' }, renderer: { type: 'text' } },
  { name: 'Gender', fieldName: 'Gender', width: '40', header: { text: 'Gender' }, renderer: { type: 'text' } },
  { name: 'Age', fieldName: 'Age', width: '40', header: { text: 'Age' }, renderer: { type: 'text' } },
  { name: 'Phone', fieldName: 'Phone', width: '100', header: { text: 'Phone' }, renderer: { type: 'text' } },
  { name: 'ProductId', fieldName: 'ProductId', width: '120', header: { text: 'Product ID' }, renderer: { type: 'text' } },
  { name: 'KorCountry', fieldName: 'KorCountry', width: '100', header: { text: 'Investment Country' }, renderer: { type: 'text' } },
  { name: 'OrderDate', fieldName: 'OrderDate', width: '100', header: { text: 'Order Date' } },
  { name: 'CardNumber', fieldName: 'CardNumber', width: '140', header: { text: 'Card Number' }, renderer: { type: 'text' } },
  { name: 'Monetary', fieldName: 'Monetary', width: '40', header: { text: 'Currency' }, renderer: { type: 'text' } },
  { name: 'StartDate', fieldName: 'StartDate', width: '100', header: { text: 'First Payment Date' } },
  { name: 'EndDate', fieldName: 'EndDate', width: '100', header: { text: 'End Date' } },
  { name: 'ToMonth', fieldName: 'ToMonth', width: '40', header: { text: 'Payment Count' } },
  { name: 'Month', fieldName: 'Month', width: '40', header: { text: 'Remaining Count' } },
  { name: 'InterestRate', fieldName: 'InterestRate', width: '40', numberFormat: '0.00', header: { text: 'Interest Rate' } },
  { name: 'SaveCost', fieldName: 'SaveCost', width: '80', numberFormat: '#,##0', header: { text: 'Payment Amount' } },
  { name: 'SaveMaturity', fieldName: 'SaveMaturity', width: '80', numberFormat: '#,##0', header: { text: 'Maturity Amount' } },
  { name: 'CurrentSave', fieldName: 'CurrentSave', width: '80', numberFormat: '#,##0', header: { text: 'Current Balance' } }
];

const { gridView, dataProvider } = RealGrid.createGrid('realgrid', {
  columns,
  header: {
    height: 40
  },
  display: {
    rowHeight: 36,
    emptyMessage: 'No data to display.'
  },
  edit: {
    insertable: true,
    appendable: true
  },
  footer: {
    height: 40
  },
  stateBar: {
    width: 16
  },
  onInitialized: (grid) => {
    fetch('/data/en/simple_data.json')
      .then(response => response.json())
      .then(data => {
        grid.getDataSource().setRows(data);
      });
  }
}, {
  fields
});

window.gridView = gridView;
window.dataProvider = dataProvider;

window.excelExport = function() {
  gridView.exportGrid({
    type: 'excel',
    target: 'local',
    fileName: 'gridExportSample.xlsx',
    footer: 'hidden',
    done: function () {
      alert('done excel export');
    }
  });
};

window.gridClear = function() {
  dataProvider.clearRows();
};

window.fixdata = function(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;
};

window.handleXlsFile = function(e) {
  const files = e.target.files;
  if (files.length === 0) return;

  const file = files[0];
  const reader = new FileReader();

  reader.onload = function (e) {
    const data = e.target.result;
    const arr = window.fixdata(data);
    const workbook = XLSX.read(btoa(arr), { type: 'base64', cellText: true, cellDates: true });
    window.processWorkbook(workbook);
  };

  reader.readAsArrayBuffer(file);
};

window.processWorkbook = function(wb) {
  const output = window.toJson(wb);
  const sheetNames = Object.keys(output);

  if (sheetNames.length > 0) {
    const colsObj = output[sheetNames[0]][0];
    if (colsObj) {
      const rawData = output[sheetNames[0]];
      const mappedData = rawData.map(item => {
        return {
          KorName: item.Name,
          Gender: item.Gender,
          Age: item.Age,
          Phone: item.Phone,
          ProductId: item.Product ID,
          KorCountry: item.Investment Country,
          OrderDate: item.Order Date,
          CardNumber: item.Card Number,
          Monetary: item.Currency,
          StartDate: item.First Payment Date,
          EndDate: item.End Date,
          ToMonth: item['Payment Count'],
          Month: item['Remaining Count'],
          InterestRate: item.Interest Rate,
          SaveCost: item.Payment Amount,
          SaveMaturity: item.Maturity Amount
        };
      });
      dataProvider.fillJsonData(mappedData, {});
    }
  }
};

window.toJson = function(workbook) {
  const result = {};
  workbook.SheetNames.forEach(function (sheetName) {
    const roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName], { rawNumbers: true, raw: false });
    if (roa.length > 0) {
      result[sheetName] = roa;
    }
  });
  return result;
};

// DOM is already ready when code runs in runner.html, so setTimeout is used
setTimeout(function() {
  const fileInput = document.getElementById('xlf');
  if (fileInput) {
    fileInput.addEventListener('change', window.handleXlsFile);
    console.log('Excel import event listener registered');
  } else {
    console.error('File input not found');
  }
}, 100);
```

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

```js
$("#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, raw: false});

       if (roa.length > 0) {
           result[sheetName] = roa;
       }
   });
   return result;
}
```