How to insert Heading into Excel Sheet

[Saif_Abc]

Hi,

How to insert heading like "ABC INDUSTRIES LLC" on A1 in excel.
I am exporting the grid contents into excel.

This grid contents start from A1. But I want to start it from A3 and on A1 I want to put the company name and on A2 I want to put the subject like "Sales Statement from 1.1.2021 to 28.7.2021

Please guide me

Thanks

Saif

 

[Mike Lewis]

You can't do this directly using COPY TO ... TYPE XL5. That will simply give you the rows and columns of data, without any headings.

If you do COPY TO ... TYPE CSV, you get a comma-delimited file that Excel can open. That will give you the field names in the first row, which isn't exactly what you want.

However, the comma-delimited fle is an ordinary text file which you can read into VFP, add the headings, and write back. Something like this:

USE TheTable
COPY TO work.csv TYPE CSV
lcWork = FILETOSTR("work.csv")
lcWork = "ABC INDUSTRIES LLC" + CHR(13) + CHR(10) + "My Company LLC" + CHR(13) + CHR(10) + lcWork
STRTOFILE(lcWork, "final.csv", 0)

final.csv now contains the data with the headings, which you can open in Excel. You can discard work.csv.

You can also do the job using Excel Automation, but the above solution is simpler.

NOTE: I have not tested the above.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Saif_Abc]

Thanks for the reply!

I am not using "copy to " command, I am using the following:

LOCAL lcExcel, loGrids
lcExcel = SYS(5) + ADDBS(SYS(2003)) + "Ledger With Aging.xlsx"
loGrids = CREATEOBJECT("Empty")
ADDPROPERTY(loGrids, "Count", 2)
ADDPROPERTY(loGrids, "List[2, 4]", "")
loGrids.List[1, 1] = This.parent.parent.PgLedger.Grid1 && Grid object reference
loGrids.List[1, 2] = "Ledger" && Sheet name
loGrids.List[1, 3] = .T. && Freeze top row indicator
loGrids.List[1, 4] = .F. && Include hidden columns indicator

loGrids.List[2, 1] = This.Parent.GrdAging
loGrids.List[2, 2] = "Aging Analysis"
loGrids.List[2, 3] = .T.
loGrids.List[2, 4] = .F.

This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)

oExcel = CREATEOBJECT('Excel.Application')
oExcel.Visible = .T.

if !file(lcExcel)
=MESSAGEBOX(lcExcel + ' not found!')
return
endif

oExcel.Worksheets ("sheet1"). Activate
oExcel.Application.UserControl=.T.
oExcel.Application.Interactive=.T.
oExcel.WindowState = -4137 && xlMaximized
oExcel.Application.Workbooks.Open(lcExcel)


*oExcel.Workbooks.Close
*oExcel.Quit
*Release oExcel

Please guide

Thanks

Saif

 

[Mike Lewis]

OK, I can see what you are doing. But surely my method is much simpler, and will do exactly what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Saif_Abc]

Yes, your method is much simpler but that method gives a lot of options, means column alignments, width, calculation, multiple sheets.

Can you guide me how to do this in that method.

Thanks

Saif

 

[Steve Bowman]

I usually do a hybrid

I will do a copy to filename.csv CSV
then create my excel automation object
then pretty it all up

loExcel.ActiveSheet.Range("A1").Value="My A heading"
loExcel.ActiveSheet.Range("B1").Value="My B heading"
loExcel.ActiveSheet.Range("C1").Value="My C heading"

then apply any other formatting
loExcel.Columns("D").Select
loExcel.Selection.NumberFormat = "$#,##0.00"

clean up and save
loExcel.Cells.Select
loExcel.Selection.Columns.AutoFit
loExcel.Range("A2").Select
loExcel.ActiveWindow.FreezePanes = .t.
loExcel.AlertBeforeOverwriting=.f.
loExcel.DisplayAlerts=.f.
xlOpenXMLWorkbook=51
loExcel.ActiveWorkbook.SaveAs("MyNewExcelFile.xlsx",xlOpenXMLWorkbook)
loExcel.quit

then delete the original
DELETE FILE ("filename.csv")

I also #include my excel.h constants file. Anything I need that I am not sure how to do I will open my excel file turn on macro record then perform what I want then use the macro to convert to fox automation code.


Steve Bowman
Independent Technology, Inc.
CA, USA

 

[Saif_Abc]

Thanks for the reply

I am facing this error.

Thanks

Saif

 

[SkipVought]

If you do COPY TO ... TYPE CSV, you get a comma-delimited file that Excel can open."

