Sorting with Flexcel

FLX TMS FlexCel for VCL & FMX
1

Hi, 


That's would be normally my last question and I will achieve my project.

I have to sort some questions, with first the family of question, and second a CRC32 (that could be anything else, just looking as randomly chose but in fact everytime in the same oder)

Thus i wrote ;

 xls.Sort(
      null,
      true, //Normally we want to sort the rows in the range. But we might want to sort columns and we can do it by setting this to false.
      Int32Array.Create(12, 3), // Sort by columns B and C (2 and 3)
      nil,
      nil,
      TSortFormulaMode.ExcelLike // Use the standard comparer.
  );

But i dont understand why, this sort method is overloaded and can't be compiled.

Could you help be to get a code compilable ?

Many thanks, cordially,

Milos
2

Hi, 

Try with


xls.Sort(
      TXlsCellRange.Null,
      true, //Normally we want to sort the rows in the range. But we might want to sort columns and we can do it by setting this to false.
      Int32Array.Create(12, 3), // Sort by columns B and C (2 and 3)
      nil,
      nil,
      TSortFormulaMode.ExcelLike // Use the standard comparer.
  );
3

Hi,


Once more again, many thanks for your help. I have always difficulties with constructors or some identifiers like this one.

Best Regards,

Milos
4

Hi,


I wil never end and I'm a real moron.. I replaced with your code and when executing it, before any toogle point, it raises an exception.
I realize that I was trying to sort lhe first line with the tags too and I replace it with :
5

Hi,

What is the exception you are getting?
I've tried with the code:



procedure TForm26.Button1Click(Sender: TObject);
var
  xls: TXlsFile;
  cell: TCellValue;
  s: string;
  col, row: integer;
begin
  xls := TXlsFile.Create(1, TExcelFileFormat.v2016, true);
  try
    for col := 1 to TCellAddress.DecodeColumn('O') do xls.SetCellValue(1, col, 'Column ' + IntToStr(col));


    for row := 2 to 2000 do
    begin
      for col := 1 to TCellAddress.DecodeColumn('O') do
      begin
        xls.SetCellValue(row, col, Random(1000));
      end;


    end;
    xls.Sort(
      TXlsCellRange.Create('A2:O3000'),
      true, //Normally we want to sort the rows in the range. But we might want to sort columns and we can do it by setting this to false.
      Int32Array.Create(12, 3), // Sort by columns L and C (12 and 3)
      nil,
      nil,
      TSortFormulaMode.ExcelLike // Use the standard comparer.
  );
    xls.Save('..\..\test.xlsx');
  finally
    xls.Free;
  end;
end;

And it works as expected, sorting first by column L and then C.

By the way, you could use:


      TXlsCellRange.Create(2, 1, xls.RowCount, xls.ColCountOnlyData),
instead of 

      TXlsCellRange.Create('A2:O3000'),
To be sure that you are including all rows even if the file gets larger. But this is a side comment, it shouldn't change the exception you are getting.
6

Hi,


Sorry and thanks, I am here with this stupid error I don't trieve.. I am a real idiot.

procedure TForm3.Demarrer_Session;
var i: integer;
begin
  nreponses :=0;
  try
    xls := TexcelFile.Create;   // xls is declared in the general section - never used before
    // if xls is declared locally the error is the same
    xls.Open('c:\examen_delphi\Questions_delphi_mod2.xlsx'); // all cells as Standard
    xls.AllowOverwritingFiles := true;
    xls.ActiveSheet:=1;
    xls.Sort(
      TXlsCellRange.Create(2, 1, xls.RowCount, xls.ColCountOnlyData),
      true, //Normally we want to sort the rows in the range. But we might want to sort columns and we can do it by setting this to false.
      Int32Array.Create(12, 3), // Sort by columns B and C (12 and 3)
      nil,
      nil,
      TSortFormulaMode.ExcelLike // Use the standard comparer.
  );
    i:= 2;
    Repeat
      if (xls.GetCellValue(i, 12)<'201') then Inc(i); // debugging, this line is never executed
    until (xls.GetCellValue(i,12)='201');
    while (i<=xls.RowCount) do begin
      if xls.GetCellValue(i,12)='201' then begin
        Inc(nreponses);
        Poser_Question(i);  // actually only showing the questions ; uses xls without modifying it
        sleep(2000);
      end;
      Inc(i);
    end;
  finally
    Stopper_Session;
    xls.Free;
    Close;
  end;
end;

Obviously, that is "Poser_Questions" that is important, I have to use it 2 times for each family as '201', '202', until "210" and the questions are ended when a time gives 30 minutes for the 20 questions, counting then the score - thus adding more habitual features, as going through the 20 questions, a timer, aso.
But if I can't first fill an array of 20 questions looking as randomly chosen (that's why I want to start on a CRC32 in the second place), do some trivial sort as a Shell-Metzner on this array and CRC32 to avoid showing each family after the other, ..

Actually, I can set a break point anywhere in the code, it is never reached. I add an image of the error I got yesterday, the code was modified following your advices but the result is the same.
Here I declared xls in the general variables, but if i do it inside the procédure it's the same.
The Excel File was filled with CRC32 and the location of each picture (of the questions) in another program, so here all I do is reading it, I doesn't have to write the file when it is sorted. If it helps I can too sort this file in the previous program, if doesn't matter as the present program is only to be used
 to simulate the exam.

920×236 43.9 KB


7

Hi,


Well, I just saw that when I put TXlsFile.Create(1, TExcelFileFormat.v2016, true); instead of what I did before, it works now..
it works now..

Sorry again for my stupid question and many thals again

Best regards,

Milos

8

Hi,

Yes, what happens is that TExcelFile is an abstract class, you shouldn't create instances of it directly.
You can just call TXlsFile.Create; instead of TExcelFile.Create and it will work.