function _sheetnode_phpexcel_export_cell_value_and_format in Sheetnode 7
Same name and namespace in other branches
- 5 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()
- 6 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()
- 7.2 modules/sheetnode_phpexcel/sheetnode_phpexcel.export.inc \_sheetnode_phpexcel_export_cell_value_and_format()
Helper function to export a cell.
1 call to _sheetnode_phpexcel_export_cell_value_and_format()
- _sheetnode_phpexcel_export_sheet in modules/
sheetnode_phpexcel/ sheetnode_phpexcel.export.inc - Helper function to export a single spreadsheet.
File
- modules/
sheetnode_phpexcel/ sheetnode_phpexcel.export.inc, line 350 - Export sheetnodes through PHPExcel to xls or xlsx spreadsheets.
Code
function _sheetnode_phpexcel_export_cell_value_and_format($c, $coord, $sc, $worksheet, $style) {
// Taken from socialcalc-3.js:SocialCalc.FormatValueForDisplay().
$cell = $worksheet
->getCell($coord);
$valuetype = empty($c['valuetype']) ? NULL : substr($c['valuetype'], 0, 1);
$valuesubtype = empty($c['valuetype']) ? NULL : substr($c['valuetype'], 1);
$displayvalue = @$c['datavalue'];
$valueformat = NULL;
// Error.
if (!empty($c['errors']) || $valuetype == 'e') {
$displayvalue = !empty($c['errors']) ? $c['errors'] : (!empty($valuesubtype) ? $valuesubtype : t('Unknown error'));
$cell
->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_ERROR);
}
else {
if ($valuetype == 't') {
// Get formatting.
$valueformat = !empty($c['textvalueformat']) ? $sc['valueformats'][$c['textvalueformat']] : (!empty($sc['defaulttextvalueformat']) ? $sc['valueformats'][$sc['defaulttextvalueformat']] : NULL);
if (in_array(strtolower($valueformat), array(
'auto',
'none',
'general',
))) {
$valueformat = NULL;
}
// Default text formatting based on cell type.
if (empty($valueformat)) {
switch ($valuesubtype) {
case 'h':
$valueformat = 'text-html';
break;
case 'w':
case 'r':
$valueformat = 'text-wiki';
break;
case 'l':
$valueformat = 'text-link';
break;
default:
$valueformat = 'text-plain';
break;
}
}
// Set the value.
if ($c['datatype'] == 'f') {
if (strpos($c['formula'], '!') !== FALSE) {
_sheetnode_phpexcel_export_reference($c, $cell, $worksheet);
}
else {
$cell
->setValue('=' . $c['formula']);
}
}
else {
switch ($valueformat) {
case 'text-plain':
$cell
->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_STRING);
break;
case 'text-html':
_sheetnode_phpexcel_load_library(SHEETNODE_PHPEXCEL_PDF_RENDERER);
module_load_include('inc', 'sheetnode_phpexcel', 'html2richtext');
$html2rtf = new Sheetnode_PHPExcel_HTML2RichText($displayvalue);
$rtf = $html2rtf
->convert($cell);
if ($rtf) {
$cell
->setValue($rtf);
}
else {
$stripped = preg_replace('/^\\s+|\\s+$/', '', strip_tags($displayvalue));
$cell
->setValueExplicit($stripped, PHPExcel_Cell_DataType::TYPE_STRING);
}
break;
case 'text-url':
case 'text-link':
$matches = array();
if (preg_match('/^(.*)<(.*)>$/', $displayvalue, $matches)) {
$text = trim($matches[1], "\r\n\t \"");
$url = $matches[2];
}
else {
$url = $displayvalue;
$parse_url = parse_url($url);
$text = $parse_url['host'] . $parse_url['path'];
}
$cell
->setValueExplicit($text, PHPExcel_Cell_DataType::TYPE_STRING);
$cell
->getHyperlink()
->setUrl($url);
break;
case 'text-wiki':
// TODO
break;
case 'text-image':
if (function_exists('curl_init')) {
// Download the file using cURL.
$ch = curl_init($displayvalue);
$filename = tempnam(file_directory_temp(), 'sheetnode_phpexcel_');
$fp = fopen($filename, 'wb');
$options = array(
CURLOPT_FILE => $fp,
CURLOPT_HEADER => 0,
CURLOPT_FOLLOWLOCATION => 1,
CURLOPT_TIMEOUT => 60,
);
curl_setopt_array($ch, $options);
curl_exec($ch);
curl_close($ch);
fclose($fp);
$image_info = image_get_info($filename);
if (!empty($image_info)) {
// Insert the image in the PHPExcel file.
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing
->setPath($filename);
$objDrawing
->setCoordinates($coord);
$objDrawing
->setWidth($image_info['width']);
$objDrawing
->setWorksheet($worksheet);
}
}
break;
case 'hidden':
// TODO
break;
case 'formula':
// TODO
break;
default:
if (substr($valueformat, 0, 12) == 'text-custom:') {
// TODO
}
else {
if (substr($valueformat, 0, 6) == 'custom') {
// TODO
}
else {
$cell
->setValue($displayvalue);
}
}
break;
}
}
}
else {
if ($valuetype == 'n') {
// Get formatting.
$valueformat = !empty($c['nontextvalueformat']) ? $sc['valueformats'][$c['nontextvalueformat']] : (!empty($sc['defaultnontextvalueformat']) ? $sc['valueformats'][$sc['defaultnontextvalueformat']] : NULL);
if (in_array(strtolower($valueformat), array(
'auto',
'none',
))) {
$valueformat = NULL;
}
// Default number formatting based on cell type.
if (empty($valueformat)) {
switch ($valuesubtype) {
case '%':
$valueformat = SocialCalc_Constants::$defaultFormatp;
break;
case '$':
$valueformat = SocialCalc_Constants::$defaultFormatc;
break;
case 'dt':
$valueformat = SocialCalc_Constants::$defaultFormatdt;
break;
case 'd':
$valueformat = SocialCalc_Constants::$defaultFormatd;
break;
case 't':
$valueformat = SocialCalc_Constants::$defaultFormatt;
break;
case 'l':
$valueformat = 'logical';
break;
default:
$valueformat = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
break;
}
}
// Set the value.
if ($c['datatype'] == 'f') {
if (strpos($c['formula'], '!') !== FALSE) {
_sheetnode_phpexcel_export_reference($c, $cell, $worksheet);
}
else {
$cell
->setValue('=' . $c['formula']);
}
}
else {
if ($valueformat == 'logical') {
$displayvalue = empty($displayvalue) ? SocialCalc_Constants::$defaultDisplayFALSE : SocialCalc_Constants::$defaultDisplayTRUE;
$cell
->setValue($displayvalue);
$valueformat = NULL;
}
else {
if ($valueformat == 'formula') {
$cell
->setValueExplicit('=' . $c['formula'], PHPExcel_Cell_DataType::TYPE_STRING);
}
else {
if ($valueformat == 'forcetext') {
$cell
->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_STRING);
}
else {
$cell
->setValueExplicit($displayvalue, PHPExcel_Cell_DataType::TYPE_NUMERIC);
}
}
}
}
// Set the formatting.
if ($valueformat == 'hidden') {
// TODO
}
else {
if (!empty($valueformat) && !empty($style)) {
$style
->getNumberFormat()
->setFormatCode($valueformat);
}
}
}
else {
$cell
->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL);
}
}
}
}