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
109 * @type {Object} grid the map[row][col] = cellData
113 * @type {Object} colInfo - list of column sizes
117 * @type {Object} rowInfo - list of row sizes
122 * @type {Number} cmax - maximum number of columns
126 * @type {Object} rmax - maximum number of rows
130 * @type {String} stylesheetID id of stylesheet created to render spreadsheat
132 stylesheetID : false,
135 * run the connection, parse document and fire load event..
136 * can be run multiple times with new data..
142 this.url = url || this.url;
150 this.colInfo = false;
151 this.rowInfo = false;
155 if (this.stylesheetID) {
157 Roo.util.CSS.removeStyleSheet(this.stylesheetID);
158 this.stylesheetID = false;
163 var c = new Roo.data.Connection();
167 success : function(resp, opts) {
169 _t.doc = resp.responseXML;
175 _t.fireEvent('load', _t);
179 Roo.MessageBox.alert("Error", "Failed to Load Template for Spreadsheet");
192 * convert 'A1' style position to row/column reference
194 * @arg {String} k cell name
195 * @return {Object} as { r: {Number} , c: {Number} }
200 var c = k.charCodeAt(0)-64;
201 var n = k.substring(1);
202 if (k.charCodeAt(1) > 64) {
204 c+=k.charCodeAt(1)-64;
207 return { c:c -1 ,r: (n*1)-1 }
211 * convert 'A1:B1' style position to array of row/column references
213 * @arg {String} k cell range
214 * @return {Array} as [ { r: {Number} , c: {Number} }. { r: {Number} , c: {Number} } ]
216 rangeToRC : function(s) {
217 var ar = s.split(':');
218 return [ this.toRC(ar[0]) , this.toRC(ar[1])]
227 * convert XML document into cells and other data..
230 parseDoc : function()
237 this.sheet = _t.doc.getElementsByTagNameNS('*','Sheet')[0];
238 var cells = this.sheet.getElementsByTagNameNS('*','Cell');
242 Roo.each(cells, function(c) {
244 var row = c.getAttribute('Row') * 1;
245 var col = c.getAttribute('Col') * 1;
246 _t.cmax = Math.max(col+1, _t.cmax);
247 _t.rmax = Math.max(row+1, _t.rmax);
248 var vt = c.getAttribute('ValueType');
249 var vf = c.getAttribute('ValueFormat');
250 var val = c.textContent;
252 if (typeof(_t.grid[row]) == 'undefined') {
255 _t.grid[row][col] = Roo.applyIf({
265 for (var r = 0; r < this.rmax;r++) {
266 if (typeof(this.grid[r]) == 'undefined') {
269 for (var c = 0; c < this.cmax;c++) {
270 if (typeof(this.grid[r][c]) == 'undefined') {
273 //this.print( "[" + r + "]["+c+"]=" + grid[r][c].value +'<br/>');
277 var merge = this.sheet.getElementsByTagNameNS('*','Merge');
279 Roo.each(merge, function(c) {
280 var rc = _t.rangeToRC(c.textContent);
281 //Roo.log(JSON.stringify(rc))
282 if (typeof(_t.grid[rc[0].r][rc[0].c]) == 'undefined') {
283 _t.grid[rc[0].r][rc[0].c] = Roo.applyIf({ r : rc[0].r, c : rc[0].c }, _t.defaultCell);
286 _t.grid[rc[0].r][rc[0].c].colspan = (rc[1].c - rc[0].c) + 1;
287 _t.grid[rc[0].r][rc[0].c].rowspan = (rc[1].r - rc[0].r) + 1;
288 for(var r = (rc[0].r); r < (rc[1].r+1); r++) {
289 for(var c = rc[0].c; c < (rc[1].c+1); c++) {
290 //Roo.log('adding alias : ' + r+','+c);
291 _t.grid[r][c] = _t.grid[rc[0].r][rc[0].c];
298 var ci = this.sheet.getElementsByTagNameNS('*','ColInfo');
301 Roo.each(ci, function(c) {
302 var count = c.getAttribute('Count') || 1;
303 var s = c.getAttribute('No')*1;
304 for(var i =0; i < count; i++) {
305 _t.colInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
310 ci = this.sheet.getElementsByTagNameNS('*','RowInfo');
313 Roo.each(ci, function(c) {
314 var count = c.getAttribute('Count') || 1;
315 var s = c.getAttribute('No')*1;
316 for(var i =0; i < count; i++) {
317 _t.rowInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
328 * put the style info onto the cell data.
331 overlayStyles : function ()
335 Roo.each(this.styles, function(s) {
337 for (var r = s.r; r < s.r1;r++) {
338 if (typeof(_t.grid[r]) == 'undefined') {
341 for (var c = s.c; c < s.c1;c++) {
342 if (c > _t.cmax) continue;
344 if (typeof(_t.grid[r][c]) == 'undefined') _t.grid[r][c] = Roo.applyIf({ r: r , c : c }, _t.defaultCell);
346 if (typeof(g.cls) =='undefined') {
350 if (g.cls.indexOf(s.name) > -1) continue;
352 g.styles.push(s.dom);
360 * read the style information
361 * generates a stylesheet for the current file
362 * this should be disposed of really.....
365 parseStyles : function() {
367 var srs = this.sheet.getElementsByTagNameNS('*','StyleRegion');
372 HAlign : function(ent,v) {
373 ent['text-align'] = { '1' : 'left', '8': 'center', '4' : 'right'}[v] || 'left';
375 VAlign : function(ent,v) {
376 ent['vertical-align'] = { '1' : 'top', '4': 'middel', '8' : 'bottom'}[v] || 'top'
378 Fore : function(ent,v) {
380 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
381 ent['color'] = 'rgb(' + col.join(',') + ')';
383 Back : function(ent,v) {
385 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
386 ent['background-color'] = 'rgb(' + col.join(',') + ')';
388 FontUnit : function(ent,v) {
389 ent['font-size'] = v + 'px';
391 FontBold : function(ent,v) {
393 ent['font-weight'] = 'bold';
395 FontItalic : function(ent,v) {
397 //ent['font-weight'] = 'bold';
399 FontName : function(ent,v) {
400 ent['font-family'] = v;
402 BorderStyle : function(ent,v) {
403 var vv = v.split('-');
404 ent['border-'+vv[0]+'-style'] = 'solid';
405 ent['border-'+vv[0]+'-width'] = vv[1]+'px';
407 BorderColor : function(ent,v) {
408 var vv = v.split('-');
410 Roo.each(vv[1].split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
411 ent['border-'+vv[0]+'-color'] = 'rgb(' + col.join(',') + ')';
414 function add(e, k, v) {
417 if (typeof(map[k]) == 'undefined') {
427 Roo.each(srs, function(sr,n)
430 c : sr.getAttribute('startCol') *1,
431 r : sr.getAttribute('startRow')*1,
432 c1 : (sr.getAttribute('endCol')*1) +1,
433 r1 : (sr.getAttribute('endRow')*1) +1,
434 style : {}, // key val of style for HTML..
435 gstyle : {}, // key val of attributes used..
436 name : sid +'-gstyle-' + n,
441 Roo.each(sr.getElementsByTagNameNS('*','Style')[0].attributes, function(e) {
442 add(ent, e.name, e.value);
444 if (sr.getElementsByTagNameNS('*','Font').length) {
445 Roo.each(sr.getElementsByTagNameNS('*','Font')[0].attributes, function(e) {
446 add(ent, 'Font'+e.name, e.value);
449 add(ent, 'FontName', sr.getElementsByTagNameNS('*','Font')[0].textContent);
452 if (sr.getElementsByTagNameNS('*','StyleBorder').length) {
453 Roo.each(sr.getElementsByTagNameNS('*','StyleBorder')[0].childNodes, function(e) {
457 Roo.each(e.attributes, function(ea) {
458 add(ent, 'Border'+ea.name, e.tagName.split(':')[1].toLowerCase() + '-' + ea.value);
464 css['.'+ent.name] = ent.style;
467 this.styles = styles;
469 this.stylesheetID = sid;
470 Roo.util.CSS.createStyleSheet(css, sid);
473 /* --------------------------------------- AFTER LOAD METHODS... ----------------------- */
476 * Set the value of a cell..
477 * @param {String} cell name of cell, eg. C10
478 * @param {Value} value to put in cell..
480 * Cells should exist at present, we do not make them up...
484 set : function(cell, v) {
486 var cs= typeof(cell) == 'string' ? this.toRC(cell) : cell;
487 //Roo.log( this.grid[cs.r][cs.c]);
488 // need to generate clell if it doe
489 if (typeof(this.grid[cs.r]) == 'undefined') {
490 Roo.log('no row:' + cell);
493 if (typeof(this.grid[cs.r][cs.c]) == 'undefined') {
494 Roo.log('cell not defined:' + cell);
496 if (typeof(this.grid[cs.r][cs.c].dom) == 'undefined') {
497 Roo.log('no default content for cell:' + cell);
500 this.grid[cs.r][cs.c].value= v;
502 this.grid[cs.r][cs.c].dom.textContent= v;
506 copyRow : function(src, dest) {
510 // Roo.log('create Row' + dest);
511 if (typeof(this.grid[dest]) == 'undefined') {
516 for (var c = 0; c < this.cmax; c++) {
518 this.copyCell({ r: src, c: c } , { r: dest, c: c});
521 this.rmax = Math.max(this.rmax, dest +1);
525 copyCell : function(src, dest)
527 var old = this.grid[src.r][src.c];
529 if ((old.c != src.c) || (old.r != src.r)) {
530 // only really works on horizonatal merges..
532 this.grid[dest.r][dest.c] = this.grid[desc.r][old.c]; // let's hope it exists.
537 var nc = Roo.apply({}, this.grid[src.r][src.c]);
540 if (typeof(old.dom) == 'undefined') {
541 Roo.log("No cell to copy for " + Roo.encode(src));
544 this.grid[dest.r][dest.c] = nc;
545 nc.dom = old.dom.cloneNode(true);
546 nc.dom.setAttribute('Row', dest.r);
547 nc.dom.setAttribute('Cell', dest.c);
548 nc.dom.textContent = '';
549 old.dom.parentNode.appendChild(nc.dom);
550 if (!old.styles || !old.styles.length) {
557 Roo.each(old.styles, function(s) {
558 // try and extend existing styles..
559 var er = s.getAttribute('endRow') * 1;
560 var ec = s.getAttribute('endCol') * 1;
563 s.setAttribute('endRow', dest.r + 1);
566 s.setAttribute('endCol', dest.c + 1);
568 /*var ns = s.cloneNode(true);
569 s.parentNode.appendChild(ns);
570 ns.setAttribute('startCol', dest.c);
571 ns.setAttribute('startRow', dest.r);
572 ns.setAttribute('endCol', dest.c + 1);
573 ns.setAttribute('endRow', dest.r +1);
582 * Set the value of a cell..
583 * @param {String} cell name of cell, eg. C10
584 * @param {Value} value to put in cell..
586 * Cells should exist at present, we do not make them up...
589 applyData : function(data)
592 data = data || this.data;
593 for (var r = 0; r < this.rmax;r++) {
594 if (typeof(this.grid[r]) == 'undefined') continue;
595 for (var c = 0; c < this.cmax;c++) {
596 if (typeof(this.grid[r][c]) == 'undefined') {
599 if (!this.grid[r][c].value.length
600 || !this.grid[r][c].value.match(/\{/)) {
604 var x = new Roo.Template({ html: this.grid[r][c].value });
606 var res = x.applyTemplate(data);
607 //Roo.log("set " + r + "," + c + ":"+res)
608 this.set({ r: r, c: c}, x.applyTemplate(data));
610 // Roo.log(e.toString());
623 * Convert spreadsheet into a HTML table.
629 function calcWidth(sc, span)
632 for(var i =sc; i< sc+span;i++) {
638 var grid = this.grid;
639 // lets do a basic dump..
640 var out = '<table style="table-layout:fixed;" cellpadding="0" cellspacing="0">';
641 for (var r = 0; r < this.rmax;r++) {
642 out += '<tr style="height:'+this.rowInfo[r]+'px;">';
643 for (var c = 0; c < this.cmax;c++) {
644 var g = (typeof(grid[r][c]) == 'undefined') ? defaultCell : grid[r][c];
646 if (typeof(g.cls) =='undefined') g.cls = [];
647 var w= calcWidth(c,g.colspan);
648 out+=String.format('<td colspan="{0}" rowspan="{1}" class="{4}"><div style="{3}">{2}</div></td>',
649 g.colspan, g.rowspan, g.value,
653 'text-overflow:ellipsis;' +
654 'white-space:nowrap;',
664 return out+'</table>';
671 * @param {String} name filename to downlaod (without xls)
672 * @param {String} callback (optional) - callback to call after callback is complete.
674 download : function(name,callback)
676 name = name || "Missing_download_filename";
678 if (this.downloadURL && this.downloadURL.charAt(this.downloadURL .length-1) != '/') {
679 this.downloadURL += '/';
682 var ser = new XMLSerializer();
683 var x = new Pman.Download({
686 xml : ser.serializeToString(this.doc),
687 format : 'xls', //xml
691 url : (this.downloadURL || (baseURL + '/GnumericToExcel/')) + name + '.xls',
692 success : function() {
693 Roo.MessageBox.alert("Alert", "File should have downloaded now");