Jump to content

PHP codes to export to excel

xiaoxin22

By xiaoxin22
in PHP Coding Help

 Share

Recommended Posts

gclx Newbie

gclx

Posted
Posted

Easiest is to export as csv

<?php
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=myexcel.csv");
header("Pragma: no-cache");
header("Expires: 0");

echo 'Test1;Test2\n'; //split columns with ; and rows with \n
exit(0);
?>

Link to comment
Share on other sites
xiaoxin22 Member

xiaoxin22

Posted
  • Author
Posted

what is csv? can it be open with microsoft excel?

 

Easiest is to export as csv

<?php
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=myexcel.csv");
header("Pragma: no-cache");
header("Expires: 0");

echo 'Test1;Test2\n'; //split columns with ; and rows with \n
exit(0);
?>

Link to comment
Share on other sites
ScotDiddle Member

ScotDiddle

Posted
Posted

xiaoxin22,

 

This :  http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected  PEAR package is top-notch.

 

Here is the code I use to generate an Excel Spreadsheet using the PEAR code.

 

Should get you started in the right direction...

 

I'm not sure if excelColorsAssignments.php is part of the PEAR package or not, so I am including it for completeness...)

 

Scot L. Diddle, Richmond VA

 

 

 

    writeOutExcelSpreadsheet.php

 

 





<?php

/**
 *
 *   '../writeOutExcelSpreadsheet.php' writes out an MS Excel Spreadsheet of the in-coming cart...
 *
 * 		Called From: 'cart/purchase.php around lines 326 & 3028...
 *
 */

	require_once('includes/application_start.inc.php');  //  DB Connect, etc...

	require_once('includes/Writer.php');

require('includes/excelColorsAssignments.php');

$ssid = session_id();

$ExcelRows = array();

/**
 *
 *  Say Hello to the current customer...
 *
 */

$email = $_SESSION['email'];
$customer_num = $_SESSION['customer_num'];

$sql = 'SELECT * FROM logins WHERE customer_num = \'' . $customer_num. '\' AND email = \''. $email . '\'';

$result = run_query($sql);

if( $result !== false ) {
	$thisContact = $result[0]['contact'];
	$thisCompany = $result[0]['company_name'];
}

else {
	echo mysql_error();
	exit;
}


$ExcelFileName = $_SESSION['ExcelFileName'];

$deleteRC = unlink($ExcelFileName);

$touchFile = touch($ExcelFileName);

$workbook = new Spreadsheet_Excel_Writer($ExcelFileName);

