Excel difficulties

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.
LVL 3
GhitzaAsked:
Who is Participating?
 
calinutzConnect With a Mentor Commented:
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
 
snehanshuCommented:
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
 
RatjeCommented:
www.maxcomponents.net nice set of export components (excell, csv, word, html etc ) from grid, dbgrid and more.

Ratje
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
GhitzaAuthor Commented:
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
 
snehanshuCommented:
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
 
GhitzaAuthor Commented:
"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
 
GhitzaAuthor Commented:
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
 
snehanshuCommented:
You sound like I am wasting your time.
Perhaps someone else may better help you.
Bye,
...Shu
0
 
calinutzCommented:
// 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.