I have an Excel worksheet with several checkboxes. Each checkbox has a name assigned.
Now I want to set the state of a Checkbox by the following code. Unfortunately, the IShapeProperties.ShapeName property is empty for each Checkbox. What can I do?
procedure Local_SetCheckBoxState(pxXlsFile : TXlsFile;
ptCheckBoxName : String;
pxCheckBoxState : TCheckboxState);
var
liI : Integer;
lxShapeProps : IShapeProperties;
begin
for liI := 1 to pxXlsFile.ObjectCount do
begin
lxShapeProps := pxXlsFile.GetObjectProperties(liI, False);
if (lxShapeProps.ObjectType = TObjectType.Checkbox) and
(lxShapeProps.ShapeName = ptCheckBoxName) then // FIXME FlexCel not set!
begin
pxXlsFile.SetCheckboxState(liI, '', pxCheckBoxState);
break;
end;
end;
end;
Hi, The only issue I am aware with this is that you need to have "real" names. That is: "Checkbox 1", "Checkbox 2", and the names Excel gives to them when you don't name them are not actual names and not stored in the file. When an object has no name, Excel shows it as "object n" but FlexCel will report the name empty.
Other than that, I don't see why your code wouldn't work. It can be made easier because you can use "@name" as objectpath to directly find any object, so you could just do: pxXlsFile.SetCheckboxState(-1, '@' + ptCheckBoxName, pxCheckBoxState);
But well, as said, I see nothing wrong with your code, as long as the objects have real names. I made a little test app here: http://www.tmssoftware.biz/flexcel/samples/checkboxes.zip which uses the "@" method and it works as expected. But if you also copy and paste your code in the app and replace the code by:
program Project15;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils,
Classes,
VCL.FlexCel.Core,
FlexCel.XlsAdapter;
procedure Local_SetCheckBoxState(pxXlsFile : TXlsFile;
ptCheckBoxName : String;
pxCheckBoxState : TCheckboxState);
var
liI : Integer;
lxShapeProps : IShapeProperties;
begin
for liI := 1 to pxXlsFile.ObjectCount do
begin
lxShapeProps := pxXlsFile.GetObjectProperties(liI, False);
if (lxShapeProps.ObjectType = TObjectType.Checkbox) and
(lxShapeProps.ShapeName = ptCheckBoxName) then // FIXME FlexCel not set!
begin
pxXlsFile.SetCheckboxState(liI, '', pxCheckBoxState);
break;
end;
end;
end;
var
xls: TXlsFile;
sp: IShapeProperties;
begin
xls := TXlsFile.Create('..\..\checkboxes.xlsx', true);
try
local_SetCheckboxState(xls, 'myCheckbox', TCheckboxState.Checked);
xls.Save('..\..\result.xlsx');
finally
xls.Free;
end;
end.
It still works. Can you send me a modified example that you can't get to work, or some file where it doesn't work so I can take a look? You can send it to adrian@tmssoftware.com if it has any info you don't want to show in the forums.
Thank you for your quick answer. Ok, the checkboxes in my Excel file had no "real" names. Once they've got real names, my original method with access by index and the @ object path syntax work. The only thing left is, that we have to adapt the Excel files, since with Excel Ole Automation, checkboxes could also be identified by their "internal" name.
If you don't have internal names, one way to get them might be with the index: "Checkbox 3" should be object 3. But I wouldn't trust this order, it isn't documented anywhere and I am not really sure it is what Excel is using. They know the exact algorithm they used to name those boxes, but we don't.
Now, there is a simple way to fix those names if you don't want to review all the files. This "internal name" thing where objects don't really have a name an Excel shows them as "object n" only happens in xls files (and I consider it a bug in Excel actually). In xlsx, all objects are saved named, as it should be.
So if you open your files in Excel, save them as xlsx, close them, then open the xlsx again in Excel and save them back to xls, all objects will be given real names. "Check Box 3" will actually be named "Check Box 3" in the xls file and not with an Empty string.
I would still recommend to use meaningful names, like "CusomerIsVIP" instead of "Check Box 3". But as a quick workaround, saving hte file to xlsx and then back to xls (with Excel, not FlexCel) should name the objects.
You cannot relate the index with the internal name. since objects, especially those with a lower index can be deleted. Moreover, my Excel file has an .xlsx extension, but the checkboxes have no real name.
Indeed, objects can be deleted, and also moved in the z-order position. But in xls files, this just means that the objects are renamed, and "checkbox 2" goes to be "checkbox 1". Excel isn't magic either, and if the name isn't saved anywhere in the file (and it isn't for xls files), it will just have to concat the text "Check Box" with the object index. This would be another reason not to use those autogenerated names and use proper meaningful names: Autogenerated names are not guaranteed to be the same if you edit the file.
Now, about xlsx, in xlsx from Excel 2010 and up names are indeed stored in the file. (and this is why you won't see anymore names changing from Check Box 3 to CheckBox 2 when you delete or change the z-order of an object. As I imagine you are not using Excel 2007 what I guess is happening is as follows: In Excel 2007, probably because they didn't got enough time to do it properly, controls are saved in a "legacy drawing" part (yes, xlsx was brand new in 2007, and it already had "legacy" parts). This legacy part behaves like xls, in that it won't save the name of the control unless it was a real name. But it also had a lot of drawbacks: It isn't proper xml so xml readers might not be able to parse it, it uses device dependent pixels instead of inches or other device independent units so controls will look wrong in high dpi, and you can't for example group a control with a shape (which you could in xls). Also controls are always above shapes because they are in a different stream.
In Excel 2010 and up, they fixed all that stuff, by saving objects in 2 different parts of the xlsx file: One legacy part so Excel 2007 can still read the file, and one new part which fixes the issues above and that only Excel 2010+ will read.There are checksums for the parts so If you create a file in Excel 2010 and then edit it in 2007 (which will only modify the legacy part, but not the Excel 2010 part), you can check the checksum was broken and ignore the Excel2010 part.
I've just checked and the name is saved as "Check Box n" inside the new Excel 2010 parts, but not in the legacy part. FlexCel up to 6.8 behaves as if it was Excel 2007, reading and writing only the legacy part. As in the legacy part names are only stored if they are "real", FlexCel won't find those. But, in 6.8.5 we have rewritten everything so now we behave like Excel 2010+: We will read the Excel 2010 part if it exists and give priority to it. As the Excel 2010 part saves the name of the shape, 6.8.5 should be able to read those "Check box n" controls.
You can email me to adrian@tmssoftware.com mentioning your registration email and I can send you a link to get 6.8.5. My guess is that it will be able to read those "check box n" controls. But even so, my advice is still to give your controls meaningful and real names.