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} colInfoDom - column sizes dom element
126 * @type {Object} rowInfo - list of row sizes
130 * @type {Object} rowInfoDom - dom elements with sizes
134 * @type {Number} cmax - maximum number of columns
138 * @type {Object} rmax - maximum number of rows
142 * @type {String} stylesheetID id of stylesheet created to render spreadsheat
144 stylesheetID : false,
146 * @type {Number} rowOffset - used by table importer to enable multiple tables to be improted
152 * @type {String} format - either XLSX (if images are used) or XLS - as ssconvert does not do images that well.
160 * run the connection, parse document and fire load event..
161 * can be run multiple times with new data..
167 this.url = url || this.url;
175 this.colInfo = false;
176 this.colInfoDom = false;
177 this.rowInfo = false;
178 this.rowInfoDom = false;
182 if (this.stylesheetID) {
184 Roo.util.CSS.removeStyleSheet(this.stylesheetID);
185 this.stylesheetID = false;
190 var c = new Roo.data.Connection();
194 success : function(resp, opts) {
196 _t.doc = resp.responseXML;
203 _t.fireEvent('load', _t);
207 Roo.MessageBox.alert("Error", "Failed to Load Template for Spreadsheet");
216 RCtoCell : function(r,c)
218 // we wil only support AA not AAA
219 var top = Math.floor(c/26);
221 var cc = top > 0 ? String.fromCharCode('A'.charCodeAt(0) + top) : '';
222 cc += String.fromCharCode('A'.charCodeAt(0) + bot);
229 * convert 'A1' style position to row/column reference
231 * @arg {String} k cell name
232 * @return {Object} as { r: {Number} , c: {Number} }
237 var c = k.charCodeAt(0)-64;
238 var n = k.substring(1);
239 if (k.charCodeAt(1) > 64) {
241 c+=k.charCodeAt(1)-64;
244 return { c:c -1 ,r: (n*1)-1 }
248 * convert 'A1:B1' style position to array of row/column references
250 * @arg {String} k cell range
251 * @return {Array} as [ { r: {Number} , c: {Number} }. { r: {Number} , c: {Number} } ]
253 rangeToRC : function(s) {
254 var ar = s.split(':');
255 return [ this.toRC(ar[0]) , this.toRC(ar[1])]
264 * convert XML document into cells and other data..
267 parseDoc : function(sheetnum)
274 this.sheet = _t.doc.getElementsByTagNameNS('*','Sheet')[sheetnum];
277 this.cellholder = this.sheet.getElementsByTagNameNS('*','Cells')[0];
278 var cells = this.sheet.getElementsByTagNameNS('*','Cell');
282 Roo.each(cells, function(c) {
284 var row = c.getAttribute('Row') * 1;
285 var col = c.getAttribute('Col') * 1;
286 _t.cmax = Math.max(col+1, _t.cmax);
287 _t.rmax = Math.max(row+1, _t.rmax);
288 var vt = c.getAttribute('ValueType');
289 var vf = c.getAttribute('ValueFormat');
290 var val = c.textContent;
292 if (typeof(_t.grid[row]) == 'undefined') {
295 _t.grid[row][col] = Roo.applyIf({
305 for (var r = 0; r < this.rmax;r++) {
306 if (typeof(this.grid[r]) == 'undefined') {
309 for (var c = 0; c < this.cmax;c++) {
310 if (typeof(this.grid[r][c]) == 'undefined') {
313 //this.print( "[" + r + "]["+c+"]=" + grid[r][c].value +'<br/>');
317 var merge = this.sheet.getElementsByTagNameNS('*','Merge');
319 Roo.each(merge, function(c) {
320 var rc = _t.rangeToRC(c.textContent);
321 //Roo.log(JSON.stringify(rc));
322 if (typeof(_t.grid[rc[0].r][rc[0].c]) == 'undefined') {
323 //Roo.log(["creating empty cell for ",rc[0].r, rc[0].c ]);
324 _t.createCell(rc[0].r, rc[0].c );
325 //_t.grid[rc[0].r][rc[0].c] = //Roo.applyIf({ r : rc[0].r, c : rc[0].c }, _t.defaultCell);
328 _t.grid[rc[0].r][rc[0].c].colspan = (rc[1].c - rc[0].c) + 1;
329 _t.grid[rc[0].r][rc[0].c].rowspan = (rc[1].r - rc[0].r) + 1;
330 for(var r = (rc[0].r); r < (rc[1].r+1); r++) {
331 for(var cc = rc[0].c; cc < (rc[1].c+1); cc++) {
332 //Roo.log('adding alias : ' + r+','+c);
333 _t.grid[r][cc] = _t.grid[rc[0].r][rc[0].c];
339 var ci = this.sheet.getElementsByTagNameNS('*','ColInfo');
341 this.colInfoDom = {};
343 Roo.each(ci, function(c) {
344 var count = c.getAttribute('Count') || 1;
345 var s = c.getAttribute('No')*1;
346 for(var i =0; i < count; i++) {
347 _t.colInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
348 _t.colInfoDom[s+i] = c;
353 ci = this.sheet.getElementsByTagNameNS('*','RowInfo');
356 this.rowInfoDom = {};
357 Roo.each(ci, function(c) {
358 var count = c.getAttribute('Count') || 1;
359 var s = c.getAttribute('No')*1;
360 for(var i =0; i < count; i++) {
361 _t.rowInfoDom[s+i] = c;
362 _t.rowInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
375 * put the style info onto the cell data.
378 overlayStyles : function ()
382 Roo.each(this.styles, function(s) {
384 for (var r = s.r; r < s.r1;r++) {
385 if (typeof(_t.grid[r]) == 'undefined') {
388 for (var c = s.c; c < s.c1;c++) {
393 if (typeof(_t.grid[r][c]) == 'undefined') {
395 //_t.grid[r][c] = Roo.applyIf({ r: r , c : c }, _t.defaultCell);
398 if (typeof(g.cls) =='undefined') {
402 if (g.cls.indexOf(s.name) > -1) {
406 g.styles.push(s.dom);
414 * read the style information
415 * generates a stylesheet for the current file
416 * this should be disposed of really.....
419 parseStyles : function() {
421 var srs = this.sheet.getElementsByTagNameNS('*','StyleRegion');
426 HAlign : function(ent,v) {
427 ent['text-align'] = { '1' : 'left', '8': 'center', '4' : 'right'}[v] || 'left';
429 VAlign : function(ent,v) {
430 ent['vertical-align'] = { '1' : 'top', '4': 'middle', '8' : 'bottom'}[v] || 'top'
432 Fore : function(ent,v) {
434 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); });
435 ent['color'] = 'rgb(' + col.join(',') + ')';
437 Back : function(ent,v) {
439 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); });
440 ent['background-color'] = 'rgb(' + col.join(',') + ')';
442 FontUnit : function(ent,v) {
443 ent['font-size'] = v + 'px';
445 FontBold : function(ent,v) {
446 if (v*1 < 1) { return; }
447 ent['font-weight'] = 'bold';
449 FontItalic : function(ent,v) {
450 if (v*0 < 1) { return; }
451 //ent['font-weight'] = 'bold';
453 FontName : function(ent,v) {
454 ent['font-family'] = v;
456 BorderStyle : function(ent,v) {
457 var vv = v.split('-');
458 ent['border-'+vv[0]+'-style'] = 'solid';
459 ent['border-'+vv[0]+'-width'] = vv[1]+'px';
461 BorderColor : function(ent,v) {
462 var vv = v.split('-');
464 Roo.each(vv[1].split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); });
465 ent['border-'+vv[0]+'-color'] = 'rgb(' + col.join(',') + ')';
468 function add(e, k, v) {
471 if (typeof(map[k]) == 'undefined') {
481 Roo.each(srs, function(sr,n)
484 c : sr.getAttribute('startCol') *1,
485 r : sr.getAttribute('startRow')*1,
486 c1 : (sr.getAttribute('endCol')*1) +1,
487 r1 : (sr.getAttribute('endRow')*1) +1,
488 style : {}, // key val of style for HTML..
489 gstyle : {}, // key val of attributes used..
490 name : sid +'-gstyle-' + n,
495 Roo.each(sr.getElementsByTagNameNS('*','Style')[0].attributes, function(e) {
496 add(ent, e.name, e.value);
498 if (sr.getElementsByTagNameNS('*','Font').length) {
499 Roo.each(sr.getElementsByTagNameNS('*','Font')[0].attributes, function(e) {
500 add(ent, 'Font'+e.name, e.value);
503 add(ent, 'FontName', sr.getElementsByTagNameNS('*','Font')[0].textContent);
506 if (sr.getElementsByTagNameNS('*','StyleBorder').length) {
507 Roo.each(sr.getElementsByTagNameNS('*','StyleBorder')[0].childNodes, function(e) {
511 Roo.each(e.attributes, function(ea) {
512 add(ent, 'Border'+ea.name, e.tagName.split(':')[1].toLowerCase() + '-' + ea.value);
518 css['.'+ent.name] = ent.style;
521 this.styles = styles;
523 this.stylesheetID = sid;
524 Roo.util.CSS.createStyleSheet(css, sid);
530 /* --------------------------------------- AFTER LOAD METHODS... ----------------------- */
533 * Set the value of a cell..
534 * @param {String} cell name of cell, eg. C10 or { c: 1, r :1 }
536 * @param {Value} value to put in cell..
537 * @param {ValueType} type of value
538 * @param {ValueFormat} value format of cell
540 * Cells should exist at present, we do not make them up...
544 set : function(cell, v, vt, vf) {
546 var cs= typeof(cell) == 'string' ? this.toRC(cell) : cell;
549 Roo.log( cs.r+ ',' + cs.c + ' = '+ v);
550 // need to generate clell if it doe
551 if (typeof(this.grid[cs.r]) == 'undefined') {
552 Roo.log('no row:' + cell);
553 this.grid[cs.r] = []; // create a row..
556 if (typeof(this.grid[cs.r][cs.c]) == 'undefined') {
557 Roo.log('cell not defined:' + cell);
558 this.createCell(cs.r,cs.c);
560 // cell might not be rendered yet... so if we try and create a cell, it overrides the default formating..
562 if (typeof(this.grid[cs.r][cs.c].dom) == 'undefined') {
563 Roo.log('no default content for cell:' + cell);
564 Roo.log(this.grid[cs.r][cs.c]);
565 //this.createCell(cs.r,cs.c);
568 this.grid[cs.r][cs.c].value= v;
569 if (this.grid[cs.r][cs.c].dom) {
570 this.grid[cs.r][cs.c].dom.textContent= v;
574 if (typeof(vt) != 'undefined') {
575 this.grid[cs.r][cs.c].valueType = vt;
576 this.grid[cs.r][cs.c].dom.setAttribute('ValueType', vt);
577 if (vt === '' || vt === false) { // value type is empty for formula's
578 this.grid[cs.r][cs.c].dom.removeAttribute('ValueType');
581 if (typeof(vf) != 'undefined' && vf !== false) {
582 this.grid[cs.r][cs.c].valueFormat = vf;
583 this.grid[cs.r][cs.c].dom.setAttribute('ValueFormat', vf);
584 if (vf === '' || vf === false) { // value type is empty for formula's
585 this.grid[cs.r][cs.c].dom.removeAttribute('ValueFormat');
592 copyRow : function(src, dest) {
596 // Roo.log('create Row' + dest);
597 if (typeof(this.grid[dest]) == 'undefined') {
602 for (var c = 0; c < this.cmax; c++) {
604 this.copyCell({ r: src, c: c } , { r: dest, c: c});
607 this.rmax = Math.max(this.rmax, dest +1);
613 createCell: function(r,c)
615 //<gnm:Cell Row="6" Col="5" ValueType="60">Updated</gnm:Cell>
616 var nc = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Cell');
617 this.cellholder.appendChild(nc);
618 var lb = this.doc.createTextNode("\n");// add a line break..
619 this.cellholder.appendChild(lb);
621 nc.setAttribute('Row', new String(r));
622 nc.setAttribute('Col', new String(c));
623 nc.setAttribute('ValueType', '60');
626 this.grid[r][c] = Roo.applyIf({
640 copyCell : function(src, dest)
642 var old = this.grid[src.r][src.c];
644 if ((old.c != src.c) || (old.r != src.r)) {
645 // only really works on horizonatal merges..
647 this.grid[dest.r][dest.c] = this.grid[desc.r][old.c]; // let's hope it exists.
652 var nc = Roo.apply({}, this.grid[src.r][src.c]);
655 if (typeof(old.dom) == 'undefined') {
656 Roo.log("No cell to copy for " + Roo.encode(src));
659 this.grid[dest.r][dest.c] = nc;
660 nc.dom = old.dom.cloneNode(true);
661 nc.dom.setAttribute('Row', dest.r);
662 nc.dom.setAttribute('Cell', dest.c);
663 nc.dom.textContent = '';
664 old.dom.parentNode.appendChild(nc.dom);
665 if (!old.styles || !old.styles.length) {
672 Roo.each(old.styles, function(s) {
673 // try and extend existing styles..
674 var er = s.getAttribute('endRow') * 1;
675 var ec = s.getAttribute('endCol') * 1;
678 s.setAttribute('endRow', dest.r + 1);
681 s.setAttribute('endCol', dest.c + 1);
683 /*var ns = s.cloneNode(true);
684 s.parentNode.appendChild(ns);
685 ns.setAttribute('startCol', dest.c);
686 ns.setAttribute('startRow', dest.r);
687 ns.setAttribute('endCol', dest.c + 1);
688 ns.setAttribute('endRow', dest.r +1);
697 * Set the value of a cell..
698 * @param {String} cell name of cell, eg. C10
699 * @param {Value} value to put in cell..
701 * Cells should exist at present, we do not make them up...
704 applyData : function(data)
707 data = data || this.data;
708 for (var r = 0; r < this.rmax;r++) {
709 if (typeof(this.grid[r]) == 'undefined') {
712 for (var c = 0; c < this.cmax;c++) {
713 if (typeof(this.grid[r][c]) == 'undefined') {
716 if (!this.grid[r][c].value.length
717 || !this.grid[r][c].value.match(/\{/)) {
721 var x = new Roo.Template({ html: this.grid[r][c].value });
723 var res = x.applyTemplate(data);
724 //Roo.log("set " + r + "," + c + ":"+res)
725 this.set({ r: r, c: c}, x.applyTemplate(data));
727 // Roo.log(e.toString());
737 readTableData : function(table)
739 // read the first row.
740 var tds = Roo.get(table).select('tr').item(0).select('td');
743 Roo.get(table).select('tr').each(function(trs) {
746 trs.select('td').each(function(td) {
747 var cs = td.dom.getAttribute('colspan');
748 cs = cs ? cs * 1 : 1;
751 maxnc = Math.max(nc, maxnc);
754 var tr = document.createElement('tr');
755 table.appendChild(tr);
757 for (i =0; i < maxnc; i++) {
758 ar[i] = document.createElement('td');
759 tr.appendChild(ar[i]);
762 var ret = { cols : maxnc, pos : {} };
763 for (i =0; i < maxnc; i++) {
764 ret.pos[ Roo.get(ar[i]).getLeft()] =i;
766 ret.near = function(p) {
767 // which one is nearest..
774 for(var i in this.pos) {
775 var dis = Math.abs(p-i);
784 table.removeChild(tr);
793 * Import a table and put it into the spreadsheet
794 * @param {HTMLTable} datagrid dom element of html table.
795 * @param {Number} xoff X offset to start rendering to
796 * @param {Number} yoff Y offset to start rendering to
800 importTable : function (datagrid, xoff,yoff)
803 Roo.log("Error table not found!?");
810 var table_data = this.readTableData(datagrid);
812 // oroginally this cleaned line breaks, but we acutally need them..
813 var cleanHTML = function (str) {
816 ret = ret.replace(/ /g,' ');
817 // ret = ret.replace(/\n/g,'.');
818 // ret = ret.replace(/\r/g,'.');
825 // <cell col="A" row="1">Test< / cell>
826 // <cell col="B" row="2" type="Number" format="test1">30< / cell>
828 var rows = datagrid.getElementsByTagName('tr');
829 //alert(rows.length);
832 for(var row=0;row<rows.length;row++) {
834 // let's see what affect this has..
835 // it might mess things up..
837 if (rows[row].getAttribute('xls:height')) {
838 this.setRowHeight(row + yoff +1, 1* rows[row].getAttribute('xls:height'));
840 this.setRowHeight( row + yoff +1, Roo.get(rows[row]).getHeight());
843 var cols = rows[row].getElementsByTagName('td');
845 for(var col=0;col < cols.length; col++) {
847 if (cols[col].getAttribute('xls:width')) {
848 this.setColumnWidth(col, 1 * cols[col].getAttribute('xls:width'));
851 var colspan = cols[col].getAttribute('colspan');
852 colspan = colspan ? colspan *1 : 1;
854 var rowspan = cols[col].getAttribute('rowspan');
855 rowspan = rowspan ? rowspan * 1 : 1;
857 var realcol = table_data.near( Roo.get(cols[col]).getLeft() );
861 if (colspan > 1 || rowspan > 1) {
863 // getting thisese right is tricky..
867 realcol+ xoff + (colspan -1),
874 // set the style first..
875 this.parseHtmlStyle( cols[col], row + yoff, realcol + xoff , colspan, rowspan);
877 if (!cols[col].childNodes.length) {
887 var xlstype = cols[col].getAttribute('xls:type');
895 vt = 40; // float!!!!
896 if (cols[col].getAttribute('xls:floatformat')) {
897 vf = cols[col].getAttribute('xls:floatformat');
903 //ValueFormat="d/m/yyyy" 38635
905 if (cols[col].getAttribute('xls:dateformat')) {
906 vf= cols[col].getAttribute('xls:dateformat');
918 if (!cols[col].childNodes[0].nodeValue) {
922 if (!cols[col].childNodes[0].nodeValue.replace(/^\s*|\s*$/g,"").length) {
927 var cell_value_text = cleanHTML(cols[col].childNodes[0].nodeValue);
929 if (cols[col].getAttribute('xls:percent')) {
930 cell_value_text = '' + ((cell_value_text * 1) / 100);
933 if (cell_value_text.length && (vt == 30) && xlstype == 'date') {
934 var bits = cell_value_text.split(/-/);
935 var cur = new Date(bits[0],bits[1]-1,bits[2]);
936 cell_value_text = '' + Math.round((cur.getTime() - Date.UTC(1899,11,30)) / (24 * 60 * 60 * 1000));
941 if (cols[col].getAttribute('xls:formula')) {
942 var s = cols[col].getAttribute('xls:formula');
944 cell_value_text = s.replace(/#row#/g,(row + yoff + 1));
946 this.set({ r: row + yoff, c : realcol + xoff }, cell_value_text, vt, vf);
954 this.rowOffset += rows.length;
960 parseHtmlStyle : function(dom, row, col, colspan, rowspan) {
962 function toCol (rgb) {
964 var ar = rgb.replace(/rgb[a]?\(/, '').replace(/\)/, '').replace(/ /, '').split(',');
967 Roo.each(ar, function(c) {
968 rcs.push((c*c).toString(16)) ;
970 return rcs.join(':');
974 var el = Roo.get(dom);
976 'text-align' : function(ent,v) {
977 ent['HAlign'] = { 'left' : '1', 'center' : '8' , 'right' : '4' }[v] || '1';
979 'vertical-align': function(ent,v) {
980 ent['VAlign'] = { 'top' : '1', 'middel' : '8' , 'bottom' : '4' }[v] || '1';
983 'color': function(ent,v) {
984 ent['Fore'] = toCol(v);
985 // this is a bit dumb.. we assume that if it's not black text, then it's shaded..
986 if (ent['Fore'] != '0:0:0') {
991 'background-color' : function(ent,v) {
992 ent['Back'] = toCol(v);
1009 Back:"FFFF:FFFF:FFFF",
1010 PatternColor:"0:0:0",
1015 var val = el.getStyle(k);
1016 if (!val || !val.length) {
1022 if (el.dom.getAttribute('xls:wraptext')) {
1025 if (el.dom.getAttribute('xls:valign')) {
1028 if (el.dom.getAttribute('xls:halign')) {
1035 'font-size' : function(ent,v) {
1036 ent['Unit'] = v.replace(/px/, '');
1038 'font-weight' : function(ent,v) {
1044 'font-style' : function(ent,v) {
1045 if (v != 'italic') {
1060 for(var k in fmap) {
1061 var val = el.getStyle(k);
1062 if (!val || !val.length) {
1067 var font = el.getStyle('font-family') || 'Sans';
1068 if (font.split(',').length > 1) {
1069 font = font.split(',')[1].replace(/\s+/, '');
1073 /// -- now create elements..
1075 var objs = this.sheet.getElementsByTagNameNS('*','Styles')[0];
1077 //<gnm:StyleRegion startCol="0" startRow="0" endCol="255" endRow="65535"
1078 var sr = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:StyleRegion');
1079 objs.appendChild(sr);
1080 objs.appendChild(this.doc.createTextNode("\n"));// add a line break..
1082 sr.setAttribute('startCol', col);
1083 sr.setAttribute('endCol', col+ colspan-1);
1084 sr.setAttribute('startRow', row);
1085 sr.setAttribute('endRow', row + rowspan -1);
1088 var st = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Style');
1090 // do we need some defaults..
1093 st.setAttribute(k, ent[k]);
1096 var fo = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Font');
1098 // do we need some defaults..
1099 for(var k in fent) {
1100 fo.setAttribute(k, fent[k]);
1102 fo.textContent = font;
1106 Roo.each(['top','left','bottom','right'], function(p) {
1107 var w = el.getStyle('border-' + p + '-width').replace(/px/, '');
1108 if (!w || !w.length || (w*1) < 1) {
1112 sb= this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:StyleBorder');
1114 var be = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:' + p[0].toUpperCase() + p.substring(1));
1115 be.setAttribute('Style', '1');
1116 be.setAttribute('Color', '0:0:0'); // fixme..
1120 // start adding them all together..
1133 * write an image in old gnumberic format (needs base64 data to write it)
1136 * @param {Number} row row to put it in (rows start at 0)
1137 * @param {Number} col column to put it in
1138 * @param {Number} data the base64 description of the images
1139 * @param {Number} width image width
1140 * @param {Number} width image height
1143 writeImageOld : function (row, col, data, width, height, type, size)
1147 throw "write Image called with missing data";
1154 var objs = this.sheet.getElementsByTagNameNS('*','Objects')[0];
1155 var soi = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:SheetObjectImage');
1159 for ( endcol=col;endcol <100; endcol++) {
1160 if (!this.colInfo[endcol]) {
1161 this.colInfo[endcol] = 100; // eak fudge
1163 colwidth += this.colInfo[endcol];
1164 if (colwidth > width) {
1169 soi.setAttribute('ObjectBound', this.RCtoCell(row,col) + ':' + this.RCtoCell(row,endcol));
1171 var ww = 0.01; // offset a bit...
1174 var rowHeight = typeof(this.rowInfoDom[row]) == 'undefined' ? 100 :
1175 this.rowInfoDom[row].getAttribute('Unit')*1;
1178 var ww2 = 1 - ((colwidth - width) / this.colInfo[endcol]);
1179 var hh2 = 1 - ((rowHeight - height) / rowHeight);
1181 var offset_str = ww + ' ' + hh + ' ' + ww2 + ' '+hh2;
1183 // offset string 0.01 0.01 0.01 0.392 << last one needs to be calculated based on proportions.
1184 // so what are our dimentions..
1190 //alert(offset_str);
1191 soi.setAttribute('ObjectOffset', offset_str);
1192 soi.setAttribute('ObjectAnchorType','16 16 16 16');
1193 soi.setAttribute('Direction','17');
1194 soi.setAttribute('crop-top','0.000000');
1195 soi.setAttribute('crop-bottom','0.000000');
1196 soi.setAttribute('crop-left','0.000000');
1197 soi.setAttribute('crop-right','0.000000');
1203 var content = this.doc.createElement('Content');
1204 content.setAttribute('image-type', type ? type : 'jpeg');
1205 content.setAttribute('size-bytes', size);
1206 content.appendChild( this.doc.createTextNode(data));
1207 soi.appendChild(content);
1208 objs.appendChild(soi);
1210 if (typeof(this.grid[row]) == 'undefined') {
1211 this.grid[row] = [];
1213 if (typeof(this.grid[row][col]) == 'undefined') {
1214 this.createCell(row,col);
1217 this.grid[row][col].value= data;
1218 this.grid[row][col].valueFormat = 'image';
1219 this.grid[row][col].imageType = type;
1220 this.grid[row][col].width = width;
1221 this.grid[row][col].height = height;
1223 var godoc = this.doc.getElementsByTagNameNS('*','GODoc')[0];
1225 if(godoc && godoc.parentNode) {
1226 godoc.parentNode.removeChild(godoc);
1234 * write an image (needs base64 data to write it)
1237 * @param {Number} row row to put it in (rows start at 0)
1238 * @param {Number} col column to put it in
1239 * @param {Number} data the base64 description of the images
1240 * @param {Number} width image width
1241 * @param {Number} width image height
1245 writeImage : function (row, col, data, width, height, type)
1249 throw "write Image called with missing data";
1251 // our default height width is 50/50 ?!
1252 //console.log('w='+width+',height='+height);
1258 var objs = this.sheet.getElementsByTagNameNS('*','Objects')[0];
1259 var soi = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:SheetObjectImage');
1261 //<gmr:SheetObjectImage
1262 // ObjectBound="A3:J8"
1263 // ObjectOffset="0.375 0.882 0.391 0.294"
1264 // ObjectAnchorType="16 16 16 16"
1266 // crop-top="0.000000"
1267 // crop-bottom="0.000000"
1268 // crop-left="0.000000"
1269 // crop-right="0.000000">
1272 //alert(gnumeric_colRowToName(row,col));
1274 // this is where we really have fun!!!...
1275 // since our design currently assumes the height is enough to fit
1276 // stuff in, we only really need to work out how wide it has to be..
1278 // note we should probably use centralized calcs if it fits in the first cell!
1280 // step 1 - work out how many columns it will span..
1281 // lets hope the spreadsheet is big enought..
1284 for ( endcol=col;endcol <100; endcol++) {
1285 if (!this.colInfo[endcol]) {
1286 this.colInfo[endcol] = 100; // eak fudge
1288 colwidth += this.colInfo[endcol];
1289 if (colwidth > width) {
1294 soi.setAttribute('ObjectBound',
1295 //gnumeric_colRowToName(row,col) + ':' + gnumeric_colRowToName(row+1,col+1));
1296 this.RCtoCell(row,col) + ':' + this.RCtoCell(row,endcol));
1298 var ww = 0.01; // offset a bit...
1301 var rowHeight = typeof(this.rowInfoDom[row]) == 'undefined' ? 100 :
1302 this.rowInfoDom[row].getAttribute('Unit')*1;
1305 var ww2 = 1 - ((colwidth - width) / this.colInfo[endcol]);
1306 var hh2 = 1 - ((rowHeight - height) / rowHeight);
1308 var offset_str = ww + ' ' + hh + ' ' + ww2 + ' '+hh2;
1310 //alert(offset_str);
1311 soi.setAttribute('ObjectOffset', offset_str);
1312 soi.setAttribute('ObjectAnchorType','16 16 16 16');
1313 soi.setAttribute('Direction','17');
1314 soi.setAttribute('crop-top','0.000000');
1315 soi.setAttribute('crop-bottom','0.000000');
1316 soi.setAttribute('crop-left','0.000000');
1317 soi.setAttribute('crop-right','0.000000');
1318 // <Content image-type="jpeg" size-bytes="3900">...... < / Content>
1320 var name = 'Image' + Math.random().toString(36).substring(2);
1321 var content = this.doc.createElement('Content');
1322 content.setAttribute('image-type', type ? type : 'jpeg');
1323 content.setAttribute('name', name);
1324 soi.appendChild(content);
1325 objs.appendChild(soi);
1327 var godoc = this.doc.getElementsByTagNameNS('*','GODoc')[0];
1329 var goimage = this.doc.createElement('GOImage');
1330 goimage.setAttribute('image-type', type ? type : 'jpeg');
1331 goimage.setAttribute('name', name);
1332 goimage.setAttribute('type', 'GOPixbuf');
1333 goimage.setAttribute('width', width);
1334 goimage.setAttribute('height', height);
1335 goimage.textContent = data;
1337 godoc.appendChild(goimage);
1339 if (typeof(this.grid[row]) == 'undefined') {
1340 this.grid[row] = [];
1342 if (typeof(this.grid[row][col]) == 'undefined') {
1343 this.createCell(row,col);
1346 this.grid[row][col].value= data;
1347 this.grid[row][col].valueFormat = 'image';
1348 this.grid[row][col].imageType = type;
1349 this.grid[row][col].width = width;
1350 this.grid[row][col].height = height;
1353 //< /gnm:SheetObjectImage>
1359 * writeFixedImageOld:
1360 * write an image in old gnumberic format (needs base64 data to write it)
1362 writeFixedImageOld : function (startCol, startRow, endCol, endRow, type, data, width, height, size)
1365 throw "write Image called with missing data";
1368 startCol = startCol * 1;
1369 startRow = startRow * 1;
1370 endCol = endCol * 1;
1371 endRow = endRow * 1;
1373 height = height * 1;
1375 var objs = this.sheet.getElementsByTagNameNS('*','Objects')[0];
1376 var soi = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:SheetObjectImage');
1378 soi.setAttribute('ObjectBound',this.RCtoCell(startRow, startCol) + ':' + this.RCtoCell(endRow, endCol));
1380 soi.setAttribute('ObjectOffset', '0 0 0 0');
1381 soi.setAttribute('ObjectAnchorType','16 16 16 16');
1382 soi.setAttribute('Direction','17');
1383 soi.setAttribute('crop-top','0.000000');
1384 soi.setAttribute('crop-bottom','0.000000');
1385 soi.setAttribute('crop-left','0.000000');
1386 soi.setAttribute('crop-right','0.000000');
1388 var content = this.doc.createElement('Content');
1389 content.setAttribute('image-type', type ? type : 'jpeg');
1390 content.setAttribute('size-bytes', size);
1391 content.appendChild( this.doc.createTextNode(data));
1392 soi.appendChild(content);
1393 objs.appendChild(soi);
1395 if (typeof(this.grid[startRow]) == 'undefined') {
1396 this.grid[startRow] = [];
1398 if (typeof(this.grid[startRow][startCol]) == 'undefined') {
1399 this.createCell(startRow,startCol);
1402 this.grid[startRow][startCol].value= data;
1403 this.grid[startRow][startCol].valueFormat = 'image';
1404 this.grid[startRow][startCol].imageType = type;
1405 this.grid[startRow][startCol].width = width;
1406 this.grid[startRow][startCol].height = height;
1408 var godoc = this.doc.getElementsByTagNameNS('*','GODoc')[0];
1410 if(godoc && godoc.parentNode) {
1411 godoc.parentNode.removeChild(godoc);
1417 writeFixedImage : function (startCol, startRow, endCol, endRow, type, data, width, height)
1420 throw "write Image called with missing data";
1423 startCol = startCol * 1;
1424 startRow = startRow * 1;
1425 endCol = endCol * 1;
1426 endRow = endRow * 1;
1428 height = height * 1;
1430 var objs = this.sheet.getElementsByTagNameNS('*','Objects')[0];
1431 var soi = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:SheetObjectImage');
1433 soi.setAttribute('ObjectBound',this.RCtoCell(startRow, startCol) + ':' + this.RCtoCell(endRow, endCol));
1435 soi.setAttribute('ObjectOffset', '0 0 0 0');
1436 soi.setAttribute('ObjectAnchorType','16 16 16 16');
1437 soi.setAttribute('Direction','17');
1438 soi.setAttribute('crop-top','0.000000');
1439 soi.setAttribute('crop-bottom','0.000000');
1440 soi.setAttribute('crop-left','0.000000');
1441 soi.setAttribute('crop-right','0.000000');
1443 var name = 'Image' + Math.random().toString(36).substring(2);
1444 var content = this.doc.createElement('Content');
1445 content.setAttribute('image-type', type ? type : 'jpeg');
1446 content.setAttribute('name', name);
1448 soi.appendChild(content);
1449 objs.appendChild(soi);
1451 var godoc = this.doc.getElementsByTagNameNS('*','GODoc')[0];
1453 var goimage = this.doc.createElement('GOImage');
1454 goimage.setAttribute('image-type', type ? type : 'jpeg');
1455 goimage.setAttribute('name', name);
1456 goimage.setAttribute('type', 'GOPixbuf');
1457 goimage.setAttribute('width', width);
1458 goimage.setAttribute('height', height);
1459 goimage.textContent = data;
1461 godoc.appendChild(goimage);
1463 if (typeof(this.grid[startRow]) == 'undefined') {
1464 this.grid[startRow] = [];
1466 if (typeof(this.grid[startRow][startCol]) == 'undefined') {
1467 this.createCell(startRow,startCol);
1470 this.grid[startRow][startCol].value= data;
1471 this.grid[startRow][startCol].valueFormat = 'image';
1472 this.grid[startRow][startCol].imageType = type;
1473 this.grid[startRow][startCol].width = width;
1474 this.grid[startRow][startCol].height = height;
1481 * Merge cells in the spreadsheet. (does not check if existing merges exist..)
1483 * @param {Number} col1 first column
1484 * @param {Number} row1 first row
1485 * @param {Number} col2 to column
1486 * @param {Number} row2 to row
1489 mergeRegion : function (col1,row1,col2,row2)
1491 var cell = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Merge');
1492 //if (col1 > 50|| col2 > 50) { // do not merge cols off to right?
1496 cell.textContent = this.RCtoCell(row1,col1) + ':' + this.RCtoCell(row2,col2);
1498 //var merges = this.gnumeric.getElementsByTagNameNS('*','MergedRegions');
1499 var merges = this.sheet.getElementsByTagNameNS('*','MergedRegions');
1500 if (!merges || !merges.length) {
1501 merges = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd','gnm:MergedRegions');
1502 var sl = this.sheet.getElementsByTagNameNS('*','SheetLayout')[0];
1503 this.sheet.insertBefore(merges,sl);
1507 merges.appendChild(cell);
1512 * Sets the height of a row.
1514 * @param {Number} r the row to set the height of. (rows start at 0)
1515 * @param {Number} height (in pixels)
1517 setRowHeight : function (r,height)
1520 //<gmr:Rows DefaultSizePts="12.75">
1521 // <gmr:RowInfo No="2" Unit="38.25" MarginA="0" MarginB="0" HardSize="1"/>
1524 // this doesnt handle row ranges very well.. - with 'count in them..'
1526 if (this.rowInfoDom[r]) {
1527 this.rowInfoDom[r].setAttribute('Unit', height);
1531 var rows = this.sheet.getElementsByTagNameNS('*','Rows')[0]; // assume this exists..
1532 var ri = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd','gnm:RowInfo');
1533 // assume we have no rows..
1534 ri.setAttribute('No', r-1);
1535 ri.setAttribute('Unit', height);
1536 ri.setAttribute('MarginA', 0);
1537 ri.setAttribute('MarginB', 0);
1538 ri.setAttribute('HardSize', 1);
1539 rows.appendChild(ri);
1540 this.rowInfoDom[r] = ri;
1545 * Set the sheet name.
1546 * @param {String} title for sheet
1548 setSheetName : function(name,sheet)
1552 <gnm:SheetNameIndex>
1553 <gnm:SheetName>Sheet1</gnm:SheetName>
1554 <gnm:SheetName>Sheet2</gnm:SheetName>
1555 <gnm:SheetName>Sheet3</gnm:SheetName>
1556 </gnm:SheetNameIndex>
1558 // has to set sheet name on index and body..
1561 var sheetnames = this.doc.getElementsByTagNameNS('*','SheetName');
1562 if (sheet >= sheetnames.length) {
1564 sheetnames[0].parentNode.appendChild(sheetnames[sheetnames.length-1].cloneNode(true));
1566 sheetnames = this.doc.getElementsByTagNameNS('*','Sheet');
1567 sheetnames[0].parentNode.appendChild(sheetnames[sheetnames.length-1].cloneNode(true));
1568 var sn = this.doc.getElementsByTagNameNS('*','Sheet')[sheet];
1569 var cls = sn.getElementsByTagNameNS('*','Cells')[0];
1570 while (cls.childNodes.length) {
1571 cls.removeChild(cls.firstChild);
1576 var sheetn = this.doc.getElementsByTagNameNS('*','SheetName')[sheet];
1577 sheetn.textContent = name;
1578 var sheetb = this.doc.getElementsByTagNameNS('*','Sheet')[sheet].getElementsByTagNameNS('*','Name')[0];
1579 sheetb.textContent = name;
1580 this.parseDoc(sheet);
1588 * Set the column width
1589 * @param {Number} column number (starts at '0')
1590 * @param {Number} width size of column
1592 setColumnWidth : function(column, width)
1596 if (typeof(this.colInfoDom[column]) == 'undefined') {
1597 var cols = this.sheet.getElementsByTagNameNS('*','Cols')[0];
1598 var ri = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:ColInfo');
1599 ri.setAttribute('No', column);
1600 ri.setAttribute('Unit', width);
1601 ri.setAttribute('MarginA', 2);
1602 ri.setAttribute('MarginB', 2);
1603 ri.setAttribute('HardSize', 1);
1604 cols.appendChild(ri);
1605 this.colInfo[column] = width;
1606 this.colInfoDom[column] = ri;
1609 this.colInfoDom[column].setAttribute('Unit', width);
1619 * Convert spreadsheet into a HTML table.
1625 function calcWidth(sc, span)
1628 for(var i =sc; i< sc+span;i++) {
1634 var grid = this.grid;
1635 // lets do a basic dump..
1636 var out = '<table style="table-layout:fixed;" cellpadding="0" cellspacing="0">';
1637 for (var r = 0; r < this.rmax;r++) {
1638 out += '<tr style="height:'+this.rowInfo[r]+'px;">';
1639 for (var c = 0; c < this.cmax;c++) {
1640 if (typeof(grid[r][c]) == 'undefined') {
1641 this.createCell(r,c);
1646 if (typeof(g.cls) =='undefined') {
1649 var w= calcWidth(c,g.colspan);
1651 var value = g.value[0] == '=' ? 'CALCULATED' : g.value;
1655 g.styles[0].firstElementChild.getAttribute('Format') == "D\\-MMM\\-YYYY;@" &&
1656 g.value[0] != '=' &&
1657 !isNaN(value * 1) &&
1660 value = new Date(value * 24 * 60 * 60 * 1000 + new Date('1899-12-30').getTime()).format('d-M-Y');
1667 if(g.valueFormat == 'image') {
1669 out+=String.format('<td colspan="{0}" rowspan="{1}" class="{2}"><div style="{3}"><img src="data:image/{4};base64, {5}" width="{6}" height="{7}"></div></td>',
1670 g.colspan, g.rowspan, g.cls.join(' '),
1671 'overflow:hidden;' +
1672 'width:'+g.width+'px;' +
1674 'text-overflow:ellipsis;' +
1675 'white-space:nowrap;',
1677 value, g.width, g.height
1684 out+=String.format('<td colspan="{0}" rowspan="{1}" class="{4}"><div style="{3}">{2}</div></td>',
1685 g.colspan, g.rowspan, value,
1686 'overflow:hidden;' +
1689 'text-overflow:ellipsis;' +
1690 'white-space:nowrap;',
1700 return out+'</table>';
1707 * @param {String} name filename to downlaod (without xls)
1708 * @param {String} callback (optional) - callback to call after callback is complete.
1710 download : function(name,callback)
1712 name = name || "Missing_download_filename";
1714 if (this.downloadURL && this.downloadURL.charAt(this.downloadURL.length-1) != '/') {
1715 this.downloadURL += '/';
1718 var ser = new XMLSerializer();
1719 Roo.get(document.body).mask("Downloading");
1720 var x = new Pman.Download({
1722 timeout : 120000, // quite a long wait.. 2 minutes.
1724 xml : ser.serializeToString(this.doc),
1725 format : this.format,
1729 url : (this.downloadURL || (baseURL + '/GnumericToExcel/')) + name + '.xls',
1730 success : function() {
1731 Roo.get(document.body).unmask();
1732 Roo.MessageBox.alert("Alert", "File should have downloaded now");
1737 failure : function() {
1738 Roo.get(document.body).unmask();
1739 Roo.MessageBox.alert("Alert", "Download failed");