Solved

JavaScript and Excel Chart Nightmare

Posted on 2004-04-23
8
1,418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
This article discusses how to implement server side field validation and display customized error messages to the client.
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

617 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