Solved

Excel difficulties

Posted on 2004-08-26
9
2,780 Views
Last Modified: 2008-02-26
I am having difficulties with formating cells in Excel. I tryed to use the ExcelApplication component and also
I tryed through OLE, but some things work and some don't.
I need to do an Excel report from delphi . Something like this:

I have a StringGrid filled with values. I need to export it to excel . Before the data from the grid I need to insert some lines (in Excel) with text...
I need to set some text in the header, and footer. And then Print preview, and save excel file



Open Excel (invisible)
On Sheet1 do

   enter some text in CELL['A5']
   enter the date from the StringGrid starting with CELL['A7']
   enter some text after the data from the StringGrid
   Format Cells (AutoFit, Numberformat for the fields that are numeric, Font, Borders)
   Save and PrintPreview
   
I need working code... because theory I read enough.

And by the way I use MS Office 2003 on the computer that I am working at.
0
Comment
Question by:Ghitza
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Expert Comment

by:snehanshu
ID: 11901369
Here's some working code from a question I had answered earlier: not exactly what you want, but some working code to get u started.
HTH,
...Shu

http://www.experts-exchange.com/Q_20796477.html
(you would need to add ComObj in the uses clause)

procedure TForm1.Button1Click(Sender: TObject);
var
  excel:Variant;
  MyStrings, MyRows: TStringList;
  i, j: integer;

begin
  try
    excel:=CreateOleObject('Excel.Application');
  except
    ShowMessage('Error...');
    exit;
  end;
  excel.Visible:=true;
  excel.Workbooks.Add;

  MyStrings := TStringList.Create;
  MyRows := TStringList.Create;
  MyRows.Delimiter := ' ';
  For i := 0 to RichEdit1.Lines.Count -1 Do
  Begin
    MyRows.DelimitedText := RichEdit1.Lines[i];
    MyStrings.Add(MyRows.CommaText);//convert richedit line to csv format
    For j := 0 to MyRows.Count -1 Do
    Begin
      if j = 0 then
        excel.Cells[i+1,j+1].NumberFormat := '0000';//adds leading zeros for first column
      excel.Cells[i+1,j+1].Value := MyRows[j];//i+1 because excel rows/columns begin from 1

    End;
  End;
  MyStrings.SaveToFile('C:\MyExcel.csv');
  MyRows.Free;
  MyStrings.Free;

//example of changing font etc.:
//  excel.Cells[1,1].Value:='Excel - Test 1.';
//  excel.Cells[1,1].Font.Bold := true; {set bold style}
//  excel.Cells[1,1].Font.Size := 20; {set size to 20}
//  excel.Cells[2,1].Value:='Excel - Test 2.';
//  excel.Cells[2,1].Font.Italic := True; {set italic style}
//  excel.Cells[1,1].Font.Size := 15; {set size to 15}
  excel.ActiveWorkbook.SaveAs('c:\test1.xls'); //Save the active workbook as c:\test1.xlx
  excel.Quit; //Terminate Excel application
end;
0
 
LVL 2

Expert Comment

by:Ratje
ID: 11901804
www.maxcomponents.net nice set of export components (excell, csv, word, html etc ) from grid, dbgrid and more.

Ratje
0
 
LVL 3

Author Comment

by:Ghitza
ID: 11911898
I know how to enter data in the Excel Sheet
The problems that I have are relatet to:
" I am having difficulties with formating cells in Excel."
...
"Format Cells (AutoFit, Numberformat for the fields that are numeric, Font, Borders)"

Al the rest are quite simple and with the components I cannot create a custom sheet... I can anly export datasets... which as I already said I know how to do it.

So if anyone can help me with the formating they will receive the points.
Thanks
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 11911962
How about these???
excel.Cells[i+1,j+1].NumberFormat := '0000';//adds leading zeros for first column
//  excel.Cells[1,1].Font.Bold := true; {set bold style}
//  excel.Cells[1,1].Font.Size := 20; {set size to 20}
//  excel.Cells[2,1].Value:='Excel - Test 2.';
//  excel.Cells[2,1].Font.Italic := True; {set italic style}
//  excel.Cells[1,1].Font.Size := 15; {set size to 15}

