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.
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
<buttontype="button"id="dl-xlsx">Download XLSX</button>
<h3>Record Data in This App</h3>
<tableid="tablerecords"class="table-to-export"data-sheet-name="Sheet1">
</table>
The View settings should look like the below screenshot.
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
.
/*
* 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) {
thrownewError('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.
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;
});
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) {
thrownewError('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);
};
with the following fields.