1 //<script type="text/javascript">
3 * @class Pman Gnumeric.
4 *-> load up a remote xml file of a gnumeric document.
6 * -> convert into a usable data structure
8 * -> ?? apply templated values ??
9 * -> allow modification of fields
11 * -> render to screen.
13 * -> send for conversion to XLS (via ssconvert)
19 url: rootURL + '/xxx/yyy/templates/reports/myreport.xml',
25 x.applyData({ ... }); // key value data looks for {value} in strings and replaces it..
29 mypanel.update(x.toHTML());
41 * @param {Object} cfg Configuration object.
46 Pman.Gnumeric = function (cfg)
48 cfg.data = cfg.data || {};
58 * Fires when source document has been loaded
59 * @param {Pman.Gnumerci} this
64 Roo.util.Observable.call(this,cfg);
83 Roo.extend(Pman.Gnumeric, Roo.util.Observable, {
86 * @cfg {String} url the source of the Gnumeric document.
90 * @cfg {Object} data overlay data for spreadsheet - from constructor.
94 * @cfg {String} downloadURL where GnumerictoExcel.php is...
100 * @type {XmlDocument} doc the gnumeric xml document
105 * @type {XmlNode} sheet the 'Sheet' element
110 * @type {XmlNode} sheet the 'Cells' element
114 * @type {Object} grid the map[row][col] = cellData
118 * @type {Object} colInfo - list of column sizes
122 * @type {Object} rowInfo - list of row sizes
127 * @type {Number} cmax - maximum number of columns
131 * @type {Object} rmax - maximum number of rows
135 * @type {String} stylesheetID id of stylesheet created to render spreadsheat
137 stylesheetID : false,
139 * @type {Number} rowOffset - used by table importer to enable multiple tables to be improted
146 * run the connection, parse document and fire load event..
147 * can be run multiple times with new data..
153 this.url = url || this.url;
161 this.colInfo = false;
162 this.rowInfo = false;
166 if (this.stylesheetID) {
168 Roo.util.CSS.removeStyleSheet(this.stylesheetID);
169 this.stylesheetID = false;
174 var c = new Roo.data.Connection();
178 success : function(resp, opts) {
180 _t.doc = resp.responseXML;
186 _t.fireEvent('load', _t);
190 Roo.MessageBox.alert("Error", "Failed to Load Template for Spreadsheet");
203 * convert 'A1' style position to row/column reference
205 * @arg {String} k cell name
206 * @return {Object} as { r: {Number} , c: {Number} }
211 var c = k.charCodeAt(0)-64;
212 var n = k.substring(1);
213 if (k.charCodeAt(1) > 64) {
215 c+=k.charCodeAt(1)-64;
218 return { c:c -1 ,r: (n*1)-1 }
222 * convert 'A1:B1' style position to array of row/column references
224 * @arg {String} k cell range
225 * @return {Array} as [ { r: {Number} , c: {Number} }. { r: {Number} , c: {Number} } ]
227 rangeToRC : function(s) {
228 var ar = s.split(':');
229 return [ this.toRC(ar[0]) , this.toRC(ar[1])]
238 * convert XML document into cells and other data..
241 parseDoc : function()
248 this.sheet = _t.doc.getElementsByTagNameNS('*','Sheet')[0];
251 this.cellholder = this.sheet.getElementsByTagNameNS('*','Cells')[0];
252 var cells = this.sheet.getElementsByTagNameNS('*','Cell');
256 Roo.each(cells, function(c) {
258 var row = c.getAttribute('Row') * 1;
259 var col = c.getAttribute('Col') * 1;
260 _t.cmax = Math.max(col+1, _t.cmax);
261 _t.rmax = Math.max(row+1, _t.rmax);
262 var vt = c.getAttribute('ValueType');
263 var vf = c.getAttribute('ValueFormat');
264 var val = c.textContent;
266 if (typeof(_t.grid[row]) == 'undefined') {
269 _t.grid[row][col] = Roo.applyIf({
279 for (var r = 0; r < this.rmax;r++) {
280 if (typeof(this.grid[r]) == 'undefined') {
283 for (var c = 0; c < this.cmax;c++) {
284 if (typeof(this.grid[r][c]) == 'undefined') {
287 //this.print( "[" + r + "]["+c+"]=" + grid[r][c].value +'<br/>');
291 var merge = this.sheet.getElementsByTagNameNS('*','Merge');
293 Roo.each(merge, function(c) {
294 var rc = _t.rangeToRC(c.textContent);
295 //Roo.log(JSON.stringify(rc))
296 if (typeof(_t.grid[rc[0].r][rc[0].c]) == 'undefined') {
297 _t.grid[rc[0].r][rc[0].c] = Roo.applyIf({ r : rc[0].r, c : rc[0].c }, _t.defaultCell);
300 _t.grid[rc[0].r][rc[0].c].colspan = (rc[1].c - rc[0].c) + 1;
301 _t.grid[rc[0].r][rc[0].c].rowspan = (rc[1].r - rc[0].r) + 1;
302 for(var r = (rc[0].r); r < (rc[1].r+1); r++) {
303 for(var c = rc[0].c; c < (rc[1].c+1); c++) {
304 //Roo.log('adding alias : ' + r+','+c);
305 _t.grid[r][c] = _t.grid[rc[0].r][rc[0].c];
312 var ci = this.sheet.getElementsByTagNameNS('*','ColInfo');
315 Roo.each(ci, function(c) {
316 var count = c.getAttribute('Count') || 1;
317 var s = c.getAttribute('No')*1;
318 for(var i =0; i < count; i++) {
319 _t.colInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
324 ci = this.sheet.getElementsByTagNameNS('*','RowInfo');
327 Roo.each(ci, function(c) {
328 var count = c.getAttribute('Count') || 1;
329 var s = c.getAttribute('No')*1;
330 for(var i =0; i < count; i++) {
331 _t.rowInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
342 * put the style info onto the cell data.
345 overlayStyles : function ()
349 Roo.each(this.styles, function(s) {
351 for (var r = s.r; r < s.r1;r++) {
352 if (typeof(_t.grid[r]) == 'undefined') {
355 for (var c = s.c; c < s.c1;c++) {
356 if (c > _t.cmax) continue;
358 if (typeof(_t.grid[r][c]) == 'undefined') _t.grid[r][c] = Roo.applyIf({ r: r , c : c }, _t.defaultCell);
360 if (typeof(g.cls) =='undefined') {
364 if (g.cls.indexOf(s.name) > -1) continue;
366 g.styles.push(s.dom);
374 * read the style information
375 * generates a stylesheet for the current file
376 * this should be disposed of really.....
379 parseStyles : function() {
381 var srs = this.sheet.getElementsByTagNameNS('*','StyleRegion');
386 HAlign : function(ent,v) {
387 ent['text-align'] = { '1' : 'left', '8': 'center', '4' : 'right'}[v] || 'left';
389 VAlign : function(ent,v) {
390 ent['vertical-align'] = { '1' : 'top', '4': 'middel', '8' : 'bottom'}[v] || 'top'
392 Fore : function(ent,v) {
394 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
395 ent['color'] = 'rgb(' + col.join(',') + ')';
397 Back : function(ent,v) {
399 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
400 ent['background-color'] = 'rgb(' + col.join(',') + ')';
402 FontUnit : function(ent,v) {
403 ent['font-size'] = v + 'px';
405 FontBold : function(ent,v) {
407 ent['font-weight'] = 'bold';
409 FontItalic : function(ent,v) {
411 //ent['font-weight'] = 'bold';
413 FontName : function(ent,v) {
414 ent['font-family'] = v;
416 BorderStyle : function(ent,v) {
417 var vv = v.split('-');
418 ent['border-'+vv[0]+'-style'] = 'solid';
419 ent['border-'+vv[0]+'-width'] = vv[1]+'px';
421 BorderColor : function(ent,v) {
422 var vv = v.split('-');
424 Roo.each(vv[1].split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
425 ent['border-'+vv[0]+'-color'] = 'rgb(' + col.join(',') + ')';
428 function add(e, k, v) {
431 if (typeof(map[k]) == 'undefined') {
441 Roo.each(srs, function(sr,n)
444 c : sr.getAttribute('startCol') *1,
445 r : sr.getAttribute('startRow')*1,
446 c1 : (sr.getAttribute('endCol')*1) +1,
447 r1 : (sr.getAttribute('endRow')*1) +1,
448 style : {}, // key val of style for HTML..
449 gstyle : {}, // key val of attributes used..
450 name : sid +'-gstyle-' + n,
455 Roo.each(sr.getElementsByTagNameNS('*','Style')[0].attributes, function(e) {
456 add(ent, e.name, e.value);
458 if (sr.getElementsByTagNameNS('*','Font').length) {
459 Roo.each(sr.getElementsByTagNameNS('*','Font')[0].attributes, function(e) {
460 add(ent, 'Font'+e.name, e.value);
463 add(ent, 'FontName', sr.getElementsByTagNameNS('*','Font')[0].textContent);
466 if (sr.getElementsByTagNameNS('*','StyleBorder').length) {
467 Roo.each(sr.getElementsByTagNameNS('*','StyleBorder')[0].childNodes, function(e) {
471 Roo.each(e.attributes, function(ea) {
472 add(ent, 'Border'+ea.name, e.tagName.split(':')[1].toLowerCase() + '-' + ea.value);
478 css['.'+ent.name] = ent.style;
481 this.styles = styles;
483 this.stylesheetID = sid;
484 Roo.util.CSS.createStyleSheet(css, sid);
487 /* --------------------------------------- AFTER LOAD METHODS... ----------------------- */
490 * Set the value of a cell..
491 * @param {String} cell name of cell, eg. C10 or { c: 1, r :1 }
493 * @param {Value} value to put in cell..
494 * @param {ValueType} type of value
495 * @param {ValueFormat} value format of cell
497 * Cells should exist at present, we do not make them up...
501 set : function(cell, v, vt, vf) {
503 var cs= typeof(cell) == 'string' ? this.toRC(cell) : cell;
504 //Roo.log( cs.r+ ',' + cs.c + ' = '+ v);
505 // need to generate clell if it doe
506 if (typeof(this.grid[cs.r]) == 'undefined') {
507 Roo.log('no row:' + cell);
508 this.grid[cs.r] = []; // create a row..
511 if (typeof(this.grid[cs.r][cs.c]) == 'undefined') {
512 Roo.log('cell not defined:' + cell);
513 this.createCell(cs.r,cs.c);
515 if (typeof(this.grid[cs.r][cs.c].dom) == 'undefined') {
516 Roo.log('no default content for cell:' + cell);
517 this.createCell(cs.r,cs.c);
520 this.grid[cs.r][cs.c].value= v;
521 this.grid[cs.r][cs.c].dom.textContent= v;
522 if (typeof(vt != 'undefined') && vt) {
523 this.grid[cs.r][cs.c].valueType = vt;
524 this.grid[cs.r][cs.c].dom.setAttribute('ValueType', vt);
526 if (typeof(vf != 'undefined') && vf) {
527 this.grid[cs.r][cs.c].valueFormat = vf;
528 this.grid[cs.r][cs.c].dom.setAttribute('ValueFormat', vf);
534 copyRow : function(src, dest) {
538 // Roo.log('create Row' + dest);
539 if (typeof(this.grid[dest]) == 'undefined') {
544 for (var c = 0; c < this.cmax; c++) {
546 this.copyCell({ r: src, c: c } , { r: dest, c: c});
549 this.rmax = Math.max(this.rmax, dest +1);
553 createCell: function(r,c)
555 //<gnm:Cell Row="6" Col="5" ValueType="60">Updated</gnm:Cell>
556 var nc = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Cell');
557 this.cellholder.appendChild(nc);
558 var lb = this.doc.createTextNode("\n");// add a line break..
559 this.cellholder.appendChild(lb);
561 nc.setAttribute('Row', new String(r));
562 nc.setAttribute('Col', new String(c));
563 nc.setAttribute('ValueType', '60');
566 this.grid[r][c] = Roo.applyIf({
580 copyCell : function(src, dest)
582 var old = this.grid[src.r][src.c];
584 if ((old.c != src.c) || (old.r != src.r)) {
585 // only really works on horizonatal merges..
587 this.grid[dest.r][dest.c] = this.grid[desc.r][old.c]; // let's hope it exists.
592 var nc = Roo.apply({}, this.grid[src.r][src.c]);
595 if (typeof(old.dom) == 'undefined') {
596 Roo.log("No cell to copy for " + Roo.encode(src));
599 this.grid[dest.r][dest.c] = nc;
600 nc.dom = old.dom.cloneNode(true);
601 nc.dom.setAttribute('Row', dest.r);
602 nc.dom.setAttribute('Cell', dest.c);
603 nc.dom.textContent = '';
604 old.dom.parentNode.appendChild(nc.dom);
605 if (!old.styles || !old.styles.length) {
612 Roo.each(old.styles, function(s) {
613 // try and extend existing styles..
614 var er = s.getAttribute('endRow') * 1;
615 var ec = s.getAttribute('endCol') * 1;
618 s.setAttribute('endRow', dest.r + 1);
621 s.setAttribute('endCol', dest.c + 1);
623 /*var ns = s.cloneNode(true);
624 s.parentNode.appendChild(ns);
625 ns.setAttribute('startCol', dest.c);
626 ns.setAttribute('startRow', dest.r);
627 ns.setAttribute('endCol', dest.c + 1);
628 ns.setAttribute('endRow', dest.r +1);
637 * Set the value of a cell..
638 * @param {String} cell name of cell, eg. C10
639 * @param {Value} value to put in cell..
641 * Cells should exist at present, we do not make them up...
644 applyData : function(data)
647 data = data || this.data;
648 for (var r = 0; r < this.rmax;r++) {
649 if (typeof(this.grid[r]) == 'undefined') continue;
650 for (var c = 0; c < this.cmax;c++) {
651 if (typeof(this.grid[r][c]) == 'undefined') {
654 if (!this.grid[r][c].value.length
655 || !this.grid[r][c].value.match(/\{/)) {
659 var x = new Roo.Template({ html: this.grid[r][c].value });
661 var res = x.applyTemplate(data);
662 //Roo.log("set " + r + "," + c + ":"+res)
663 this.set({ r: r, c: c}, x.applyTemplate(data));
665 // Roo.log(e.toString());
675 importTable : function (datagrid, xoff,yoff)
678 Roo.log("Error table not found!?");
684 var cleanHTML = function (str) {
687 ret = ret.replace(/ /g,'.');
688 ret = ret.replace(/\n/g,'.');
689 ret = ret.replace(/\r/g,'.');
691 while (-1 != (i = ret.indexOf(unescape('%A0')))) {
692 ret = ret.substring(0,i) + ' ' + ret.substring(i+1,str.length);
698 // <cell col="A" row="1">Test< / cell>
699 // <cell col="B" row="2" type="Number" format="test1">30< / cell>
701 var rows = datagrid.getElementsByTagName('tr');
702 //alert(rows.length);
704 for(var row=0;row<rows.length;row++) {
705 //var style = document.defaultView.getComputedStyle(rows[row], "");
707 //if (rows[row].getAttribute('xls:height')) {
708 // this.setRowHeight(row+y_offset, 0 + rows[row].getAttribute('xls:height'));
710 // this.setRowHeight(row+y_offset, 0 + style.height.replace(/[^0-9.]+/g,''));
714 // if (rowOffsets[row]) {
715 // coloffset += rowOffsets[row];
717 var cols = rows[row].getElementsByTagName('td');
720 for(var col=0;col < cols.length; col++) {
723 //var colat = col + coloffset;
725 if (cols[col].getAttribute('colspan') && (cols[col].getAttribute('colspan') > 1)) {
731 colat + (cols[col].getAttribute('colspan') - 1),
733 (cols[col].getAttribute('rowspan') > 1) ?
734 (cols[col].getAttribute('rowspan') - 1) : 0
740 coloffset += (cols[col].getAttribute('colspan') - 1);
743 if (cols[col].getAttribute('rowspan') && (cols[col].getAttribute('rowspan') > 1)) {
744 // this should really do a merge, but it's pretty damn complex...
745 //this.mergeRegion(colat,row +y_offset,colat + (cols[col].getAttribute('colspan') - 1), row+y_offset);
746 var rroff = cols[col].getAttribute('colspan') ? (cols[col].getAttribute('colspan') -0): 1;
748 for (rr = 0; rr < cols[col].getAttribute('rowspan');rr++) {
749 rowOffsets[rr + row] = col + rroff;
756 var style = this.newStyle();
757 if (style.setFrom(cols[col])) {
762 colat+x_offset + ((cols[col].getAttribute('colspan') > 1) ?
763 (cols[col].getAttribute('colspan') - 1) : 0),
764 row+y_offset + ((cols[col].getAttribute('rowspan') > 1) ?
765 (cols[col].getAttribute('rowspan') - 1) : 0)
771 if (!cols[col].childNodes.length) {
781 switch(cols[col].getAttribute('xls:type')) {
787 vt = 40; // float!!!!
788 if (cols[col].getAttribute('xls:floatformat')) {
789 vf = cols[col].getAttribute('xls:floatformat');
795 //ValueFormat="d/m/yyyy" 38635
797 if (cols[col].getAttribute('xls:dateformat')) {
798 vf= cols[col].getAttribute('xls:dateformat');
810 if (cols[col].getAttribute('xls:src')) {
811 //alert(cols[col].childNodes[0].width);
814 colat+x_offset+coloffset,
815 cols[col].getAttribute('xls:src'),
816 cols[col].childNodes[0].width,
817 cols[col].childNodes[0].height
825 if (!cols[col].childNodes[0].nodeValue) {
828 if (!cols[col].childNodes[0].nodeValue.replace(/^\s*|\s*$/g,"").length) {
832 var cell_value_text = cleanHTML(cols[col].childNodes[0].nodeValue);
834 if (cols[col].getAttribute('xls:percent')) {
835 cell_value_text = '' + ((cell_value_text * 1) / 100);
838 if (cell_value_text.length && (vt == 30)) {
839 var bits = cell_value_text.split(/-/);
840 var cur = new Date(bits[0],bits[1]-1,bits[2]);
841 cell_value_text = '' + Math.round((cur.getTime() - Date.UTC(1899,11,30)) / (24 * 60 * 60 * 1000));
846 if (cols[col].getAttribute('xls:formula')) {
847 var s = cols[col].getAttribute('xls:formula');
848 cell.removeAttribute('ValueType');
849 cell_value_text = s.replace(/#row#/g,(row + y_offset + 1));
851 this.set({ r: row + yoff, c : col + xoff }, cell_value_text, vt, vf);
859 this.rowOffset += rows.length;
867 * Convert spreadsheet into a HTML table.
873 function calcWidth(sc, span)
876 for(var i =sc; i< sc+span;i++) {
882 var grid = this.grid;
883 // lets do a basic dump..
884 var out = '<table style="table-layout:fixed;" cellpadding="0" cellspacing="0">';
885 for (var r = 0; r < this.rmax;r++) {
886 out += '<tr style="height:'+this.rowInfo[r]+'px;">';
887 for (var c = 0; c < this.cmax;c++) {
888 var g = (typeof(grid[r][c]) == 'undefined') ? defaultCell : grid[r][c];
890 if (typeof(g.cls) =='undefined') g.cls = [];
891 var w= calcWidth(c,g.colspan);
892 out+=String.format('<td colspan="{0}" rowspan="{1}" class="{4}"><div style="{3}">{2}</div></td>',
893 g.colspan, g.rowspan, g.value,
897 'text-overflow:ellipsis;' +
898 'white-space:nowrap;',
908 return out+'</table>';
915 * @param {String} name filename to downlaod (without xls)
916 * @param {String} callback (optional) - callback to call after callback is complete.
918 download : function(name,callback)
920 name = name || "Missing_download_filename";
922 if (this.downloadURL && this.downloadURL.charAt(this.downloadURL .length-1) != '/') {
923 this.downloadURL += '/';
926 var ser = new XMLSerializer();
927 var x = new Pman.Download({
930 xml : ser.serializeToString(this.doc),
931 format : 'xls', //xml
935 url : (this.downloadURL || (baseURL + '/GnumericToExcel/')) + name + '.xls',
936 success : function() {
937 Roo.MessageBox.alert("Alert", "File should have downloaded now");