APImate does not seem to handle pattern fills

Martel_David · November 8, 2021, 1:59pm

Not necessarily looking for a fix to this now as I hope to be able to work it out myself. But just for completeness (since APImate is so generally useful)....

If I put a rectangle shape on a graph (can be embedded chart, or chart sheet) and set the fill to a pattern (e.g. diagonal lines), the file does not get picked up in the APImate generated code.

It looks like I should be able to find this as a Flexcel source code search shows the tag that the Excel file uses ("pattFill"). Just got to work out now how to fill it. No need to do this for me, unless I get really stuck (or in case you are bored... :-) )

Martel_David · November 8, 2021, 3:52pm

No. Can't seem to get it to work. I feel I have the right code, but it is not working

This is the original code for a simple rectangle from APImate

  Chart1ShapeOptions1 := TShapeProperties_Create();
  Chart1ShapeOptions1.Anchor := TClientAnchor.Create(true, TFlxAnchorType.MoveAndResize, 583, 0, 597, 0, 1072, 0, 1207, 0);
  Chart1ShapeOptions1.ShapeType := TShapeType.Rectangle;
  Chart1ShapeOptions1.ObjectType := TObjectType.MicrosoftOfficeDrawing;
  Chart1ShapeOptions1.ShapeName := 'Rectangle 1';
  Chart1ShapeOptions1.RotateTextWithShape := true;
  Chart1ShapeOptions1.ShapeThemeFont := TShapeFont_Create(TFontScheme.Minor, TDrawingColor.FromTheme(TThemeColor.Light1));
  Chart1ShapeOptions1.Print := true;
  Chart1ShapeOptions1.Visible := true;
  Chart1ShapeOptions1.ShapeGeometry := '<?xml version="1.0" encoding="utf-8" standalone="yes"?><a:shapeGeom xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:prstGeom'
  + ' prst="rect"><a:avLst /></a:prstGeom></a:shapeGeom>';
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFilled, true);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineColor, 65535);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineWidth, 12700);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowColor, 16777215);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowOpacity, 0);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.wzName, 'Rectangle 1');
  Chart1.AddAutoShape(Chart1ShapeOptions1);

I kind of feel that the ShapeOptions.ShapeFill property should work.. maybe something like this

  Chart1ShapeOptions1 := TShapeProperties_Create();
  Chart1ShapeOptions1.Anchor := TClientAnchor.Create(true, TFlxAnchorType.MoveAndResize, 583, 0, 597, 0, 1072, 0, 1207, 0);
  Chart1ShapeOptions1.ShapeType := TShapeType.Rectangle;
  Chart1ShapeOptions1.ObjectType := TObjectType.MicrosoftOfficeDrawing;
  Chart1ShapeOptions1.ShapeName := 'Rectangle 1';
  Chart1ShapeOptions1.RotateTextWithShape := true;
  Chart1ShapeOptions1.ShapeThemeFont := TShapeFont_Create(TFontScheme.Minor, TDrawingColor.FromTheme(TThemeColor.Light1));
  Chart1ShapeOptions1.Print := true;
  Chart1ShapeOptions1.Visible := true;
  Chart1ShapeOptions1.ShapeGeometry := '<?xml version="1.0" encoding="utf-8" standalone="yes"?><a:shapeGeom xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:prstGeom'
  + ' prst="rect"><a:avLst /></a:prstGeom></a:shapeGeom>';
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFilled, true);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineColor, 65535);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineWidth, 12700);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowColor, 16777215);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowOpacity, 0);
  Chart1ShapeOptions1.ShapeFill:= TShapeFill_Create(true, TPatternFill_Create(TDrawingColor.FromRgb(255,0,0,nil), TDrawingColor.FromRgb(255,255,0,nil), TDrawingPattern.wdUpDiag));
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.wzName, 'Rectangle 1');
  Chart1.AddAutoShape(Chart1ShapeOptions1);

I have tried it in various places, I have removed the initial setting of the Filled property in ShapeOptions.SetValue.

I just can't seem to work out where this goes.

I have traced into the code for the ShapeFill setter method and it fires OK with a value from TShapeFill_Create.

When it comes to the generation of the xml for the xlsx file I never get a hit in the code that writes a pattern fill.

I am probably missing something really obvious. Sorry

Martel_David · November 8, 2021, 5:47pm

