Exporting formatted data to Excel quickly 1

[simo]

I need to be able to export data from foxpro into an Excel spreadsheet with formatting.<br><br>I've tried creating an Excel object and setting the cell object properties but this is too slow.&nbsp;&nbsp;<br><br>I was wondering if there was an API or somthing similar for creating Excel workbook files, or some kind of utility for printing Visual Reports to Excel.<br><br>Thanks in advance.

 

[BlindPete]

Have you tried the export command?<br><br>I'm not sure how it handles the formating but it is quick. <p> <br><a href=mailto:blindpete@mail.com>blindpete@mail.com</a><br><a href= > </a><br>

 

[simo]

I've had a look at the export command, and yes, it is really, really fast, but unfortunatly it does not allow you to store format information (such as bold, text size, underlining etc)&nbsp;&nbsp;<br><br>You may think that I am asking to much, but one of my customers who used to spend 4 or 5 days creating a sales report in Excel manually is complaining that it takes an hour to run my Excel export code - but insists on formatting.<br><br>I could try exporting the information using EXPORT and then writing Excel VBA code to do the formatting in Excel itself.<br><br>Thanks for your help <br>

 

[jonscott8]

<i>one of my customers who used to spend <b>4 or 5 days</b> creating a sales report in Excel manually is complaining that it takes an <b>hour</b> to run my Excel export code</i><br><br>Hmmm.....an hour on one hand.........4 or 5 days on the other hand? Now that's a touch decision. LOL!! Maybe you need to remind the customer that he use to spend 4 or 5 days creating this report manually and now all he has to do is click a button and the report is created for him. It's people like that who get under my skin!<br><br><i>I could try exporting the information using EXPORT and then writing Excel VBA code to do the formatting in Excel itself.</i><br><br>After EXPORTing the data to an excel spreadsheet, is there a particualar reason you would have to use VBA code? Why not use Excel Automation within VFP?<br>Maybe if you post your current code, you might get some suggestions on how to cut out some of the bottlenecks in your algorithm. Good Luck.<br><br> <p>Jon<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem!

 

[JDurbin]

_cliptext or a string with tabs and carriage returns already processed in vfp is the fastest way to send data over.&nbsp;&nbsp;I'm curious how you will already have data formatted to send over. <p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>ICQ #9466492<br>
ICQ VFP ActiveList #73897253

 

[simo]

What I do is get my report data into a cursor, then in a loop, enter all the fields and records into an Excel Object, e.g.<br><br>FOR colCount_n = 1 to (COLTOTAL_N - 1)<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).NumberFormat = &quot;0.00&quot;<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).font.Size = 8 <br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).value = ;<br> custTotals_a(custCount_n,colCount_n)<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).borders(4).lineStyle = 1<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).borders(4).weight = 2<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).borders(3).lineStyle = 1<br> excelAutoObject_o.Cells(rowIndex_n,colIndex_n).borders(3).weight = 4<br> colIndex_n = colindex_n+1<br> NEXT colCount_n<br><br>This is basically the same technique as used in VisFox 6 Enterprise Development by Paddock & Peterson, chapter 6 'Communicating With Other Applications' - but it is so sloooow!!

 

[jonscott8]

Hi Simo,<br>&nbsp;&nbsp;I am not an Excel automation buff, but since you are using it, I will assume you are familiar with it. The apparent bottleneck,IMO, appears to be the handling of each and every cell individually. Assuming you are using large datasets, 1000+ records, this could defenitely affect performance. Try exporting the data to an excel spreadsheet and then doing your formatting on the entire selection instead of each and every cell.<br>For Example,<br><br>*First export the data<br>Use MyTable<br>Export to MyFile.xls TYPE XLS<br>oExcel=CreateObject('Excel.Application')<br>oExcel.Workbooks.Open('C:\myfile.xls')<br><br>*do all your formatting<br>ThisSpreadSheet.Range(&quot;A1:G1000&quot.Select<br>*example with 7 fields, 1000 records<br>With ThisSpreadSheet.Selection<br>&nbsp;&nbsp;.Numberformat = &quot;0.00&quot;<br>&nbsp;&nbsp;.Font.Size=8<br>&nbsp;&nbsp;.borders(4).lineStyle = 1<br>&nbsp;&nbsp;.borders(4).weight = 2<br>&nbsp;&nbsp;.borders(3).lineStyle = 1<br>&nbsp;&nbsp;.borders(3).weight = 4<br>End With<br><br>Let me know if this helps or you need any additional info. <p>Jon Hawkins<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem! - Seize the Day!

 

[jonscott8]

Oops! Typo.<br><br>ThisSpreadSheet should be oExcel.<br><br>They have the air conditioning set so low in our office, that I believe my blood is freezing up and my brain was suffering from lack of oxygen.&nbsp;&nbsp; <p>Jon Hawkins<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem! - Seize the Day!

 

[simo]

That sounds like a really good idea..<br>I will try it and see if it makes an approvement.<br>Thanks,<br><br>Simon.<br>

 

[simo]

Jon, I tried the method you suggested and now instead of taking 60 minutes to run, it takes 1 - I think as a percentage, that is quite an impressive speed improvement!<br><br>I was a complete pain to re-do my code to work but is was worth it in the end(and I will know for next time) <br><br>Thanks for your help.

 

[jonscott8]

WOW! Glad to hear it.<br><br>Just out of curiousity, what size is your average data set?<br>(Records & fields) <p>Jon Hawkins<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem! - Seize the Day!

 

[simo]

The average data set is about 900 records x 27 fields of 10.2 numeric format.<br><br>This equates to about 12 pages in Excel.<br><br>I don't think that this size is in any way excessive really, it's just that Excel was really slow.<br><br>Simon<br><br>

 

[jonscott8]

FWIW, using the above mentioned methodology, I exported data from a table consisting of over 9800 records and 44 fields. It equated to 1075 printed pages in Excel.<br><br>The total time to perform this operation was just under 15 seconds.<br><br>Is it this individual process that takes 1 minute or your overall process? <p>Jon Hawkins<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem! - Seize the Day!

 

[simo]

No, the system needs to create the table that holds the data to export in the first place.&nbsp;&nbsp;It is this that takes the time.<br><br>The user wishes to extract sales information, selectable by product and customer for a year, starting on any month, and for each month, sales qty's and values need to be displayed, and of course, we need qty, value totals per month, and totals of these per product / customer and grand totals.<br><br>The query method is 200 lines of code !<br><br><br><br>

 

[jonscott8]

You're always welcome to post the code. You never know. Someone here might be able to help you optimize it. <p>Jon Hawkins<br><a href=mailto: > </a><br><a href= > </a><br>Carpe Diem! - Seize the Day!