How to do cell hatching ?

[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 ?

[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

[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.

[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.