Export Records to an Excel File

Contents

Overview

This article introduces a method to output data as an Excel file directly from a Kintone App with one click.

Sample Image

In this example, a Custom View in the Kintone App displays the record data in a table format. Clicking the button above the table exports the data into an Excel file.

Prepare the App

Create the form

Create an App (External link) with the following fields.

Field type Field name Field code
Record number Record number record_number
Text Title title
Text area Details details

The form settings should look like the below screenshot.

Save the form when finished.

Custom a Custom View

Navigate to the Views tab and click the [+] button to create a new View. Select the Custom view option and enter the following code into the HTML Code option.

1
2
3
4
5

<button type="button" id="dl-xlsx">Download XLSX</button>
<h3>Record Data in This App</h3>

<table id="tablerecords" class="table-to-export" data-sheet-name="Sheet1">
</table>

The View settings should look like the below screenshot.

Save the View when finished.

Set the libraries

This example uses Kintone REST API Client (External link) , FileSaver (External link) v1.3.4 and SheetJS (External link) v0.20.1.

Set the following URLs and files into the Upload JavaScript for PC option of the App's JavaScript and CSS Customization settings (External link) .

  • File: FileSaver.min.js (downloaded from here (External link) )
  • File: xlsx.full.min.js (downloaded from here (External link) )
  • Link: Kintone Rest API Client - https://js.kintone.com/kintone-rest-api-client/5.0.7/KintoneRestAPIClient.min.js

Sample Code

Prepare the following JavaScript code in a text editor and navigate to the Kintone App's settings. Upload the file into the Upload JavaScript for PC option of the JavaScript and CSS Customization settings (External link) .

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104

/*
 * Kintone to Excel sample program
 * Copyright (c) 2018 Cybozu
 *
 * Licensed under the MIT License
 * https://opensource.org/license/mit/
*/
(() => {
  'use strict';
  /* global saveAs */
  const CYB = {};
  // Kintone fields to export in the xlsx file
  CYB.cols = ['title', 'details'];

  // Create element
  const createElement = (parent, element, id, css, attributes, html) => {
    const el = document.createElement(element);

    if (id) el.id = id;
    if (css) {
      Object.assign(el.style, css);
    }
    if (attributes) {
      Object.keys(attributes).forEach(attr => {
        el.setAttribute(attr, attributes[attr]);
      });
    }
    if (html) {
      el.innerHTML = html;
    }
    parent.appendChild(el);
  };

  // string to array buffer
  const s2ab = (s) => {
    const buf = new ArrayBuffer(s.length);
    const view = new Uint8Array(buf);

    for (let i = 0; i !== s.length; i += 1) {
      view[i] = s.charCodeAt(i) & 0xFF;
    }

    return buf;
  };

  // Create an XLSX file
  const makeExcelFile = () => {
    const workbookOptions = {
      bookType: 'xlsx',
      bookSST: false,
      type: 'binary'
    };
    const workbook = {SheetNames: [], Sheets: {}};
    const HTMLNodes = document.querySelectorAll('table.table-to-export');
    const node = Array.prototype.slice.call(HTMLNodes, 0);
    node.forEach((currentValue, index) => {
      let n = currentValue.getAttribute('data-sheet-name');

      if (!n) {
        n = 'Sheet' + index;
      }
      workbook.SheetNames.push(n);
      workbook.Sheets[n] = XLSX.utils.table_to_sheet(currentValue, workbookOptions);
    });
    const workbookOutput = XLSX.write(workbook, workbookOptions);
    saveAs(new Blob([s2ab(workbookOutput)], {type: 'application/octet-stream'}), 'test.xlsx');
  };
  // Operations to run when the Excel export view is opened
  const indexDisplayMakeExcelElement = async () => {
    const tab = document.getElementById('tablerecords');
    document.getElementById('dl-xlsx').addEventListener('click', makeExcelFile);
    try {
    // Get all Kintone data
      const client = new KintoneRestAPIClient();
      const allRecords = await client.record.getAllRecords({app: kintone.app.getId(), fields: CYB.cols});
      if (allRecords.length < 1) {
        throw new Error('There are no records to display.');
      } else {
        // Export Kintone data to a table
        allRecords.forEach((record, i) => {
          const trname = 'tr' + i;
          createElement(tab, 'tr', trname);
          const trElement = document.getElementById(trname);

          CYB.cols.forEach((col, j) => {
            const tdname = trname + 'td' + j;
            createElement(trElement, 'td', tdname,
              {borderWidth: 'thin', borderStyle: 'solid'},
              null, record[col].value.replace(/\r?\n/g, '<br>'));
          });
        });
      }
    } catch (error) {
      alert(error.message);
    }
  };
  // List view show event
  kintone.events.on(['app.record.index.show'], (event) => {
    if (document.getElementById('dl-xlsx')) {
      indexDisplayMakeExcelElement();
    }
    return event;
  });
})();

