Solved

Excel Server: Why oh why oh why...

Posted on 2000-03-02
7
157 Views
Last Modified: 2012-03-15
Ok experts, anyone fancy stopping me from commiting suicide?

I'm supposed to writing some code to import Excel data via the Excel Server component. Just one problem: I have no idea what is going on.

What I need to do is open a file, be able to extract data, modify the data, replace it, and save the data back out (possibly into another file).There's also the added bonus of possibly needing to create new tables and mess around with deleting/creating rows and columns.

Considering the fact that I don't even know which component I actually need, is there either a fantastic tutorial/demo or is some kindly soul in Ex-Ex land going to give me enough code samples to at least get me started.

Oh I can do without all this today...

The Neil
0
Comment
Question by:TheNeil
  • 4
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
RBertora earned 25 total points
ID: 2577340
Here is a little demo I wrote:

unit ExcellDemo01;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Comobj,ActiveX, StdCtrls, Grids,Excel_TLB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    OpenDialog1: TOpenDialog;
    StringGrid1: TStringGrid;
    Button6: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
   Function CheckIfValidToMakeACall : Boolean;
  end;

var
  Form1: TForm1;
  ExcelApp : Variant;

implementation

{$R *.DFM}

// Open Excell
procedure TForm1.Button1Click(Sender: TObject);
begin
(*  try
    // if Excell is already open then connect to it
    ExcelApp := GetActiveOleObject('Excel.Application');
  except
    // otherwise open up a new connection
    ExcelApp := CreateOleObject('Excel.Application');
  end;*)


  ExcelApp := CoApplication_.Create;

  // Display Excell
  ExcelApp.Visible := True;

(* ok here is how to minimize / maximize Excell
  //The VB constants are not declared in delphi so we need to use the enumerated value for
  //the constant eg xlNormal = 1 where xlNormal is a window state.
  //1 = xlNormal
  //2 = xlMinimized
  //3 = xlMaximized
  ExcelApp.WindowState := 3; // this is maximize
*)

end;


// Open an Excell file
procedure TForm1.Button2Click(Sender: TObject);
begin
  if (OpenDialog1.Execute) then
  begin
    //Opens up the excel template file, file returned from the ini file
    try
      ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
    except on exception do
      begin
        messagedlg('Could not open ' + OpenDialog1.FileName,mterror,[mbok],0);
        exit;
      end;
    end;
  end;
end;

// Put in some values
procedure TForm1.Button3Click(Sender: TObject);
var
  LoopCol : char;
  LoopRow : Integer;
begin
  if CheckIfValidToMakeACall = false then exit;
  For LoopCol := 'E' to 'J' do
  begin
    For LoopRow := 5 to 10 do
    begin
      ExcelApp.WorkBooks[1].ActiveSheet.range[LoopCol+IntToStr(LoopRow)].Value := LoopCol+IntToStr(LoopRow);
    end;
  end;
end;


// Open a new workbook
procedure TForm1.Button4Click(Sender: TObject);
begin
  ExcelApp.WorkBooks.Add;
end;


// get some data
procedure TForm1.Button5Click(Sender: TObject);
var
  LoopCol : char;
  Col : integer;
  LoopRow : Integer;
begin

  if CheckIfValidToMakeACall = false then exit;

  Col := 5;
  For LoopCol := 'E' to 'J' do
  begin
    For LoopRow := 5 to 10 do
    begin
      StringGrid1.Cells[Col-5,LoopRow-5] :=
        ExcelApp.WorkBooks[1].ActiveSheet.range[LoopCol+IntToStr(LoopRow)].Text;
    end;
  inc(Col);
  end;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  ExcelApp.Quit;
end;



// Excell will not allow an ole call if a cell is being edited thus we need to check this
// before making a call
function TForm1.CheckIfValidToMakeACall: Boolean;
var
  CallRejectionTest : String;
begin
  result := True;
  try
    CallRejectionTest := ExcelApp.WorkBooks[1].ActiveSheet.range['A1'].Text;
  except  on exception do
    begin
      messagedlg('Please make sure you are not currently editing a cell in Excell!!',mterror,[mbok],0);
      result := False;
    end;
  end;
end;

end.


you will need to import the Excel8.olb file using project import type library.. this will convert this file into a _tlb.pas file. include that _tlb.pas file in your project.

should work
Rob;-)
0
 
LVL 5

Author Comment

by:TheNeil
ID: 2586867
That seems to beincedibly close to what I want with just one problem...I can't get it to work!

I've pasted your code in, and tried to create the type library but Delphi complains that the several of the types for Excel 8 already exist. I'm using Delphi 5 Enterprise and it already has the server components installed. I'm having trouble understanding how to use them

The Neil
0
 
LVL 7

Expert Comment

by:RBertora
ID: 2586978
Hmm I really can't help more than that as I don't have D5. You will just have to work it out from the D5 help, I hope its good help.. otherwise your'e gonna struggle for a while.
sorry I can't help any further
Rob.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Author Comment

by:TheNeil
ID: 2587103
Sorry Rob but there must be something going on in that switch from Delhi 4 to Delphi 5. I think I have a solution but this isn't an easy job using the Delphi help. If you still want the points then you can have them if you can give me a way to do one of the following (with example code if you can)

1. How do I save my data to a CSV file via Excel (either as a direct function/procedure or using the SendKeys function)

OR

2. How do I get the actual row and column counts (Rows.Count and Columns.Count just return stupid values)

The Neil
0
 
LVL 7

Expert Comment

by:RBertora
ID: 2587178

Hi,

1.

ExcelApp.WorkBooks[1].ActiveSheet.SaveAs('C:\WINNT\Profiles\RBertora\Personal\Book1.csv',6,False);


2. Rows.Count and Columns.Count will return the number of rows and columns that are highlited (ie the selected columns and rows) if nothing is selected/highlited this will return rubbish.

Rob.
0
 
LVL 5

Author Comment

by:TheNeil
ID: 2587224
Thanks Rob,

I didn't know you could just specify the file format in the filename - It all becomes so easy now.

As for the row/column count then nothing would have been selected which is why I was getting 256 columns and 65536 rows.

The Neil
0
 
LVL 7

Expert Comment

by:RBertora
ID: 2587368
thanks and
good luck in your project.
Rob.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now