Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit 338208c

Browse files
rombertrombert
committedSep 4, 2011
Allow more control over the excel api's output
The following changes have been made: - allow declaration of Spreadsheet styles to control the appearance of rows and cells - allow setting of attributes on individual cells The actual output of the excel export is unchanged. The API changes are completely backwards compatible. Fixes #13290: Allow more control over excel export format
·
release-1.2.20release-1.2.8
1 parent 0a636b3 commit 338208c

File tree

1 file changed

+280
-10
lines changed

1 file changed

+280
-10
lines changed
 

‎core/excel_api.php

+280-10
Original file line numberDiff line numberDiff line change
@@ -26,16 +26,39 @@
2626
/**
2727
* A method that returns the header for an Excel Xml file.
2828
*
29-
* @param $p_worksheet_title The worksheet title.
29+
* @param string $p_worksheet_title The worksheet title.
30+
* @param array $p_styles An optional array of ExcelStyle entries . Parent entries must be placed before child entries
3031
* @returns the header Xml.
3132
*/
32-
function excel_get_header( $p_worksheet_title ) {
33+
function excel_get_header( $p_worksheet_title, $p_styles = array() ) {
3334
$p_worksheet_title = preg_replace( '/[\/:*?"<>|]/', '', $p_worksheet_title );
3435
return "<?xml version=\"1.0\" encoding=\"UTF-8\"?><?mso-application progid=\"Excel.Sheet\"?>
3536
<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
3637
xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
3738
xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
38-
xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n<Worksheet ss:Name=\"" . urlencode( $p_worksheet_title ) . "\">\n<Table>\n<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n";
39+
xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n ". excel_get_styles( $p_styles ). "<Worksheet ss:Name=\"" . urlencode( $p_worksheet_title ) . "\">\n<Table>\n<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n";
40+
}
41+
42+
/**
43+
* Returns an XML string containing the <tt>ss:Styles</tt> entry, possibly empty
44+
*
45+
* @param array $p_styles an array of ExcelStyle entries
46+
* @return void|string
47+
*/
48+
function excel_get_styles( $p_styles ) {
49+
50+
if ( count ( $p_styles ) == 0 ) {
51+
return;
52+
}
53+
54+
$t_styles_string = '<ss:Styles>';
55+
56+
foreach ( $p_styles as $t_style ) {
57+
$t_styles_string .= $t_style->asXml();
58+
}
59+
$t_styles_string .= '</ss:Styles>';
60+
61+
return $t_styles_string;
3962
}
4063

4164
/**
@@ -56,10 +79,16 @@ function excel_format_column_title( $p_column_title ) {
5679

5780
/**
5881
* Generates the xml for the start of an Excel row.
82+
*
83+
* @param string $p_style_id The optional style id
5984
* @returns The Row tag.
6085
*/
61-
function excel_get_start_row() {
62-
return '<Row>';
86+
function excel_get_start_row( $p_style_id = '') {
87+
if ( $p_style_id != '' ) {
88+
return '<Row ss:StyleID="' . $p_style_id . '">';
89+
} else {
90+
return '<Row>';
91+
}
6392
}
6493

6594
/**
@@ -71,12 +100,13 @@ function excel_get_end_row() {
71100
}
72101

73102
/**
74-
* Gets an Xml Row that contains all column titles.
103+
* Gets an Xml Row that contains all column titles
104+
* @param string $p_style_id The optional style id.
75105
* @returns The xml row.
76106
*/
77-
function excel_get_titles_row() {
107+
function excel_get_titles_row( $p_style_id = '') {
78108
$t_columns = excel_get_columns();
79-
$t_ret = '<Row>';
109+
$t_ret = excel_get_start_row( $p_style_id );
80110

81111
foreach( $t_columns as $t_column ) {
82112
$t_custom_field = column_get_custom_field_name( $t_column );
@@ -119,9 +149,34 @@ function excel_prepare_string( $p_value ) {
119149
$t_type = is_numeric( $p_value ) ? 'Number' : 'String';
120150

121151
$t_value = str_replace( array ( '&', "\n", '<', '>'), array ( '&amp;', '&#10;', '&lt;', '&gt;' ), $p_value );
122-
$t_ret = "<Cell><Data ss:Type=\"$t_type\">" . $t_value . "</Data></Cell>\n";
152+
153+
return excel_get_cell( $t_value, $t_type );
154+
}
123155

124-
return $t_ret;
156+
/**
157+
* Returns an <tt>Cell</tt> as an XML string
158+
*
159+
* <p>All the parameters are assumed to be valid and escaped, as this function performs no
160+
* escaping of its own.</p>
161+
*
162+
* @param string $p_value
163+
* @param string $p_type
164+
* @param array $p_attributes An array where the keys are attribute names and values attribute
165+
* values for the <tt>Cell</tt> object
166+
* @return string
167+
*/
168+
function excel_get_cell( $p_value, $p_type, $p_attributes = array() ) {
169+
$t_ret = "<Cell ";
170+
171+
foreach ( $p_attributes as $t_attribute_name => $t_attribute_value ) {
172+
$t_ret .= $t_attribute_name. '="' . $t_attribute_value . '" ';
173+
}
174+
175+
$t_ret .= ">";
176+
177+
$t_ret .= "<Data ss:Type=\"$p_type\">" . $p_value . "</Data></Cell>\n";
178+
179+
return $t_ret;
125180
}
126181

127182
/**
@@ -426,3 +481,218 @@ function excel_format_custom_field( $p_issue_id, $p_project_id, $p_custom_field
426481
function excel_format_due_date( $p_due_date ) {
427482
return excel_prepare_string( date( config_get( 'short_date_format' ), $p_due_date ) );
428483
}
484+
485+
/**
486+
* The <tt>ExcelStyle</tt> class is able to render style information
487+
*
488+
* <p>For more information regarding the values taken by the parameters of this class' methods
489+
* please see <a href="http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx#odc_xmlss_ss:style">
490+
* the ss:Style documentation</a>.</p>
491+
*
492+
*/
493+
class ExcelStyle {
494+
495+
private $id;
496+
private $parent_id;
497+
498+
private $interior;
499+
private $font;
500+
private $border;
501+
502+
/**
503+
* @param string $p_id The unique style id
504+
* @param string $p_parent_id The parent style id
505+
*/
506+
function __construct( $p_id , $p_parent_id = '') {
507+
508+
$this->id = $p_id;
509+
$this->parent_id = $p_parent_id;
510+
}
511+
512+
function getId() {
513+
514+
return $this->id;
515+
}
516+
517+
/**
518+
* @param string $p_color the color in #rrggbb format or a named color
519+
* @param string $p_pattern
520+
*/
521+
function setBackgroundColor( $p_color, $p_pattern = 'Solid' ) {
522+
523+
if ( ! isset ( $this->interior ) ) {
524+
$this->interior = new Interior();
525+
}
526+
527+
$this->interior->color = $p_color;
528+
$this->interior->pattern = $p_pattern;
529+
}
530+
531+
/**
532+
*
533+
* @param int $p_bold 1 for bold, 0 for not bold
534+
* @param string $p_color the color in #rrggbb format or a named color
535+
* @param string $p_name the name of the font
536+
* @param int $p_italic 1 for italic, 0 for not italic
537+
*/
538+
539+
function setFont( $p_bold, $p_color = '', $p_name = '', $p_italic = -1 ) {
540+
541+
if ( ! isset ( $this->font ) ) {
542+
$this->font = new Font();
543+
}
544+
545+
if ( $p_bold != -1 ) {
546+
$this->font->bold = $p_bold;
547+
}
548+
if ( $p_color != '' ) {
549+
$this->font->color = $p_color;
550+
}
551+
if ( $p_name != '' ) {
552+
$this->font->fontName = $p_name;
553+
}
554+
if ( $p_italic != -1 ) {
555+
$this->font->italic = $p_italic;
556+
}
557+
}
558+
559+
560+
/**
561+
* Sets the border values for the style
562+
*
563+
* <p>The values are set for the following positions: Left, Top, Right, Bottom. There is no
564+
* support for setting individual values.</p>
565+
*
566+
* @param string $p_color the color in #rrggbb format or a named color
567+
* @param string $p_line_style None, Continuous, Dash, Dot, DashDot, DashDotDot, SlantDashDot, or Double
568+
* @param string $p_weight Thickness in points
569+
*/
570+
function setBorder( $p_color, $p_line_style = 'Continuous', $p_weight = 1) {
571+
572+
if ( ! isset ( $this->border ) ) {
573+
$this->border = new Border();
574+
}
575+
576+
if ( $p_color != '' ) {
577+
$this->border->color = $p_color;
578+
}
579+
580+
if ( $p_line_style != '' ) {
581+
$this->border->lineStyle = $p_line_style;
582+
}
583+
584+
if ( $p_weight != -1 ) {
585+
$this->border->weight = $p_weight;
586+
}
587+
}
588+
589+
function asXml() {
590+
591+
$xml = '<ss:Style ss:ID="' . $this->id.'" ss:Name="'.$this->id.'" ';
592+
if ( $this->parent_id != '' ) {
593+
$xml .= 'ss:Parent="' . $this->parent_id .'" ';
594+
}
595+
$xml .= '>';
596+
if ( $this->interior ) {
597+
$xml .= $this->interior->asXml();
598+
}
599+
if ( $this->font ) {
600+
$xml .= $this->font->asXml();
601+
}
602+
if ( $this->border ) {
603+
$xml .= $this->border->asXml();
604+
}
605+
$xml .= '</ss:Style>'."\n";
606+
607+
return $xml;
608+
}
609+
}
610+
611+
class Interior {
612+
613+
public $color;
614+
public $pattern;
615+
616+
function asXml() {
617+
618+
$xml = '<ss:Interior ';
619+
620+
if ( $this->color ) {
621+
$xml .= 'ss:Color="' . $this->color .'" ss:Pattern="'. $this->pattern . '" ';
622+
}
623+
624+
$xml .= '/>';
625+
626+
return $xml;
627+
}
628+
}
629+
630+
class Font {
631+
632+
public $bold;
633+
public $color;
634+
public $fontName;
635+
public $italic;
636+
637+
function asXml() {
638+
639+
$xml = '<ss:Font ';
640+
641+
if ( $this->bold ) {
642+
$xml .= 'ss:Bold="' . $this->bold .'" ';
643+
}
644+
645+
if ( $this->color ) {
646+
$xml .= 'ss:Color="' . $this->color .'" ';
647+
}
648+
649+
if ( $this->fontName) {
650+
$xml .= 'ss:FontName="' . $this->fontName .'" ';
651+
}
652+
653+
if ( $this->italic ) {
654+
$xml .= 'ss:Italic="' . $this->italic .'" ';
655+
}
656+
657+
$xml .= '/>';
658+
659+
return $xml;
660+
}
661+
}
662+
663+
class Border {
664+
665+
private $positions = array('Left', 'Top', 'Right', 'Bottom');
666+
667+
public $color;
668+
public $lineStyle;
669+
public $weight;
670+
671+
function asXml() {
672+
673+
$xml = '<ss:Borders>';
674+
675+
foreach ( $this->positions as $p_position ) {
676+
677+
$xml.= '<ss:Border ss:Position="' . $p_position .'" ';
678+
679+
if ( $this->lineStyle ) {
680+
$xml .= 'ss:LineStyle="' . $this->lineStyle .'" ';
681+
}
682+
683+
if ( $this->color ) {
684+
$xml .= 'ss:Color="' . $this->color .'" ';
685+
}
686+
687+
if ( $this->weight) {
688+
$xml .= 'ss:Weight="' . $this->weight .'" ';
689+
}
690+
691+
$xml.= '/>';
692+
}
693+
694+
$xml .= '</ss:Borders>';
695+
696+
return $xml;
697+
}
698+
}

0 commit comments

Comments
 (0)
Please sign in to comment.