1 //<script type="text/javascript">
6 writer = new GnumericWriter(rooturl + '/FlexyShipping/templates/spreadsheets/base.gnumeric.xml', function(w) {
7 w.setSheetName(document.getElementById('title').childNodes[0].nodeValue);
9 w.writeString(0,0, 'Summary for ' + document.getElementById('title').childNodes[0].nodeValue +
10 ' as of ' + document.getElementById('date').childNodes[0].nodeValue);
14 w.importGrid(grid, rows)
17 return true; // will post the form.. false will stop it..
22 cell information for auto tables:
24 <td xls:type="int|float|string"> (string default)
26 <td xls:Format="#,##0.00" (2 decimal places) on a float
27 <td xls:Format="0.00%"> percentage with float
28 <td xls:Format="0%"> percentage (with no float)
32 - added so the editor works..
35 function GnumericWriter(cfg) { }
38 function GnumericWriter(cfg)
43 if (!this.targetURL.length) {
44 this.targetURL = baseURL + '/GnumericToExcel';
47 if (!GnumericWriter.postform) {
48 GnumericWriter.postform = Roo.get(document.body).createChild({
51 //defaultaction : baseURL + '/GnumericToExcel',
55 GnumericWriter.postxml = GnumericWriter.postform.createChild({
56 tag: 'input', type: 'hidden', name: 'xml'
58 GnumericWriter.postformat = GnumericWriter.postform.createChild({
59 tag: 'input', type: 'hidden', name: 'format', value: 'xls'
61 GnumericWriter.postdebug = GnumericWriter.postform.createChild({
62 tag: 'input', type: 'hidden', name: 'debug', value: ''
71 success : function(resp, opts) {
72 _this.init(resp.responseXML);
76 GnumericWriter.postform = false;
77 GnumericWriter.postxml = false;
78 GnumericWriter.postformat = false;
79 GnumericWriter.postdebug = false;
81 Roo.apply(GnumericWriter.prototype, {
86 sheetName : 'unnamed',
87 filename : 'test.gnumeric',
88 init : function(resp) {
90 alert('Failed to load ' + this.template);
95 //alert(this.gnumeric);
97 var g = this.gnumeric;
98 this.worksheet = Roo.isIE ? g.getElementsByTagName('gmr:Sheet')[0] : g.getElementsByTagNameNS('*','Sheet')[0];
99 this.workbook = Roo.isIE ? g.getElementsByTagName('gmr:Workbook')[0] : this.gnumeric.getElementsByTagNameNS('*','Workbook')[0];
100 this.worksheet_cells = Roo.isIE ? g.getElementsByTagName('gmr:Cells')[0] : this.worksheet.getElementsByTagNameNS('*','Cells')[0];
102 this.widths = new Array();
105 // set some defaults....
106 this.setSheetName(this.sheetName);
107 if (!this.afterInit) {
108 alert('GnumericWriter: no afterInit defined');
111 if (false === this.afterInit(this)) {
114 this.writeImages(); // which in turn posts the form..
123 return this.gnumeric.xml;
125 var ser = new XMLSerializer();
126 return ser.serializeToString(this.gnumeric);
129 postForm : function () // post a form wit the contents..?
131 var form = GnumericWriter.postform.dom;
132 //alert(form.action);
134 alert('GnumericWriter: form not loaded yet?');
137 GnumericWriter.postxml.dom.setAttribute('value', this.toXML());
138 GnumericWriter.postdebug.dom.setAttribute('value', this.debug);
139 GnumericWriter.postformat.dom.setAttribute('value', this.format);
143 form.setAttribute('action', this.targetURL + '/' + this.filename);
144 //alert( form.getAttribute('action'));
151 setFileName : function(name)
153 this.filename = name;
156 setSheetName: function(name)
158 // this doesnt work yet!!!
159 //this.worksheet.setAttribute('name',name);
164 setColumnWidth: function(col, width)
166 // <gmr:Cols DefaultSizePts="48">
167 // <gmr:ColInfo No="1" Unit="67.5" MarginA="2" MarginB="2" HardSize="1"/>
169 var g = this.gnumeric;
170 var cols = Roo.isIE ? g.getElementsByTagName('gmr:Cols')[0] : this.gnumeric.getElementsByTagNameNS('*','Cols')[0];
171 var ri = this.gnumeric.createElement('gmr:ColInfo');
172 ri.setAttribute('No', col);
173 ri.setAttribute('Unit', width);
174 ri.setAttribute('MarginA', 2);
175 ri.setAttribute('MarginB', 2);
176 ri.setAttribute('HardSize', 1);
177 cols.appendChild(ri);
178 this.widths[col+0] = width+0;
181 setRowHeight: function(row,height)
183 //<gmr:Rows DefaultSizePts="12.75">
184 // <gmr:RowInfo No="2" Unit="38.25" MarginA="0" MarginB="0" HardSize="1"/>
186 var g = this.gnumeric;
187 var rows = Roo.isIE ? g.getElementsByTagName('gmr:Rows')[0] : this.gnumeric.getElementsByTagNameNS('*','Rows')[0];
188 var ri = this.gnumeric.createElement('gmr:RowInfo');
189 ri.setAttribute('No', row);
190 ri.setAttribute('Unit', height);
191 ri.setAttribute('MarginA', 0);
192 ri.setAttribute('MarginB', 0);
193 ri.setAttribute('HardSize', 1);
194 rows.appendChild(ri);
198 writeString: function (x,y,string)
201 var cell = this.gnumeric.createElement('gmr:Cell');
202 cell.setAttribute('Row',y);
203 cell.setAttribute('Col',x);
204 cell.setAttribute('ValueType',60); // a string.
206 var cell_value = this.gnumeric.createTextNode(string);
207 cell.appendChild(cell_value);
208 this.worksheet_cells.appendChild(cell);
210 // not sure what this is for..
211 mergeRegion: function(col1,row1,col2,row2)
213 var cell = this.gnumeric.createElement('gmr:Merge');
214 var cell_value = this.gnumeric.createTextNode(
215 this.colRowToName(row1,col1) + ':' + this.colRowToName(row2,col2)
217 cell.appendChild(cell_value);
219 //var merges = this.gnumeric.getElementsByTagNameNS('*','MergedRegions');
220 var g = this.gnumeric;
222 this.merges = this.gnumeric.createElement('gmr:MergedRegions');
223 var sl = Roo.isIE ? g.getElementsByTagName('gmr:SheetLayout')[0] : this.gnumeric.getElementsByTagNameNS('*','SheetLayout')[0];
225 this.worksheet.insertBefore(this.merges,sl);
227 this.merges.appendChild(cell);
232 // -- you may want to load with all the data.. if paged...
233 // grid = grid object
234 // row = grid.getSelectionModel.getSelected()
236 // row = grid.getDataSource().getRange(0, grid.getDataSource().getCount());
241 importGrid: function(cfgIn)
249 colModel : cfgIn.grid.getColumnModel().config
251 Roo.apply(cfg, cfgIn);
255 alert('no grid selected.');
259 var _ds = cfg.grid.getDataSource();
261 // get the grid column model.. - and use for headings..
263 Roo.each(cfg.colModel, function(c,col) {
264 _this.setColumnWidth(col,c.width);
265 //this.setRowHeight(0, 0 + col.getAttribute('xls:height'));
266 var cell = _this.gnumeric.createElement('gmr:Cell');
267 cell.setAttribute('Row',0);
268 cell.setAttribute('Col',col);
269 cell.setAttribute('ValueType',60); // string!!!!
270 var cell_value = _this.gnumeric.createTextNode(c.header);
271 cell.appendChild(cell_value);
272 _this.worksheet_cells.appendChild(cell);
276 Roo.each(cfg.rows, function(r,rownum) {
278 Roo.each(cfg.colModel, function(c,col) {
279 var val = r.get(c.dataIndex);
280 if (typeof(val) == 'undefined') {
284 var cell = _this.gnumeric.createElement('gmr:Cell');
285 cell.setAttribute('Row',rownum+1);
286 cell.setAttribute('Col',col);
289 // then - just use the datasource
290 //cell.setAttribute('ValueType',60); // string!!!!
291 if (false === c.gRenderer(_this, val, r, cell, rownum+1, col)) {
294 //cell.appendChild(cell_value);
295 _this.worksheet_cells.appendChild(cell);
296 var lb = _this.gnumeric.createTextNode('\n');
297 _this.worksheet_cells.appendChild(lb);
300 // otherwise use the details..
304 var cell_cfg = _ds.getAt(0).fields.get(c.dataIndex);
305 switch(cell_cfg.type) {
307 cell.setAttribute('ValueType',30);
310 cell.setAttribute('ValueType',40);
313 cell.setAttribute('ValueType',30);
314 cell.setAttribute('ValueFormat','d/mmm/yyyy');
315 val = '' + Math.round((val.getTime() -
316 Date.UTC(1899,11,30)) / (24 * 60 * 60 * 1000));
319 cell.setAttribute('ValueType',60);
323 var cell_value = _this.gnumeric.createTextNode(val);
324 cell.appendChild(cell_value);
325 _this.worksheet_cells.appendChild(cell);
326 var lb = _this.gnumeric.createTextNode('\n');
327 _this.worksheet_cells.appendChild(lb);
338 newStyle: function(cfg)
340 return new GnumericWriter.Style(this, cfg);
345 colRowToName: function(row,col)
347 // we dont support > 26 cols yet!
349 return String.fromCharCode(65+col) + (row + 1);
353 // { row: 0, col: 0, url: xxx, data: xxx, width: xxx, height: yyy }
357 writeImages: function()
359 this.images = this.images || [];
360 var ar = this.images;
365 Roo.MessageBox.show({
366 title: 'Please wait...",
367 msg: "Adding Images...",
375 var wis = function () {
376 if (i == ar.length) {
377 Roo.MessageBox.hide();
381 Roo.MessageBox.updateProgress(
382 (i+1)/ar.length, 'Adding Image ' + (i+1) + ' of ' + ar.length
391 success : function(resp, opts) {
392 c.data = resp.responseText;
398 // error condition!?!?
413 addImage: function(cfg) {
414 this.images = this.images || [];
415 this.images.push(cfg);
418 writeImage: function(cfg)
421 // our default height width is 50/50 ?!
422 //console.log('w='+width+',height='+height);
424 this.setRowHeight(cfg.row,cfg.height);
425 var g = this.gnumeric;
426 var objs = Roo.isIE ? g.getElementsByTagName('gmr:Objects')[0] : this.gnumeric.getElementsByTagNameNS('*','Objects')[0];
427 var soi = this.gnumeric.createElement('gmr:SheetObjectImage');
429 //<gmr:SheetObjectImage
430 // ObjectBound="A3:J8"
431 // ObjectOffset="0.375 0.882 0.391 0.294"
432 // ObjectAnchorType="16 16 16 16"
434 // crop-top="0.000000"
435 // crop-bottom="0.000000"
436 // crop-left="0.000000"
437 // crop-right="0.000000">
440 //alert(gnumeric_colRowToName(row,col));
442 // this is where we really have fun!!!...
443 // since our design currently assumes the height is enough to fit
444 // stuff in, we only really need to work out how wide it has to be..
446 // note we should probably use centralized calcs if it fits in the first cell!
448 // step 1 - work out how many columns it will span..
449 // lets hope the spreadsheet is big enought..
450 var colwidth = this.widths[cfg.col];
452 //for (var endcoll=cfg.col;endcol <100; endcol++) {
453 // if (!this.widths[endcol]) {
454 // this.widths[endcol] = 100; // eak fudge
456 // colwidth += this.widths[endcol];
457 // if (colwidth > cfg.width) {
462 soi.setAttribute('ObjectBound',
463 this.colRowToName(cfg.row,cfg.col)
465 //gnumeric_colRowToName(row,col) + ':' + gnumeric_colRowToName(row+1,col+1));
466 //this.colRowToName(cfg.row,cfg.col) + ':' + this.colRowToName(cfg.row,endcol));
468 var ww = 0.01; // offset a bit...
471 // cfg/widths == % of cell taken up..
472 // 1/1 = all of image.. so we should say '1'
473 // 50/100 = 50% -> say '0.5'
474 // 1/100 => 1% say 0.01 ?
476 var ww2 = (cfg.width *0.1/ this.widths[cfg.col]*0.1);
479 var offset_str = ww + ' ' + hh + ' ' + ww2 + ' '+hh2;
480 //console.log(offset_str );
482 soi.setAttribute('ObjectOffset', offset_str);
483 soi.setAttribute('ObjectAnchorType','16 16 16 16');
484 soi.setAttribute('Direction','17');
485 soi.setAttribute('crop-top','0.000000');
486 soi.setAttribute('crop-bottom','0.000000');
487 soi.setAttribute('crop-left','0.000000');
488 soi.setAttribute('crop-right','0.000000');
489 // <Content image-type="jpeg" size-bytes="3900">...... < / Content>
490 var content = this.gnumeric.createElement('Content');
491 content.setAttribute('image-type','jpeg');
493 content.setAttribute('size-bytes', cfg.data.length);
495 var body = this.gnumeric.createTextNode(cfg.data) ;
496 content.appendChild(body);
497 soi.appendChild(content);
498 objs.appendChild(soi);
500 //< /gmr:SheetObjectImage>
506 cleanHtml: function(str)
509 ret = ret.replace(/ /g,'.');
510 ret = ret.replace(/\n/g,'.');
511 ret = ret.replace(/\r/g,'.');
513 while (-1 != (i = ret.indexOf(unescape('%A0')))) {
514 ret = ret.substring(0,i) + ' ' + ret.substring(i+1,str.length);
523 GnumericWriter.Style = function (gnumeric,cfg)
527 this.gnumeric = gnumeric.gnumeric;
531 this.WrapText = 0; // set to 1 for wrapping...
532 this.ShrinkToFit = 0;
534 this.Shade = 0; // set to 1 for fill?
539 this.Back = 'FFFF:FFFF:FFFF';
540 this.PatternColor = '0:0:0';
541 this.Format = 'General';
542 // dubious - font as it's own objecT???
546 this.FontUnderline = 0;
547 this.FontStrikeThrough = 0;
548 this.FontName = 'Sans'; // or .. ."Courier New"
550 this.Style = 0; // set to 1 to set all styles...
551 this.StyleColor = '';
554 this.TopStyleColor = '';
555 this.BottomStyle = 0;
556 this.BottomStyleColor = '';
558 this.LeftStyleColor = '';
560 this.RightStyleColor = '';
561 Roo.apply(this, cfg);
568 Roo.apply( GnumericWriter.Style.prototype, {
571 toString : function()
576 if ((t[i]+'').match(/function/)) {
579 ret += i + '=' + t[i] + '\n';
585 rgbToHex : function(str)
587 str = str.replace(/[^0-9,]+/g,'');
589 var bits = str.split(',');
590 return '' + (bits[0]*256).toString(16) +
592 ((bits.length > 2) ? (bits[1]*256).toString(16) : '00') +
594 ((bits.length > 2) ? (bits[2]*256).toString(16) : '00');
598 debugNode : function(node)
600 var s = document.defaultView.getComputedStyle(node, '');
605 if ((s[i] + '').match(/function/)) { continue; }
606 str += i + '=' + s[i] + '<br>';
608 document.write('xls:debug = Note, outputing this, will skew the results \n' + str);
612 setFrom : function(node)
615 var style = document.defaultView.getComputedStyle(node, '');
619 var sides = new Array( 'Left' , 'Right', 'Top' , 'Bottom' );
620 for (var i in sides) {
623 if (style['border'+side+'Style'] == 'solid') {
624 this[side+'Style'] = 1;
625 this[side+'StyleColor'] = this.rgbToHex(style['border'+side+'Color']);
631 switch(style.textAlign) {
646 if (style.verticalAlign != 'middle') {
651 if ((style.backgroundColor != 'rgb(255, 255, 255)') &&
652 style.backgroundColor.match(/rgb/))
654 this.Back = this.rgbToHex(style.backgroundColor);
655 this.Shade = 1; // set to 1 for fill?
658 if ((style.color != 'rgb(0, 0, 0)') &&
659 style.color.match(/rgb/))
661 this.Fore = this.rgbToHex(style.color);
665 // font stuff.. - only valid if you specify a specific font!
666 if (node.getAttribute('xls:debug')) {
667 alert( style['fontFamily'] + ' : ' + ( (style.fontFamily + '').match(/xls/) ? 'isxls' : 'not xls'));
669 if (style.fontFamily.match(/xls/)) {
671 this.FontUnit = style.fontSize.replace(/[^0-9.]+/g,'');
672 this.FontBold = (style.fontWeight == 'bold') ? 1 : 0;
673 this.FontItalic = (style.fontStyle == 'italic') ? 1 : 0;
674 //this.FontUnderline = 0;
675 //this.FontStrikeThrough = 0;
676 this.FontName = style.fontFamily.split(',')[0];
684 if (node.getAttribute('xls:'+i)) {
685 this[i] = node.getAttribute('xls:'+i);
689 if (ret && this.Style > 0) {
690 this.TopStyle = this.Style;
691 this.TopStyleColor = this.StyleColor;
692 this.BottomStyle = this.Style;
693 this.BottomStyleColor = this.StyleColor;
694 this.LeftStyle = this.Style;
695 this.LeftStyleColor = this.StyleColor;
696 this.RightStyle = this.Style;
697 this.RightStyleColor = this.StyleColor;
699 if (node.getAttribute('xls:debug')) {
700 alert(node.innerHTML + ':' + ret + '\n' + this.toString());
702 if (node.getAttribute('xls:debug')) {
703 //alert(style.fontFamily);
704 GnumericWriter_Style_debugNode(node);
710 add : function(startCol, startRow, endCol, endRow)
714 // <gmr:StyleRegion startCol="4" startRow="13" endCol="4" endRow="13">
715 // <gmr:Style HAlign="1" VAlign="1" WrapText="1" ShrinkToFit="0" Rotation="0" Shade="1" Indent="0" Locked="0" Hidden="0" Fore="FFFF:0:0" Back="CCCC:FFFF:FFFF" PatternColor="0:0:0" Format="General">
716 // <gmr:Font Unit="8" Bold="0" Italic="1" Underline="0" StrikeThrough="0">Courier New</gmr:Font>
718 // <gmr:Top Style="0"/>
719 // <gmr:Bottom Style="1" Color="0:0:0"/>
720 // <gmr:Left Style="0"/>
721 // <gmr:Right Style="1" Color="0:0:0"/>
722 // <gmr:Diagonal Style="0"/>
723 // <gmr:Rev-Diagonal Style="0"/>
724 // </gmr:StyleBorder>
728 sr = this.gnumeric.createElement('gmr:StyleRegion');
729 s = this.gnumeric.createElement('gmr:Style');
730 f = this.gnumeric.createElement('gmr:Font');
731 sb = this.gnumeric.createElement('gmr:StyleBorder');
732 sbt = this.gnumeric.createElement('gmr:Top');
733 sbb = this.gnumeric.createElement('gmr:Bottom');
734 sbl = this.gnumeric.createElement('gmr:Left');
735 sbr = this.gnumeric.createElement('gmr:Right');
736 sbd = this.gnumeric.createElement('gmr:Diagonal');
737 sbrd = this.gnumeric.createElement('gmr:Rev-Diagonal');
738 fn = this.gnumeric.createTextNode(this.FontName);
741 sr.setAttribute('startCol',startCol);
742 sr.setAttribute('startRow',startRow);
743 sr.setAttribute('endCol',endCol);
744 sr.setAttribute('endRow',endRow);
747 s.setAttribute('HAlign',this.HAlign);
748 s.setAttribute('VAlign',this.VAlign);
749 s.setAttribute('WrapText',this.WrapText);
750 s.setAttribute('ShrinkToFit',this.ShrinkToFit);
751 s.setAttribute('Rotation',this.Rotation);
752 s.setAttribute('Shade',this.Shade);
753 s.setAttribute('Indent',this.Indent);
754 s.setAttribute('Locked',this.Locked);
755 s.setAttribute('Hidden',this.Hidden);
756 s.setAttribute('Fore',this.Fore);
757 s.setAttribute('Back',this.Back);
758 s.setAttribute('PatternColor',this.PatternColor);
759 s.setAttribute('Format',this.Format);
762 f.setAttribute('Unit', this.FontUnit );
763 f.setAttribute('Bold', this.FontBold);
764 f.setAttribute('Italic', this.FontItalic);
765 f.setAttribute('Underline', this.FontUnderline);
766 f.setAttribute('StrikeThrough', this.FontStrikeThrough);
772 sbt.setAttribute('Style', this.TopStyle);
773 if (this.TopStyle > 0) sbt.setAttribute('Color', this.TopStyleColor);
775 sbb.setAttribute('Style', this.BottomStyle);
776 if (this.BottomStyle > 0) sbb.setAttribute('Color', this.BottomStyleColor);
778 sbl.setAttribute('Style', this.LeftStyle);
779 if (this.LeftStyle > 0) sbl.setAttribute('Color', this.LeftStyleColor);
781 sbr.setAttribute('Style', this.RightStyle);
782 if (this.RightStyle > 0) sbr.setAttribute('Color', this.RightStyleColor);
784 sbd.setAttribute('Style', 0);
785 sbrd.setAttribute('Style', 0);
787 // now add them all together!!!
793 sb.appendChild(sbrd);
799 // .. this will only hit the first occurance!!!
800 var g = this.gnumeric;
802 var styles = Roo.isIE ? g.getElementsByTagName('gmr:Styles')[0] : this.gnumeric.getElementsByTagNameNS('*','Styles')[0];
804 // and finally to the document..
805 styles.appendChild(sr);