How to do cell hatching ?

Discuss the spreadsheet application
Post Reply
omara007
Posts: 15
Joined: Wed Aug 04, 2010 5:41 am

How to do cell hatching ?

Post by omara007 »

Hi folks

I'm want to distinguish some Calc cells from others by hatching them as my printout will only be in black and white .. can anyone tell me how to hatch a cell in Calc ?
OOo 3.2.1, Win7
User avatar
Zizi64
Volunteer
Posts: 10926
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to do cell hatching ?

Post by Zizi64 »

There is not such option (for hatching the background of a cell) in my OxygenOffice3.2.1
I think the hatching would make it worse the visibility of the cell data.

You can use differential COLORs for cell background (If you want to print without colors: you can to use different gray color)

see:
http://user.services.openoffice.org/en/ ... 9&p=126083
and:
http://user.services.openoffice.org/en/ ... 9&p=106679
and:
http://user.services.openoffice.org/en/ ... 1&p=106376
Tibor Kovacs, Hungary; 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.4.4;AOO4.1.13
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
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to do cell hatching ?

Post by Charlie Young »

It is quite possible for a macro to precisely fill a cell with a semitransparent, hatched, RectangleShape. The attached spreadsheet should give some idea of the possibilities. The top button will fill all the cells on the active sheet containing the letter "x" with such rectangles, and the bottom button removes them.
The filling button also realigns any rectangles that have been knocked out of position by dragging them or by resizing cells, but it is also interesting to note that the rectangles also adjust by themselves with column width and row height to a certain extent.

The hatching styles, method of choosing cells, degree of transparency, etc., are all open for modification and suggestions.
Attachments
hatch_some_cells.ods
"hatch" cells
(10.2 KiB) Downloaded 415 times
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to do cell hatching ?

Post by Charlie Young »

In case anybody is seriously looking at my solution above, I have found 2 problems with it (so far).

The first is, clicking on a hatched cell selects the rectangle instead of the cell. This can be fixed by adding the line

oRectangle.LayerID = 1

to the HatchCells macro

Code: Select all

Sub HatchCells
    Dim Doc As Object
    Dim Sheet As Object
    Dim Cell As Object
    Dim hCells
    Dim oRectangle As Object
    Doc = ThisComponent
    Sheet = Doc.CurrentController.ActiveSheet
    
    hCells = Sheet.queryContentCells(com.sun.star.sheet.CellFlags.STRING)
    
    for each Cell in hCells.Cells
        
        if InStr(Cell.String,"x") > 0 or InStr(Cell.String,"X") > 0 then
            Cell.ClearContents(com.sun.star.sheet.CellFlags.STRING)
            if not hasHatch(Cell) then
                oRectangle = Doc.createInstance("com.sun.star.drawing.RectangleShape")
                Sheet.DrawPage.add(oRectangle)
                oRectangle.setPosition(Cell.Position)
                oRectangle.SetSize(Cell.Size)
                oRectangle.FillStyle = 3
                oRectangle.FillHatchName = "Red Crossed 45 Degrees" 
                oRectangle.FillTransparence = 50
                oRectangle.LineStyle = com.sun.star.drawing.LineStyle.NONE
                oRectangle.LayerID = 1
            endif
        endif
    next
    FixHatches    
End Sub
The other problem is that if a large number of cells are "x'ed" the adding of rectangles gets very slow. This is because I'm checking to see if each such cell already has a rectangle with the "hasHatch" function. This is to keep the rectangles from piling on top of each other, which is something that happened to me while I was writing the thing. If one is careful, this check could be done without, but I'm looking at a better solution.

And of course the method of selecting cells is a bit ad hoc.
Apache OpenOffice 4.1.1
Windows XP
Post Reply

Return to “Calc”