The JavaScript and CSS Customization* settings should look like the following screenshot.

caution
Attention

Caution: The order in which JavaScript and CSS are uploaded to an app matters. In this example, ensure that the jQuery, FileSaver, and SheetJS JavaScript imports are uploaded before the custom JavaScript file. You can change the order of uploads by clicking and dragging on the arrows for each item on the Upload JavaScript / CSS page.

After saving the settings and clicking on Update App, navigate to the Custom View. A button labeled Download XLSX should appear above the Custom View. Clicking the button should output the Kintone data in xlsx format.

Code Explanation

Lines 97-102 

1
2
3
4
5
6
7

// List view show event
kintone.events.on(['app.record.index.show'], (event) => {
  if (document.getElementById('dl-xlsx')) {
    indexDisplayMakeExcelElement();
  }
  return event;
});

Creates data for output when the app.record.index.show event is triggered as the page loads.

Lines 69-96 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

const indexDisplayMakeExcelElement = async () => {
  const tab = document.getElementById('tablerecords');
  document.getElementById('dl-xlsx').addEventListener('click', makeExcelFile);
  try {
    // Get all Kintone data
    const client = new KintoneRestAPIClient();
    const allRecords = await client.record.getAllRecords({app: kintone.app.getId(), fields: CYB.cols});
    if (allRecords.length < 1) {
      throw new Error('There are no records to display.');
    } else {
      // Export Kintone data to a table
      allRecords.forEach((record, i) => {
        const trname = 'tr' + i;
        createElement(tab, 'tr', trname);
        const trElement = document.getElementById(trname);

        CYB.cols.forEach((col, j) => {
          const tdname = trname + 'td' + j;
          createElement(trElement, 'td', tdname,
            {borderWidth: 'thin', borderStyle: 'solid'},
            null, record[col].value.replace(/\r?\n/g, '<br>'));
        });
      });
    }
  } catch (error) {
    alert(error.message);
  }
};

This function grabs the div created in the custom view by its ID. Then, using the KintoneRestAPIClient(), grabs all Kintone records, and creates an HTML table with the data.

Lines 16-32 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// Create element
const createElement = (parent, element, id, css, attributes, html) => {
  const el = document.createElement(element);

  if (id) el.id = id;
  if (css) {
    Object.assign(el.style, css);
  }
  if (attributes) {
    Object.keys(attributes).forEach(attr => {
      el.setAttribute(attr, attributes[attr]);
    });
  }
  if (html) {
    el.innerHTML = html;
  }
  parent.appendChild(el);
};

Creates an element from the retrieved data.

Lines 47-67 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

// Create the xlsx file
let makeExcelFile = function() {
  let workbookOptions, workbook, wbOutput, HTMLNodes, node;

  workbookOptions = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  };

  workbook = {SheetNames: [], Sheets: {}};
  HTMLNodes = document.querySelectorAll('table.table-to-export');
  node = Array.prototype.slice.call(HTMLNodes, 0);

  node.forEach(function(currentValue, index) {
    let n = currentValue.getAttribute('data-sheet-name');

    if (!n) {
      n = 'Sheet' + index;
    }
    workbook.SheetNames.push(n);
    workbook.Sheets[n] = XLSX.utils.table_to_sheet(currentValue, workbookOptions);
  });

  wbOutput = XLSX.write(workbook, workbookOptions);

  saveAs(new Blob([s2ab(wbOutput)], {type: 'application/octet-stream'}), 'test.xlsx');
};

This code creates an excel workbook using sheetjs, converts it into a blob, and then initiates the download using FileSaver.

Reference