Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Raul Urquilla
  Where is Raul Urquilla?
 San Salvador
 El Salvador
 Raul Urquilla
 To: Andy Smith
  Where is Andy Smith?
 Bloomfield
 New Jersey - United States
 Andy Smith
 Tags
Subject: RE: Best approach of creating Excel Report From...
Thread ID: 43065 Message ID: 58771 # Views: 8 # Ratings: 0
Version: Visual FoxPro 7 Category: Microsoft Office Automation
Date: Friday, December 31, 2004 10:05:10 AM         
   


> > Hi Everyone:
> >
> > I would like to ask your expert opinion in relates to how I creates Excel Report From VFP. Hopefully some of you might have a better way of doing it.
> > Here how I do it.
> >
> > This is the sample type of report I have to create:
> > ================================
> >
> > ABC Company (Company)
> > John Smith (Client)
> > Order Item #1 1000.00
> > Order Item #2 1050.00
> > Order Item #3 800.00
> > Total 2850.00
> >
> > Peter Johnson (Client)
> > Order Item #1 500.00
> > Order Item #2 900.00
> > Order Item #3 1500.00
> > Total 2900.00
> >
> > Total: ABC Company 5750.00
> >
> > XYZ Company (Company)
> > Sam Jamesh (Client)
> > Order Item #1 1000.00
> > Order Item #2 1050.00
> > Order Item #3 800.00
> > Total 2850.00
> >
> > Chic Pea (Client)
> > Order Item #1 500.00
> > Order Item #2 900.00
> > Order Item #3 1500.00
> > Total 2900.00
> > Total: XYZ Company 5750.00
> >
> >
> > This is how I approach this in VFP :
> > ================================
> > Step 1: Create Excel layout cursor, i.e.
> >
> > Scan through company
> > add company into cursor
> > scan through client belonging this this company
> > add client into cursor
> > add order
> > add Total for this client
> > endscan
> > add total for this company
> > endscan
> >
> > Step2: Create Excel object and use this command to append data into Excel sheet
> >
> > .Range("A1").Activate
> > SELECT C_ExcelData
> > GO Top
> > _VFP.DataToClip("C_ExcelData", , 3) && Copy to buffer
> > .ActiveSheet.Paste() && Paste the Data
> >
> > ..
> > .. and so on..
> >
> > Step3: Apply formatting, bold, merge cell, etc, i.e.
> > lnRowCount = .ActiveSheet.UsedRange.rows.Count
> > lnColumnCount = .ActiveSheet.UsedRange.columns.Count
> >
> > *** Go through each row, customize the needed columns and rows
> > For i = 1 to lnRowCount
> > * Formatting, bolding,... .... ... etc
> > endfor
> >
> >
> > Here my questions, Is there a better way of doing this?
> > Can I merge Step 1 right into Step 3, skipping step 2. which means I am scanning through cursors and
> > inserting into excel rows and columns, applying formatting at the same time instead of having to create the cursor first then append and apply formating.
> >
> > The drawback with my method is that in all the total or sub total section, I don't have a fomula in it, adding formula after appending data is a bit tricky,
> > If I have to add formula to it, I have to know how many rows that total or sub-total sections belongs to it, then use the .ActiveCell.FormulaR1C1 property
> > setting to set the formula. If I can merget step 1 to step 3, then it would be much easier.
> >
> > Thank you.
> >
> > Chieh
> >
If the structure of the cursor that you obtain in step 1 could be like this Title c(30), amount n(10,2) you have all you need to create a report in vfp and not export to excell.

let's see:

Step 1: Create Excel layout cursor, i.e. &&Just a cursor

Scan through company
add company into cursor && in title field "ABC Company (Company)" start variable for company's total
scan through client belonging this this company
add client into cursor && in title field "John Smith (Client)"
add order && in title field "Order Item #1" and 1000.00 in amount field
&& use a variable to calculate the client's total (1000+1050+800)
add Total for this client && 2850.00 in amount field and in the title field "Total"
&& company's total=company's total+client's total
endscan
add total for this company && "Total: ABC Company" in title field and total company in amount field
endscan

Your cursor should looks like this:

Title amount
ABC Company (Company)
John Smith (Client)
Order Item #1 1000.00
Order Item #2 1050.00
Order Item #3 800.00
Total 2850.00

Then create your report with the formating, type of font, bold ... all you want even color.

Happy New Year
Raul Urquilla

ENTIRE THREAD

Best approach of creating Excel Report From VFP Posted by chieh chiu @ 5/17/2004 6:20:04 AM
RE: Best approach of creating Excel Report From... Posted by Yuri Rubinov @ 5/17/2004 3:30:26 PM
RE: Best approach of creating Excel Report From... Posted by chieh chiu @ 5/17/2004 4:18:25 PM
RE: Best approach of creating Excel Report From... Posted by Satyapal Singh @ 12/27/2004 8:30:26 AM
RE: Best approach of creating Excel Report From... Posted by DNAunion @ 12/29/2004 5:47:09 PM
RE: Best approach of creating Excel Report From... Posted by Andy Smith @ 12/29/2004 9:57:36 PM
RE: Best approach of creating Excel Report From... Posted by Raul Urquilla @ 12/31/2004 10:05:10 AM