Bold to <b> in Calc

Discuss the spreadsheet application
Post Reply
etibmw
Posts: 6
Joined: Tue Sep 06, 2016 11:39 pm

Bold to <b> in Calc

Post by etibmw »

I have a calc sheet with bold on part of the text.
I need to export this to a csv and wrap the bold text with <b></b> in order to keep the style.
How can this be done?

thanks eti
OpenOffice version: 4.1.2
OS: Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in Calc

Post by Zizi64 »

I need to export this to a csv and wrap the bold text with <b></b> in order to keep the style.
A csv type file can not store any formatting property.
Maybe you can put the simpliest format codes into a csv file, but you can not restore it when you will open it.

Try to save that spreadsheet as HTML...


But always work in the native, International Standard ODF file format.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
etibmw
Posts: 6
Joined: Tue Sep 06, 2016 11:39 pm

Re: bold to <b> in clac

Post by etibmw »

Hi

I dont need to restore it from csv, I use the csv to upload to a website.
what i need is a script that will replace all the bold text in the sheet with html bold tag around it, then when i export to csv and and upload i will see the bold text on the site.
OpenOffice version: 4.1.2
OS: Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in clac

Post by Zizi64 »

I dont need to restore it from csv, I use the csv to upload to a website.
what i need is a script that will replace all the bold text in the sheet with html bold tag around it, then when i export to csv and and upload i will see the bold text on the site.
Then you need save a copy of that spreadsheet as HTML...

File - Save As - select the "HTML document" option.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
etibmw
Posts: 6
Joined: Tue Sep 06, 2016 11:39 pm

Re: bold to <b> in clac

Post by etibmw »

Hi

That will not work for me since i need the data in columns, and not as an html page
OpenOffice version: 4.1.2
OS: Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in clac

Post by Zizi64 »

Here is a simple example:
Formatted table in HTML format.ods
(10.78 KiB) Downloaded 207 times
And here is the HTML code of the converted HTML file. As you see, the Worksheet will be converted as HTML table:

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
	
	<meta http-equiv="content-type" content="text/html; charset=windows-1250"/>
	<title></title>
	<meta name="generator" content="LibreOffice 4.4.7.2 (Windows)"/>
	<meta name="author" content="Tibor Kovács"/>
	<meta name="created" content="2016-09-09T10:59:32.227000000"/>
	<meta name="changedby" content="Tibor Kovács"/>
	<meta name="changed" content="2016-09-09T11:02:23.455000000"/>
	
	<style type="text/css">
		body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small }
	</style>
	
</head>

<body>
<table cellspacing="0" border="0">
	<colgroup span="2" width="85"></colgroup>
	<tr>
		<td height="17" align="center"><b><i>number</i></b></td>
		<td align="center"><b><i>color</i></b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="1" sdnum="1038;">1</td>
		<td align="center"><b>red</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="2" sdnum="1038;">2</td>
		<td align="center"><b>blue</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="3" sdnum="1038;">3</td>
		<td align="center"><b>yellow</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="4" sdnum="1038;">4</td>
		<td align="center"><b>green</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="5" sdnum="1038;">5</td>
		<td align="center"><b>braun</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="6" sdnum="1038;">6</td>
		<td align="center"><b>black</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="7" sdnum="1038;">7</td>
		<td align="center"><b>white</b></td>
	</tr>
	<tr>
		<td height="17" align="center" sdval="8" sdnum="1038;">8</td>
		<td align="center"><b>magenta</b></td>
	</tr>
</table>
<!-- ************************************************************************** -->
</body>

</html>
You can copy the Table part of the code into an another HTML document.
Last edited by Zizi64 on Fri Sep 09, 2016 11:35 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in clac

Post by Zizi64 »

The "Save as: HTML" function can export the Cell border properties too:

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
	
	<meta http-equiv="content-type" content="text/html; charset=windows-1250"/>
	<title></title>
	<meta name="generator" content="LibreOffice 4.4.7.2 (Windows)"/>
	<meta name="author" content="Tibor Kovács"/>
	<meta name="created" content="2016-09-09T10:59:32.227000000"/>
	<meta name="changedby" content="Tibor Kovács"/>
	<meta name="changed" content="2016-09-09T11:26:19.376000000"/>
	
	<style type="text/css">
		body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Liberation Sans"; font-size:x-small }
	</style>
	