$workbook->setVersion(; // Allow data to be > 256 bytes.
// See : http://forums.codewalkers.com/pear-packages-47/why-cell-size-limit-in-excel-writer-868437.html


$worksheet  =& $workbook->addWorksheet('Cart');
$worksheet2 =& $workbook->addWorksheet('Page 2');

// $worksheet->activate();
$worksheet->select();
$worksheet->setRow(0, 30); // 30 Pts High...
// $worksheet->writeNote(0, 0, 'This assumes g=16%');

/**
 *
 *  Format for the title row 1...
 *
 */

$format_title1 =& $workbook->addFormat(array('vAlign' => 'vcenter', 'hAlign' => 'hcenter'));
$format_title1->setBold(1);
$format_title1->setColor($seaGreen);
$format_title1->setFgColor($veryLightYellow);
$format_title1->setAlign('merge');
$format_title1->setBorderColor($seaGreen);
$format_title1->setRight(1);

/**
 *
 *  Format for the title row 1, left-most cell, add border...
 *
 */

$format_title1LeftBorder =& $workbook->addFormat(array('vAlign' => 'vcenter', 'hAlign' => 'hcenter'));
$format_title1LeftBorder->setBold(1);
$format_title1LeftBorder->setColor($seaGreen);
$format_title1LeftBorder->setFgColor($veryLightYellow);
$format_title1LeftBorder->setAlign('merge');
$format_title1LeftBorder->setLeft(1);

/**
 *
 *  Format for the title row 1, right-most cell, add border...
 *
 */

$format_title1Close =& $workbook->addFormat(array('vAlign' => 'vcenter', 'hAlign' => 'hcenter'));
$format_title1Close->setBold(1);
$format_title1Close->setColor($seaGreen);
$format_title1Close->setFgColor($veryLightYellow);
$format_title1Close->setAlign('merge');
$format_title1Close->setRight(1);

/**
 *
 *  Format for the title row 2, left-most cell, add border...
 *
 */

$format_title2 =& $workbook->addFormat();
$format_title2->setBold(1);
$format_title2->setColor($seaGreen);
$format_title2->setFgColor($white);
$format_title2->setAlign('center');
$format_title2->setBorderColor($seaGreen);
$format_title2->setTop(1);
$format_title2->setBottom(1);
$format_title2->setAlign('merge');

/**
 *
 *  Format for the title row 2, right-most cell, add border...
 *
 */

$format_title2Close =& $workbook->addFormat();
$format_title2Close->setBold(1);
$format_title2Close->setColor($seaGreen);
$format_title2Close->setFgColor($white);
$format_title2Close->setAlign('center');
$format_title2Close->setBorderColor($seaGreen);
$format_title2Close->setTop(1);
$format_title2Close->setBottom(1);
$format_title2Close->setAlign('merge');
$format_title2Close->setRight(1);

/**
 *
 *  Format for the title row 1, sheet 2...
 *
 */


$format_page2 =& $workbook->addFormat();
$format_page2->setBold(0);
$format_page2->setColor($seaGreen);
$format_page2->setFgColor($white);
$format_page2->setAlign('center');
$format_page2->setBorderColor($grey);
$format_page2->setTop(1);
$format_page2->setBottom(1);
$format_page2->setLeft(1);
$format_page2->setRight(1);

/**
 *
 *  Header Format
 *
 */

$header_format =& $workbook->addFormat();
$header_format->setBold(1);
$header_format->setColor($seaGreen);
$header_format->setFgColor($veryLightYellow);
$header_format->setAlign('center');
$header_format->setBorderColor($seaGreen);
$header_format->setTop(1);
$header_format->setBottom(1);
$header_format->setLeft(1);
$header_format->setRight(1);

$todaysDate = date('r');

$todaysDatePieces = explode(' ', $todaysDate);

$today = array_pop($todaysDatePieces); // Eliminate time-zone offset...
$today = array_pop($todaysDatePieces); // Eliminate time...

$today = implode(' ', $todaysDatePieces);



//
//         write(row,column)
//                |  |
//                |  |
$worksheet->write(0, 0, "", $format_title1LeftBorder);

switch($gridName) {

	case 'cartGrid' :
		$worksheet->write(0, 1, " Cart Contents  for :: $thisContact :: $thisCompany :: $today ", $format_title1);
	break;

	case 'cartHistoryGrid' :
		$worksheet->write(0, 1, "Contents  for :: $thisCompany :: $today ", $format_title1);
	break;

}

/**
 *
 *  Write out Row 0 of the Header
 *
 */

$worksheet->write(0, 2, "", $format_title1);
$worksheet->write(0, 3, "", $format_title1);
$worksheet->write(0, 4, "", $format_title1);
$worksheet->write(0, 5, "", $format_title1);
$worksheet->write(0, 6, "", $format_title1);
$worksheet->write(0, 7, "", $format_title1);

$text = NULL;

for ($c = 8; $c <= 11; $c++) {

	if ($c <= 11) {
		$worksheet->write(0, $c, "$text", $format_title1);
	}
	else {
		$worksheet->write(0, $c, "$text", $format_title1Close);
	}

}



$showCellIDX = FALSE;

if ($showCellIDX) {

	for ($c = 0; $c <= 11; $c++) {

		if ($c < 11) {
			$worksheet->write(1, $c, "$c", $format_title2);
		}
		else {
			$worksheet->write(1, $c, "$c", $format_title2Close);
		}
	}

}
else {

	for ($c = 0; $c <= 11; $c++) {

		if ($c < 11) {
			$worksheet->write(1, $c, "$text", $format_title2);
		}
		else {
			$worksheet->write(1, $c, "$text", $format_title2Close);
		}

	}

}

/**
 *
 *  Create a "blank" second page for the user...
 *
 */

for ($c = 0; $c <= 11; $c++) {
	$worksheet2->write(1, $c, "$text", $format_page2);
}


$worksheet->write(1, 0, "", $format_title2);
$worksheet->write(1, 1, "", $format_title2);
$worksheet->write(1, 2, "", $format_title2);
$worksheet->write(1, 3, "", $format_title2);
$worksheet->write(1, 4, "", $format_title2);
$worksheet->write(1, 5, "", $format_title2);
$worksheet->write(1, 6, "", $format_title2);

/**
 *
 *  Set Column Widths...
 *
 */

//                     Start Column
//                        End Column
//                            Column Width
$worksheet->setColumn (0, 0,  27);  // Item Desc
$worksheet->setColumn (1, 1,  15);  // Color
$worksheet->setColumn (2, 3,  ;   // Size, Qty
$worksheet->setColumn (4, 4,  10);  // Price
$worksheet->setColumn (5, 5,  15);  // Sub-total
$worksheet->setColumn (6, 6, 20);	// Email
$worksheet->setColumn (7, 8, 12);   // Count Per Case
$worksheet->setColumn (9, 9, 18);   // Free Freight Eligible ?
$worksheet->setColumn (10, 11, 12); // Entry Order, Date Ordered.


/**
 *
 *  Freeze the first 3 columns...
 *
 */

//                     		  Number of rows to freeze
//                         		 Sheet Number
//                            		Un-freeze, starting with this row
//									   Sheet Number
$worksheet->freezePanes(array(3, 0, 3, 0));


/**
 *
 * HEADER Row Format
 *
 */

$formatExcelGreenBold =& $workbook->addFormat();
$formatExcelGreenBold->setBorderColor($seaGreen);
$formatExcelGreenBold->setTop(1);
$formatExcelGreenBold->setBottom(1);
$formatExcelGreenBold->setLeft(1);
$formatExcelGreenBold->setRight(1);
$formatExcelGreenBold->setFGColor($ExcelGreen);
$formatExcelGreenBold->setColor($black);
$formatExcelGreenBold->setAlign('left');
$formatExcelGreenBold->setBold(1);

$_SESSION['formatExcelGreenBold'] = $formatExcelGreenBold;

$formatExcelGreenBold =& $workbook->addFormat();
$formatExcelGreenBold->setBorderColor($seaGreen);
$formatExcelGreenBold->setTop(1);
$formatExcelGreenBold->setBottom(1);
$formatExcelGreenBold->setLeft(1);
$formatExcelGreenBold->setRight(1);
$formatExcelGreenBold->setFGColor($ExcelGreen);
$formatExcelGreenBold->setColor($black);
$formatExcelGreenBold->setAlign('left');
$formatExcelGreenBold->setBold(1);



// our green (overwriting color on index 12)
$workbook->setCustomColor(21, 000, 128, 128);
$format_our_green =& $workbook->addFormat();
$format_our_green->setFgColor(21);


/**
 *
 *  Total Line Format...
 *
 */

$formatExcelReversedGreenBold =& $workbook->addFormat();
$formatExcelReversedGreenBold->setBorderColor($seaGreen);
$formatExcelReversedGreenBold->setTop(1);
$formatExcelReversedGreenBold->setBottom(1);
$formatExcelReversedGreenBold->setLeft(1);
$formatExcelReversedGreenBold->setRight(1);
$formatExcelReversedGreenBold->setFGColor($ExcelGreen);
$formatExcelReversedGreenBold->setColor($seaGreen);
$formatExcelReversedGreenBold->setAlign('center');
$formatExcelReversedGreenBold->setBold(1);

/**
 *
 *  Total Line, Purchase Total Cell Content
 *
 */

$formatExcelReversedDecimalGreenBold =& $workbook->addFormat();
$formatExcelReversedDecimalGreenBold->setBorderColor($seaGreen);
$formatExcelReversedDecimalGreenBold->setTop(1);
$formatExcelReversedDecimalGreenBold->setBottom(1);
$formatExcelReversedDecimalGreenBold->setLeft(1);
$formatExcelReversedDecimalGreenBold->setRight(1);
$formatExcelReversedDecimalGreenBold->setFGColor($ExcelGreen);
$formatExcelReversedDecimalGreenBold->setColor($seaGreen);
$formatExcelReversedDecimalGreenBold->setAlign('left');
$formatExcelReversedDecimalGreenBold->setBold(1);
$formatExcelReversedDecimalGreenBold->setNumFormat('$ 0.00;');

/**
 *
 * Even Rows - Non Centered
 *
 */

$formatExcelGreen =& $workbook->addFormat();
$formatExcelGreen->setBorderColor($seaGreen);
$formatExcelGreen->setTop(1);
$formatExcelGreen->setBottom(1);
$formatExcelGreen->setLeft(1);
$formatExcelGreen->setRight(1);
$formatExcelGreen->setFGColor($ExcelGreen);
$formatExcelGreen->setColor($black);
$formatExcelGreen->setAlign('left');
$formatExcelGreen->setBold(0);

/**
 *
 *  Even Rows for cell content containing a decimal point...
 *
 */

$formatExcelGreenLeftDecimal =& $workbook->addFormat();
$formatExcelGreenLeftDecimal->setBorderColor($seaGreen);
$formatExcelGreenLeftDecimal->setTop(1);
$formatExcelGreenLeftDecimal->setBottom(1);
$formatExcelGreenLeftDecimal->setLeft(1);
$formatExcelGreenLeftDecimal->setRight(1);
$formatExcelGreenLeftDecimal->setFGColor($ExcelGreen);
$formatExcelGreenLeftDecimal->setColor($black);
$formatExcelGreenLeftDecimal->setBold(0);
$formatExcelGreenLeftDecimal->setAlign('left');
$formatExcelGreenLeftDecimal->setNumFormat('$ 0.00');

/**
 *
 * Even Rows - Centered
 *
 */

$formatExcelGreenCentered =& $workbook->addFormat();
$formatExcelGreenCentered->setBorderColor($seaGreen);
$formatExcelGreenCentered->setTop(1);
$formatExcelGreenCentered->setBottom(1);
$formatExcelGreenCentered->setLeft(1);
$formatExcelGreenCentered->setRight(1);
$formatExcelGreenCentered->setFGColor($ExcelGreen);
$formatExcelGreenCentered->setColor($black);
$formatExcelGreenCentered->setBold(0);
$formatExcelGreenCentered->setAlign('center');


/**
 *
 *  Footer row cells which are empty
 *
 */

$formatExcelYellowCentered =& $workbook->addFormat();
$formatExcelYellowCentered->setBorderColor($seaGreen);
$formatExcelYellowCentered->setTop(1);
$formatExcelYellowCentered->setBottom(1);
$formatExcelYellowCentered->setLeft(1);
$formatExcelYellowCentered->setRight(1);
$formatExcelYellowCentered->setFGColor($veryLightYellow);
$formatExcelYellowCentered->setColor($seaGreen);
$formatExcelYellowCentered->setBold(1);
$formatExcelYellowCentered->setAlign('center');

/**
 *
 * Odd Rows
 *
 */

$formatVLB =& $workbook->addFormat();
$formatVLB->setBorderColor($seaGreen);
$formatVLB->setTop(1);
$formatVLB->setBottom(1);
$formatVLB->setLeft(1);
$formatVLB->setRight(1);
$formatVLB->setFGColor($veryLightBlue);
$formatVLB->setAlign('left');
$formatVLB->setColor($black);

/**
 *
 *  Odd Rows for cell content containing a decimal point...
 *
 */

$formatVLBCenteredDecimal =& $workbook->addFormat();
$formatVLBCenteredDecimal->setBorderColor($seaGreen);
$formatVLBCenteredDecimal->setTop(1);
$formatVLBCenteredDecimal->setBottom(1);
$formatVLBCenteredDecimal->setLeft(1);
$formatVLBCenteredDecimal->setRight(1);
$formatVLBCenteredDecimal->setFGColor($veryLightBlue);
$formatVLBCenteredDecimal->setColor($black);
$formatVLBCenteredDecimal->setAlign('center');
$formatVLBCenteredDecimal->setNumFormat('$ 0.00');

/**
 *
 *  Odd Rows for cell content containing a decimal point...
 *
 */

$formatVLBCentered =& $workbook->addFormat();
$formatVLBCentered->setBorderColor($seaGreen);
$formatVLBCentered->setTop(1);
$formatVLBCentered->setBottom(1);
$formatVLBCentered->setLeft(1);
$formatVLBCentered->setRight(1);
$formatVLBCentered->setFGColor($veryLightBlue);
$formatVLBCentered->setColor($black);
$formatVLBCentered->setAlign('center');

/**
 *
 *  Left Align for decimal ( Price Related ) data...
 *
 */

$formatVLBLeftDecimal =& $workbook->addFormat();
$formatVLBLeftDecimal->setBorderColor($seaGreen);
$formatVLBLeftDecimal->setTop(1);
$formatVLBLeftDecimal->setBottom(1);
$formatVLBLeftDecimal->setLeft(1);
$formatVLBLeftDecimal->setRight(1);
$formatVLBLeftDecimal->setFGColor($veryLightBlue);
$formatVLBLeftDecimal->setColor($black);
$formatVLBLeftDecimal->setAlign('left');
$formatVLBLeftDecimal->setNumFormat('$ 0.00');

$rowNum = 1;

$headerCandidateOut = array();

// $ExcelRowsHeadings = $gridWidget->columnHeadings;
$ExcelRowsHeadings = $_SESSION['gridWidget']['columnHeadings'];

foreach ($ExcelRowsHeadings as $IDX => $headerCandidate) {
	$headerCandidateOut[$IDX] = "'" . $headerCandidate . "', ";
}

/**
 *
 *  Get the users data...
 *
 */

$ExcelRows[0] = implode(' ', $headerCandidateOut);
$ExcelRows[1] = "'', '', '', '', '', '', '', '', '', '', '', '', ''";

$heading = $ExcelRows;

$footers = array();

// $incomingFooters = $gridWidget->ExcelFooters; // These bad boys come from 'classes/buildgrid.class.php' around
$incomingFooters = $_SESSION['gridWidget']['ExcelFooters']; // These bad boys come from 'classes/buildgrid.class.php' around
											  				// around line 686.

$footers[0] = 	$incomingFooters[0];
$footers[1] = 	$incomingFooters[1];
$footers[2] = 	$incomingFooters[2];
$footers[3] = 	$incomingFooters[3];
$footers[4] = 	$incomingFooters[4];
$footers[5] = 	$incomingFooters[5];
$footers[6] = 	$incomingFooters[6];

$display = FALSE;

if ($display) {

    require_once($toodles . 'includes/debugFunctions.php');
    printArray($heading , '$heading ', __FILE__, __LINE__);

}

$display = FALSE;

if ($display) {

    require_once($toodles . 'includes/debugFunctions.php');

    $pieces1 = explode(',', $footers[0]);
    $pieces2 = explode(',', $footers[1]);
    $pieces3 = explode(',', $footers[2]);
    $pieces4 = explode(',', $footers[3]);
    $pieces5 = explode(',', $footers[4]);
    $pieces6 = explode(',', $footers[5]);
    $pieces7 = explode(',', $footers[6]);

    printArray($pieces1 , '$footers 0', __FILE__, __LINE__);
    printArray($pieces2 , '$footers 1', __FILE__, __LINE__);
    printArray($pieces3 , '$footers 2', __FILE__, __LINE__);
    printArray($pieces4 , '$footers 3', __FILE__, __LINE__);
    printArray($pieces5 , '$footers 4', __FILE__, __LINE__);
    printArray($pieces6 , '$footers 5', __FILE__, __LINE__);
    printArray($pieces7 , '$footers 6', __FILE__, __LINE__);

    exit;

}

$ExcelData =  $_SESSION['gridWidget']['ExcelData'];

$display = FALSE;

if ($display) {

    require_once($toodles . 'includes/debugFunctions.php');
    printArray($ExcelData , '$ExcelData ', __FILE__, __LINE__);

}


$ExcelRows =  array_merge($heading, $ExcelData);
$ExcelRows =  array_merge($ExcelRows, $footers);

$rowCount  = count($ExcelRows);

$footerLine6  = $rowCount;

$footerLine5 = $footerLine6 - 1;

$totalLine   = $footerLine5 - 1;

$footerLine3 = $totalLine - 1;

$footerLine2 = $footerLine3 - 1;

$footerLine1 = $footerLine2 - 1;

	$display = FALSE;

if ($display) {

    require_once($toodles . 'includes/debugFunctions.php');
    printArray($ExcelRows , '$this->ExcelData ', __FILE__, __LINE__);
    printArray($ExcelRows , '$this->ExcelData ', __FILE__, __LINE__);

	echo "\$rowCount: $rowCount" . "<br /><br/> \n";
	echo "\$last: $last" . "<br /><br/> \n";
	echo "\$totalLine: $totalLine" . "<br /><br/> \n";
	echo "\$footerLine0:".  $footers[0] . "<br /><br/> \n";
	echo "\$footerLine1:".  $footers[1] . "<br /><br/> \n";
	echo "\$footerLine2:".  $footers[2] . "<br /><br/> \n";
	echo "\$footerLine3:".  $footers[3] . "<br /><br/> \n";
	echo "\$totalLine:".  $footers[4] . "<br /><br/> \n";
	echo "\$footerLine5:".  $footers[5] . "<br /><br/> \n";
	echo "\$footerLine6:".  $footers[6] . "<br /><br/> \n";

	exit;
}


if(is_array($ExcelRows)) {

	foreach($ExcelRows as $rowIDX => $row) {

		$rowLength = strlen($row);

		if ($rowLength != 0) {

			if ($rowNum == 2) {
				$three = 4;
			}

			if ($rowNum == 0) {

				/**
				 *
				 *  Even Rows
				 *
				 */

				// Highlight the Column Titles...
				$format 		= $header_format;
				$formatCentered = $formatExcelGreenBold;
				$formatType     = 'formatExcelGreenBold';
			}
			else {
				$format = $formatExcelGreen;
				$formatType = 'formatExcelGreen';
			}

			$rowPieces = explode(",", $row);

			$modulo = $rowNum % 2;

			if ($formatType != 'formatExcelGreenBold') {

				if ($modulo === 0) {

					$format = $formatExcelGreen;
					$formatType = 'ExcelGreen';

				}
				else {

					$format = $formatVLB;
					$formatType = 'VLB';

				}

			}

			array_pop($rowPieces); // Kill the Wabbit...

			foreach($rowPieces as $contentIDX => $cellContent) {

				// Merge cells from row 0, col 0 to row 2, col 2
				// $worksheet->setMerge($footerLine1, $contentIDX, $footerLine1 + 1, $contentIDX);
				// No Workee...

				$cellContent = str_ireplace('"', '', $cellContent);
				$cellContent = str_ireplace("'", " ", $cellContent);
				$cellContent = trim($cellContent);

				if ($formatType != 'formatExcelGreenBold') {

					if ($formatType == 'ExcelGreen') {

						$hasDash = $cellContent[0]; // Sizes: Vis. --S--

						$isNumeric = is_numeric($cellContent);

						if ( ($hasDash == "-") || ($isNumeric) ) {
							$format  = $formatExcelGreenCentered;
						}
						else {
							$format  = $formatExcelGreen;
						}


					}
					elseif ($formatType == 'VLB') {

						$hasDash = $cellContent[0];  // Sizes: Vis. --S--

						$isNumeric = is_numeric($cellContent);

						if ( ($hasDash == "-") || ($isNumeric) ) {
							$format  = $formatVLBCentered;
						}
						else {
							$format  = $formatVLB;
						}

					}

				} // END if ($formatType != 'formatExcelGreenBold') {

				/**
				 *
				 *  Testing Hint:
				 *
				 * 		To see the effects of the various "If: statements,
				 *
				 * 			Change:
				 *
				 * 				 $worksheet->write($rowNum, $contentIDX, "$cellContent", $format);
				 *
				 * 			To:
				 *
				 * 				$worksheet->write($rowNum, $contentIDX, "Here I Am", $format);
				 *
				 *
				 * 		Then, open the generated Excel Spreadsheet to see which cells were formatted
				 * 		by the write statement... ( They will contain the text: 'Here I Am' .)
				 *
				 */

				switch ($contentIDX) { // (Cell number)

					case 2   :
					case 3   :
					case 6   :
					case 7   :
					case 8   :
					case 9   :
					case 10  :
					case 11  :

						if ($rowNum !== 0) { // Meaing we are not dealing with the title row...

							if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

								if ($rowNum == $totalLine) {

									/**
									 *
									 *  Footer Total row cells which are empty...
									 *
									 * 		( Note the word "Total" in the comment above... )
									 *
									 */

										/**
										 *
										 *  The folllowing works... '../ needed because the inserBitMap
										 *                          method is called from 'includes/Worksheet.php'
										 *
										 */

									// $photog = "../images/1x1pixel.bmp";
									// $worksheet->insertBitmap($rowNum, $contentIDX, $photog, $format);

									$format = $formatExcelReversedGreenBold;
									$worksheet->write($rowNum, $contentIDX, $cellContent, $format);

								}
								else {

									/**
									 *
									 *  Footer row cells which are empty...
									 *
									 */

									$format = $formatExcelYellowCentered;
									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								}

							} // END if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

							else { // Not a footer row

								if ($rowNum != 2) {

									/**
									 *
									 *  Empty cell content on  row 2 ?????.
									 *
									 */

									// $cellContent = 'Hi Mom';

									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								}
								else {

									/**
									 *
									 *  Row 3, the row that separates the header from the data base on valid
									 *          switch($cellContent) hit. ( Non-Defalult )
									 *
									 */

									$format = $formatExcelYellowCentered;
									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								}

							} // END else { // Not a footer row

						} // END if ($rowNum !== 0) {

					break;

					default :

						$hasDecimal = stristr($cellContent, ".");

						if ($hasDecimal) {

							$modulo = $rowNum % 2;

							if ($modulo === 0) {

								/**
								 *
								 *  Even numbered rows...
								 *
								 */

								$format = $formatExcelGreenLeftDecimal;
							}
							else {

								/**
								 *
								 *  Odd numbered rows...
								 *
								 */

								$format = $formatVLBLeftDecimal;
							}

							if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

								if ($rowNum == $totalLine) {

									if ($hasDecimal) {
										$format = $formatExcelReversedDecimalGreenBold;
									}
									else {
										$format = $formatExcelReversedGreenBold;
									}

								}

							} // END if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

							/**
							 *
							 *  Normal Cell Content which has decimal point, but is not in the footer total row...
							 *
							 */

							$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

						} // END if ($hasDecimal) {

						else {

							if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

								if ($rowNum == $totalLine) {

									/**
									 *
									 *  Default switch($contentIDX) criteria,
									 *
									 *  	Total Line normal cell content
									 *
									 */

									$format = $formatExcelReversedGreenBold;
										$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								}
								else {

									/**
									 *
									 *  Footer lines (excluding Total Line .) Default switch($contentIDX) criteria
									 *
									 * 		for blank cell content
									 *
									 */

									$format = $formatExcelYellowCentered;
									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								}

							} // END if ( ($rowNum == $footerLine1) ||  ($rowNum == $footerLine2) ||  ($rowNum == $footerLine3) ||  ($rowNum == $totalLine) ||  ($rowNum == $footerLine5) ||  ($rowNum == $footerLine6)  ) {

							else { // We are not dealing with a footer line

								if ($rowNum != 2) {

									/**
									 *
									 *   Default switch($contentIDX) criteria, Non-decimal, non-empty,
									 * 		normal cell content
									 *
									 */

									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);
								}
								else { // We are dealing with line 3...

									/**
									 *
									 *  Row 3, the row that separates the header from the data base on invalid
									 *          switch($cellContent) hit. ( Defalult )
									 *
									 */

									$format = $formatExcelYellowCentered;
									$worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

								} // END else { // We are dealing with line 3...

							} // END else { // We are not dealing with a footer line

						} // END ELSE if ($hasDecimal) { returned false, meaning this $cellContend
						  //                                             did not contain a decimal point

					break;

				} // END switch ($contentIDX) { // (Cell number)

			} // END foreach($rowPieces as $contentIDX => $cellContent) {

			// $worksheet->writeNote ( $rowNum , 10 , 'HiMom', $format);
			// No Workee... Why ?

			$rowNum++;

		} // END if ($rowLength != 0) {

	} // END foreach($ExcelRows as $rowIDX => $row) {

} // END if(is_array($ExcelRows)) {
else {

	if (empty($ExcelRows)) {
		$ExcelRows = '*** Empty ***';
	}

	if (!isset($ExcelRows)) {
		$ExcelRows = '*** Not Set ***';
	}

	if ($ExcelRows == NULL) {
		$ExcelRows = '*** NULL ***';
	}

	echo "<br /><ul><h3><font color=\"#205E75\"><font color=\"red\">Error</font> occured during generation of the Cart Excel File....<br /><br /><ul>The input file contained no data and was found to be: <font color=\"red\">$ExcelRows</font><br/ ></br ><ul>Please report this error to the IS Helpdesk.</ul></ul><ul></font>";
	exit;

} // END else ( // Somebody made a boo-boo... Boo Hoo.