OK. More info. I looks like I am a little closer. I was not expecting to have to fill in fill color and fill background colour in the shape properties lst as well as creating the fill pattern, but I don't have any issue with doing that. Once I added these I got very close... hard coding stuff to make it work, but here is the code that I believe ought to work...

  Chart1ShapeOptions1 := TShapeProperties_Create();
  Chart1ShapeOptions1.Anchor := TClientAnchor.Create(true, TFlxAnchorType.MoveAndResize, 583, 0, 597, 0, 1072, 0, 1207, 0);
  Chart1ShapeOptions1.ShapeType := TShapeType.Rectangle;
  Chart1ShapeOptions1.ObjectType := TObjectType.MicrosoftOfficeDrawing;
  Chart1ShapeOptions1.ShapeName := 'Rectangle 1';
  Chart1ShapeOptions1.RotateTextWithShape := true;
  Chart1ShapeOptions1.ShapeThemeFont := TShapeFont_Create(TFontScheme.Minor, TDrawingColor.FromTheme(TThemeColor.Light1));
  Chart1ShapeOptions1.Print := true;
  Chart1ShapeOptions1.Visible := true;
  Chart1ShapeOptions1.ShapeGeometry := '<?xml version="1.0" encoding="utf-8" standalone="yes"?><a:shapeGeom xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:prstGeom'
  + ' prst="rect"><a:avLst /></a:prstGeom></a:shapeGeom>';
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineColor, 255);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineWidth, 12700);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowColor, 16777215);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.shadowOpacity, 0);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFilled, true);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fillType, ord(TFillType.Pattern));
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.FillColor, 255);
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.FillBackColor, 0);
  Chart1ShapeOptions1.ShapeFill:= TShapeFill_Create(true, TPatternFill_Create(TDrawingColor.FromRgb(255,0,0,nil), TDrawingColor.FromRgb(255,255,0,nil), TDrawingPattern.wdUpDiag));
  Chart1ShapeOptions1.ShapeOptions.SetValue(TShapeOption.wzName, 'Rectangle 1');
  Chart1.AddAutoShape(Chart1ShapeOptions1);

But! It still doesn't work. I think I have traced the culprit since the file that gets created is correct except for the pattern being "pct10" instead of "wdUpDiag". I put a break point in

_UEscherRecords.TEscherOPTRecord.pas in the method
function TEscherOPTRecord.GetPatternBrush(). At the end there is the following

   //Sadly the pattern type doesn't seem to be saved in the file. It is given by the bitmap,
   //and we could figure out the pattern from the bitmap, but seems too unreliable.
   //So instead we use a pattern which mostly uses the main color.
  Result := TShapeFill_Create(HasFill(DefaultHasFill), TPatternFill_Create(ColorFg, ColorBk, TDrawingPattern.pct10));

and you can see where pct10 is hardcoded. I stepped in to the call and once the fill gets created, I manually edited the field in the debugger and let the code finish running. My Excel file loaded with a nice rectangle with the correct fill colours and patterns, so YAY!

I don't believe I can change this behaviour without changing the code and recompiling (unless I am missing something about why I am getting a TEscherOPTRecord, an odd name, and maybe another clue???). I may do at some point if needed, but if this is the issue and it gets fixed in the next release or 2, then I will probably leave it.

I can also confirm by putting a break point as I read back the file I just created above with the correct pattern enumerator, that flexcel does correctly pick the pattern value from the file. Hopefully this is useful.

adrian · November 9, 2021, 10:25am

Hi,
Sorry for the delay, yesterday ms released .NET 6 and we are kind of on a hurry to finish testing on it.
About this issue, I have to look at it in more detail, but I guess it has to do with the same API targetting xls and xlsx, while patterns in xls and xlsx are incompatible. That is, a xlsx pattern in a xls file is saved as a bitmap, so we lose the information about what pattern it really was. Same with a xls pattern in a xlsx file. So FlexCel tries its best to handle both, but as mentioned in the comment, to know what pattern a bitmap represents, we should do some analysis of the pixels of the bitmap and see what pattern they are trying to form.

I'll look to see if it is possible to unify both xls and xlsx in a more complete way once we get a little less busy :)

Martel_David · November 9, 2021, 11:00am

Adrian,

Thanks. No need to apologize. You are always quick to respond, and organizing priorities is never easy, especially when all your customers probably feel their problem is "the most important".

I understand the difficulty of trying to dealing with xls and xlsx seamlessly.

Some more comments (for when you do get a chance to look at it).

