Delphi write data to excel, but provide outline mode ( multiple groups ) previous id 31273869

Good Day

cyberkiwi helped me with this question (31273869) , and it works perfectly, but now it becomes more complicated.

I need to group by multiple columns, please see attached screen shot.

I build my data with a query see image  ( Data generated )

If you look at my code I loop threw all the data, if I found a entry that says TL then I know I must do a sum of the range, but I am not sure how to do the sum for the entrire group.

thanx


with ADOQuery do
  begin
    close;
    sql.Clear;
    sql.Add(sQry);
    open;
    first;
    while not eof do
    begin
      Sheet.Cells(row,1) := trim(FieldByName('Id').AsString);
      Sheet.Cells(row,2) := trim(FieldByName('System').AsString);

      Range         := Sheet.Range['C'+IntToStr(row)];
      Range.Formula := FormatFloat('#0.00',FieldByName('amount').AsFloat);


      //do some for Line total
      if trim(FieldByname('RecType').AsString) = 'TL' then
      begin
         Range.Formula :=  Format('=SUM(C%d:C%d)', [lastwrittenrow+1, row-1]);
         lastwrittenrow := row;
      end

      //code for group total ??????????????????????


      Inc(Row);
      next;
    end;
  end;

Open in new window

Excel-sample.JPG
Data-generated.JPG
henryreynoldsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Emmanuel PASQUIERFreelance Project ManagerCommented:
try this
Var
 TCRows:TList; // will contain all row int
 i:integer;
 Formula:String;
begin
 TCRows:=TList.Create;
 with ADOQuery do
  begin
   close;
   sql.Clear;
   sql.Add(sQry);
   open;
   first;
   while not eof do
    begin
     Sheet.Cells(row,1) := trim(FieldByName('Id').AsString);
     Sheet.Cells(row,2) := trim(FieldByName('System').AsString);
     Range := Sheet.Range['C'+IntToStr(row)];

      //do some for Line total
     if trim(FieldByname('RecType').AsString) = 'TL' then
      begin
         Range.Formula :=  Format('=SUM(C%d:C%d)', [lastwrittenrow+1, row-1]);
         lastwrittenrow := row;
         TCRows.Add(Pointer(row));
      end else
     if trim(FieldByname('RecType').AsString) = 'TC' then
      begin
       if TCRows.Count>0 Then
        begin
         Formula:='=SUM(';
         for i:=0 to TCRows.Count-1 do Formula:=Formula+Format('C%d;',[Integer(TCRows[i])]);
         Formula[Length(Formula)]:=')';
         Range.Formula :=  Formula;
         TCRows.Clear;
        end; 
      end 
     else Range.Formula := FormatFloat('#0.00',FieldByName('amount').AsFloat);
     Inc(Row);
     next;
    end;
  end;
 TCRows.Free;
end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
henryreynoldsAuthor Commented:
Hi

I am not at the office but I will try it still tonight or tomorrow, thank you very much
0
henryreynoldsAuthor Commented:
Hi I ambusy testing it
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

henryreynoldsAuthor Commented:
Hi epasquier

Thank you for your great help, epasquier how can I show only the final total for each group. I want the final total for each group, and then when the user click on the plus it must only expand that selected group.

I have included a screen shot and my excel file.

Thanx
Output.xlsx
screen-shot.JPG
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
you have a problem in your formulas. For ex. C20 should be :
=SOMME(C16;C19)
where it is :
=SOMME(C16:C19)

check again the line 32 in what i posted you


for i:=0 to TCRows.Count-1 do Formula:=Formula+Format('C%d;' // <== ; not :
,[Integer(TCRows[i])]);

Open in new window

0
Emmanuel PASQUIERFreelance Project ManagerCommented:

another error (my fault this time), C16 is summing also the last group total
=SOMME(C6:C15)
it should be
=SOMME(C7:C15)

and insert in this same block :

TCRows.Clear;
lastwrittenrow := row; // <== 

Open in new window

0
Emmanuel PASQUIERFreelance Project ManagerCommented:
as for you need to have expandable/collapsable groups, i suppose you are referring to the blue gutter in the left. They are subtotals created with Data menu, and they are not corresponding to your datas/formulas anymore. I don't know how to fix that with excel. With Delphi, you could create a function scanning the same query and hiding all corresponding lines that are not totals
procedure HideDetails(ShowSubTotals:Boolean=False);
Var
 Row:integer;
 RowType:String;
begin
 Row:=1;
 with ADOQuery do
  begin
   first;
   while not eof do
    begin
     RowType:=trim(FieldByname('RecType').AsString);
     Sheet.Rows(row).Hidden:=
       Not( (RowType='TC') 
            Or (ShowSubTotals And (RowType='TL')) );
     Inc(Row);
     next;
    end;
  end;
end;

Open in new window

0
henryreynoldsAuthor Commented:
Hi

if I change the following
you have a problem in your formulas. For ex. C20 should be :
=SOMME(C16;C19)
where it is :
=SOMME(C16:C19)

then I get an excel error


error.JPG
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
now that is strange. I works fine for me, with excel 2003 (french version)

Do it manually with excel to check the syntax : type
=SUM(
then select one cell, and a second one HOLDING Ctrl key. that is not the sum of a continuous range but of the listed cells. maybe in english version it's not ; but , ?
0
henryreynoldsAuthor Commented:
HI epasquier

I it seems it must be :.

Thanx a lot for all your help.

Regards
0
henryreynoldsAuthor Commented:
Thank you
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
well, thanks M$ for the urgent need of changing such syntax details from one country to others...
0
henryreynoldsAuthor Commented:
Hi

I still dont understand why I must use this SUM(C1:C38) and you must use SUM(C1;C38), do you really think M$ would have done it for diffrent languages
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
SUM( C1 : C38 ) will sum all cells between C1 & C38 , for you and me
SUM( C1 ; C38 ) will sum only the 2 cells (for me)

> do you really think M$ would have done it for diffrent languages
they have already changed the name of the functions, which is understandable for such a public tool. Still I would have preferred if they have done it so that english version of the functions were available WITH the localized language.
Since . and , can have different signification in numbers from one country to the other, it's also understandable that they changed some of the syntax. But I don't understand why ; do not work for you. And I'm still unsure which you should use to add separate cells, not range. I know that it's not :
0
henryreynoldsAuthor Commented:
Hi epasquier

I need to ask n related question about this question, I will create a new question id, but I just would like know if I will get a solution.

When my data display, I need to show only the TG groups, and when you click on the + then I need to see all the sum of TL groups.

Example

+1 Group total 1 500
+2 Group  total 2 200

now click on +
+1 Total A        250
+1 Total B         250
  1 Total Group 500

now if you click on the + again then the detail must show for A or B.

Do you think this is possible ?
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
the + as you say is in excel, and as I said previously it's not very clear how it is managed. Doing such a function in Delphi, reacting on some kind of Delphi event such as a button, yes that would be very possible, only knowing the group & sub group names. Or getting those information just by reading in excel which cell/row is selected (like 'Total A')
I don't understand yet why you mix Delphi with Excel user interface like that. Do you embed excel in a delphi application ?
In your next question, try to show an image of your Delphi application interface, so that we understand what is the relation between those 2, and how the user is supposed to work with them.
0
henryreynoldsAuthor Commented:
Hi epasquier

The only purpose of this routine is to output an excel file. This excel file is then used to by
other people for controlling sales. When they open the excel file they want to see a summary of all groups and when they open a group they want to see a summary of all sections of that group.

If I take this report generated as it is now, and I open it in excel and I select all rows and columns and click on Hide details, then it works 100%. I thought it was possible in Delphi to select all and then to add the following.

//Sheet.Range['A1:B8'].Select;
Excel.Selection.AutoOutline;
Sheet.Outline.ShowLevels(1);
--- sheet.hidedetails; //something like this

I hope I explain myself better
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
ok, yes i understand now how this Auto outline works now in excel. It has a strange name in french that never I would have associated to this functionality, therefore never used it.

You need to clear the former outline to be sure before AutoOutline it, and more important : the hiding of details does not work well if the sum or grouping is only concerning one cell. Don't ask why, it's an excel bug and you can't do a thing about it (except maybe defining the outines manually, which promises to be painful). But yes, if you have always more than one detail in each sub group and more than one sub group in each group, then autooutline will work perfectly.

Best way to solve excel automation is to find a way to do what you want manually, record the operation, and look in the VB code generated the function names you want. They will be the same in Delphi
0
henryreynoldsAuthor Commented:
Hi I lost you at this part " record the operation, and look in the VB code generated the function names you want " How can I access this part
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
It's all under Tools -> Macro menu
- Record Macro
- Visual Basic Editor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.