MediaWiki:Gadget-TabularImportExport.js
(Redirected from User:TheDJ/tabularImportExport.js)
Note: After saving, you have to bypass your browser's cache to see the changes. Internet Explorer: press Ctrl-F5, Mozilla: hold down Shift while clicking Reload (or press Ctrl-Shift-R), Opera/Konqueror: press F5, Safari: hold down Shift + Alt while clicking Reload, Chrome: hold down Shift while clicking Reload.
This user script seems to have a documentation page at MediaWiki:Gadget-TabularImportExport. |
/**
* Copyright (c) 2017 Derk-Jan Hartman [[User:TheDJ]]
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in all
* copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*/
/**
* This script provides import and export functionality of CSV and XLSX files
* into Commons' tabular data sets.
*
* Known issues:
* - Files saved in Safari are saved without filename
* - You always need to make sure your file has a header
* - Types of imported data are a bit of guess work
* - The localized type gets exported to a single localized string by the API
*/
(function( $, mw ) {
var selectFileWidget,
selectedFile,
api;
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
function datenum(v, date1904) {
if(date1904) v+=1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(var R = 0; R != data.length; ++R) {
for(var C = 0; C != data[R].length; ++C) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v === null) continue;
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if(!(this instanceof Workbook)) {
return new Workbook();
}
this.SheetNames = [];
this.Sheets = {};
}
var tabularImportExport = {
selectFileWidget: null,
install: function () {
api = new mw.Api();
if ( mw.config.get( 'wgAction' ) === 'edit'
|| mw.config.get( 'wgAction' ) === 'submit'
) {
selectFileWidget = new OO.ui.SelectFileWidget( { placeholder: 'A .csv or .xlsx file to import' } );
selectFileWidget.on( 'change', tabularImportExport.selectFile );
$( '#editform:not([readonly])' ).before( selectFileWidget.$element );
} else if( mw.config.get( 'wgAction' ) === 'view' ) {
var button1 = new OO.ui.ButtonWidget( { label: 'Export to CSV' } );
button1.on( 'click', function () {
tabularImportExport.fetchData( 'csv' );
} );
var button2 = new OO.ui.ButtonWidget( { label: 'Export to Excel' } );
button2.on( 'click', function () {
tabularImportExport.fetchData( 'excel' );
} );
var layout = new OO.ui.HorizontalLayout( {
items: [
button1,
button2
]
} );
$( '#mw-content-text' ).append( layout.$element );
}
},
fetchData: function(type) {
api.get( {
action: 'jsondata',
formatversion: 2,
format: 'json',
title: mw.config.get( 'wgTitle' )
} ).done( function( results ) {
if( type == 'csv') {
tabularImportExport.exportToCSV( results.jsondata );
} else if ( type == 'excel' ) {
tabularImportExport.exportToExcel( results.jsondata );
}
} );
},
exportToExcel: function( jsondata ) {
var workbook = tabularImportExport.convertToExcel( jsondata );
/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
var wopts = { bookType:'xlsx', bookSST:false, type:'binary' };
var wbout = XLSX.write( workbook, wopts );
/* the saveAs call downloads a file on the local machine */
saveAs( new Blob( [s2ab(wbout) ],
{ type: 'application/octet-stream' } ),
mw.config.get( 'wgTitle' ) + '.xlsx' );
},
exportToCSV: function( jsondata ) {
var rowArray,
csvdata = {
fields: [],
data: []
};
jsondata.schema.fields.forEach( function( element, index, array ) {
csvdata.fields.push( element.title || element.name );
} );
jsondata.data.forEach( function( row, yIndex ) {
rowArray = [];
row.forEach( function( xElement, xIndex ) {
if ( jsondata.schema.fields[ xIndex ].type === 'localized' ) {
rowArray.push( null );
} else {
rowArray.push( xElement );
}
} );
csvdata.data.push( rowArray );
} );
var csv = Papa.unparse( csvdata );
var blob = new Blob( [ csv ], { type: 'application/octet-stream' } );
saveAs( blob, mw.config.get( 'wgTitle' ) + '.csv');
},
convertToExcel: function( jsondata ) {
var wb = new Workbook();
var dataArray = [];
dataArray.push([]);
jsondata.schema.fields.forEach( function( element, index, array ) {
dataArray[0].push( element.title || element.name );
} );
jsondata.data.forEach( function( yElement, yIndex, row ) {
var rowData = [];
dataArray.push( yElement );
} );
var ws = sheet_from_array_of_arrays(dataArray);
var ws_name = mw.config.get('wgTitle')
.replace( /[/\\*'?[\]:]/g, ' ' )
.toLowerCase()
.substring(0, 31);
/* add ranges to worksheet */
ws['!merges'] = [];
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
wb.Props = {
Author: mw.config.get('wgUserName'),
SheetNames: wb.SheetNames,
Worksheets: wb.SheetNames.length
};
return wb;
},
selectFile: function() {
selectedFile = selectFileWidget.getValue();
var name = selectedFile.name;
if ( name.endsWith( '.csv' ) ) {
tabularImportExport.importCSVFile( selectedFile );
return;
} else if ( name.endsWith( '.xlsx' ) ) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read( data, {type: 'binary'} );
tabularImportExport.importXLSX(workbook);
}
reader.readAsBinaryString( selectedFile );
return;
} else {
OO.ui.alert( 'Unsupported file type. Please select either a .csv or .xlsx file.' );
}
},
importCSVFile: function(file) {
Papa.parse( file, {
header: true,
dynamicTyping: true,
complete: tabularImportExport.importCSV,
error: tabularImportExport.importCSVFailed,
encoding: 'utf-8'
} );
},
importCSV: function( csvdata ) {
var jsondata = {};
jsondata.schema = {};
jsondata.schema.fields = [];
jsondata.data = [];
csvdata.meta.fields.forEach( function( columnName ) {
jsondata.schema.fields.push( {
"name": columnName.replace( /\W/g, '' ).toLowerCase(),
"type": "string",
"title": {
"en": columnName
}
} );
} );
csvdata.data.forEach( function( row, index, array ) {
var columnData = [];
csvdata.meta.fields.forEach( function ( columnName ) {
columnData.push( row[ columnName ] === '' ? null : row[ columnName ] );
} );
jsondata.data.push( columnData );
} );
tabularImportExport.writeTextbox( jsondata );
},
importCSVFailed: function( error ) {
OO.ui.alert( 'Unable to import file: ' + error );
},
importXLSX: function( workbook ) {
var cell,
header,
rowData;
var jsondata = {};
jsondata.schema = {};
jsondata.schema.fields = [];
jsondata.data = [];
/* We only look at the first sheet */
var sheet = workbook.Sheets[ workbook.SheetNames[ 0 ] ];
var jsonsheet = XLSX.utils.sheet_to_json( sheet, { raw: true, defval:null } );
if ( jsonsheet.length < 2 ) {
return;
}
for ( header in jsonsheet[ 0 ] ) {
jsondata.schema.fields.push( {
"name": header.replace( /\W/g, '' ).toLowerCase(),
"type": "string",
"title": {
"en": header
}
} );
}
jsonsheet.forEach( function( row, index, array) {
rowData = [];
for ( header in jsonsheet[ 0 ] ) {
cell = row[ header ];
if ( cell === undefined || cell === '' ) {
cell = null;
}
rowData.push( cell );
}
jsondata.data.push( rowData );
} );
tabularImportExport.writeTextbox( jsondata );
},
writeTextbox: function ( jsondata ) {
var metadata, username;
/* Guess the data type, based on the first row of data */
jsondata.data[ 0 ].forEach( function( columnEl, index ) {
if ( typeof columnEl === 'number' ) {
jsondata.schema.fields[ index ][ 'type' ] = 'number';
}
if ( typeof columnEl === 'boolean' ) {
jsondata.schema.fields[ index ][ 'type' ] = 'boolean';
}
} );
/* Write some metadata */
username = mw.config.get( 'wgUserName' );
username = ( username ?
' by ' + mw.config.get( 'wgFormattedNamespaces' )[mw.config.get( 'wgNamespaceIds' )['user'] ]
+ ':' + username
: '' );
metadata = {
license: 'CC0-1.0',
description: {
en: 'Please enter a description'
},
sources: 'Imported from file ' + selectedFile.name + username
}
/* Merge it */
jsondata = $.extend( metadata, jsondata );
$('#wpTextbox1').textSelection( 'setContents', JSON.stringify( jsondata, null, '\t' ) );
$('#wpSummary').val( 'Importing data ' + selectedFile.name );
}
};
if ( mw.config.get( 'wgNamespaceNumber' ) === 486 && mw.config.get( 'wgTitle' ).endsWith( '.tab' ) ) {
mw.loader.using( [ 'oojs-ui', 'oojs-ui-core', 'oojs-ui-widgets', 'mediawiki.api', 'jquery.textSelection' ] ).done( function() {
$.when( [
$.getScript( 'https://tools-static.wmflabs.org/cdnjs/ajax/libs/PapaParse/4.1.2/papaparse.min.js' ),
$.getScript( 'https://tools-static.wmflabs.org/cdnjs/ajax/libs/xlsx/0.14.5/xlsx.full.min.js' ),
$.getScript( 'https://commons.wikimedia.org/w/index.php?title=User:TheDJ/fileSaver.js&action=raw&ctype=text/javascript' )
] ).done( function () {
$(tabularImportExport.install);
});
});
}
} ) ( jQuery, mediaWiki );