Solved

Excel Server: Why oh why oh why...

Posted on 2000-03-02
7
162 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

810 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