Just because you CAN doesn't mean you SHOULD.

FAQ68-7375

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[tomk3]

Hello,

basically this can be done like this :

loexcel = CREATEOBJECT("Excel.application")
loexcel.Workbooks.Add
loexcel.Sheets[1].activate
loexcel.cells(1,1).value = "A"
loexcel.cells(1,2).value = "B"
loexcel.cells(1,4).value = "C"
loexcel.Rows(1).Insert(-4121) && insert one line and move down
loexcel.Cells(1,1).Value = "Title1" && set title
loexcel.Visible = .t.

regards
tom

 

[Saif_Abc]

Thanks for the reply!

Kindly find the code below which I am using with your code:
It is opening two different excel, one is containing without the data but the title is coming, and,
other is showing data without the title.

LOCAL lcExcel, loGrids
lcExcel  = SYS(5) + ADDBS(SYS(2003)) + "Ledger With Aging.xlsx"
loGrids  = CREATEOBJECT("Empty")
ADDPROPERTY(loGrids, "Count", 2)
ADDPROPERTY(loGrids, "List[2, 4]", "")
loGrids.List[1, 1] = This.parent.parent.PgLedger.Grid1 && Grid object reference
loGrids.List[1, 2] = "Ledger"              && Sheet name
loGrids.List[1, 3] = .T.                      && Freeze top row indicator
loGrids.List[1, 4] = .F.                      && Include hidden columns indicator

loGrids.List[2, 1] = This.Parent.GrdAging
loGrids.List[2, 2] = "Aging Analysis"
loGrids.List[2, 3] = .T.
loGrids.List[2, 4] = .F.

This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)

loExcel = CREATEOBJECT('Excel.Application')    
loexcel.Workbooks.Add
loexcel.Sheets[1].activate
loexcel.cells(1,1).value = "A"
loexcel.cells(1,2).value = "B"
loexcel.cells(1,4).value = "C"
loexcel.Rows(1).Insert(-4121) && insert one line and move down
loexcel.Cells(1,1).Value = "Title1" && set title

if !file(lcExcel)
  =MESSAGEBOX(lcExcel + ' not found!')
  return
endif

loExcel.Worksheets ("sheet1"). Activate
loExcel.Application.UserControl=.T.
loExcel.Application.Interactive=.T.
loExcel.WindowState = -4137  && xlMaximized
loExcel.Application.Workbooks.Open(lcExcel)
loExcel.Visible = .T.
    
*oExcel.Workbooks.Close
*oExcel.Quit
*Release oExcel

Kindly let me know how to adjustment your code, in order to get the data along with the title by using your code.

Thanks

Saif

 

[Chris Miller]

ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx points out you use the

https://github.com/ggreen86/XLSX-Workbook-Class

by Gregory Green.
That's fine.

You want to load the XLSX saved by this class into your own (the second) Excel instance, before you add the titles.

...your code up to...
This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)

loExcel = CREATEOBJECT('Excel.Application')    
loexcel.Workbooks..open("set this to the XLSX file name created by ClsVFPxWorkbookXLSX")
loexcel.Sheets[1].activate
...rest of your code

Workbooks Add just adds a new empty workbook.

I bet Gregory's class can also solve to output data with headers and you don't need this kind of post processing. Look into the documentation and/or source code how SaveMultiGridToWorkbookEx can be called, what parameters and options it offers regarding the Excel header row.



Chriss

 

[Saif_Abc]

Thanks for the reply!

I am now applying this code through which I can combine "Title" as well as Data

Local lcExcel, loGrids
lcExcel  = Sys(5) + Addbs(Sys(2003)) + "Ledger With Aging.xlsx"
loGrids  = Createobject("Empty")
AddProperty(loGrids, "Count", 2)
AddProperty(loGrids, "List[2, 4]", "")
loGrids.List[1, 1] = This.Parent.Parent.PgLedger.Grid1 && Grid object reference
loGrids.List[1, 2] = "Ledger"              && Sheet name
loGrids.List[1, 3] = .T.                      && Freeze top row indicator
loGrids.List[1, 4] = .F.                      && Include hidden columns indicator

loGrids.List[2, 1] = This.Parent.GrdAging
loGrids.List[2, 2] = "Aging Analysis"
loGrids.List[2, 3] = .T.
loGrids.List[2, 4] = .F.

This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)

