|
Cookbook /
Tabtable-jtSummary: Support embedded line feeds, trim spaces and add prior patch (make first row a header)
Version: 1.04
Prerequisites: Install ExcelPaste recipe
Status: tested 2007/08/15
Maintainer: jtankers
Categories: Uncategorized
Files: tabtable-2007-08-15.zipΔ
Questions answered by this recipeWhat if an Excel cell contains embedded line feeds (line feeds pasted into Excel cells to create multiple text lines)?
DescriptionEnter your recipe's description here. To implement the patch:
NotesI don't understand why we need to add this code in the php file???? Skink 30/11/2007 If I understand the question, the code is optional and only required if you wish to convert Excel cells that contain multiple lines per cell, where each line ends with Chr(13) followed by Chr(10). This type of text may be entered into an Excel cell using cut and paste from other applications such as word processors. The code below allows the conversion code to recognize and process this type of complex textual cell data. jtankers 2008/05/19
<?php if (!defined('PmWiki')) exit();
/* Copyright 2005 Patrick R. Michaud (pmichaud@pobox.com)
This file is tabtable.php; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published
by the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This script makes it possible to convert spreadsheets copy-and-pasted
from other applications (such as Microsoft Excel) into PmWiki
simple tables. Numeric cells are automatically right-aligned,
while other cells are left-aligned. Internally, the script
simply converts lines containing tab characters into PmWiki
table cells.
Currently the script enables itself via the GUIButton bar.
(Contact the author if you want this feature without GUIButtons.)
Place this script in the cookbook/ directory, the excel.gif
file in pub/guiedit/ , and add the following line to config.php:
include_once('cookbook/tabtable.php');
Updates:
2005 Pm 1.00 Original by Patrick R. Michaud
2007/01/23 RussFink 1.01 make first row a heading
2007/08/09 jtankers 1.02 support embedded line-feed ("\n"), quote and trim extra spaces
2007/08/10 jtankers 1.03 detect header row
2007/08/15 jtankers 1.04 support embedded pipes |
*/
SDV($EnableGUIButtons, 1);
SDV($GUIButtons['tabtable'], array(1100, '', '', '',
"<input type='image' name='tabtable' src='\$GUIButtonDirUrlFmt/excel.gif' title='\$[Convert tabs to table]' />"));
SDV($HTMLStylesFmt['tabtable'],'
table.tabtable { border-collapse: collapse; }
table.tabtable td { border:1px solid #cccccc; }
');
if (@$_POST['tabtable_x']) array_unshift($EditFunctions, 'TabTable');
else return;
# detect header row (patch-jtankers-2007/08/10)
function IsHeaderRow($col) {
$return = true;
for($i=0; $i<count($col); $i++) {
if (preg_match('/^\\s*\\(?-?[\\d,]+\\.?\\d*\\)?\\s*$/', @$col[$i])) { $return = false; break; }
}
return $return;
}
# determine if line is valid end of row and not just a line feed embedded in a table cell "\n" (patch-jtankers-2007/08/09)
# note: cells with embedded line feeds are enclosed in double quotes and embedded double quotes are doubled again
function IsEndOfRow($line) {
$return = true;
if ($line != "") {
if (substr_count($line, '"') % 2 == 1) { $return = false; } # odd number of double quotes
}
return $return;
}
# convert tab and line feed delimited table to PmWiki table syntax
function TabTable($pagename, &$page, &$new) {
if (!@$_POST['tabtable_x']) return;
$lines = explode("\n", $new['text']);
$row = array(); $wid = array();
# detect line feeds embedded in cells (patch-jtankers-2007/08/09)
$lf = "<_lf>";
$x = "";
while ($lines) {
$x = $x . array_shift($lines);
if (IsEndOfRow($x)) {
$linesNorm[] = $x;
$x = "";
} else {
$x .= $lf;
}
}
if ($x != "") { $linesNorm[] = $x; }
$lines = $linesNorm;
# process rows
while ($lines || $row) {
$x = array_shift($lines);
if ($x==='' && $row) { $row[] = array(); continue; }
# fill rows of columns and wid arrays
if (strpos($x, "\t") !== false) {
$col = explode("\t", $x);
$row[] = $col;
for($i=0; $i<count($col); $i++) { $wid[$i] = (max(@$wid[$i], strlen($col[$i]))) > 0 ? 1 : 0; } # (length of zero or 1, patch-jtankers-2007/08/09)
continue;
}
# process empty rows
while ($row && count(end($row))==0) { array_pop($row); $x = "\n".$x; }
# format row columns with PmWiki table syntax
if ($row) {
$y[] = "||class='tabtable'";
$_first = '!'; # (patch-RAF-2007/01/23)
while ($row) {
$col = array_shift($row);
if ($_first != '') { if (IsHeaderRow($col) == false) { $_first = ''; } } # (patch-jtankers-2007/08/10)
$r = '||';
for($i=0; $i<count($wid); $i++) {
$m = preg_match('/^\\s*\\(?-?[\\d,]+\\.?\\d*\\)?\\s*$/', @$col[$i])
? " $_first%{$wid[$i]}s||" : "$_first%-{$wid[$i]}s ||"; # (patch-RAF-2007/01/23)
$r .= sprintf($m,str_replace('|', '|', @$col[$i])); # replace | with pipe special character (patch-jtankers-2007/08/15)
}
$y[] = $r;
$_first = ''; # (patch-RAF-2007/01/23)
}
$row = array(); $wid = array();
}
if (!is_null($x)) $y[] = $x;
}
# return as PmWiki table markup
$new['text'] = implode("\n", $y);
# (patch-jtankers-2007/08/09)
# note: cells with embedded line feeds are enclosed in double quotes and embedded double quotes are doubled again
$dq = "<_dq>";
$new['text'] = str_replace('""', $dq, $new['text']);
$new['text'] = str_replace('"', '', $new['text']);
$new['text'] = str_replace($dq, '"', $new['text']);
$new['text'] = str_replace($lf, "\\\\\n", $new['text']);
$_POST['preview'] = 1;
}
?>
Release Notes
CommentsSee AlsoContributors
|