Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

JavaScript and Excel Chart Nightmare

Posted on 2004-04-23
8
Medium Priority
?
1,440 Views
Last Modified: 2011-09-20
Normally, I would not rightly admit to this, but it seems that vbscript is better suited to doing excel automation with the chart object. This is hurting my current project as I am more familiar with Javascript, but can't get past the seemingly undocumented way to script the chart object on the client side. I want to be able to display a 3d exploded pie chart with excel and then export the resulting picture with javascript. I know it can be done in vbscript, but everything else I have done on the project is javascript and is working fine. I can't even find anyone else who seems to have tried to do this, so maybe it can't be done the way I want. Thanks in advance to those who respond.

Dan
0
Comment
Question by:robotman757
  • 6
  • 2
8 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 10901997
Do you have some vbscript for convert or do you want some example from scratch?
0
 
LVL 3

Author Comment

by:robotman757
ID: 10902034
Zvonko...here is something I found that works well, but I could not convert it to Javascript, and I have tried several ways. I know how to get Excel to run, and put info into it, but getting the range and chart stuff just would not work.

Thanks

<SCRIPT LANGUAGE="VBScript">

       sub button1_onclick()

          ' Launch Excel
          dim app
          set app = createobject("Excel.Application")
   
          ' Make it visible
          app.Visible = true
   
          ' Add a new workbook
          dim wb
          set wb = app.workbooks.add
   
          ' Fill array of values first...
          dim arr(19,9) ' Note: VBScript is zero-based
          for i = 1 to 20
             for j = 1 to 10
                arr(i-1,j-1) = i*j
             next
          next
   
          ' Declare a range object to hold our data
          dim rng
          set rng = wb.Activesheet.Range("A1").Resize(20,10)
   
          ' Now assign them all in one shot...
          rng.value = arr
   
          ' Add a new chart based on the data
          wb.Charts.Add
          wb.ActiveChart.ChartType = 70 'xl3dPieExploded
          wb.ActiveChart.SetSourceData rng, 2 ' xlColumns
          wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
   
          ' Rotate it around...
          for i = 1 to 360 step 30
             wb.activechart.rotation = i
          next
                   
          wb.Activesheet.PageSetup.Orientation = 2

          ' Give the user control of Excel
          app.UserControl = true

       end sub
</SCRIPT>
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10902181
Thanks, there even the Excel constants in it.
Give me a minute...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 63

Expert Comment

by:Zvonko
ID: 10902505
The conversion was simple, but Chart copy is becomming hard...

<SCRIPT>
function drawChart(){
  // Launch Excel
  app = new ActiveXObject("Excel.Application");
   
  // Make it visible
  app.Visible = true
   
  // Add a new workbook
  wb = app.workbooks.add
   
  ws = wb.Worksheets(1);
   
  // Declare a range object to hold our data
  rng = wb.Activesheet.Range("A1").Resize(20,10)
   
  // Now assign them all
  for(i=1; i<=20; i++){
    for(j=1; j<=10; j++){
      wb.Activesheet.Cells(i, j) = i * j;
    }
  }

  // Add a new chart based on the data
  xlChart = wb.Charts.Add
  wb.ActiveChart.ChartType = 70 //xl3dPieExploded
  wb.ActiveChart.SetSourceData(rng, 2) // xlColumns
   
               
  wb.Activesheet.PageSetup.Orientation = 2

  xlChart.CopyPicture( 1, 2);
   
  // Give the user control of Excel
  app.UserControl = true
}
drawChart()
</SCRIPT>


0
 
LVL 63

Accepted Solution

by:
Zvonko earned 2000 total points
ID: 10902550
Ok, job done:

<SCRIPT>
function drawChart(){
  // Launch Excel
  app = new ActiveXObject("Excel.Application");
   
  // Make it visible
  app.Visible = true
   
  // Add a new workbook
  wb = app.workbooks.add
   
  ws = wb.Worksheets(1);
   
  // Declare a range object to hold our data
  rng = wb.Activesheet.Range("A1").Resize(20,10)
   
  // Now assign them all
  for(i=1; i<=20; i++){
    for(j=1; j<=10; j++){
      wb.Activesheet.Cells(i, j) = i * j;
    }
  }

  // Add a new chart based on the data
  xlChart = wb.Charts.Add
  wb.ActiveChart.ChartType = 70 //xl3dPieExploded
  wb.ActiveChart.SetSourceData(rng, 2) // xlColumns
   
               
  wb.Activesheet.PageSetup.Orientation = 2

  xlChart.Export("D:\\tmp\\chrt.gif", "GIF");
   
  // Give the user control of Excel
  app.UserControl = true
}
drawChart()
</SCRIPT>


0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10902624
And here only the picture:


<SCRIPT>
function drawChart(){
  // Launch Excel
  app = new ActiveXObject("Excel.Application");
   
  // Add a new workbook
  wb = app.workbooks.add
   
  // Declare a range object to hold our data
  rng = wb.Activesheet.Range("A1").Resize(20,10)
   
  // Now assign them all
  for(i=1; i<=20; i++){
    for(j=1; j<=10; j++){
      wb.Activesheet.Cells(i, j) = i * j;
    }
  }

  // Add a new chart based on the data
  xlChart = wb.Charts.Add
  wb.ActiveChart.ChartType = 70 //xl3dPieExploded
  wb.ActiveChart.SetSourceData(rng, 2) // xlColumns
   
               
  wb.Activesheet.PageSetup.Orientation = 2

  xlChart.Export("D:\\tmp\\chrt.gif", "GIF");
   
  // Close Excel
  app.UserControl = false;
  app.DisplayAlerts = false
  wb.Close();
  app.Quit();  
  window.location = "D:///tmp/chrt.gif";
}
drawChart()
</SCRIPT>



0
 
LVL 3

Author Comment

by:robotman757
ID: 10903029
Zvonko...thanks for the info. I had to change the range line to do what I wanted, but that worked. I was so close, but the differences were just not quite there.
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 10903535
Thanks for the points.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've been trying to accomplish this for a while and it just struck me yesterday how to accomplish this task. I have done searches all over the internet looking for ways to email pages from my applications and finally I have done it!!! Every single s…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question