Solved

Excel difficulties

Posted on 2004-08-26
9
2,810 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
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

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
 
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

Technology Partners: 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!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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