How to create Validation List in Excel using XLSReadWriteII5

[rahulsharma1991]

Hello All,
I am trying to create validation list in Excel file (.xlsx) using XLSReadWriteII5 Component. I am using Delphi6. I have managed to install this component in Delphi 6 environment and now facing difficulty in creating Validation List. I am working on Demo version and download the setup from axolot site. As per my knowledge I am working on version 50.20.41. I have seen that there are some validation related issue fixed in the version 50.20.13 in the change log. How to download specific version of XLS Suit. Currently we are evaluating the product.
This is my code.

Code: Select all

var
  LwWorksheet : TXLSWorksheet;
  LValidation: TXLSDataValidation;
  LCell  :TXLSCell;
begin
  LwWorksheet:= XLS.Add;
  try
    LwWorksheet.Name:= 'This is a test';
    LwWorksheet.AsString[1,1] := 'This is a test';
 
   //Populate dummy data .
    LwWorksheet.AsString[3,2] := 'A';
    LwWorksheet.AsString[3,3] := 'B';
    LwWorksheet.AsString[3,4] := 'C';

    LwWorksheet.AsString[2,2] := 'A';
    LwWorksheet.AsString[2,3] := 'B';
    LwWorksheet.AsString[2,4] := 'C';

    LwWorksheet.AsString[4,2] := 'A';
    LwWorksheet.AsString[4,3] := 'B';
    LwWorksheet.AsString[4,4] := 'C';

    LValidation:= LwWorksheet.Validations.Add;
    LValidation.Areas.Add(5,5);
    LValidation.Formula1:='$B$2:$B$4';
   
   // I don't understand the purpose of Sqref.add and I simply passed some parameter in it.
    LValidation.Sqref.Add(5,5);
    LValidation.Type_:= x12dvtList;
    LValidation.ShowDropDown:= True;
    LValidation.ShowInputMessage:= True;
    LValidation.AllowBlank:= False; 
  finally
    LwWorksheet.Free;
  end;//try...finally...

I have also added the same question in Stackoverflow. Please refer the Url.
http://stackoverflow.com/questions/2806 ... adwriteii5
Where to find details documentation/Help of XLSReadWriteII5 component. ? Thanks in advance.

[mwhiting]

I'm using version 50.20.41:

As Lars hasn't answered and I had to get this working for a project I'm on, here is my solution:

The xpgParserXLSX unit has a lot of TODO comments and looks unfinished. The TCT_DataValidations.WriteAttributes procedure below needs to be replaced with the following.

Code: Select all

procedure TCT_DataValidations.WriteAttributes(AWriter: TXpgWriteXML);
begin
  if FDisablePrompts <> False then 
    AWriter.AddAttribute('disablePrompts',XmlBoolToStr(FDisablePrompts));
  if FXWindow <> 0 then 
    AWriter.AddAttribute('xWindow',XmlIntToStr(FXWindow));
  if FYWindow <> 0 then 
    AWriter.AddAttribute('yWindow',XmlIntToStr(FYWindow));
  //Added by MCW  1/28/2015 (Inclding commenting out if FCount <> 0 then...)
  if FDataValidationXpgList.Count <> 0 then
    AWriter.AddAttribute('count',XmlIntToStr(FDataValidationXpgList.Count));
//  if FCount <> 0 then
//    AWriter.AddAttribute('count',XmlIntToStr(FCount));
end;

FCount is set, but it appears to be set to the count attribute, which you can see from the code above creates a chicken and egg problem.

The code below is what I am using to correctly generate my xlsx.

You will need Xc12DataWorksheet5 in your uses clause for the definition of x12dvtList.

Code: Select all

  with XLS[0].Validations.Add do begin
    Areas.Add(1,1, 2, 1); //This will set the Sqref to "B2:C2"
    Type_ := x12dvtList;
    Error := 'Invalid entry. Please select the type of data';
    ErrorTitle := 'Status Error';
    Prompt := 'Select type of data to show.';
    ShowInputMessage := true;
    ShowErrorMessage := true;
    //ShowDropDown false results in this showing, it appears this is a bug in Excel as all the other options in the xml file show when 1 but this shows when not included.
    ShowDropDown := false;
    Formula1 := '"% Differences,Old Version,New Version"';
  end;

I've written Formula1 as a comma separated list (which has to be enclosed in double quotes "). When using cell references I think you should not use the double quotes.