Page 1 of 1
How to do cell hatching ?
Posted: Sun Sep 05, 2010 6:04 am
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 ?
Re: How to do cell hatching ?
Posted: Sun Sep 05, 2010 8:02 am
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
Re: How to do cell hatching ?
Posted: Sun Sep 05, 2010 10:08 am
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.
Re: How to do cell hatching ?
Posted: Sun Sep 05, 2010 11:02 pm
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.