Solved

Excel difficulties

Posted on 2004-08-26
9
2,824 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
[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
  • 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
Independent Software Vendors: 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

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!

Question has a verified solution.

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

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

734 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