Apache OpenOffice (AOO) Bugzilla – Issue 35913
reference to content with explicit newlines in cells
Last modified: 2013-08-07 15:14:30 UTC
I want to use explicit newlines (CTRL+Return) in cells and reference this cell with newlines in it (e.g. B1: =A1), all the newlines are deleted The Input of: A | B 1 one\ntwo\nthree | =A1 2 | results in: A | B 1 one\ntwo\nthree | one two three 2 | All the newline are replaced with spaces.
Created attachment 18578 [details] reference to cells with newlines
Hi, as we mostly (we do display spaces between the single lines, Excel shows them in a single line without delimiter) act in the same way as Excel does, this is not a defect. As we are not able to add some linebreaks to such a reference, I'd like to see this as an enhancement request. So re-flagged and re-assigned. Frank
I am using OOo. 1.9m56: on winxp home edition. I am using \n to indicate newline 1. When I enter 1 "newline" 2 ""newline" 3 "newline" in a cell, it displays properly. ___ 1| 2| 3| ----| 2. When I copy and paste the cell staying within the same spreadsheet. (selected outline cell in black, right click etc.) It copies fine. If I copy as before into another application though, it is now spaced out, not newlined properly. 3. If I copy and paste the cell contents by selecting the text within (double clicking) the cell (not to be confused with the input line at top). It copies each entry as a new cell. If I do this, but paste into notepad instead, they are newlined correctly. 4. If I select the cell and then right click on the input bar (where it displays the 1 2 3 uptop) *\dramatic pause\* The actual cell entry changes into spaced numbers!!!!!!!!! They continue to behave in this manner (1 2 3 not 1\n 2\n 3\n) even when copied out to notepad. I have had similar results in OOo 1.1.2 WinXP home ed. (I have both OOo 1.1.2 and OOo 1.9m56 installed if that matters) My friend has reproduced this in OOo 1.1.0 on SuSE 9.0 Prof. Linux 2.6.7, instead of notepad he used Kate.
Besides the simple preservation of these \n when referencing a cell (and any format in general, like italics or bold parts of texts), I'd like to have it preserved when the spreadsheet is used as a database (in Base), and then imported in Writer (using mailing function). That would make OOo even more powerful!
Created attachment 55450 [details] Patch to fix this bug against DEV300_m16
I've attached a patch to fix the reported problem. The patch also fixes #83666. Summary of notable changes that the patch adds: - Copying text out of a cell containing multiple lines (an ScEditCell) and pasting into a text editor like gvim will keep the newline characters, previously the newlines were converted to spaces. - Similarly for formula cells (ScFormulaCell). - When entering/modifying a formula that results in more than one line, the cell will automatically be resized to the optimal height, like when entering multiple lines in an edit cell using CTRL-ENTER. - Double clicking the horizontal bar between row numbers to auto-size the row (after a manual resizing) will size the row correctly to the optimal height (like it does for an ScEditCell containing multiple lines) - Using char(10) in a formula will correctly display a newline (previously it filtered it out completely) - Newlines in the result of a formula are now available from Basic using the cell's 'String' property.
Issue 14558 is also fixed by the attached patch.
Issue 15339 (VLOOKUP regular expressions involving \n) is also fixed by the patch.
The patch also fixes some regular expression problems. The Find and Replace dialog will now match correctly for the results of formulae when selecting "Regular expressions" and the "Search in" option is set to "Values", eg searching for "\n" or "^abc$" or "bc\nde" will now match both A1 and A2 below, previously just A1 matched: A1: abc def A2: =A1 I also tried the COUNTIF, SUMIF and SEARCH formulae (as they support regex) with the above search patterns and these also now give consistent results, ie the results are the same when these formulae operate on either A1 or A2 above.
Created attachment 55515 [details] Spreadsheet demonstrating the various multi-line improvements
Created attachment 55516 [details] Screenshot of MultilinePatchShowcase.ods without the patch
Created attachment 55517 [details] Screenshot of MultilinePatchShowcase.ods with the patch
FYI: discussion of this patch takes place on the dev@sc mailing list.
Could someone with appropriate permissions mark the following as also being duplicates of this bug or fixed by the patch: Issue 18302 and all duplicates assigned to it. Issue 50000 line feed aspect. Issue 30781 and the duplicates assigned to it. Issue 5250.
*** Issue 18302 has been marked as a duplicate of this issue. ***
*** Issue 30781 has been marked as a duplicate of this issue. ***
Issue 50000 and issue 5350 are not duplicates of this issue, they describe something different.
Issue 5250 (not 5350) is similar to this one. I apologise, but on rechecking, the patch does not actually fix it. However, issue 50000 describes a line feed problem amongst many others, and this aspect is fixed by the patch.
Of course 5350 was a typo.. anyway, issue 5250 is not a duplicate, it is about entering a formula with newlines such that the formula would look like =expression1 +expression2 when the formula is edited. This is not possible, the cell created in this case is an ScEditCell and thus loses formula functionality, and is not covered by this patch. The one aspect of linefeed in issue 50000 indeed would be fixed with this patch, but the underlying problem of that issue is something completely different.
Created attachment 55814 [details] original patch plus further improvements, details to follow...
Additional changes to first patch: xestyle.hxx xestyle.cxx xetable.cxx - Excel xls export converter, "wrap text" turned on for formula cells like it is for non-formula cells cell.hxx cell.cxx formularesult.hxx - Eike Rathke suggestions on the original patch to improve performance column3.cxx - Fixes for Copy, Paste Special, untick formula, ie paste text only from a formula with multilines transobj.cxx - Calc8 OLE linking and embedding multiline formula fixes impex.cxx - Save as SYLK with multilines
Is this patch work well? Any possible regression? Kohei: If you agree I'll integrate it into ooo-build system.
@kami_: well, I don't agree yet. Since this will affect file export, we need to do a thorough testing to make sure import/export works. I'm mostly interested in xls/ods file import/export. Niklas mentioned one potential issue about ods file import/export on the dev@sc list. So we need to at least look into that before we can use this patch.
@kami_: either that, or wait until we branch ooo-build for 3.0, then you can put the patch in trunk for testing.
Ok. I just ran a quick test, and the patch seems to work pretty well all around. I saw the issue with ODS export that Niklas mentioned. Yes, it works with the line breaks but we should be consistent with the output of a text cell and use <text:p> there in the formula cell output. Luckily it's an easy fix, so I'll see if I can take care of that there.
One thing I noticed, when exporting to SYLK format, Excel has trouble importing the cell reference =A1 due to "name conflict", and Calc has trouble importing the encoded LF character. But that's probably a bug in Calc's SYLK importer so it's not the fault of this patch. (Besides, who uses SYLK these days?)
Created attachment 55967 [details] Here is the patch with the odf issue fixed.
One minor point. Since the difference between ScEditUtil::GetSpaceDelimitedString() and ScEditUtil::GetMultilineString() was just the delimiter character, I moved the common part to a local function to eliminate duplicated code.
Some problems: - Conversion to unformatted text still unconditionally includes the LF character, regardless of the system's line delimiter. For the clipboard, where we don't use text delimiters (quotes), including any line delimiter will break the table structure. - Eike's suggestion "Multiline meMultiline : 2" doesn't work with MSVC, because it uses (signed) int as the default underlying type for enums. - Kohei's change, with ScXMLExport::IsMultiLineFormulaCell always calling ScDocument::GetCell, adds some performance overhead. I didn't measure it, but load/save is something we don't want to slow down (at least if multiple-line formula results aren't used).
Created attachment 56621 [details] updated patch
I've updated the patch to address the 2nd and 3rd issues that nn raised. > - Eike's suggestion "Multiline meMultiline : 2" doesn't work with MSVC, because > it uses (signed) int as the default underlying type for enums. I simply removed the trailing :2 size specifier. Will this be enough? We (William and I) don't seem to have a good Windows develoment environment, so any suggestion for this would be appreciated if this change will not be adequate. > - Kohei's change, with ScXMLExport::IsMultiLineFormulaCell always calling > ScDocument::GetCell, adds some performance overhead. I didn't measure it, but > load/save is something we don't want to slow down (at least if multiple-line > formula results aren't used). I've made some change so that ScDocument::GetCell() gets called only once.
Created attachment 56622 [details] a little more updated to check if the cell is a formula cell before checking for its multi-line state.
> > - Eike's suggestion "Multiline meMultiline : 2" doesn't work with MSVC, because > > it uses (signed) int as the default underlying type for enums. > > I simply removed the trailing :2 size specifier. Will this be enough? That would add 4 bytes to every formula cell of which 30 bit are unused.. which was the reason I suggested to add to the bit field instead where we have some unused space left. Better would be typedef unsigned char Multiline; const Multiline MULTILINE_UNKNOWN = 0; ... Multiline meMultiline :2;
Created attachment 56639 [details] Eike's unsigned char suggestion added and removed warning introduced in ScXMLExport::IsMultiLineFormulaCell
*** Issue 95444 has been marked as a duplicate of this issue. ***
*** Issue 95452 has been marked as a duplicate of this issue. ***
@wsfulton: How's progress on testing and adapting all places that would be affected by this change, or adapting the change, as discussed on the dev@sc mailing list?
@er, I have a Windows build environment working and as such have been able to test interoperability better and test more filters. I have fixed various issues and will post an intermediate patch soon. I don't think there is any obvious route to take for fixing DDE links on Unix, but will post to the mailing list when I've better understood the DDE implementation.
Created attachment 57798 [details] Further multiline improvements
multilines4.patch improvements since the previous patch: - Copy/paste text - add in quotes around the cell contents (string delimiter) if cell text is multiline (like it does if string contains a tab): impex.cxx - Export and paste as HTML fix for multiline text (both formula and non-formula cells): htmlexp.cxx - Fix multiline DIF format import and paste special (formula and non-formula cells) for example when importing from Excel: dif.hxx difimp.cxx - Fix SYLK import containing newlines - now OOo is compatible with Excel: impex.cxx (Doc2Sylk and Sylk2Doc). - Fix SYLK import/export containing the quote character (") and semicolon. The escaping of these characters is now the same as other spreadsheets, I checked Excel, Quattro Pro and Gnumeric. Fixes copy from OOo to Excel as Excel chooses the SYLK format as the default when pasting from OOo. Note that this is only noticeable if from the menu Tools, Autocorrect, Double Quotes, Custom Quotes, Replace is not ticked. Note that this is ticked by default: impex.cxx - Auto resize cell heights so multiline cells display correctly when importing formats DIF and SYLK : docsh.cxx - QuattroPro import filter fix for multi-line non-formula cells: qpro.cxx
GREAT! I think this does it, does someone think differently?
Created attachment 60759 [details] multilines5.patch
multilines5.patch - This patch contains the previous patch (multilines4.patch) plus the following modifications: - Newlines are converted into spaces for DDE linking as per discussion on openoffice-sc-dev list in Nov 2008. - Pasting cell contents as text into external application never adds quotes, ie the pasting text behaviour of patch 4 is removed. The text is now pasted exactly as stored in the cell, including newlines. Files modified since previous patch: transobj.cxx, docsh4.cxx, impex.hxx, impex.cxx, servobj.cxx, cellsh2.cxx
Created attachment 60760 [details] multilines6.patch
multilines6.patch - This patch contains the previous patch (multilines5.patch) plus the following modifications: - Fixed DDE linking when a cell contains a tab - it gets converted to a space, as per openoffice-sc-dev list discussion in Nov 2008. Files modified since last patch: transobj.cxx, docsh4.cxx, impex.hxx, impex.cxx, servobj.cxx, cellsh2.cxx
I'll apply this to CWS calcmultiline. Thanks again, Eike
Congratulations ! So nice to see that old problems are going away ! And many, many thanks to the developers. Robert
In cws calcmultiline: revision 269286 sc/inc/cell.hxx sc/inc/editutil.hxx sc/inc/formularesult.hxx sc/source/core/data/cell.cxx sc/source/core/data/cell2.cxx sc/source/core/data/column.cxx sc/source/core/data/column2.cxx sc/source/core/data/column3.cxx sc/source/core/tool/editutil.cxx sc/source/filter/dif/difimp.cxx sc/source/filter/excel/xestyle.cxx sc/source/filter/excel/xetable.cxx sc/source/filter/html/htmlexp.cxx sc/source/filter/inc/dif.hxx sc/source/filter/inc/xestyle.hxx sc/source/filter/qpro/qpro.cxx sc/source/filter/xml/XMLExportIterator.cxx sc/source/filter/xml/XMLExportIterator.hxx sc/source/filter/xml/xmlexprt.cxx sc/source/filter/xml/xmlexprt.hxx sc/source/ui/app/transobj.cxx sc/source/ui/docshell/docsh.cxx sc/source/ui/docshell/docsh4.cxx sc/source/ui/docshell/impex.cxx sc/source/ui/docshell/servobj.cxx sc/source/ui/inc/impex.hxx sc/source/ui/view/cellsh2.cxx sc/source/ui/view/output2.cxx Note that I changed ScXMLExport::IsMultiLineFormulaCell() and ScFormulaResult::IsMultiline() to const methods, the latter mutating meMultiline through a const_cast.
Reassigning to QA for verification.
revision 269502 sc/source/ui/docshell/impex.cxx Fixed a problem with string data followed by a formula expression containing quoted constant string data. Something like K"this data";EA1&" data" lead to the expression being appended to the cell data. Primarily the change fixes the overall handling of embedded quotes in formula expressions, that incorrectly were escaped by doubling them, and embedded semicolons, that incorrectly were not escaped. For details see issue 100205.
verified in internal build cws_calcmultiline
Verified in DEV300m49 on WinXP Closing
This patch seems not working in version DEV300m56. See attached test image please. What's wrong
Created attachment 64528 [details] Test
I tested the DEV300m56 on a different Win system, and I got the same result. I think that this issue should be reopened... Thanks
also checked in M56 and can confirm the results from vitriol.
Reopening. Issue *not* fixed in DEV300m56.
@wsfulton: could you take a look what breaks this now? Must had been introduced between m49 and m56. Thanks.
Sigh, I'll take a look. If anyone checks any versions between m49 and m56 and that will help me a lot.
I just checked m51 on Linux and multiline formulas are broken in this version.
m50 works fine, so the problem was introduced in m51. A lot of the multiline patch does actually work in m51, so it hasn't all been removed. It looks like just the newline character is ignored when displaying formula results. The formula does actually contain the newline as copy/paste as text from the formula cell does retain the new line.
I have tested m50 (build 9406) on WinXP. Multiline formulas are OK there.
Regression is in output2.cxx revision 272382 on http://svn.services.openoffice.org/ooo/cws/calc50/sc with comment "CWS-TOOLING: rebase CWS calc50 to trunk@272291 (milestone: DEV300:m49)"
Created attachment 64570 [details] Fix multiline formula regression in output2.cxx
Sorry, correction to the point of regression, 272382 on calc50 was correct. svn diff -r272983:272984 http://svn.services.openoffice.org/ooo/trunk/sc/source/ui/view/output2.cxx shows the regression occurring, ie "CWS-TOOLING: integrate CWS fhawfixes1"
@wsfulton: Many Thanks! In cws dr69: revision 275862 sc/source/ui/view/output2.cxx Note that strangely there was an extraneous colon in the patch.
Judging from the topic and other commits of CWS fhawfixes1 the commit of output2.cxx was an error anyway.
Already fixed again, see issue 104059.
verified in internal build cws_dr69
Tested with a MacBook with OSX 10.5.x on OOO320_m9 works as expected closing issue