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
131 * @type {Number} cmax - maximum number of columns
135 * @type {Object} rmax - maximum number of rows
139 * @type {String} stylesheetID id of stylesheet created to render spreadsheat
141 stylesheetID : false,
143 * @type {Number} rowOffset - used by table importer to enable multiple tables to be improted
150 * run the connection, parse document and fire load event..
151 * can be run multiple times with new data..
157 this.url = url || this.url;
165 this.colInfo = false;
166 this.rowInfo = false;
170 if (this.stylesheetID) {
172 Roo.util.CSS.removeStyleSheet(this.stylesheetID);
173 this.stylesheetID = false;
178 var c = new Roo.data.Connection();
182 success : function(resp, opts) {
184 _t.doc = resp.responseXML;
191 _t.fireEvent('load', _t);
195 Roo.MessageBox.alert("Error", "Failed to Load Template for Spreadsheet");
204 RCtoCell : function(r,c)
206 // we wil only support AA not AAA
207 var top = Math.floor(c/26);
209 var cc = top > 0 ? String.fromCharCode('A'.charCodeAt(0) + top) : '';
210 cc += String.fromCharCode('A'.charCodeAt(0) ) + bot;
217 * convert 'A1' style position to row/column reference
219 * @arg {String} k cell name
220 * @return {Object} as { r: {Number} , c: {Number} }
225 var c = k.charCodeAt(0)-64;
226 var n = k.substring(1);
227 if (k.charCodeAt(1) > 64) {
229 c+=k.charCodeAt(1)-64;
232 return { c:c -1 ,r: (n*1)-1 }
236 * convert 'A1:B1' style position to array of row/column references
238 * @arg {String} k cell range
239 * @return {Array} as [ { r: {Number} , c: {Number} }. { r: {Number} , c: {Number} } ]
241 rangeToRC : function(s) {
242 var ar = s.split(':');
243 return [ this.toRC(ar[0]) , this.toRC(ar[1])]
252 * convert XML document into cells and other data..
255 parseDoc : function(sheetnum)
262 this.sheet = _t.doc.getElementsByTagNameNS('*','Sheet')[sheetnum];
265 this.cellholder = this.sheet.getElementsByTagNameNS('*','Cells')[0];
266 var cells = this.sheet.getElementsByTagNameNS('*','Cell');
270 Roo.each(cells, function(c) {
272 var row = c.getAttribute('Row') * 1;
273 var col = c.getAttribute('Col') * 1;
274 _t.cmax = Math.max(col+1, _t.cmax);
275 _t.rmax = Math.max(row+1, _t.rmax);
276 var vt = c.getAttribute('ValueType');
277 var vf = c.getAttribute('ValueFormat');
278 var val = c.textContent;
280 if (typeof(_t.grid[row]) == 'undefined') {
283 _t.grid[row][col] = Roo.applyIf({
293 for (var r = 0; r < this.rmax;r++) {
294 if (typeof(this.grid[r]) == 'undefined') {
297 for (var c = 0; c < this.cmax;c++) {
298 if (typeof(this.grid[r][c]) == 'undefined') {
301 //this.print( "[" + r + "]["+c+"]=" + grid[r][c].value +'<br/>');
305 var merge = this.sheet.getElementsByTagNameNS('*','Merge');
307 Roo.each(merge, function(c) {
308 var rc = _t.rangeToRC(c.textContent);
309 //Roo.log(JSON.stringify(rc))
310 if (typeof(_t.grid[rc[0].r][rc[0].c]) == 'undefined') {
311 _t.grid[rc[0].r][rc[0].c] = Roo.applyIf({ r : rc[0].r, c : rc[0].c }, _t.defaultCell);
314 _t.grid[rc[0].r][rc[0].c].colspan = (rc[1].c - rc[0].c) + 1;
315 _t.grid[rc[0].r][rc[0].c].rowspan = (rc[1].r - rc[0].r) + 1;
316 for(var r = (rc[0].r); r < (rc[1].r+1); r++) {
317 for(var c = rc[0].c; c < (rc[1].c+1); c++) {
318 //Roo.log('adding alias : ' + r+','+c);
319 _t.grid[r][c] = _t.grid[rc[0].r][rc[0].c];
326 var ci = this.sheet.getElementsByTagNameNS('*','ColInfo');
328 this.colInfoDom = {};
330 Roo.each(ci, function(c) {
331 var count = c.getAttribute('Count') || 1;
332 var s = c.getAttribute('No')*1;
333 for(var i =0; i < count; i++) {
334 _t.colInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
335 _t.colInfoDom[s+i] = c;
340 ci = this.sheet.getElementsByTagNameNS('*','RowInfo');
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.rowInfo[s+i] = Math.floor(c.getAttribute('Unit')*1);
360 * put the style info onto the cell data.
363 overlayStyles : function ()
367 Roo.each(this.styles, function(s) {
369 for (var r = s.r; r < s.r1;r++) {
370 if (typeof(_t.grid[r]) == 'undefined') {
373 for (var c = s.c; c < s.c1;c++) {
374 if (c > _t.cmax) continue;
376 if (typeof(_t.grid[r][c]) == 'undefined') _t.grid[r][c] = Roo.applyIf({ r: r , c : c }, _t.defaultCell);
378 if (typeof(g.cls) =='undefined') {
382 if (g.cls.indexOf(s.name) > -1) continue;
384 g.styles.push(s.dom);
392 * read the style information
393 * generates a stylesheet for the current file
394 * this should be disposed of really.....
397 parseStyles : function() {
399 var srs = this.sheet.getElementsByTagNameNS('*','StyleRegion');
404 HAlign : function(ent,v) {
405 ent['text-align'] = { '1' : 'left', '8': 'center', '4' : 'right'}[v] || 'left';
407 VAlign : function(ent,v) {
408 ent['vertical-align'] = { '1' : 'top', '4': 'middel', '8' : 'bottom'}[v] || 'top'
410 Fore : function(ent,v) {
412 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
413 ent['color'] = 'rgb(' + col.join(',') + ')';
415 Back : function(ent,v) {
417 Roo.each(v.split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
418 ent['background-color'] = 'rgb(' + col.join(',') + ')';
420 FontUnit : function(ent,v) {
421 ent['font-size'] = v + 'px';
423 FontBold : function(ent,v) {
425 ent['font-weight'] = 'bold';
427 FontItalic : function(ent,v) {
429 //ent['font-weight'] = 'bold';
431 FontName : function(ent,v) {
432 ent['font-family'] = v;
434 BorderStyle : function(ent,v) {
435 var vv = v.split('-');
436 ent['border-'+vv[0]+'-style'] = 'solid';
437 ent['border-'+vv[0]+'-width'] = vv[1]+'px';
439 BorderColor : function(ent,v) {
440 var vv = v.split('-');
442 Roo.each(vv[1].split(':'), function(c) { col.push(Math.round(parseInt(c,16)/256)); })
443 ent['border-'+vv[0]+'-color'] = 'rgb(' + col.join(',') + ')';
446 function add(e, k, v) {
449 if (typeof(map[k]) == 'undefined') {
459 Roo.each(srs, function(sr,n)
462 c : sr.getAttribute('startCol') *1,
463 r : sr.getAttribute('startRow')*1,
464 c1 : (sr.getAttribute('endCol')*1) +1,
465 r1 : (sr.getAttribute('endRow')*1) +1,
466 style : {}, // key val of style for HTML..
467 gstyle : {}, // key val of attributes used..
468 name : sid +'-gstyle-' + n,
473 Roo.each(sr.getElementsByTagNameNS('*','Style')[0].attributes, function(e) {
474 add(ent, e.name, e.value);
476 if (sr.getElementsByTagNameNS('*','Font').length) {
477 Roo.each(sr.getElementsByTagNameNS('*','Font')[0].attributes, function(e) {
478 add(ent, 'Font'+e.name, e.value);
481 add(ent, 'FontName', sr.getElementsByTagNameNS('*','Font')[0].textContent);
484 if (sr.getElementsByTagNameNS('*','StyleBorder').length) {
485 Roo.each(sr.getElementsByTagNameNS('*','StyleBorder')[0].childNodes, function(e) {
489 Roo.each(e.attributes, function(ea) {
490 add(ent, 'Border'+ea.name, e.tagName.split(':')[1].toLowerCase() + '-' + ea.value);
496 css['.'+ent.name] = ent.style;
499 this.styles = styles;
501 this.stylesheetID = sid;
502 Roo.util.CSS.createStyleSheet(css, sid);
505 /* --------------------------------------- AFTER LOAD METHODS... ----------------------- */
508 * Set the value of a cell..
509 * @param {String} cell name of cell, eg. C10 or { c: 1, r :1 }
511 * @param {Value} value to put in cell..
512 * @param {ValueType} type of value
513 * @param {ValueFormat} value format of cell
515 * Cells should exist at present, we do not make them up...
519 set : function(cell, v, vt, vf) {
521 var cs= typeof(cell) == 'string' ? this.toRC(cell) : cell;
522 //Roo.log( cs.r+ ',' + cs.c + ' = '+ v);
523 // need to generate clell if it doe
524 if (typeof(this.grid[cs.r]) == 'undefined') {
525 Roo.log('no row:' + cell);
526 this.grid[cs.r] = []; // create a row..
529 if (typeof(this.grid[cs.r][cs.c]) == 'undefined') {
530 Roo.log('cell not defined:' + cell);
531 this.createCell(cs.r,cs.c);
533 if (typeof(this.grid[cs.r][cs.c].dom) == 'undefined') {
534 Roo.log('no default content for cell:' + cell);
535 this.createCell(cs.r,cs.c);
538 this.grid[cs.r][cs.c].value= v;
539 this.grid[cs.r][cs.c].dom.textContent= v;
540 if (typeof(vt) != 'undefined') {
541 this.grid[cs.r][cs.c].valueType = vt;
542 this.grid[cs.r][cs.c].dom.setAttribute('ValueType', vt);
543 if (vt === '' || vt === false) { // value type is empty for formula's
544 this.grid[cs.r][cs.c].dom.removeAttribute('ValueType');
547 if (typeof(vf) != 'undefined' && vf !== false) {
548 this.grid[cs.r][cs.c].valueFormat = vf;
549 this.grid[cs.r][cs.c].dom.setAttribute('ValueFormat', vf);
550 if (vf === '' || vf === false) { // value type is empty for formula's
551 this.grid[cs.r][cs.c].dom.removeAttribute('ValueFormat');
558 copyRow : function(src, dest) {
562 // Roo.log('create Row' + dest);
563 if (typeof(this.grid[dest]) == 'undefined') {
568 for (var c = 0; c < this.cmax; c++) {
570 this.copyCell({ r: src, c: c } , { r: dest, c: c});
573 this.rmax = Math.max(this.rmax, dest +1);
579 createCell: function(r,c)
581 //<gnm:Cell Row="6" Col="5" ValueType="60">Updated</gnm:Cell>
582 var nc = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Cell');
583 this.cellholder.appendChild(nc);
584 var lb = this.doc.createTextNode("\n");// add a line break..
585 this.cellholder.appendChild(lb);
587 nc.setAttribute('Row', new String(r));
588 nc.setAttribute('Col', new String(c));
589 nc.setAttribute('ValueType', '60');
592 this.grid[r][c] = Roo.applyIf({
606 copyCell : function(src, dest)
608 var old = this.grid[src.r][src.c];
610 if ((old.c != src.c) || (old.r != src.r)) {
611 // only really works on horizonatal merges..
613 this.grid[dest.r][dest.c] = this.grid[desc.r][old.c]; // let's hope it exists.
618 var nc = Roo.apply({}, this.grid[src.r][src.c]);
621 if (typeof(old.dom) == 'undefined') {
622 Roo.log("No cell to copy for " + Roo.encode(src));
625 this.grid[dest.r][dest.c] = nc;
626 nc.dom = old.dom.cloneNode(true);
627 nc.dom.setAttribute('Row', dest.r);
628 nc.dom.setAttribute('Cell', dest.c);
629 nc.dom.textContent = '';
630 old.dom.parentNode.appendChild(nc.dom);
631 if (!old.styles || !old.styles.length) {
638 Roo.each(old.styles, function(s) {
639 // try and extend existing styles..
640 var er = s.getAttribute('endRow') * 1;
641 var ec = s.getAttribute('endCol') * 1;
644 s.setAttribute('endRow', dest.r + 1);
647 s.setAttribute('endCol', dest.c + 1);
649 /*var ns = s.cloneNode(true);
650 s.parentNode.appendChild(ns);
651 ns.setAttribute('startCol', dest.c);
652 ns.setAttribute('startRow', dest.r);
653 ns.setAttribute('endCol', dest.c + 1);
654 ns.setAttribute('endRow', dest.r +1);
663 * Set the value of a cell..
664 * @param {String} cell name of cell, eg. C10
665 * @param {Value} value to put in cell..
667 * Cells should exist at present, we do not make them up...
670 applyData : function(data)
673 data = data || this.data;
674 for (var r = 0; r < this.rmax;r++) {
675 if (typeof(this.grid[r]) == 'undefined') continue;
676 for (var c = 0; c < this.cmax;c++) {
677 if (typeof(this.grid[r][c]) == 'undefined') {
680 if (!this.grid[r][c].value.length
681 || !this.grid[r][c].value.match(/\{/)) {
685 var x = new Roo.Template({ html: this.grid[r][c].value });
687 var res = x.applyTemplate(data);
688 //Roo.log("set " + r + "," + c + ":"+res)
689 this.set({ r: r, c: c}, x.applyTemplate(data));
691 // Roo.log(e.toString());
703 * Import a table and put it into the spreadsheet
704 * @param {HTMLTable} datagrid dom element of html table.
705 * @param {Number} xoff X offset to start rendering to
706 * @param {Number} yoff Y offset to start rendering to
710 importTable : function (datagrid, xoff,yoff)
713 Roo.log("Error table not found!?");
719 var cleanHTML = function (str) {
722 ret = ret.replace(/ /g,'.');
723 ret = ret.replace(/\n/g,'.');
724 ret = ret.replace(/\r/g,'.');
726 while (-1 != (i = ret.indexOf(unescape('%A0')))) {
727 ret = ret.substring(0,i) + ' ' + ret.substring(i+1,str.length);
733 // <cell col="A" row="1">Test< / cell>
734 // <cell col="B" row="2" type="Number" format="test1">30< / cell>
736 var rows = datagrid.getElementsByTagName('tr');
737 //alert(rows.length);
739 for(var row=0;row<rows.length;row++) {
740 //var style = document.defaultView.getComputedStyle(rows[row], "");
742 //if (rows[row].getAttribute('xls:height')) {
743 // this.setRowHeight(row+y_offset, 0 + rows[row].getAttribute('xls:height'));
745 // this.setRowHeight(row+y_offset, 0 + style.height.replace(/[^0-9.]+/g,''));
749 // if (rowOffsets[row]) {
750 // coloffset += rowOffsets[row];
752 var cols = rows[row].getElementsByTagName('td');
755 for(var col=0;col < cols.length; col++) {
758 //var colat = col + coloffset;
760 if (cols[col].getAttribute('colspan') && (cols[col].getAttribute('colspan') > 1)) {
762 //row + yoff, c : col + xoff + coloffset
764 col + xoff + coloffset,
766 col + xoff + coloffset + (cols[col].getAttribute('colspan') - 1),
768 (cols[col].getAttribute('rowspan') > 1) ?
769 (cols[col].getAttribute('rowspan') - 1) : 0
772 coloffsetadd = (cols[col].getAttribute('colspan')*1) - 1;
776 if (cols[col].getAttribute('rowspan') && (cols[col].getAttribute('rowspan') > 1)) {
777 // this should really do a merge, but it's pretty damn complex...
778 //this.mergeRegion(colat,row +y_offset,colat + (cols[col].getAttribute('colspan') - 1), row+y_offset);
779 var rroff = cols[col].getAttribute('colspan') ? (cols[col].getAttribute('colspan') -0): 1;
781 for (rr = 0; rr < cols[col].getAttribute('rowspan');rr++) {
782 rowOffsets[rr + row] = col + rroff;
789 var style = this.newStyle();
790 if (style.setFrom(cols[col])) {
795 colat+x_offset + ((cols[col].getAttribute('colspan') > 1) ?
796 (cols[col].getAttribute('colspan') - 1) : 0),
797 row+y_offset + ((cols[col].getAttribute('rowspan') > 1) ?
798 (cols[col].getAttribute('rowspan') - 1) : 0)
804 if (!cols[col].childNodes.length) {
805 coloffset += coloffsetadd;
814 var xlstype = cols[col].getAttribute('xls:type');
822 vt = 40; // float!!!!
823 if (cols[col].getAttribute('xls:floatformat')) {
824 vf = cols[col].getAttribute('xls:floatformat');
830 //ValueFormat="d/m/yyyy" 38635
832 if (cols[col].getAttribute('xls:dateformat')) {
833 vf= cols[col].getAttribute('xls:dateformat');
845 if (cols[col].getAttribute('xls:src')) {
846 //alert(cols[col].childNodes[0].width);
849 colat+x_offset+coloffset,
850 cols[col].getAttribute('xls:src'),
851 cols[col].childNodes[0].width,
852 cols[col].childNodes[0].height
860 if (!cols[col].childNodes[0].nodeValue) {
861 coloffset += coloffsetadd;
864 if (!cols[col].childNodes[0].nodeValue.replace(/^\s*|\s*$/g,"").length) {
865 coloffset += coloffsetadd;
869 var cell_value_text = cleanHTML(cols[col].childNodes[0].nodeValue);
871 if (cols[col].getAttribute('xls:percent')) {
872 cell_value_text = '' + ((cell_value_text * 1) / 100);
875 if (cell_value_text.length && (vt == 30) && xlstype == 'date') {
876 var bits = cell_value_text.split(/-/);
877 var cur = new Date(bits[0],bits[1]-1,bits[2]);
878 cell_value_text = '' + Math.round((cur.getTime() - Date.UTC(1899,11,30)) / (24 * 60 * 60 * 1000));
883 if (cols[col].getAttribute('xls:formula')) {
884 var s = cols[col].getAttribute('xls:formula');
886 cell_value_text = s.replace(/#row#/g,(row + yoff + 1));
888 this.set({ r: row + yoff, c : col + xoff + coloffset }, cell_value_text, vt, vf);
890 coloffset += coloffsetadd;
899 this.rowOffset += rows.length;
905 * write an image (needs base64 data to write it)
908 * @param {Number} row row to put it in
909 * @param {Number} col column to put it in
910 * @param {Number} data the base64 description of the images
911 * @param {Number} row2 to row
916 writeImage : function (row, col, data, width, height)
919 // our default height width is 50/50 ?!
920 //console.log('w='+width+',height='+height);
922 var objs = this.doc.getElementsByTagNameNS('*','Objects')[0];
923 var soi = this.doc.createElement('gnm:SheetObjectImage');
925 //<gmr:SheetObjectImage
926 // ObjectBound="A3:J8"
927 // ObjectOffset="0.375 0.882 0.391 0.294"
928 // ObjectAnchorType="16 16 16 16"
930 // crop-top="0.000000"
931 // crop-bottom="0.000000"
932 // crop-left="0.000000"
933 // crop-right="0.000000">
936 //alert(gnumeric_colRowToName(row,col));
938 // this is where we really have fun!!!...
939 // since our design currently assumes the height is enough to fit
940 // stuff in, we only really need to work out how wide it has to be..
942 // note we should probably use centralized calcs if it fits in the first cell!
944 // step 1 - work out how many columns it will span..
945 // lets hope the spreadsheet is big enought..
948 for (var endcol=col;endcol <100; endcol++) {
949 if (!this.colInfo[endcol]) {
950 this.colInfo[endcol] = 100; // eak fudge
952 colwidth += this.colInfo[endcol];
953 if (colwidth > width) {
959 soi.setAttribute('ObjectBound',
960 //gnumeric_colRowToName(row,col) + ':' + gnumeric_colRowToName(row+1,col+1));
961 this.RCtoCell(row,col) + ':' + this.RCtoCell(row,col));
963 var ww = 0.01; // offset a bit...
966 var ww2 = 1 - ((colwidth - width) / this.colInfo[endcol]);
969 var offset_str = ww + ' ' + hh + ' ' + ww2 + ' '+hh2;
970 //console.log(offset_str );
972 soi.setAttribute('ObjectOffset', offset_str);
973 soi.setAttribute('ObjectAnchorType','16 16 16 16');
974 soi.setAttribute('Direction','17');
975 soi.setAttribute('crop-top','0.000000');
976 soi.setAttribute('crop-bottom','0.000000');
977 soi.setAttribute('crop-left','0.000000');
978 soi.setAttribute('crop-right','0.000000');
979 // <Content image-type="jpeg" size-bytes="3900">...... < / Content>
980 var content = this.doc.createElement('Content');
981 content.setAttribute('image-type','jpeg');
984 content.setAttribute('size-bytes',data.length);
985 content.textContent = data;
986 soi.appendChild(content);
987 objs.appendChild(soi);
989 //< /gnm:SheetObjectImage>
996 * Merge cells in the spreadsheet. (does not check if existing merges exist..)
998 * @param {Number} col1 first column
999 * @param {Number} row1 first row
1000 * @param {Number} col2 to column
1001 * @param {Number} row2 to row
1004 mergeRegion : function (col1,row1,col2,row2)
1006 var cell = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:Merge');
1007 //if (col1 > 50|| col2 > 50) { // do not merge cols off to right?
1011 cell.textContent = this.RCtoCell(row1,col1) + ':' + this.RCtoCell(row2,col2)
1013 //var merges = this.gnumeric.getElementsByTagNameNS('*','MergedRegions');
1014 var merges = this.sheet.getElementsByTagNameNS('*','MergedRegions');
1015 if (!merges || !merges.length) {
1016 merges = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd','gnm:MergedRegions');
1017 var sl = this.sheet.getElementsByTagNameNS('*','SheetLayout')[0];
1018 this.sheet.insertBefore(merges,sl);
1022 merges.appendChild(cell);
1027 * Sets the height of a row.
1029 * @param {Number} row the row to set the height of.
1030 * @param {Number} height (in pixels)
1032 setRowHeight : function (row,height)
1034 //<gmr:Rows DefaultSizePts="12.75">
1035 // <gmr:RowInfo No="2" Unit="38.25" MarginA="0" MarginB="0" HardSize="1"/>
1037 var rows = this.doc.getElementsByTagNameNS('*','Rows')[0]; // assume this exists..
1038 var ri = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd','gnm:MRowInfo');
1039 // assume we have no rows..
1040 ri.setAttribute('No', row);
1041 ri.setAttribute('Unit', height);
1042 ri.setAttribute('MarginA', 0);
1043 ri.setAttribute('MarginB', 0);
1044 ri.setAttribute('HardSize', 1);
1045 rows.appendChild(ri);
1050 * Set the sheet name.
1051 * @param {String} title for sheet
1053 setSheetName : function(name,sheet)
1057 <gnm:SheetNameIndex>
1058 <gnm:SheetName>Sheet1</gnm:SheetName>
1059 <gnm:SheetName>Sheet2</gnm:SheetName>
1060 <gnm:SheetName>Sheet3</gnm:SheetName>
1061 </gnm:SheetNameIndex>
1063 // has to set sheet name on index and body..
1066 var sheetnames = this.doc.getElementsByTagNameNS('*','SheetName');
1067 if (sheet >= sheetnames.length) {
1069 sheetnames[0].parentNode.appendChild(sheetnames[sheetnames.length-1].cloneNode(true));
1071 sheetnames = this.doc.getElementsByTagNameNS('*','Sheet');
1072 sheetnames[0].parentNode.appendChild(sheetnames[sheetnames.length-1].cloneNode(true));
1073 var sn = this.doc.getElementsByTagNameNS('*','Sheet')[sheet];
1074 var cls = sn.getElementsByTagNameNS('*','Cells')[0]
1075 while (cls.childNodes.length) {
1076 cls.removeChild(cls.firstChild);
1081 var sheetn = this.doc.getElementsByTagNameNS('*','SheetName')[sheet];
1082 sheetn.textContent = name;
1083 var sheetb = this.doc.getElementsByTagNameNS('*','Sheet')[sheet].getElementsByTagNameNS('*','Name')[0];
1084 sheetb.textContent = name;
1085 this.parseDoc(sheet);
1093 * Set the column width
1094 * @param {Number} column number (starts at '0')
1095 * @param {Number} width size of column
1097 setColumnWidth : function(column, width)
1101 if (typeof(this.colInfoDom[column]) == 'undefined') {
1102 var cols = this.doc.getElementsByTagNameNS('*','Cols')[0];
1103 var ri = this.doc.createElementNS('http://www.gnumeric.org/v10.dtd', 'gnm:ColInfo');
1104 ri.setAttribute('No', column);
1105 ri.setAttribute('Unit', width);
1106 ri.setAttribute('MarginA', 2);
1107 ri.setAttribute('MarginB', 2);
1108 ri.setAttribute('HardSize', 1);
1109 cols.appendChild(ri);
1110 this.colInfo[column] = width;
1111 this.colInfoDom[column] = ri;
1114 this.colInfoDom[column].setAttribute('Unit', width);
1124 * Convert spreadsheet into a HTML table.
1130 function calcWidth(sc, span)
1133 for(var i =sc; i< sc+span;i++) {
1139 var grid = this.grid;
1140 // lets do a basic dump..
1141 var out = '<table style="table-layout:fixed;" cellpadding="0" cellspacing="0">';
1142 for (var r = 0; r < this.rmax;r++) {
1143 out += '<tr style="height:'+this.rowInfo[r]+'px;">';
1144 for (var c = 0; c < this.cmax;c++) {
1145 var g = (typeof(grid[r][c]) == 'undefined') ? defaultCell : grid[r][c];
1147 if (typeof(g.cls) =='undefined') g.cls = [];
1148 var w= calcWidth(c,g.colspan);
1149 out+=String.format('<td colspan="{0}" rowspan="{1}" class="{4}"><div style="{3}">{2}</div></td>',
1150 g.colspan, g.rowspan, g.value,
1151 'overflow:hidden;' +
1154 'text-overflow:ellipsis;' +
1155 'white-space:nowrap;',
1165 return out+'</table>';
1172 * @param {String} name filename to downlaod (without xls)
1173 * @param {String} callback (optional) - callback to call after callback is complete.
1175 download : function(name,callback)
1177 name = name || "Missing_download_filename";
1179 if (this.downloadURL && this.downloadURL.charAt(this.downloadURL .length-1) != '/') {
1180 this.downloadURL += '/';
1183 var ser = new XMLSerializer();
1184 var x = new Pman.Download({
1187 xml : ser.serializeToString(this.doc),
1188 format : 'xls', //xml
1192 url : (this.downloadURL || (baseURL + '/GnumericToExcel/')) + name + '.xls',
1193 success : function() {
1194 Roo.MessageBox.alert("Alert", "File should have downloaded now");