ASP.NET(vb.net) & Charts/Graph - Cylinder Column Clustered

Author: admin | Posted: 19-06-2010 | Category: ASP.NET(vb.net) & Chart/Graph (Excel.Application)

ASP.NET(vb.net) & Charts/Graph - Cylinder Column Clustered - The in this tutorial, you’ll learn and example scripts how to Create charts Cylinder Column Clustered using by ASP.NET & Excel Application scripts.

ShotDev Focus:
- ASP.NET(vb.net) & Charts/Graph - Cylinder Column Clustered

Example

AspNetExcelChartsPart5.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="Excel"%>
<%@ Import Namespace="System.IO"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New System.Data.DataTable

Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database/mydatabase.mdb") & ";"
objConn = New OleDbConnection(strConnString)
objConn.Open()

Dim strSQL As String
strSQL = "SELECT * FROM customer"

'*** Create DataTable ***'
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)

'*** Export To Excel ***'
Dim FileName As String = "MyXls/MyExcel.xls"

'*** Create Exce.Application ***'
Dim xlApp As New Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim i, intStartRows As Integer

xlBook = xlApp.Workbooks.Add()
xlSheet = xlBook.Worksheets(1)

xlApp.Application.Visible = False

'*** Delete Sheet (2,3) - Sheet Default ***'
xlBook.Worksheets(2).Select()
xlBook.Worksheets(2).Delete()
xlBook.Worksheets(2).Select()
xlBook.Worksheets(2).Delete()

xlBook.ActiveSheet.Name = "Customer"

With xlBook.Sheets("Customer").Cells(1,1)
.Value = "Customer Name"
.Font.Name = "Tahoma"
.BORDERS.Weight = 1
.Font.Size = 10
.MergeCells = True
End With

With xlBook.Sheets("Customer").Cells(1,2)
.Value = "Budget"
.BORDERS.Weight = 1
.Font.Name = "Tahoma"
.Font.Size = 10
.MergeCells = True
End With

With xlBook.Sheets("Customer").Cells(1,3)
.Value = "Used"
.BORDERS.Weight = 1
.Font.Name = "Tahoma"
.Font.Size = 10
.MergeCells = True
End With

intStartRows = 2
For i = 0 To dt.Rows.Count - 1
xlBook.Sheets("Customer").Cells(intStartRows+i,1).Value = dt.Rows(i)("Name")
xlBook.Sheets("Customer").Cells(intStartRows+i,2).Value = dt.Rows(i)("Budget")
xlBook.Sheets("Customer").Cells(intStartRows+i,2).NumberFormat = "$#,##0.00"
xlBook.Sheets("Customer").Cells(intStartRows+i,3).Value = dt.Rows(i)("Used")
xlBook.Sheets("Customer").Cells(intStartRows+i,3).NumberFormat = "$#,##0.00"

Next

Dim objRange,colCharts,objChart As Object
objRange = xlBook.Sheets("Customer").UsedRange
objRange.Select

colCharts = xlApp.Charts
colCharts.Add()
objChart = colCharts(1)
xlApp.ActiveChart.Name = "MyChart"
With objChart
.ChartType = 92
.HasLegend = True
.HasTitle = 1
.ChartTitle.Text = "Customer Report"
.ChartTitle.Characters.Text = "Customer Report"
.ChartTitle.Font.Name = "Tahoma"
.ChartTitle.Font.FontStyle = "Bold"
.ChartTitle.Font.Size = 30
.ChartTitle.Font.ColorIndex = 3
.Activate
End With

'*** If Files Already Exist Delete files ***'
Dim MyFile As New FileInfo(Server.MapPath(FileName))
If MyFile.Exists Then
MyFile.Delete()
End IF
MyFile = Nothing

'*** Save Excel ***'
'xlSheet.PrintOut 1 '*** Print to printer ***'
xlSheet.SaveAs(Server.MapPath(FileName))

xlApp.Application.Quit()
xlApp.Quit()

'*** Quit and Clear Object ***'
xlSheet = Nothing
xlBook = Nothing
xlApp = Nothing

'*** End Export To Excel ***'
Me.lblText.Text = "Charts Created <a href=" & FileName & ">Click here</a> to Download."

dtAdapter = Nothing
objConn.Close()
objConn = Nothing
End Sub
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>

Screenshot

.
.
.
Download this script.

.
.
.