Solved

Excel difficulties

Posted on 2004-08-26
9
2,767 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

9 Experts available now in Live!

Get 1:1 Help Now