Solved

Excel Server: Why oh why oh why...

Posted on 2000-03-02
7
163 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 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