MediaWiki:Gadget-TabularImportExport.js

/**
 * 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 );