For our use case we are actually not interested in being able to read back and change the pattern (though I know you have consider all use cases). This is solely creating files from scratch and we are happy to stick with xlsx. The problem line of code does seem to make statements as though it is loading back from a file, but this is not true. It is the code that wants to get some info on the fill just before writing it to disk. We create the object from scratch, it is never on the disk. The issue seems to be that the pattern enumeration in xlsx is stored as an attribute rather than as a value in the ShapeProperties dictionary. When about to write out the xml, the code reads things like the fill color and the background fill colour which are in the shapeproperties dictionary. The pattern enumeration is not in the dictionary. If I knew a bit more about the internal I might have been able to come up with a possible solution, but at this point it is probably better for me to leave it to you.

Thanks once again.

(Now on to the more sophisticated bits of creating text boxes with per character formatting, I have got what looks like working code frmo API mate so I think this should be a bit more straightforward, though obviously I would like to wrap it in some code to make it a little cleaner to our programmers... ).

adrian · November 9, 2021, 1:07pm

The problem is actually ShapeProperties. ShapeProperties is an xls (not xlsx) struct, and as we are discussing, has a different pattern type. We can’ really add attributes to the ShapeProperty, because it is the xls structure that will be persisted to disk, and so it can’t have things that will crash Excel when reading the xls file. That’s why it stores the extra information (the xlsx pattern) in a different place, and that is what it tries to get before writing it to the disk. For the case where you open a file, modify it and save it, it will get the actual pattern from the extra information, and not save the ShapeProperties value in that place, which would have a broken pattern. But I am not sure there is a way to do it from the API, since you can only modify ShapeProperties, and ShapeProperties doesn’t have xlsx patterns.

There is also a problem of backward compatibility API: ShapeProperties exists since the beginnings in 2003, and it was ok for xls files. But we don’t want to add a new incompatible ShapePropertiesXlsx (Which wouldn’t even be a dictionary anymore), because if so old code would stop working. So we painfully convert between the ShapeProperties in xls and xlsx, and some border cases like the pattern which can’t be converted aren’t. The approach works almost flawlessly for most cases, and you can have the same API to write xls and xlsx files, but some things don’t work. Charts is the biggest one (that’s why we don’t support adding charts in xls), because the chart engine was completely rewritten in 2007 and not even Excel converts xls to xlsx files faithfully. Patterns are another issue, because patterns aren’t that much used anymore (they have that ‘90s look when patterns were cool), and so the people doing Excel didn’t care about being more compatible. It is mostly in not-so-used stuff that you can get some differences, but most of the stuff just converts seamlessly between xls and xlsx, and that’s why we think there is value to have an unified API, even if in this case you are writing ShapeProperties, and xlsx files don’t have them so each property is manually translated.

As said, we’ll look on how we can improve the pattern handling, maybe some property separated from ShapeProperties.

Martel_David · November 9, 2021, 2:07pm

Just a thought. You say that the ShapeProperties are really just xls. Doews this mean that by using them we are forcing it to an xls-compatible mode (I got that code from APImate). If there is a way of doing it the simply uses the IFill stuff without properties and circumvents this, then I would be fine with it. But obviously that may just a simple-minded suggestion.

Anyway thanks

adrian · November 9, 2021, 2:51pm

It is not really a xls-compatible mode, it is more of a unified API. Xlsx changed everything, so to provide a Xlsx-only API, we would have to provide a duplicated component. You would have a SetXlsCellFormat and SetXlsxCellFormat methods, and so for everything. But in reality, changes from a high-level are far less, so there is no need to repeat every method. But in reality, while internally those are completely different, externally they are similar, because Excel had to keep compatibility to Excel. So in most we convert both to a common struct, and there is only a SetCellFormat method that will set the corresponding stuff in both xls and xlsx. As said, this works quite well except for some border cases (like textures in autoshapes). For those border cases, we have different structures that keep the xlsx values when you open and save a xlsx file, and normally a way for the API to set those values. But it looks like for picture patterns, we just didn't provide a way in the API, so I'll look to provide one.

ShapeProperties comes from xls (and existed way before xlsx was a thing), but it is also our external API to set shape properties. There isn't a duplicated way to set xlsx properties.

adrian · December 11, 2021, 6:52pm

Hi,
The just-released 7.13 changed how APIMate reports the fills (now it will show you to create xlsx-like fills), and now patterns should be supported. Of course the old way of using ShapeOptions will still work, but the new system of adding fills and lines is better

Martel_David · December 11, 2021, 11:33pm

Looking forward to trying this out next week when I can download the update

Martel_David · December 13, 2021, 10:30am

@adrian . Tested and working perfectly in our application.

Thanks