Solved

JavaScript and Excel Chart Nightmare

Posted on 2004-04-23
8
1,389 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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 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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

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…

770 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