Skip to content

Commit 887d9e5

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-2.27.1release-1.3.0-beta.1
1 parent 03f1c7b commit 887d9e5

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
@@ -51,16 +51,39 @@
5151
/**
5252
* A method that returns the header for an Excel Xml file.
5353
*
54-
* @param $p_worksheet_title The worksheet title.
54+
* @param string $p_worksheet_title The worksheet title.
55+
* @param array $p_styles An optional array of ExcelStyle entries . Parent entries must be placed before child entries
5556
* @returns the header Xml.
5657
*/
57-
function excel_get_header( $p_worksheet_title ) {
58+
function excel_get_header( $p_worksheet_title, $p_styles = array() ) {
5859
$p_worksheet_title = preg_replace( '/[\/:*?"<>|]/', '', $p_worksheet_title );
5960
return "<?xml version=\"1.0\" encoding=\"UTF-8\"?><?mso-application progid=\"Excel.Sheet\"?>
6061
<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
6162
xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
6263
xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
63-
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";
64+
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";
65+
}
66+
67+
/**
68+
* Returns an XML string containing the <tt>ss:Styles</tt> entry, possibly empty
69+
*
70+
* @param array $p_styles an array of ExcelStyle entries
71+
* @return void|string
72+
*/
73+
function excel_get_styles( $p_styles ) {
74+
75+
if ( count ( $p_styles ) == 0 ) {
76+
return;
77+
}
78+
79+
$t_styles_string = '<ss:Styles>';
80+
81+
foreach ( $p_styles as $t_style ) {
82+
$t_styles_string .= $t_style->asXml();
83+
}
84+
$t_styles_string .= '</ss:Styles>';
85+
86+
return $t_styles_string;
6487
}
6588

6689
/**
@@ -81,10 +104,16 @@ function excel_format_column_title( $p_column_title ) {
81104

82105
/**
83106
* Generates the xml for the start of an Excel row.
107+
*
108+
* @param string $p_style_id The optional style id
84109
* @returns The Row tag.
85110
*/
86-
function excel_get_start_row() {
87-
return '<Row>';
111+
function excel_get_start_row( $p_style_id = '') {
112+
if ( $p_style_id != '' ) {
113+
return '<Row ss:StyleID="' . $p_style_id . '">';
114+
} else {
115+
return '<Row>';
116+
}
88117
}
89118

90119
/**
@@ -96,12 +125,13 @@ function excel_get_end_row() {
96125
}
97126

98127
/**
99-
* Gets an Xml Row that contains all column titles.
128+
* Gets an Xml Row that contains all column titles
129+
* @param string $p_style_id The optional style id.
100130
* @returns The xml row.
101131
*/
102-
function excel_get_titles_row() {
132+
function excel_get_titles_row( $p_style_id = '') {
103133
$t_columns = excel_get_columns();
104-
$t_ret = '<Row>';
134+
$t_ret = excel_get_start_row( $p_style_id );
105135

106136
foreach( $t_columns as $t_column ) {
107137
$t_custom_field = column_get_custom_field_name( $t_column );
@@ -144,9 +174,34 @@ function excel_prepare_string( $p_value ) {
144174
$t_type = is_numeric( $p_value ) ? 'Number' : 'String';
145175

146176
$t_value = str_replace( array ( '&', "\n", '<', '>'), array ( '&amp;', '&#10;', '&lt;', '&gt;' ), $p_value );
147-
$t_ret = "<Cell><Data ss:Type=\"$t_type\">" . $t_value . "</Data></Cell>\n";
177+
178+
return excel_get_cell( $t_value, $t_type );
179+
}
148180

149-
return $t_ret;
181+
/**
182+
* Returns an <tt>Cell</tt> as an XML string
183+
*
184+
* <p>All the parameters are assumed to be valid and escaped, as this function performs no
185+
* escaping of its own.</p>
186+
*
187+
* @param string $p_value
188+
* @param string $p_type
189+
* @param array $p_attributes An array where the keys are attribute names and values attribute
190+
* values for the <tt>Cell</tt> object
191+
* @return string
192+
*/
193+
function excel_get_cell( $p_value, $p_type, $p_attributes = array() ) {
194+
$t_ret = "<Cell ";
195+
196+
foreach ( $p_attributes as $t_attribute_name => $t_attribute_value ) {
197+
$t_ret .= $t_attribute_name. '="' . $t_attribute_value . '" ';
198+
}
199+
200+
$t_ret .= ">";
201+
202+
$t_ret .= "<Data ss:Type=\"$p_type\">" . $p_value . "</Data></Cell>\n";
203+
204+
return $t_ret;
150205
}
151206

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

0 commit comments

Comments
 (0)
Please sign in to comment.