// $rowNum++;

// $contentIDX = 1;

// $cellContent = 'MyNewLogo.bmp';

// $worksheet->write($rowNum, $contentIDX, "$cellContent", $format);

// fclose($ExcelHandle);

	// $sendRC =  $workbook->send($ExcelFileName); // Can't send as an included file
												   // if the parent file has written any output...

$closeRC = $workbook->close();

// unlink($ExcelFileName);

$stop = 'Here';

?>

 

 

excelColorAssignments.php

 


<?php

/**
 *
 *  'include/excelColors.php
 *
 * 		Set colors for PEAR Excel Writer.
 *
 */

$displayColors = FALSE; // If true, overwrite current sheet cell values with color chart.

/**
 *
 * Cell or Text Colors
 *
 */

// Very Light Grey
$veryLightGrey = 0;

// White
$white = 1;

// Red
$red = 2;

// Green
$green = 3;

// Blue
$blue = 4;

// Yellow
$yellow = 5;

// Magenta
$magenta = 6;

// Cyan
$cyan = 7;

// Black
$black = 8;

// Dark Green
$darkGreen = 12;

// Brick Red
$brickRed = 16;

// Very Dark Green
$veryDarkGreen = 17;

// Dark Blue
$darkBlue = 18;

// Olive
$olive = 19;