</head>

<body>
<table cellspacing="0" border="0">
	<colgroup span="2" width="85"></colgroup>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center"><b><i>number</i></b></td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b><i>color</i></b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="1" sdnum="1038;">1</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>red</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="2" sdnum="1038;">2</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>blue</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="3" sdnum="1038;">3</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>yellow</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="4" sdnum="1038;">4</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>green</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="5" sdnum="1038;">5</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>braun</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="6" sdnum="1038;">6</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>black</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="7" sdnum="1038;">7</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>white</b></td>
	</tr>
	<tr>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="17" align="center" sdval="8" sdnum="1038;">8</td>
		<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center"><b>magenta</b></td>
	</tr>
</table>
<!-- ************************************************************************** -->
</body>

</html>
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in clac

Post by Zizi64 »

If you want upload your document as a discrete data file, then use the ODF format and alternatively you can upload a converted Excel version and/of a PDF version.

Once again: the csv is a pure text file with separator characters between the data values. It can not store and display any formatting properties.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
etibmw
Posts: 6
Joined: Tue Sep 06, 2016 11:39 pm

Re: bold to <b> in clac

Post by etibmw »

hi

the html version of the sheet will not help me
the file has multiple rows, each row when uploaded to the site will be a separate page.
the column with the body text has bold text.

If i could do a replace of BOLD TEXT to <b>BOLD TEXT</b> and export to csv the bold text will appear on the site. if i save the sheet as html i will lose the ability to upload the sheet to the site.

hope my question is clear now
OpenOffice version: 4.1.2
OS: Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: bold to <b> in clac

Post by Zizi64 »

the file has multiple rows, each row when uploaded to the site will be a separate page.
Is it your opinion, or it is the desired apearance?

Try the HTML code above. Just copy the code into a new, empty .txt file, and save it with .html extension. Open the created HTML file in your browser.

You can try to put the format codes into an exported small .csv file manually. The codes will not work, when you open the file. They will displayed as pure characters: <b>some text</b> instead of some text
Last edited by Zizi64 on Fri Sep 09, 2016 2:45 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: bold to <b> in clac

Post by acknak »

etibmw wrote:... If i could do a replace of BOLD TEXT to <b>BOLD TEXT</b> and export to csv ...
This is not possible in Calc, except maybe through macro programming.

Calc has no way at all to handle a search for formatted text.

You could possibly manage it by copy/paste into Writer (which can do search/replace for bold text) and then export that as plain text. Even may require several steps, depending on exactly what flavor of csv you need.
 Edit: PS: 
Or, you could copy/paste the column of data into Writer, make the change, then copy/paste back into Calc and export to csv.
AOO4/LO5 • Linux • Fedora 23
etibmw
Posts: 6
Joined: Tue Sep 06, 2016 11:39 pm

Re: bold to <b> in clac

Post by etibmw »

Hi

So this is what i did in the end, if anyone ever looks for something like this.

1. copy the column i wanted to change to a new clac sheet
2. save the sheet as html
3. write a script that will split the html to an array by the table row attribute (<tr>)
4. now clean up the html from each line while preserving the styles needed
5. copy back to clac

hopefully no new lines were added and now we have the text with html style encoding

thanks for all the help
OpenOffice version: 4.1.2
OS: Windows 10
ouch
Posts: 27
Joined: Thu Oct 13, 2011 11:14 pm

Re: Bold to <b> in Calc

Post by ouch »

You could also just make a custom format like: <b>@</b>

Then just give the whole column that format.

This won't break formulas who use that data as the formulas see the contents without the formatting.

When saving as csv just make sure the "export as displayed" box is checked. (it is by default if I recall)
OpenOffice 4 on Windows 7 64bit
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Bold to <b> in Calc

Post by Lupp »

I still miss:
1. A clear statement about the relevant question if the attribute to be 'Bold' is assured to be applied exclusively to complete cells or (probably) to zero or more specific parts of the Text contained in any cells.
2. A simplified but relevant example file (.ods) attached and making clear the situation with respect to the above question - and probably to many more I did not yet get an impulse to ask.

