Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Some useful command before export to excell...

Hi.
In my app i export to excell some numbers. In the 2nd column as shown in the code below...
I want to know how i can set the 2nd column properties to be number with zero decimal places.
And in the 1st column how to change the font.

procedure TForm2.Image9Click(Sender: TObject);
var
  XL, Sheet: Variant;
  i: integer;
begin

if MessageDlg('Command : Export all entries to Excell.'+#13#10+
  'Continue ?',mtConfirmation, [mbYes, mbNo], 0) = mrYes then
Begin
XL := CreateOleObject('Excel.Application');
  try
    if XL.Workbooks.Count = 0 then begin
      XL.SheetsInNewWorkbook := 1;
      XL.Workbooks.Add;
    end else
      XL.ActiveWorkbook.WorkSheets.Add;
    // show Excel
    XL.Visible := True;
    Sheet := XL.ActiveSheet;
    Sheet.Name := 'Excel Test';
    try
      i := 1;
      Table1.First;
      while not Table1.EOF do begin
        Sheet.Cells[i, 1].Value := Table1.FieldByName('Name').AsString;
        Sheet.Cells[i, 2].Value := Table1.FieldByName('Number').AsString;
        i := i + 1;
        Table1.Next;
      end;
    finally
      XL.ActiveWorkbook.SaveAs
      (FileName:= 'Numbers.xls');
    end;
  finally
    XL.Quit;
    ShowMessage('Finished..File saved as Numbers in My Documents Folder.');
    Beep;
  end;

end;
End;

Thanks in advance.
0
CodedK
Asked:
CodedK
  • 4
  • 4
  • 2
  • +2
4 Solutions
 
TheRealLokiSenior DeveloperCommented:
Sorry, I do not know the answer to this, however, I got around it by having a "base" xls file with the columns formatted correctly which I then "opened" and populated. The xls was embedded in my application. I'm sure there is a more graceful solution, but I just don't know it :-D
0
 
mokuleCommented:

The best method to solve such problems is to record macro in Excel with appropriate commands and then look what was saved :)

mokule
0
 
calinutzCommented:


Try the following:
procedure TForm2.Image9Click(Sender: TObject);
var
 xls, wb, Range: OLEVariant;
 arrData: Variant;
 i,j:integer;
begin
 {create variant array where we'll copy our data}
 arrData := VarArrayCreate([1, ks.RowCount+2, 1,ks.Columns.Count], varVariant);
 {fill array}
 for i := 1 to ks.RowCount do
   for j := 1 to ks.Columns.Count do
 begin
     arrData[i, j] := ks.Cells[j-1, i-1];
//     arrdata[2,j]:=j;
     end;
 {initialize an instance of Excel}
 xls := CreateOLEObject('Excel.Application');
 {create workbook}
 wb := xls.Workbooks.Add;
 {retrieve a range where data must be placed}
 Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
wb.WorkSheets[1].Cells[ks.RowCount, ks.Columns.Count]];
 {copy data from allocated variant array}
 Range.Value := arrData;

//xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
//here comes the formating you requested

wb.WorkSheets[1].Columns['A:A'].Select;
wb.WorkSheets[1].Columns['A:A'].NumberFormat:='#,##0';

wb.WorkSheets[1].Columns['B:B'].Select;
wb.WorkSheets[1].Columns['B:B'].Font.Name:='Tahoma';
wb.WorkSheets[1].Columns['B:B'].Font.Size:=15;

 {show Excel with our data}
 xls.Visible := True;

end;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
calinutzCommented:
It works fine for me
Ah, yeah... ks is a kStringGrid... you can use anything else to populate the array that will be sent to excel. I used the kStringGrid
Cheers
0
 
calinutzCommented:
!!! And it is not a fast way to export to excel the way you are doing it. It is actualy the slowest.
The fastest way is to use an array like I described in my procedure.
 It is a known fact that this method is faster than the cell by cell asignation.
Cheers
0
 
CodedKAuthor Commented:
@TheRealLoki.

:) Its a nice thought. Creating a ready excell file with every formatting that i need and then embed it !!!
Its really a useful trick so i dont have to ask everytime something else comes up..

Though the question was that commands ... it wont be fair... :/

@Calinutz.
Thanks man...
I'll try that tomorrow. Its late at night here.
The funny thing is that excell opens really quick, like a flash, even on my old PC.
I tried to understand what XP does to excell to make it open so slow...

Sleep(2000);
Do nothing for 4000;

Lol...

Anyway, it just a portion in a large app and really dont have what it takes at the moment to change even a bit... :)
But i'll try it..
So thanks all of u i'll get back tomorrow.
Thanks
0
 
Sergio_HdezCommented:
I have modified the while loop so the formating is done cell by cell before filling the data:

      while not Table1.EOF do begin
        //Set the cell formatting...
        Sheet.Cells[i, 1].Font.Name:= 'Arial Black';
        Sheet.Cells[i, 2].NumberFormat:= '0';
        //Put the data inside...
        Sheet.Cells[i, 1].Value := Table1.FieldByName('Name').AsString;
        Sheet.Cells[i, 2].Value := Table1.FieldByName('Number').AsString;
        i := i + 1;
        Table1.Next;
      end;

Calinutz solution seem similar to better, so just try this if Calinutz doesn't work well for you.
0
 
CodedKAuthor Commented:
Thanks everyone.
Calinutz solution worked... :)

Zero decimal is not wb.WorkSheets[1].Columns['A:A'].NumberFormat:='#,##0';
but  NumberFormat:= '0';
as Sergio_Hdez  point out.

Do u know where i can find some commands... a help file maybe for Excell commands ?
I tried to do this :

wb.WorkSheets[1].Columns['A:A'].Width:=45;  but didnt worked...
I want to set the width and height size to Auto.

It seems that for a fully formatted excell sheet a have to ask 10 questions.
So TheRealLoki and mokule provided a solution for questions to come :)

I'll have to split points because u all helped me.

Thanks.
0
 
CodedKAuthor Commented:
I'll add points so all can be happy.
0
 
Sergio_HdezCommented:
CodedK, it is very easy to know how to make anything in excel from delphi, I didn't know the answer to your question when I first read it, I asked it to excel!

My method is very simple: Open Excel, record a macro doing what you want to achieve from delphi, stop the macro recording and go to the macro editor to see how excel coded it, then translate it to delphi.

In the translation, somethings have to be changed, for instance, in excel macro you will see "cells(2,3)" but in delphi it translate to "cells[1,2]". I just try a couple of different translation until it runs, it is not dificult at all.

The only real problem comes when some commands are not allowed from a ole connection... if delphi says it to you at run time, then may be you used () instead of [], or you have to find a different way to do the same thing... using cells[] instead of columns[], or using ActiveSheet.something instead of ActiveWorkBook.something or XLS.something (trying those 3 options usually clear any problem)... but finally everything can be translated in some way to delphi.

Working this way, I managed to work out a huge number of "translations" from excel macro VB to delphi, even I have done it with the free suite OpenOffice, so now i have some delphi functions to manage excel and openoffice, you can learn some tricks reading this .pas here: http://www.oooforum.org/forum/viewtopic.phtml?t=8878

0
 
calinutzCommented:
>>I'll add points so all can be happy
:) I'm happy :)
0
 
CodedKAuthor Commented:
Thanks Sergio_Hdez ... I did that and worked :)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now