JavaScript and Excel Chart Nightmare

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
LVL 3
robotman757Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZvonkoConnect With a Mentor Systems architectCommented:
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
 
ZvonkoSystems architectCommented:
Do you have some vbscript for convert or do you want some example from scratch?
0
 
robotman757Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ZvonkoSystems architectCommented:
Thanks, there even the Excel constants in it.
Give me a minute...
0
 
ZvonkoSystems architectCommented:
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
 
ZvonkoSystems architectCommented:
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
 
robotman757Author Commented:
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
 
ZvonkoSystems architectCommented:
Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.