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.

Screenshot: Kintone outputting 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.

Screenshot: The Kintone App's form.

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="tabrecs" class="table-to-export" data-sheet-name="Sheet1">
</table>

The View settings should look like the below screenshot.

Screenshot: The Custom View settings.

Save the View when finished.

Set the libraries

This example uses jQuery (External link) v3.3.1, FileSaver (External link) v1.3.4 and SheetJS (External link).

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

  • URL: https://js.kintone.com/jquery/3.3.1/jquery.min.js
  • File: FileSaver.min.js (downloaded from here (External link))
  • File: xlsx.full.min.js (downloaded from here (External link))

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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

/*
 * Kintone to Excel sample program
 * Copyright (c) 2018 Cybozu
 *
 * Licensed under the MIT License
*/
(function() {

  'use strict';

  /* global $ */
  /* global kintone */
  /* global XLSX */
  /* global saveAs */
  /* global Blob */

  var CYB = {};

  // Kintone fields to export in the xlsx file
  CYB.cols = ['title', 'detail'];

  // Create element
  var createElement = function(parent, element, id, objcss, objattr, html) {
    var el = $('<' + element + '>');

    el.attr('id', id);
    if (objcss) {
      el.css(objcss);
    }
    if (objattr) {
      el.attr(objattr);
    }
    if (html) {
      el.html(html);
    }
    $(parent).append(el);
  };

  // Search within the Kintone records
  function fetchRecords(appId, query, opt_offset, opt_limit, opt_records) {
    var offset = opt_offset || 0,
      limit = opt_limit || 100,
      allRecords = opt_records || [],
      params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset};

    return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
      allRecords = allRecords.concat(resp.records);
      if (resp.records.length === limit) {
        return fetchRecords(appId, query, offset + limit, limit, allRecords);
      }
      return allRecords;
    });
  }

  // String to array buffer
  var s2ab = function(s) {
    var buf = new ArrayBuffer(s.length),
      view = new Uint8Array(buf);

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

    return buf;
  };

  // Create the xlsx file
  var makeExcelFile = function() {
    var wopts, workbook, wbout, nodelist, node;

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

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

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

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

    wbout = XLSX.write(workbook, wopts);

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

  // Operations to run when the Excel export view is opened
  var indexDisplayMakeExcelElement = function(el) {
    var tab, trname, tdname;

    $('#dl-xlsx').click(makeExcelFile);

    // Get all Kintone data
    return fetchRecords(kintone.app.getId(), '$id>0').then(function(resrec) {

      if (resrec.length < 1) {

        throw new Error('There are no records');

      } else {

        tab = $('#tabrecs')[0];

        // Export Kintone data to a table
        for (var i = 0; i < resrec.length; i += 1) {

          trname = 'tr' + i;
          createElement(tab, 'tr', trname);

          for (var j = 0; j < CYB.cols.length; j += 1) {
            tdname = trname + 'td' + j;
            createElement($('#' + trname)[0], 'td', tdname,
              {'border-width': 'thin', 'border-style': 'solid'},
              null, resrec[i][CYB.cols[j]].value.replace(/\r?\n/g, '<br>'));
          }
        }
      }
    }).catch(function(dep) {
      alert(dep.message);
    });

  };


  // List view show event
  kintone.events.on(['app.record.index.show'], function(ev) {
    if ($('#dl-xlsx').length > 0) {
      indexDisplayMakeExcelElement();
    }
  });

})();

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

Screenshot: The JavaScript and CSS Customization settings

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 132-136

1
2
3
4
5
6

// List view show event
kintone.events.on(['app.record.index.show'], function(ev) {
  if ($('#dl-xlsx').length > 0) {
    indexDisplayMakeExcelElement();
  }
});

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

Lines 33-47

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

// Search within the Kintone records
function fetchRecords(appId, query, opt_offset, opt_limit, opt_records) {
  var offset = opt_offset || 0,
    limit = opt_limit || 100,
    allRecords = opt_records || [],
    params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset};

  return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
    allRecords = allRecords.concat(resp.records);
    if (resp.records.length === limit) {
      return fetchRecords(appId, query, offset + limit, limit, allRecords);
    }
    return allRecords;
  });
}

Uses the function fetchRecords() to retrieve all Kintone records.

Lines 22-31

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

// Create element
var createElement = function(parent, element, id, objcss, objattr, html) {
  var el = $('<' + element + '>');

  el.attr('id', id);
  if (objcss) {
    el.css(objcss);
  }
  if (objattr) {
    el.attr(objattr);
  }
  if (html) {
    el.html(html);
  }
  $(parent).append(el);
};

Creates an element from the retrieved data.

Lines 105-117

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// Export Kintone data to a table
for (var i = 0; i < resrec.length; i += 1) {

  trname = 'tr' + i;
  createElement(tab, 'tr', trname);

  for (var j = 0; j < CYB.cols.length; j += 1) {
    tdname = trname + 'td' + j;
    createElement($('#' + trname)[0], 'td', tdname,
      {'border-width': 'thin', 'border-style': 'solid'},
      null, resrec[i][CYB.cols[j]].value.replace(/\r?\n/g, '<br>'));
  }
}

Creates an HTML table from the element.

Lines 61-86

 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
var makeExcelFile = function() {
  var wopts, workbook, wbout, nodelist, node;

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

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

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

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

  wbout = XLSX.write(workbook, wopts);

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

Reference