Hi,
I have this code to generate Excel charts, but I can´t manage to generate customs excel charts, I think is because I don´t have the Gallery constants, can someane take a look and help me find whow to generate this type of charts.
Thanks
Albano
All Code with html form:
<%@ LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
Dim mintPass, mstrFileName
Dim i ' misc variable
Randomize Timer
mintPass = Request("pass")
Select Case Int(mintPass)
Case 1
HandleRepeatVisit
End Select
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Create "Excel"lent Charts with ASP</TITLE>
<STYLE TYPE="text/css">
<!-- //
BODY {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
}
TH {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
font-weight: bold;
background-color: #DDDDDD;
}
TD {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
background-color: #EEEEEE;
}
// -->
</STYLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<BASEFONT FACE="Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<FORM ACTION="10MinChart.asp" METHOD="post">
<INPUT TYPE="hidden" NAME="pass" VALUE="1">
<TABLE BORDER="0" CELLPADDING="5" CELLSPACING="2" WIDTH="750">
<TR>
<TD ALIGN="CENTER" VALIGN="TOP" COLSPAN="3"><STRONG>Create "Excel" lent Charts with ASP!</STRONG></TD></TR>
<TR>
<TD ALIGN="CENTER" VALIGN="TOP" COLSPAN="3"><STRONG>- by Rama Ramachandran</STRONG>
<FONT FACE="ARIAL" SIZE="1"><A HREF="10MinChart.Zip">Get the Code for this page here</A>.</FONT>
</TD></TR>
<TR>
<TD ALIGN="CENTER" VALIGN="TOP">Stock Price for:</TD>
<TD ALIGN="LEFT" VALIGN="TOP"><INPUT TYPE="text" NAME="co" SIZE="25" VALUE="Microsoft"></TD>
<TD ALIGN="CENTER" VALIGN="MIDDLE" ROWSPAN="13">
<% If mstrFileName <> "" Then
Response.write "<IMG SRC=""/graficos/chartExcel2/" & mstrFileName & """>"
Else
Response.write "<IMG SRC=""spacer.gif"" WIDTH=""100"" HEIGHT=""1"" BORDER=""0"">"
End If
%>
</TD>
</TR>
<%
For i = 1 to 12 %>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">Date: <%= FormatDateTime(i & "/01/99", 2)%></TD>
<TD ALIGN="LEFT" VALIGN="TOP">$ :<INPUT TYPE="text" NAME="v<%= i%>" VALUE="<% If mintPass=1 Then Response.Write Request("v" & i) Else Response.Write Int((100)*Rnd+1) End If %>" SIZE="10" MAXLENGTH="5"></TD>
</TR>
<%
Next %>
</TABLE>
<INPUT TYPE="submit" NAME="cmd" VALUE=" Generate Chart">
</FORM>
</BODY>
</HTML>
<%
Sub HandleRepeatVisit()
Dim xlapp ' Our Excel App
Dim wb ' Our Workbook within the Excel App
Dim ws ' Our Worksheet within the Workbook
Dim crt ' The chart object
Dim SourceRange ' The Source Range for the chart object
Const xlWorkSheet = -4167
Const xlLineMarkers = 65
Const xlCombination = -4111
Const xlColumn = 3
Const xlBuiltIn = 21
Const xlUserDefined = 22
' -- Create an instance of Excel Application
Set xlapp = Server.CreateObject("Excel.Application")
' -- Create a new workbook
Set wb = xlapp.Workbooks.Add(xlWorksheet)
' -- Grab the first worksheet of the new workbook
Set ws = wb.Worksheets(1)
' -- Insert the data the user requested
' -- First, the title
ws.Range("A1").Value = Request("co") ' -- defaults to "Microsoft"
' -- Then the data in two vertical columns
For i = 1 To 12
ws.Range("A" & i + 1).Value = FormatDateTime(i & "/01/99", 2)
ws.Range("B" & i + 1).Formula = "=" & Request("v" & i)
Next
' -- Set our source range
Set SourceRange = ws.Range("A2:B13")
' -- Create a new Chart Object
Set crt = ws.ChartObjects.Add(20, 20, 300, 200)
' -- Generate the Chart using the ChartWizard
' -- Syntax is:
' -- crt.Chart.ChartWizard Source:=SourceRange, gallery:=xlLine(4), PlotBy:=xlColumns(default), _
' -- categorylabels:=1, serieslabels:=0, HasLegend:=2, Title:="Company Stock Value"
crt.Chart.ChartWizard SourceRange, 23, , 2, 1, 0, 2, Request("co") & " Stock Value"
' -- Configure the Chart
crt.Chart.ChartType = xlUserDefined 'xlLineMarkers
crt.Chart.SeriesCollection(1).Name = "Sheet1!R1C1"
crt.Chart.HasTitle = True
crt.Chart.Axes(1, 1).HasTitle = True
crt.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Months"
crt.Chart.Axes(2, 1).HasTitle = True
crt.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Stock Price"
' -- Determine the name to save this chart as. Use the current Seconds value, overwriting previous
' -- ones
mstrFileName = "junk" & Second(Now()) & ".jpg"
' -- Save the chart on web server
crt.Chart.Export Server.Mappath("/graficos/chartExcel2/") & "\" & mstrFileName, "jpg"
' -- Fool Excel into thinking the Workbook is saved
wb.Saved = True
' -- Set all objects back to nothing
Set crt = Nothing
Set wb = Nothing
' -- Quit Excel to conserve resources
xlapp.Quit
Set xlapp = Nothing
' -- Make sure the Image is not cached but is loaded fresh from the web server
Response.AddHeader "expires","0"
Response.AddHeader "pragma", "no-cache"
Response.AddHeader "cache-control","no-cache"
End Sub
%>
I have this code to generate Excel charts, but I can´t manage to generate customs excel charts, I think is because I don´t have the Gallery constants, can someane take a look and help me find whow to generate this type of charts.
Thanks
Albano
All Code with html form:
<%@ LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
Dim mintPass, mstrFileName
Dim i ' misc variable
Randomize Timer
mintPass = Request("pass")
Select Case Int(mintPass)
Case 1
HandleRepeatVisit
End Select
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Create "Excel"lent Charts with ASP</TITLE>
<STYLE TYPE="text/css">
<!-- //
BODY {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
}
TH {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
font-weight: bold;
background-color: #DDDDDD;
}
TD {
font-family: Verdana, Arial, Helvetica, Sans Serif;
font-size: 10pt;
background-color: #EEEEEE;
}
// -->
</STYLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<BASEFONT FACE="Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<FORM ACTION="10MinChart.asp" METHOD="post">
<INPUT TYPE="hidden" NAME="pass" VALUE="1">
<TABLE BORDER="0" CELLPADDING="5" CELLSPACING="2" WIDTH="750">
<TR>
<TD ALIGN="CENTER" VALIGN="TOP" COLSPAN="3"><STRONG>Create "Excel" lent Charts with ASP!</STRONG></TD></TR>
<TR>
<TD ALIGN="CENTER" VALIGN="TOP" COLSPAN="3"><STRONG>- by Rama Ramachandran</STRONG>
<FONT FACE="ARIAL" SIZE="1"><A HREF="10MinChart.Zip">Get the Code for this page here</A>.</FONT>
</TD></TR>
<TR>
<TD ALIGN="CENTER" VALIGN="TOP">Stock Price for:</TD>
<TD ALIGN="LEFT" VALIGN="TOP"><INPUT TYPE="text" NAME="co" SIZE="25" VALUE="Microsoft"></TD>
<TD ALIGN="CENTER" VALIGN="MIDDLE" ROWSPAN="13">
<% If mstrFileName <> "" Then
Response.write "<IMG SRC=""/graficos/chartExcel2/" & mstrFileName & """>"
Else
Response.write "<IMG SRC=""spacer.gif"" WIDTH=""100"" HEIGHT=""1"" BORDER=""0"">"
End If
%>
</TD>
</TR>
<%
For i = 1 to 12 %>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">Date: <%= FormatDateTime(i & "/01/99", 2)%></TD>
<TD ALIGN="LEFT" VALIGN="TOP">$ :<INPUT TYPE="text" NAME="v<%= i%>" VALUE="<% If mintPass=1 Then Response.Write Request("v" & i) Else Response.Write Int((100)*Rnd+1) End If %>" SIZE="10" MAXLENGTH="5"></TD>
</TR>
<%
Next %>
</TABLE>
<INPUT TYPE="submit" NAME="cmd" VALUE=" Generate Chart">
</FORM>
</BODY>
</HTML>
<%
Sub HandleRepeatVisit()
Dim xlapp ' Our Excel App
Dim wb ' Our Workbook within the Excel App
Dim ws ' Our Worksheet within the Workbook
Dim crt ' The chart object
Dim SourceRange ' The Source Range for the chart object
Const xlWorkSheet = -4167
Const xlLineMarkers = 65
Const xlCombination = -4111
Const xlColumn = 3
Const xlBuiltIn = 21
Const xlUserDefined = 22
' -- Create an instance of Excel Application
Set xlapp = Server.CreateObject("Excel.Application")
' -- Create a new workbook
Set wb = xlapp.Workbooks.Add(xlWorksheet)
' -- Grab the first worksheet of the new workbook
Set ws = wb.Worksheets(1)
' -- Insert the data the user requested
' -- First, the title
ws.Range("A1").Value = Request("co") ' -- defaults to "Microsoft"
' -- Then the data in two vertical columns
For i = 1 To 12
ws.Range("A" & i + 1).Value = FormatDateTime(i & "/01/99", 2)
ws.Range("B" & i + 1).Formula = "=" & Request("v" & i)
Next
' -- Set our source range
Set SourceRange = ws.Range("A2:B13")
' -- Create a new Chart Object
Set crt = ws.ChartObjects.Add(20, 20, 300, 200)
' -- Generate the Chart using the ChartWizard
' -- Syntax is:
' -- crt.Chart.ChartWizard Source:=SourceRange, gallery:=xlLine(4), PlotBy:=xlColumns(default), _
' -- categorylabels:=1, serieslabels:=0, HasLegend:=2, Title:="Company Stock Value"
crt.Chart.ChartWizard SourceRange, 23, , 2, 1, 0, 2, Request("co") & " Stock Value"
' -- Configure the Chart
crt.Chart.ChartType = xlUserDefined 'xlLineMarkers
crt.Chart.SeriesCollection(1).Name = "Sheet1!R1C1"
crt.Chart.HasTitle = True
crt.Chart.Axes(1, 1).HasTitle = True
crt.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Months"
crt.Chart.Axes(2, 1).HasTitle = True
crt.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Stock Price"
' -- Determine the name to save this chart as. Use the current Seconds value, overwriting previous
' -- ones
mstrFileName = "junk" & Second(Now()) & ".jpg"
' -- Save the chart on web server
crt.Chart.Export Server.Mappath("/graficos/chartExcel2/") & "\" & mstrFileName, "jpg"
' -- Fool Excel into thinking the Workbook is saved
wb.Saved = True
' -- Set all objects back to nothing
Set crt = Nothing
Set wb = Nothing
' -- Quit Excel to conserve resources
xlapp.Quit
Set xlapp = Nothing
' -- Make sure the Image is not cached but is loaded fresh from the web server
Response.AddHeader "expires","0"
Response.AddHeader "pragma", "no-cache"
Response.AddHeader "cache-control","no-cache"
End Sub
%>
