Solved

JavaScript and Excel Chart Nightmare

Posted on 2004-04-23
8
1,380 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 63

Accepted Solution

by:
Zvonko earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This article discusses how to create an extensible mechanism for linked drop downs.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now