PageRenderTime 31ms CodeModel.GetById 17ms app.highlight 9ms RepoModel.GetById 1ms app.codeStats 0ms

/pudlExcel.php

https://github.com/darkain/pudl
PHP | 212 lines | 166 code | 44 blank | 2 comment | 32 complexity | e7a185529896bd95f69631ba2699166c MD5 | raw file
  1<?php
  2
  3function xmlheader() {
  4	return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
  5}
  6
  7
  8
  9function pudlExcel($result, $filename) {
 10	$zip = new ZipArchive();
 11
 12	@unlink($filename);
 13	if ($zip->open($filename, ZIPARCHIVE::CREATE) !== true) {
 14	    return 'Cannot open file: ' . $filename;
 15	}
 16
 17	$zip->addFromString('[Content_Types].xml', xmlheader().'<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/></Types>');
 18	$zip->addFromString('_rels/.rels', xmlheader().'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>');
 19	$zip->addFromString('docProps/app.xml', xmlheader().'<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector size="2" baseType="variant"><vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector size="1" baseType="lpstr"><vt:lpstr>Catalog</vt:lpstr></vt:vector></TitlesOfParts><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>12.0000</AppVersion></Properties>');
 20	$zip->addFromString('docProps/core.xml', xmlheader().'<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dc:creator>Darkain</dc:creator><cp:lastModifiedBy>Darkain</cp:lastModifiedBy><dcterms:created xsi:type="dcterms:W3CDTF">2013-04-11T16:42:30Z</dcterms:created><dcterms:modified xsi:type="dcterms:W3CDTF">2013-04-11T21:25:10Z</dcterms:modified></cp:coreProperties>');
 21	$zip->addFromString('xl/_rels/workbook.xml.rels', xmlheader().'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>');
 22	$zip->addFromString('xl/theme/theme1.xml', xmlheader().'<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme"><a:themeElements><a:clrScheme name="Office"><a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1><a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1><a:dk2><a:srgbClr val="1F497D"/></a:dk2><a:lt2><a:srgbClr val="EEECE1"/></a:lt2><a:accent1><a:srgbClr val="4F81BD"/></a:accent1><a:accent2><a:srgbClr val="C0504D"/></a:accent2><a:accent3><a:srgbClr val="9BBB59"/></a:accent3><a:accent4><a:srgbClr val="8064A2"/></a:accent4><a:accent5><a:srgbClr val="4BACC6"/></a:accent5><a:accent6><a:srgbClr val="F79646"/></a:accent6><a:hlink><a:srgbClr val="0000FF"/></a:hlink><a:folHlink><a:srgbClr val="800080"/></a:folHlink></a:clrScheme><a:fontScheme name="Office"><a:majorFont><a:latin typeface="Cambria"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="MS Pゴシック"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="宋体"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Times New Roman"/><a:font script="Hebr" typeface="Times New Roman"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="MoolBoran"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Times New Roman"/><a:font script="Uigh" typeface="Microsoft Uighur"/></a:majorFont><a:minorFont><a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan" typeface="MS Pゴシック"/><a:font script="Hang" typeface="맑은 고딕"/><a:font script="Hans" typeface="宋体"/><a:font script="Hant" typeface="新細明體"/><a:font script="Arab" typeface="Arial"/><a:font script="Hebr" typeface="Arial"/><a:font script="Thai" typeface="Tahoma"/><a:font script="Ethi" typeface="Nyala"/><a:font script="Beng" typeface="Vrinda"/><a:font script="Gujr" typeface="Shruti"/><a:font script="Khmr" typeface="DaunPenh"/><a:font script="Knda" typeface="Tunga"/><a:font script="Guru" typeface="Raavi"/><a:font script="Cans" typeface="Euphemia"/><a:font script="Cher" typeface="Plantagenet Cherokee"/><a:font script="Yiii" typeface="Microsoft Yi Baiti"/><a:font script="Tibt" typeface="Microsoft Himalaya"/><a:font script="Thaa" typeface="MV Boli"/><a:font script="Deva" typeface="Mangal"/><a:font script="Telu" typeface="Gautami"/><a:font script="Taml" typeface="Latha"/><a:font script="Syrc" typeface="Estrangelo Edessa"/><a:font script="Orya" typeface="Kalinga"/><a:font script="Mlym" typeface="Kartika"/><a:font script="Laoo" typeface="DokChampa"/><a:font script="Sinh" typeface="Iskoola Pota"/><a:font script="Mong" typeface="Mongolian Baiti"/><a:font script="Viet" typeface="Arial"/><a:font script="Uigh" typeface="Microsoft Uighur"/></a:minorFont></a:fontScheme><a:fmtScheme name="Office"><a:fillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="50000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="35000"><a:schemeClr val="phClr"><a:tint val="37000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="15000"/><a:satMod val="350000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000" scaled="1"/></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:shade val="51000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="80000"><a:schemeClr val="phClr"><a:shade val="93000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="94000"/><a:satMod val="135000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000" scaled="0"/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w="9525" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"><a:shade val="95000"/><a:satMod val="105000"/></a:schemeClr></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="25400" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="38100" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="38000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst><a:scene3d><a:camera prst="orthographicFront"><a:rot lat="0" lon="0" rev="0"/></a:camera><a:lightRig rig="threePt" dir="t"><a:rot lat="0" lon="0" rev="1200000"/></a:lightRig></a:scene3d><a:sp3d><a:bevelT w="63500" h="25400"/></a:sp3d></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="40000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="40000"><a:schemeClr val="phClr"><a:tint val="45000"/><a:shade val="99000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="20000"/><a:satMod val="255000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="-80000" r="50000" b="180000"/></a:path></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="80000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="30000"/><a:satMod val="200000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="50000" r="50000" b="50000"/></a:path></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/></a:theme>');
 23	$zip->addFromString('xl/styles.xml', xmlheader().'<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="1"><font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font></fonts><fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF00FF00"/><bgColor indexed="64"/></patternFill></fill></fills><borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="1"/></cellXfs><cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles><dxfs count="0"/><tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/><colors><mruColors><color rgb="FF00FF00"/></mruColors></colors></styleSheet>');
 24	$zip->addFromString('xl/workbook.xml', xmlheader().'<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4507"/><workbookPr defaultThemeVersion="124226"/><bookViews><workbookView xWindow="0" yWindow="120" windowWidth="28755" windowHeight="13095"/></bookViews><sheets><sheet name="Catalog" sheetId="1" r:id="rId1"/></sheets><calcPr calcId="125725"/><fileRecoveryPr repairLoad="1"/></workbook>');
 25
 26	ob_start();
 27	echo '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1:B6"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/><selection pane="bottomLeft" activeCell="A1" sqref="A1"/></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15"/><cols><col min="1" max="1" width="12" bestFit="1" customWidth="1"/></cols><sheetData>';
 28
 29	$x = 1;
 30	$total = 0;
 31	$strings = array();
 32	$colcount = $result->fields();
 33
 34
 35	//EXPORT HEADERS
 36	$y = 0;
 37	$fields = $result->listFields();
 38	echo '<row r="' . $x . '" spans="1:' . $colcount . '" s="1" customFormat="1">';
 39	foreach ($fields as $key => $val) {
 40		$name = $val->name;
 41		$total++;
 42		$index = array_search($name, $strings, true);
 43		if ($index === false) {
 44			$strings[] = $name;
 45			$index = count($strings) - 1;
 46		}
 47		if ($key < 26) {
 48			$cell = chr(65 + $key) . $x;
 49		} else {
 50			$cell = chr(64 + floor($key / 26)) . chr(65 + ($key % 26)) . $x;
 51		}		
 52		echo '<c r="' . $cell . '" s="1" t="s"><v>' . $index . '</v></c>';
 53	}
 54	echo '</row>';
 55
 56
 57	//EXPORT CELL DATA
 58	$x++;
 59	while ($data = $result->row(PUDL_NUMBER)) {
 60		echo '<row r="' . $x . '" spans="1:' . $colcount . '">';
 61		$y = 0;
 62		foreach ($data as $key => $val) {
 63			if ($key < 26) {
 64				$cell = chr(65 + $key) . $x;
 65			} else {
 66				$cell = chr(64 + floor($key / 26)) . chr(65 + ($key % 26)) . $x;
 67			}				
 68			if ($val==='0'  ||  $val==='0.0') {
 69				echo '<c r="' . $cell . '"><v>' . $val . '</v></c>';
 70			} else if (preg_match('/^[1-9][0-9]{0,12}\.?[0-9]{0,10}$/', $val)) {
 71				echo '<c r="' . $cell . '"><v>' . $val . '</v></c>';
 72			} else if (preg_match('/^[0-9]\.[0-9]{0,10}$/', $val)) {
 73				echo '<c r="' . $cell . '"><v>' . $val . '</v></c>';
 74			} else if (!empty($val)) {
 75				$total++;
 76				$index = array_search($val, $strings, true);
 77				if ($index === false) {
 78					$strings[] = $val;
 79					$index = count($strings) - 1;
 80				}
 81				echo '<c r="' . $cell . '" t="s"><v>' . $index . '</v></c>';
 82			}
 83		}
 84		echo '</row>';
 85		$x++;
 86	}
 87
 88	echo '</sheetData><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup orientation="portrait" r:id="rId1"/></worksheet>';
 89	$zip->addFromString('xl/worksheets/sheet1.xml', xmlheader() . ob_get_clean());
 90
 91	ob_start();
 92	echo '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ';
 93	echo 'count="' . $total . '" uniqueCount="' . count($strings) . '">';
 94	foreach ($strings as $key => $val) {
 95		echo '<si><t>' . htmlspecialchars($val) . '</t></si>';
 96	}
 97	echo '</sst>';
 98	$zip->addFromString('xl/sharedStrings.xml', xmlheader() . ob_get_clean());
 99
100	$zip->close();
101
102    header('Content-Description: File Transfer');
103    header('Content-Type: application/octet-stream');
104    header('Content-Disposition: attachment; filename='.basename($filename));
105    header('Content-Transfer-Encoding: binary');
106    header('Expires: 0');
107    header('Cache-Control: must-revalidate');
108    header('Pragma: public');
109    header('Content-Length: ' . filesize($filename));
110
111	readfile($filename);
112	@unlink($filename);
113}
114
115
116
117
118function pudlPHPExcel($list, $filename) {
119	global $site;
120	
121	if (is_a($list, 'pudlResult')) {
122		$list = $list->rows();
123	}
124	
125	if (!is_array($list)) {
126		return false;
127	}
128	
129
130	define('PHPEXCEL_ROOT', '');
131	
132	require_once('PHPExcel/Autoloader.php');
133	require_once('PHPExcel/PHPExcel.php');
134	require_once('PHPExcel/Writer/Excel2007.php');
135
136	$excel = new PHPExcel();
137	$excel->setActiveSheetIndex(0);
138	$excel->getProperties()->setCreator($site['title']);
139
140	$sheet = $excel->getActiveSheet();
141	$sheet->freezePane('A2');
142
143	$excel->getActiveSheet()->setTitle('Price Sheet');
144
145
146	$i = 0;
147	$item = reset($list);
148	foreach ($item as $key => &$val) {
149		if ($i < 26) {
150			$cell = chr(65 + $i) . '1';
151		} else {
152			$cell = chr(64 + floor($i / 26)) . chr(65 + ($i % 26)) . '1';
153		}
154		$sheet->SetCellValue($cell, $key);
155
156		$style = $sheet->getStyle($cell);
157		$style->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('0000FF00');
158		$style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
159		$style->getFont()->setBold(true);
160
161		$i++;
162	} unset($val);
163
164
165	$row = 2;	
166	foreach ($list as $item) {
167		$i = 0;
168		foreach ($item as $key => &$val) {
169			if ($i < 26) {
170				$cell = chr(65 + $i) . $row;
171			} else {
172				$cell = chr(64 + floor($i / 26)) . chr(65 + ($i % 26)) . $row;
173			}
174			$sheet->SetCellValue($cell, $val);
175			$style = $sheet->getStyle($cell);
176
177			if ($key === 'price'  ||  $key === 'list'  ||  $key === 'cost'  ||  $key === 'part_cost') {
178				$style->getNumberFormat()->setFormatCode('$ #,##0.00');
179			}
180			
181			if ($key === 'part_number'  ||  $key === 'part_description'  ||  $key === 'part_information'  ||  $key === 'part_inactive_text') {
182				$style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
183			}
184			
185			$i++;
186		}
187		$row++;
188	}
189
190
191	$item = reset($list);
192	$i = 0;
193	foreach ($item as $key => &$val) {
194		if ($i < 26) {
195			$cell = chr(65 + $i);
196		} else {
197			$cell = chr(64 + floor($i / 26)) . chr(65 + ($i % 26));
198		}
199		$sheet->getColumnDimension($cell)->setAutoSize(true);
200		$i++;
201	} unset($val);
202
203
204	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
205	header('Content-Disposition: attachment;filename="' . $filename . '"');
206	header('Cache-Control: max-age=0');
207
208	$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
209	$writer->save('php://output');
210	
211	return true;
212}