// Purple
$purple = 20;

// SeaGreen

$seaGreen = 21;

// Grey
$grey = 22;

// Dark Grey
$darkGrey = 23;

// Periwinkle
$periwinkle = 24;

// Peri-Purple
$periPurple = 25;

// Very Light Yellow
$veryLightYellow = 26;

// Very Light Blue
$veryLightBlue = 27;

// Dark Purple
$darkPurple = 28;

// Sugar Mellon
$sugarMellon = 29;

// Muted Blue
$mutedBlue = 30;

// Light-Light Purple
$lightLightPurple = 31;

// Very Dark Blue
$veryDarkBlue = 32;

// Medium-dark blue
$mediumDarkBlue = 39;

// Dark Sky Blue
$darkSkyBlue = 40;

// Light Sky Blue
$lightSkyBlue = 41;

// Excel Green
$ExcelGreen = 42;

// Muted Yellow
$mutedYellow = 43;

// MS Word Blue
$MSWordBlue = 44;

// Rose Pink
$rosePink = 45;

// Orchid
$orchid = 46;

// Grey
$grey = 47;

// Artic Blue
$articBlue = 48;

// Gulf Green
$gulfGreen = 49;

// Dull Lime
$dullLime = 50;

// Yellow-Orange
$yellowOrange = 51;

// Orange
$orange = 52;

// Dark Orange
$darkOrange = 53;

// Purple-grey
$purpleGrey = 54;

// Medium Grey
$mediumGrey = 55;

// Twlight Blue
$twilightBlue = 56;

// Dark Gulf Green
$darkGulfGreen = 57;

// Army Green
$armyGreen = 58;

// Brown-olive
$brownOlive = 59;

// Dark Apricot
$darkApricot = 60;

// Dark Rose Pink
$darkRosePink = 61;

// Dark Periwinkle
$darkPeriwinkle = 62;

// Very Dark Grey
$veryDarkGrey = 63;

?>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.