?
Solved

Some useful command before export to excell...

Posted on 2005-03-07
12
Medium Priority
?
169 Views
Last Modified: 2010-04-05
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
Comment
Question by:CodedK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 17

Assisted Solution

by:TheRealLoki
TheRealLoki earned 332 total points
ID: 13481471
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
 
LVL 17

Assisted Solution

by:mokule
mokule earned 328 total points
ID: 13481650

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

mokule
0
 
LVL 11

Accepted Solution

by:
calinutz earned 1000 total points
ID: 13481964


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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:calinutz
ID: 13481975
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
 
LVL 11

Expert Comment

by:calinutz
ID: 13481996
!!! 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
 
LVL 16

Author Comment

by:CodedK
ID: 13482135
@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
 
LVL 6

Assisted Solution

by:Sergio_Hdez
Sergio_Hdez earned 340 total points
ID: 13486423
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
 
LVL 16

Author Comment

by:CodedK
ID: 13487907
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
 
LVL 16

Author Comment

by:CodedK
ID: 13487915
I'll add points so all can be happy.
0
 
LVL 6

Expert Comment

by:Sergio_Hdez
ID: 13494226
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
 
LVL 11

Expert Comment

by:calinutz
ID: 13495085
>>I'll add points so all can be happy
:) I'm happy :)
0
 
LVL 16

Author Comment

by:CodedK
ID: 13496867
Thanks Sergio_Hdez ... I did that and worked :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question