/**
* Bulk export: "spreadsheet" export: a simplified HTML export in which the date/time columns are split in two column: date AND time
*
* @copyright Copyright (C) 2015 Combodo SARL
* @license http://opensource.org/licenses/AGPL-3.0
*/
class SpreadsheetBulkExport extends TabularBulkExport
{
public function DisplayUsage(Page $oP)
{
$oP->p(" * spreadsheet format options:");
$oP->p(" *\tfields: (mandatory) the comma separated list of field codes to export (e.g: name,org_id,service_name...).");
$oP->p(" *\tno_localize: (optional) pass 1 to retrieve the raw (untranslated) values for enumerated fields. Default: 0.");
$oP->p(" *\tdate_format: the format to use when exporting date and time fields (default = the SQL format). e.g. 'Y-m-d H:i:s'");
$oP->p(" *\tformatted_text: set to 1 to formatted text fields with their HTML markup, 0 to remove formatting. Default is 1 (= formatted text)");
}
public function EnumFormParts()
{
return array_merge(parent::EnumFormParts(), array('spreadsheet_options' => array('no-localize') ,'interactive_fields_spreadsheet' => array('interactive_fields_spreadsheet')));
}
public function DisplayFormPart(WebPage $oP, $sPartId)
{
switch($sPartId)
{
case 'interactive_fields_spreadsheet':
$this->GetInteractiveFieldsWidget($oP, 'interactive_fields_spreadsheet');
break;
case 'spreadsheet_options':
$sChecked = (utils::ReadParam('no_localize', 0) == 1) ? ' checked ' : '';
$oP->add('');
$sJSTooltip = json_encode('
');
$oP->add_ready_script(
<<aStatusInfo['formatted_text'] = (bool)utils::ReadParam('formatted_text', 1, true);
$sDateFormatRadio = utils::ReadParam('spreadsheet_date_format_radio', '');
switch($sDateFormatRadio)
{
case 'default':
// Export from the UI => format = same as is the UI
$this->aStatusInfo['date_format'] = (string)AttributeDateTime::GetFormat();
break;
case 'custom':
// Custom format specified from the UI
$this->aStatusInfo['date_format'] = utils::ReadParam('date_format', (string)AttributeDateTime::GetFormat(), true, 'raw_data');
break;
default:
// Export from the command line (or scripted) => default format is SQL, as in previous versions of iTop, unless specified otherwise
$this->aStatusInfo['date_format'] = utils::ReadParam('date_format', (string)AttributeDateTime::GetSQLFormat(), true, 'raw_data');
}
}
protected function GetSampleData($oObj, $sAttCode)
{
if ($sAttCode != 'id')
{
$oAttDef = MetaModel::GetAttributeDef(get_class($oObj), $sAttCode);
if ($oAttDef instanceof AttributeDateTime) // AttributeDate is derived from AttributeDateTime
{
$sClass = (get_class($oAttDef) == 'AttributeDateTime') ? 'user-formatted-date-time' : 'user-formatted-date';
return '
";
break;
default:
$oAttDef = MetaModel::GetAttributeDef(get_class($oObj), $sAttCode);
$oFinalAttDef = $oAttDef->GetFinalAttDef();
if (get_class($oFinalAttDef) == 'AttributeDateTime')
{
// Split the date and time in two columns
$sDate = $oDateFormat->Format($oObj->Get($sAttCode));
$sTime = $oTimeFormat->Format($oObj->Get($sAttCode));
$sData .= "
";
}
else if($oAttDef instanceof AttributeCaseLog)
{
$rawValue = $oObj->Get($sAttCode);
$sField = str_replace("\n", " ", htmlentities($rawValue->__toString(), ENT_QUOTES, 'UTF-8'));
// Trick for Excel: treat the content as text even if it begins with an equal sign
$sData .= "
$sField
";
}
elseif ($oAttDef instanceof AttributeText)
{
if ($bFormattedText)
{
// Replace paragraphs (..., etc) by line breaks ( ) since Excel (pre-2016) splits the cells when there is a paragraph
$sField = static::HtmlToSpreadsheet($oObj->GetAsHTML($sAttCode));
}
else
{
// Convert to plain text
$sField = utils::HtmlToText($oObj->GetAsHTML($sAttCode));
}
$sData .= "
$sField
";
}
elseif ($oAttDef instanceof AttributeCustomFields)
{
// GetAsHTML returns a table that would not fit
$sField = utils::TextToHtml($oObj->GetEditValue($sAttCode));
$sData .= "
\n";
return $sData;
}
public function GetSupportedFormats()
{
return array('spreadsheet' => Dict::S('Core:BulkExport:SpreadsheetFormat'));
}
public function GetMimeType()
{
return 'text/html';
}
public function GetFileExtension()
{
return 'html';
}
/**
* Cleanup all markup displayed as line breaks (except tags) since this
* causes Excel (pre-2016) to generate extra lines in the table, thus breaking
* the tabular disposition of the export
* Note: Excel 2016 also refuses line breaks, so the only solution for this case is alas plain text
* @param string $sHtml The HTML to cleanup
* @return string The cleaned HTML
*/
public static function HtmlToSpreadsheet($sHtml)
{
if (trim(strip_tags($sHtml)) === '')
{
// Display this value as an empty cell in the table
return ' ';
}
// The tags listed here are a subset of the whitelist defined in HTMLDOMSanitizer
// Tags causing a visual "line break" in the displayed page (i.e. display: block) are to be replaced by a followed by a
// in order to preserve any inline style/attribute of the removed tag
$aTagsToReplace = array(
'pre', 'div', 'p', 'hr', 'center', 'h1', 'h2', 'h3', 'h4', 'li', 'fieldset', 'legend', 'nav', 'section', 'tr', 'caption',
);
// Tags to completely remove from the markup
$aTagsToRemove = array(
'table', 'thead', 'tbody', 'ul', 'ol', 'td', 'th',
);
// Remove the englobing
...
to prevent an extra line break
$sHtml = preg_replace('|^
(.*)
$|s', '$1', $sHtml); // Must use the "s" (. matches newline) modifier
foreach($aTagsToReplace as $sTag)
{
$sHtml = preg_replace("|<{$sTag} ?([^>]*)>|is", '', $sHtml);
$sHtml = preg_replace("||i", ' ', $sHtml);
}
foreach($aTagsToRemove as $sTag)
{
$sHtml = preg_replace("|<{$sTag} ?([^>]*)>|is", '', $sHtml);
$sHtml = preg_replace("||i", '', $sHtml);
}
// Remove any trailing , if any, to prevent an extra line break
$sHtml = preg_replace("| $|", '', $sHtml);
return $sHtml;
}
}