robotman757
asked on
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
Dan
Zvonko
Do you have some vbscript for convert or do you want some example from scratch?
ASKER
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.Applic
' 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")
' 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.SetSourceDa
wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
' Rotate it around...
for i = 1 to 360 step 30
wb.activechart.rotation = i
next
wb.Activesheet.PageSetup.O
' Give the user control of Excel
app.UserControl = true
end sub
</SCRIPT>
Thanks
<SCRIPT LANGUAGE="VBScript">
sub button1_onclick()
' Launch Excel
dim app
set app = createobject("Excel.Applic
' 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")
' 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.SetSourceDa
wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
' Rotate it around...
for i = 1 to 360 step 30
wb.activechart.rotation = i
next
wb.Activesheet.PageSetup.O
' Give the user control of Excel
app.UserControl = true
end sub
</SCRIPT>
Thanks, there even the Excel constants in it.
Give me a minute...
Give me a minute...
The conversion was simple, but Chart copy is becomming hard...
<SCRIPT>
function drawChart(){
// Launch Excel
app = new ActiveXObject("Excel.Appli
// 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")
// 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.SetSourceDa
wb.Activesheet.PageSetup.O
xlChart.CopyPicture( 1, 2);
// Give the user control of Excel
app.UserControl = true
}
drawChart()
</SCRIPT>
<SCRIPT>
function drawChart(){
// Launch Excel
app = new ActiveXObject("Excel.Appli
// 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")
// 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.SetSourceDa
wb.Activesheet.PageSetup.O
xlChart.CopyPicture( 1, 2);
// Give the user control of Excel
app.UserControl = true
}
drawChart()
</SCRIPT>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And here only the picture:
<SCRIPT>
function drawChart(){
// Launch Excel
app = new ActiveXObject("Excel.Appli
// Add a new workbook
wb = app.workbooks.add
// Declare a range object to hold our data
rng = wb.Activesheet.Range("A1")
// 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.SetSourceDa
wb.Activesheet.PageSetup.O
xlChart.Export("D:\\tmp\\c
// Close Excel
app.UserControl = false;
app.DisplayAlerts = false
wb.Close();
app.Quit();
window.location = "D:///tmp/chrt.gif";
}
drawChart()
</SCRIPT>
<SCRIPT>
function drawChart(){
// Launch Excel
app = new ActiveXObject("Excel.Appli
// Add a new workbook
wb = app.workbooks.add
// Declare a range object to hold our data
rng = wb.Activesheet.Range("A1")
// 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.SetSourceDa
wb.Activesheet.PageSetup.O
xlChart.Export("D:\\tmp\\c
// Close Excel
app.UserControl = false;
app.DisplayAlerts = false
wb.Close();
app.Quit();
window.location = "D:///tmp/chrt.gif";
}
drawChart()
</SCRIPT>
ASKER
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.
Thanks for the points.