loExcel = Createobject('Excel.Application')
loExcel.Workbooks.Add
loExcel.Sheets[1].Activate
loExcel.Rows(1).Insert(-4121) && insert one line and move down
loExcel.Worksheets ("sheet1"). Activate
loExcel.Application.UserControl=.T.
loExcel.Application.Interactive=.T.
loExcel.WindowState = -4137  && xlMaximized
loExcel.Application.Workbooks.Open(lcExcel)
loExcel.cells(1,1).Value = "NATIONAL TRADERS LLC"
loExcel.cells(2,1).Value = "CUSTOMER WISE AGING REPORT"
loExcel.cells(2,9).Value = "Datetime:"
loExcel.cells(2,11).Value = "Page No."
loExcel.Visible = .T.

*oExcel.Workbooks.Close
*oExcel.Quit
*Release oExcel

*!*	If !File(lcExcel)
*!*	   =Messagebox(lcExcel + ' not found!')
*!*	   Return
*!*	Endif

But the problem is the data is now starting from A3 and also a blank workbook is opening along with the data sheet.

Please see in the image attached

Thanks

Saif

 

[Chris Miller]

AS I said, you don't call loExcel.Workbooks.Add but loExcel.Workbooks.Open() to load the file you just created and then add headers instead. You also did that, but too late. What you do is create a new emtpy workbook, add an empty line to it, and then load the excel file created by his.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel).

Of course you get what you see.

Open does not open data to the current sheet. It opens an Excel file, that's having it's own Workbooks/Sheets.

The rest should be easy enough to fix. You should not write to cells of row 2 or further down, when you just add one row for the headings. The line 1 cells are in ActiveSheet.Cells(1,columnnumber).
Your code only sets 4 cells, but the screenshot shows headings on all columns. Which makes me conclude that ClsVFPxWorkbookXLSX acrtually already writes heading. If you want to change them, then you don't need a new row of cells, you can just add lines to the already existing headings.


Chriss

 

[Saif_Abc]

Thanks Chris for the reply!

I am now getting only one workbook with the data along with Headers. But the same problem as shown in image.
I want the data from Row 4 onward, but it is coming from row1.

lcExcel  = Sys(5) + Addbs(Sys(2003)) + "Ledger With Aging.xlsx"
loGrids  = Createobject("Empty")
AddProperty(loGrids, "Count", 2)
AddProperty(loGrids, "List[2, 4]", "")
loGrids.List[1, 1] = This.Parent.Parent.PgLedger.Grid1 && Grid object reference
loGrids.List[1, 2] = "Ledger"              && Sheet name
loGrids.List[1, 3] = .T.                      && Freeze top row indicator
loGrids.List[1, 4] = .F.                      && Include hidden columns indicator

loGrids.List[2, 1] = This.Parent.GrdAging
loGrids.List[2, 2] = "Aging Analysis"
loGrids.List[2, 3] = .T.
loGrids.List[2, 4] = .F.

loExcel = Createobject('Excel.Application')
loExcel.Caption = "Customer Ledger With Aging"
loExcel.Application.Workbooks.Open(lcExcel)
loExcel.Sheets[1].Activate
*-------------------------------
This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)
loExcel.cells(1,1).Value = "NATIONAL TRADERS LLC"
loExcel.cells(2,1).Value = "CUSTOMER WISE AGING REPORT"
loExcel.cells(3,1).Value = "Datetime:"
loExcel.cells(4,1).Value = "Page No."
loExcel.ActiveSheet.Cells (1,1). Font . Name = "Bold"
loExcel.ActiveSheet.Cells (1,1). Font . Size = 21
loExcel.ActiveSheet.Cells (2,1). Font .Italic = .T.
loExcel.ActiveSheet.Rows(4).Insert
*-------------------------------
loExcel.ActiveSheet.PageSetup.CenterHeader = "Report 1"
loExcel.Application.UserControl=.T.
loExcel.Application.Interactive=.T.
loExcel.WindowState = -4137  && xlMaximized
loExcel.Visible = .T.

Please guide me where I am getting wrong in the above code.

Thanks

Saif

 

[Chris Miller]

Why did you move the creation of the XLSX down?

This line has to be before you postprocess the file it creates:

This.Parent.ClsVFPxWorkbookXLSX.SaveMultiGridToWorkbookEx(loGrids, lcExcel)

You still don't get the plan behind all this.

It is to
1. let ClsVFPxWorkbookXLSX save the grid as XLSX file
2. Automate another Excel instance to open the file just created
3. Change or add headers

I don't think you really need your data to start in row 4. If you have headings that need 4 lines, they still can be in just one cell, just add their text with linefeeds CRLF, which means CHR(13)+CHR(10), or prepare such headings within TEXT..ENDTEXT. And after all you do use the tip from Tom to autofit cells, so those multiline headers will get the height needed to see them.

Chriss