By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Calling external data by a function to create pivot table in excel format

P: 1
This program works.
upuse Win32::OLE;
Expand|Select|Wrap|Line Numbers
  1. # Start Excel and make it visible
  2. $xlApp = Win32::OLE->new('Excel.Application');
  3. $xlApp->{Visible} = 1;
  4.  
  5. # Create a new workbook
  6. $xlBook = $xlApp->Workbooks->Add;
  7.  
  8. # Our data that we will add to the workbook...
  9. $mydata = [["Item",     "Category", "Price"], 
  10.            ["Nails",    "Hardware",  "5.25"],
  11.            ["Shirt",    "Clothing", "23.00"],
  12.            ["Hammer",   "Hardware", "16.25"],
  13.            ["Sandwich", "Food",      "5.00"],
  14.            ["Pants",    "Clothing", "31.00"],
  15.            ["Drinks",   "Food",      "2.25"]];
  16.  
  17. # Write all the data at once...
  18. $rng = $xlBook->ActiveSheet->Range("A1:C7");
  19. $rng->{Value} = $mydata;
  20.  
  21. # Create a PivotTable for the data...
  22. $tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");
  23.  
  24. # Set pivot fields...
  25. $tbl->AddFields("Category", "Item");
  26. $tbl->PivotFields("Price")->{Orientation} = 4; # 4=xlDataField
  27.  
  28. # Create a chart too...
  29. $chart = $xlBook->Charts->Add;
  30. $chart->SetSourceData($rng, 2);
  31. $chart->{ChartType} = 70; # 3D-pie chart
  32. $chart->Location(2, "Sheet4");
  33.  
  34. # Wait for user input...
  35. print "Press <return> to continue...";
  36. $x = <STDIN>;
  37.  
  38. # Clean up
  39. $xlBook->{Saved} = 1;
  40. $xlApp->Quit;
  41. $xlBook = 0;
  42. $xlApp = 0;
  43.  
  44. print "All done.";
  45.  
except my data is a function call from a external server,and it is : http://127.0.0.1:16239/req?GetHistory(Stock symbol,01/20/2010,1/26/2012,1D,YES) with output as
OK
01/20/2010,15.42,15.47,15.15,15.23,35671454
01/21/2010,15.19,15.30,14.22,14.25,69930509
01/22/2010,14.18,14.18,13.33,13.40,84478420
01/25/2010,13.76,13.85,13.31,13.39,44190316
256 deep represent Date Open High Low Volume columns
Mar 11 '12 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.