Purpose:
Converting two-dimensional structure tables such as xls to tab-separated txt text content
Usually used for the transformation of txt database (emphasis: go cell, return line, tab)
Advantage: independent of the operating environment. The local computer can open a single page html file. ,
Based on: SimpleExcel.js v0.1.3 has been streamlined and modified.
Two steps:
1. Save your excel 2-D structure table as xml 2003 (.xml) file
2. Save the following single-page source code as. html file and double-click access (locally available)
3. Select the xml file you just saved, and you can get the text file with separate tabs and no return line change and tab interference.
Three generations of codes:
<!doctype html>
<html>
<head>
<script type="text/javascript" >
// SimpleExcel.js v0.1.3
// https://github.com/faisalman/simple-excel-js
// Copyright © 2013-2014 Faisal Salman <fyzlman@gmail.com>
// Dual licensed under GPLv2 & MIT
(function (window, undefined) {
'use strict';
///////////////////////
// Constants & Helpers
///////////////////////
var Char = {
COMMA : ',',
RETURN : '\r',
NEWLINE : '\n',
SEMICOLON : ';',
TAB : '\t'
};
var DataType = {
CURRENCY : 'CURRENCY',
DATETIME : 'DATETIME',
FORMULA : 'FORMULA',
LOGICAL : 'LOGICAL',
NUMBER : 'NUMBER',
TEXT : 'TEXT'
};
var Exception = {
CELL_NOT_FOUND : 'CELL_NOT_FOUND',
COLUMN_NOT_FOUND : 'COLUMN_NOT_FOUND',
ROW_NOT_FOUND : 'ROW_NOT_FOUND',
ERROR_READING_FILE : 'ERROR_READING_FILE',
ERROR_WRITING_FILE : 'ERROR_WRITING_FILE',
FILE_NOT_FOUND : 'FILE_NOT_FOUND',
//FILE_EXTENSION_MISMATCH : 'FILE_EXTENSION_MISMATCH',
FILETYPE_NOT_SUPPORTED : 'FILETYPE_NOT_SUPPORTED',
INVALID_DOCUMENT_FORMAT : 'INVALID_DOCUMENT_FORMAT',
INVALID_DOCUMENT_NAMESPACE : 'INVALID_DOCUMENT_NAMESPACE',
MALFORMED_JSON : 'MALFORMED_JSON',
UNIMPLEMENTED_METHOD : 'UNIMPLEMENTED_METHOD',
UNKNOWN_ERROR : 'UNKNOWN_ERROR',
UNSUPPORTED_BROWSER : 'UNSUPPORTED_BROWSER'
};
var Format = {
XML : 'xml'
};
var MIMEType = {
XML : 'text/xml',
XML2003 : 'application/xml'
};
var Regex = {
FILENAME : /.*\./g,
LINEBREAK : /\r\n?|\n/g,
COMMA : /(,)(?=(?:[^"]|"[^"]*")*$)/g,
QUOTATION : /(^")(.*)("$)/g,
TWO_QUOTES : /""/g
};
var Utils = {
getFiletype : function (filename) {
return filename.replace(Regex.FILENAME, '');
},
isEqual : function (str1, str2, ignoreCase) {
return ignoreCase ? str1.toLowerCase() == str2.toLowerCase() : str1 == str2;
},
isSupportedBrowser: function() {
return !![].forEach && !!window.FileReader;
},
overrideProperties : function (old, fresh) {
for (var i in old) {
if (old.hasOwnProperty(i)) {
old[i] = fresh.hasOwnProperty(i) ? fresh[i] : old[i];
}
}
return old;
}
};
// Spreadsheet Constructors
var Cell = function (value, dataType) {
var defaults = {
value : value || '',
dataType : dataType || DataType.TEXT
};
if (typeof value == typeof {}) {
defaults = Utils.overrideProperties(defaults, value);
}
this.value = defaults.value;
this.dataType = defaults.dataType;
this.toString = function () {
return value.toString();
};
};
var Records = function() {};
Records.prototype = [];
Records.prototype.getCell = function(colNum, rowNum) {
return this[rowNum - 1][colNum - 1];
};
Records.prototype.getColumn = function (colNum) {
var col = [];
this.forEach(function (el, i) {
col.push(el[colNum - 1]);
});
return col;
};
Records.prototype.getRow = function (rowNum) {
return this[rowNum - 1];
};
var Sheet = function () {
this.records = new Records();
};
Sheet.prototype.getCell = function (colNum, rowNum) {
return this.records.getCell(colNum, rowNum);
};
Sheet.prototype.getColumn = function (colNum) {
return this.records.getColumn(colNum);
};
Sheet.prototype.getRow = function (rowNum) {
return this.records.getRow(rowNum);
};
Sheet.prototype.insertRecord = function (array) {
this.records.push(array);
return this;
};
Sheet.prototype.removeRecord = function (index) {
this.records.splice(index - 1, 1);
return this;
};
Sheet.prototype.setRecords = function (records) {
this.records = records;
return this;
};
// Base Class
var BaseParser = function () {};
BaseParser.prototype = {
_filetype : '',
_sheet : [],
getSheet : function(number) {
number = number || 1;
return this._sheet[number - 1].records;
},
loadFile : function (file, callback) {
var self = this;
//var filetype = Utils.getFiletype(file.name);
//if (Utils.isEqual(filetype, self._filetype, true)) {
var reader = new FileReader();
reader.onload = function () {
self.loadString(this.result, 0);
callback.apply(self);
};
reader.readAsText(file);
//} else {
//throw Exception.FILE_EXTENSION_MISMATCH;
//}
return self;
},
loadString : function (string, sheetnum) {
throw Exception.UNIMPLEMENTED_METHOD;
}
};
// XML
var XMLParser = function () {};
XMLParser.prototype = new BaseParser();
XMLParser.prototype._filetype = Format.XML;
XMLParser.prototype.loadString = function(str, sheetnum) {
var self = this;
var domParser = new DOMParser();
var domTree = domParser.parseFromString(str, MIMEType.XML);
var sheets = domTree.getElementsByTagName('Worksheet');
sheetnum = sheetnum || 0;
[].forEach.call(sheets, function(el, i) {
self._sheet[sheetnum] = new Sheet();
var rows = el.getElementsByTagName('Row');
[].forEach.call(rows, function (el, i) {
var cells = el.getElementsByTagName('Data');
var row = [];
[].forEach.call(cells, function (el, i) {
row.push(new Cell(el.innerHTML));
});
self._sheet[sheetnum].insertRecord(row);
});
sheetnum++;
});
return self;
};
// Export var
var Parser = {
XML : XMLParser
};
var SimpleExcel = {
Cell : Cell,
DataType : DataType,
Exception : Exception,
isSupportedBrowser : Utils.isSupportedBrowser(),
Parser : Parser,
Sheet : Sheet
};
window.SimpleExcel = SimpleExcel;
})(this);
</script>
<style>
* { font-family: Helvetica, Arial, sans-serif; font-size: 12px; padding : 0px }
textarea {margin:0 auto; border : 1px solid #000; width : 92%; }
</style>
</head>
<body>
<label>Choice Xml2003(.xml) Format file: </label>
<input type="file" id="fileInputXML" accept="application/xml" /><br/>
<textarea rows='24' id="result" name="result" ></textarea>
<script type="text/javascript">
function $(Obj){
return document.getElementById(Obj);
}
function Trim(Str){
return Str.replace(/(^\s*)|(\s*$)/g,"");
}
var fileInputXML = $('fileInputXML');
fileInputXML.addEventListener('change', function (e) {
var file = e.target.files[0];
var xmlParser = new SimpleExcel.Parser.XML();
xmlParser.loadFile(file, function () {
// draw HTML table based on sheet data
var sheet = xmlParser.getSheet();
var table = $('result');
table.innerHTML = "";
sheet.forEach(function (el, i) {
var row = "";
el.forEach(function (el, i) {
vala = el.value.replace(/\r\n/g,"<br>");
vala = vala.replace(/\n/g,"<br>");
vala = vala.replace(/\t/g," ");
if (row == ""){ row = vala; }else{ row += "\t"+ vala; }
});
table.innerHTML += row + "\n";
});
});
});
</script>
</body>
</html>