Perhaps, I'll post more later.
...Shu
P.S.
BTW, you can use the excel application components in Delphi's "server" tab to find all properties that excel.cells supports.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:Ghitza
ID: 11914339
"BTW, you can use the excel application components in Delphi's "server" tab to find all properties that excel.cells supports."
I may have been not too exact with my question but :they are the only components I use.

With them I try to format the cells...
The problem that I have is again:
"Format Cells (AutoFit, Numberformat for the fields that are numeric, Font, Borders)"
More specifically: Borders... and Autofit, and Cell.Width and Cell.Height

I know how to format the Numbers... I was just trying to explain you that I am interested in FORMATING not inserting data.
 The information that you gave me can be found on Deborah Pate's Automation pages... And I have read them a long time ago, along with some other automation tutorials and FAQ's.
 And I also used the record macro option from excel to get a picture of what it should look like... but still no result. Maybe it's because I am to busy doing 3 things at a time...

 Please let me know if you found the formating functions for borders and Width, Height and you will receive the points...
0
 
LVL 3

Author Comment

by:Ghitza
ID: 11914426
From Deborah's page:



  var
    Format: OleVariant;
  ...
    WS := Excel.ActiveSheet as _Worksheet;
To format one cell in the General number style

    Format := 'General';
    WS.Range['A1', 'A1'].NumberFormat := Format;
To format a range in the 'Text' style, aligned right

  with WS.Range['A1', 'M10'] do
  begin
    NumberFormat := '@';
    HorizontalAlignment := xlHAlignRight;
  end;
To format a range of cells with the 'March 4, 1999' date style

    Format := 'mmmm d, yyyy';
    WS.Range['B1', 'C10'].NumberFormat := Format;
To format an entire column in a customized currency style

    Format := '$#,##0.00_);[Red]($#,##0.00)';
    WkSheet.Range['C1', 'C1'].EntireColumn.NumberFormat := Format;
To set the text in a cell to 20pt Arial, bold, and fuchsia

    with Excel.ActiveCell.Font do
    begin
      Size := 20;
      FontStyle := 'Bold';
      Color := clFuchsia;
      Name := 'Arial';
    end;

To change the cell's colour

    Excel.ActiveCell.Interior.Color := clBtnFace;
or

    Excel.Range['B2', 'C6'].Interior.Color := RGB(223, 123, 123);
To make the first three characters in a cell bold

  var
    Start, Length: OleVariant;
  ...
    Start := 1;
    Length := 3;
    Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Bold';
    Start := 4;
    Length := 16;
    Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Regular';
0
 
LVL 5

Expert Comment

by:snehanshu
ID: 11914484
You sound like I am wasting your time.
Perhaps someone else may better help you.
Bye,
...Shu
0
 
LVL 11

Expert Comment

by:calinutz
ID: 11914987
// Set width
 ws.Columns.Range['H1','H1'].ColumnWidth:=30;
//Autofit column
 ws.Columns.Range['G1','G1'].EntireColumn.AutoFit;
//set font name
 ws.Rows.Range['A27','W27'].Font.Name:='Tahoma';
//set font size
 ws.Rows.Range['A27','W27'].Font.Size:=8;

 //Set height
 ws.Rows.Range['G27','G27'].RowHeight:=5;
 //Autofit
 ws.Rows.Range['G27','G27'].EntireRow.AutoFit:=true;



/// And for the borders I'll be back
0
 
LVL 11

Accepted Solution

by:
calinutz earned 250 total points
ID: 11916749
Abvout the borders...

  ws.Rows.Range['G27','H27'].Borders.Item[1].LineStyle:=1;
  ws.Rows.Range['G27','H27'].Borders.Item[2].LineStyle:=1;
  ws.Rows.Range['G27','H27'].Borders.Item[3].LineStyle:=1;
  ws.Rows.Range['G27','H27'].Borders.Item[4].LineStyle:=1;


Of course
var
 ws:_worksheet;

Cheers
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

23 Experts available now in Live!

Get 1:1 Help Now