Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Server: Why oh why oh why...

Posted on 2000-03-02
7
Medium Priority
?
185 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
[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
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
RBertora earned 100 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

636 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