How to remove conditional formatting?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Sulochana
Posts: 17
Joined: Sat Jun 08, 2013 5:53 am

How to remove conditional formatting?

Post by Sulochana »

Hi,

I am new to macro's. I am writing a macro that copies data from a sheet that contains conditional formatting.

In the target sheet, I delete all the data before I paste the copied data.

I even deleted columns after selecting all cells but the existing conditional formatting will not go away. (These gets accumulated each time the macro runs).

Please show me a way to remove all conditional formatting in a given sheet.

Really appreciate your support.

Thanks in advance.

Sulochana
OpenOffice 3.1 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11482
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to remove conditional formatting?

Post by Zizi64 »

I am writing a macro that copies data from a sheet that contains conditional formatting.
Is that macro RECORDED?

You can WRITE (or RECORD) macros to copy data only, but not the format.

If you record the macro:
Use the "Paste special" - "unformatted text" option (from menu, when you record the macro).

If you write the macro:
Use the API functions to get 'value' or 'formula' from the desired cell(s).
http://forum.openoffice.org/en/forum/vi ... f=5&t=7915
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.
Sulochana
Posts: 17
Joined: Sat Jun 08, 2013 5:53 am

Re: How to remove conditional formatting?

Post by Sulochana »

Thanks for the suggestion.

The macro is a combination of recording and writing.

I can paste values and formulas and ensure that the conditional formatting is not copied to the new sheet.

But I put in a conditional formatting to the new sheet through my macro. Therefore, each time I run the macro, the conditional formatting gets accumulated.

Please show me a way to remove all conditional formatting in a sheet.

Thank you
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to remove conditional formatting?

Post by Villeroy »

You can find the answer to this question and many thousands of similar ones all by your own with a little help from http://extensions.services.openoffice.o ... roject/MRI
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Sulochana
Posts: 17
Joined: Sat Jun 08, 2013 5:53 am

Re: How to remove conditional formatting?

Post by Sulochana »

I tried but could not find out how to use the suggested method.

Any help is greatly appreciated.

Thanks,

Sulochana
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to remove conditional formatting?

Post by Villeroy »

The MRI extension is the tool which lets me find all properties and methods for every office object. If you can not use that tool you should give up all macro programming.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Keith Wild
Posts: 56
Joined: Wed Aug 12, 2009 1:14 pm
Location: London, UK

Re: How to remove conditional formatting?

Post by Keith Wild »

Villeroy is correct in that the MRI tool is very useful, but I think there are other things you need to do as well.

1. Get hold of the Basic Programmers Guide and read it, particularly the chapter about spreadsheets.You can find a link to it at the top of the Macros and UNO API forum.
2. Get hold of the MRI extension and install. You can then start it off in a Calc spreadsheet from the menu by Tools, Add-Ons, MRI. Read how to use it, again starting in a Calc spreadsheet, by Help, Open Office.org help, Open Office.org Basic (in the drop down menu at the top left of the dialog box), Contents, MRI.
3. Look at this documentation of the API (http://www.openoffice.org/api/docs/comm ... le-ix.html). It documents services, interfaces, methods and properties. In particular use the Index (near the top). I looked for Conditional Format and then followed the link to it in Sheet Cell Range, and then clicked on the link there to XSheetConditionalEntries. Eventually I found the method "clear", which looked like a good bet.
4. When you have a clue as to what you are looking for then fire up MRI and try to find it. We are looking for a method "clear" , which when applied to a Conditional Format in Sheet CellRange hopefully will clear the CF's. (In fact using MRI it looks like we can actually apply it to a sheet, not just a Sheet Cell Range).
5. I also looked at the Developer's Guide from the link in 3. This gives a clue that we don't just need to "clear", but also reset the CF property, or as the developers guide puts it "After a conditional format is changed, it has to be reinserted into the property set of the cell or cell range."
6. Finally, I also searched this forum for Conditional Format, which provided a few useful links.

The result of all this is (from MRI with the macro name amended):

Code: Select all

Sub CFClear
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oConditionalFormat As Variant

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByIndex(0)
  oConditionalFormat = oObj1.ConditionalFormat
  oConditionalFormat.clear()
  oObj1.setPropertyValue("ConditionalFormat", oConditionalFormat)
End Sub
In order to test it I wrote the following (based on something I found from my search) so I had something to clear:

Code: Select all

Sub CFAdd
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oCellRangeByName As Variant
  Dim oConditionalFormat As Variant
  Dim oNewCondition(3) as new com.sun.star.beans.PropertyValue    ' Create New Property Value

	'Set range and CF
  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByIndex(0)
  oRange = oObj1.getCellRangeByName("A1:Z100")
  oConditionalFormat = oRange.ConditionalFormat
 
	'Set new CF condition     
  oNewCondition(0).Name = "Operator"
  oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.BETWEEN
  oNewCondition(1).Name = "Formula1"
  oNewCondition(1).Value = "1"          
  oNewCondition(2).Name = "Formula2"
  oNewCondition(2).Value = "5"         
  oNewCondition(3).Name = "StyleName"	
  oNewCondition(3).Value = "Heading"      'just use one of the inbuilt styles; can define your own    
  
   ' Add New Conditional Format to CF
  oConditionalFormat.addNew(oNewCondition())                                         
  oRange.setPropertyValue("ConditionalFormat", oConditionalFormat)   
  
End Sub
Let us know if it works for you.
AOO 4.1.10
macOS Big Sur version 11.6.2
Post Reply