In any case a solution by user code (say, in BASIC) is possible. For this very specific task it may actually be preferable. The needed information is available from the famous "Useful Macro Information For OpenOffice.org" by Andrew Pitonyak (See: http://www.pitonyak.org/oo.php).
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Bold to <b> in Calc

Post by Lupp »

I decided not to wait for another post by the OQ. I attach a Calc file containing the user code and a demonstration of a rough solution. All the contributors here are encouraged to criticise and to improve the proposed function. (As a rule I prefer functions over subs.)

Sorry. In the demo attached first, you find an orphaned line of code:

Code: Select all

If NOT (theSubElement.TextportionType = "Text") Then Exit Do
You should delete it.
Attachments
BoldToTags001.ods
(16.25 KiB) Downloaded 193 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Bold to <b> in Calc

Post by Lupp »

I meanwhile enhanced the function (see above). The new function is capable of converting the text attributes Bold, Italic, Strike, and Underline to html tags (partly already deprecated). It also recreates links associated with parts of text contained in a cell in html format. (It does not extract functional links assigned to cells by HYPERLINK.) You see:

Code: Select all

Option Explicit
Function convert_Bold_Italic_Strike_Under_Link_To_Tags(ByVal pX As Long, ByVal pY As Long, ByVal pZ As Long) As String
'To pass the cell to work on by position should be an easy and reliable way. 
'The inline tags for striking through and for underscoring were deprecated with V5 of html. 
'I suppose the rendering of most browsers still supports them. 
'A few lines of code are repeated (essentially) to avoid helper functions most likely not usable in a different context.
	Dim theDoc As Object
theDoc = ThisComponent
If	NOT theDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then 
	convert_Bold_Italic_Strike_Under_Link_To_Tags = "Need Calc Document!"
	Exit Function
End If
Dim theSheets As Object, sheetTally As Long
theSheets = theDoc.Sheets
sheetTally = theSheets.GetCount()
If	(pZ < 1) OR (pZ > sheetTally) Then 
	convert_Bold_Italic_Strike_Under_Link_To_Tags = "Inacceptable SheetNumber!"
	Exit Function
End If
	Dim theSheet As Object
theSheet = theDoc.Sheets(pZ - 1)
If	(pX < 1) OR (pY < 1) or (pX > theSheet.RangeAddress.EndColumn + 1) OR (pY > theSheet.RangeAddress.EndRow + 1) Then
	convertBoldItalicStrikeUnderLinkToTags = "No cell with that position!"
	Exit Function
End If
	Dim theCell As Object, rText As String
theCell = theSheet.GetCellByPosition(pX - 1, pY - 1)
If	theCell.Type <> 2 Then
	rText = theCell.String
	If	theCell.CharWeight >= com.sun.star.awt.FontWeight.BOLD Then
		rText = "<b>" & rText & "</b>"
	End If
	If	theCell.CharPosture >= 1 Then
		rText = "<i>" & rText & "</i>"
	End If
	If	theCell.CharStrikeOut >= 1 Then
		rText = "<strike>" & rText & "</strike>"
	End If
	If	theCell.CharUnderline >= 1 Then
		rText = "<u>" & rText & "</u>"
	End If
	convert_Bold_Italic_Strike_Under_Link_To_Tags = rText 
	Exit Function
End If
rText = ""
	Dim theParEnum As Object, theParElement As Object
	Dim theSubEnum As Object, theSubElement As Object 
	Dim textSlice
theParEnum = theCell.GetText().CreateEnumeration
Do	While	theParEnum.HasMoreElements
	theParElement = theParEnum.NextElement
	theSubEnum = theParElement.CreateEnumeration 
	Do	While	theSubEnum.HasMoreElements
		textSlice = ""
		theSubElement = theSubEnum.NextElement
		textSlice = theSubElement.String
		If	theSubElement.CharWeight >= com.sun.star.awt.FontWeight.BOLD Then
			textSlice = "<b>" & textSlice & "</b>"
		End If
		If	theSubElement.CharPosture >= 1 Then
			textSlice = "<i>" & textSlice & "</i>"
		End If
		If	theSubElement.CharStrikeOut >= 1 Then
			textSlice = "<strike>" & textSlice & "</strike>"
		End If
		If	theSubElement.CharUnderline >= 1 Then
			textSlice = "<u>" & textSlice & "</u>"
		End If
		If	theSubElement.TextPortionType = "TextField" Then
			If	theSubElement.TextField.SupportsService("com.sun.star.text.TextField.URL") Then 
				textSlice = "<a href=" & Chr(34) & theSubElement.TextField.URL & Chr(34) & ">" & textSlice & "</a>"
			End If
		End If
		rText = rText & textSlice
	Loop
Loop
convert_Bold_Italic_Strike_Under_Link_To_Tags = rText
End Function
A demo including the enhanced function is added.
Would you prefer to enclose the resulting text in a <p> </p> pair?
Attachments
BoldToTags002.ods
(19.07 KiB) Downloaded 207 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Aivaras
Posts: 8
Joined: Fri Jun 27, 2014 10:10 pm

Re: Bold to <b> in Calc

Post by Aivaras »

This a great macro. Thank you very much.

Is there a way to apply the macro to a range of cells such that the tags are inserted into the cells where the formatting occurs?

1. Select a range of cells that contain inline formatting (e.g. word digit word | digit digit word).
2. Apply the macro.
3. Have the formatting in the cells replaced with HTML tags (e.g. <b>word</b> digit <i>word</i> | digit <u>digit</u> word).
OpenOffice 4.1.3 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Bold to <b> in Calc

Post by Lupp »

(With "the function I supplied" I refer to this post in another branch of the forum: viewtopic.php?f=21&t=85152.)

This is basically not the way spreadsheets work.
A cell containing a formula and referencing another cell (or many) to obtain the result should not change anything but the value (number or text) to display by the cell containing the formula.
As everywhere you may try to disregard the principles. In AOO you will need to rewrite the function I provided into a Sub. This Sub would have to get the range to work on in some way (Passing parameters to Sub is a topic of its own.) Then it would need to work through it cell by cell, to apply the conversion of the content, and to put back the result into the current cell's cell.String property.
This would, of course, destroy the original content and burn the bridge for the way back.

Why?

The conversion of actual formats into the respective html tags will yield a result needingg interpretation to produce formats again. As we are talking of html tags this will be an html interpreter aka browser. We therefore should consider in what way the results obtained in a spreadsheet will be passed into a html file in the appropriate position. Just remaining in the original cells would make no sense, imo.

May I suppose there will be some editing of a html file and the above mentioned results will be pasted therein? Will they form there a table? Tables are created in html with specific syntactical means again. How will a table containing the results achieved in Calc be created and get its contents cell by cell?...
Will you write another Sub to transfer everything to a html-Writer document? Let's be a bit more modest:

Suppose your original formatted cells ar in a sheet 'Source' in the range B7:K45. Create a sheet 'ToTags'. Go to cell B7 there and enter the formula

Code: Select all

=CATTRIBS2HTML(COLUMN(B7);ROW(B7);SHEET($Source.B7)-1;"bisul";$Source.B7)
there. Now fill it to the right till K7 and then down to row 45.
You see?

You may export the 'ToTags' sheet now as a .csv file, or 'Copy' / 'Paste Special...' (results only) back into the source file or ... paste into a html file and create the surrounding structure with whatever means are appropriate for. It's not my field. The html I wrote up to date was a total of a few hundred lines, and mostly just for learning.

You may, of course, also pass the results to another "macro" doing whatever you want. A very smart macro, of course.

Please also note: The function I provided is not perfect.
You might prefer a variant accepting an address instead of the numeric (3 parts) identification of the cell to work on. Simply write it. I also considered to do it this way. In fact the function doing the conversion itself should simply get a cell object as the first parameter. Gaining access to a specific cell should be done by specialised functions. The function(s) to call directly from Calc formulae should then in turn call the getCellByNumbers... OR getCellByAddress... and the conversion kernel on its own behalf as needed.
You may also dislike the fact that the pairs of tags for formatting any textpiece are closed as soon as a new piece starts, even if the specific format continues to apply. You are welcome. Add a routine to reduce unneeded pairs.
When I wrote that function I stressed the aspects relying on the use of some API specials.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply