Hi,
I am using Excel chart for web based application. I have created one application for generate the excel chart using vb dll. Vb code is based on excel macro. But if 10 users generate the chart then 10 excel instances are running in server machine. If 100 users connect this dll at same time then 100 excel instances will be running in server system. I want to generate the excel chart with out opening the excel instance. Any solution is there? The following is the sample code for my application.
Note: I have not select the sheet or cell at any place in my work book. Why should i open the work book.? Can i generate the chart without open the excel book. Like file format.
Dim xlApp As Excel.Application '// Hold reference to Excel Application
Dim xlWrkbk As Excel.Workbook '// Hold reference to Full Template Excel Workbook
Dim objWorkSheet As Excel.Worksheet '// Hold reference to Excel sheet
Set xlApp = New Excel.Application
xlApp.Visible = False
'// Open the Report Excel and the Full Template Excel
Set xlWrkbk = xlApp.Workbooks.Open(OutputFileName & ".xls")
' Generate the excel chart:
'// Chart generation part
With xlWrkbk.Sheets(Sheet_name).ChartObjects.Add(Top:=xlApp.Sheets(Sheet_name).Range("B" + CStr(iPosition)).Top, Left:=xlApp.Sheets(Sheet_name).Range("B" + CStr(iPosition)).Left, Height:=sHeight, Width:=483)
With .Chart
.ChartType = xlBarClustered
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=(" + sXValues + ")"
.SeriesCollection(1).Values = "=(" + sProject_values + ")"
.SeriesCollection(1).Name = "=""project"""
.SeriesCollection(2).Values = "=(" + sComparison_values + ")"
.SeriesCollection(2).Name = "=""Comparison"""
.SeriesCollection(3).Values = "=(" + iStandard_values + ")"
.SeriesCollection(3).Name = "=""Industry Standard"""
.SeriesCollection(4).Name = "=""Target Data"""
If iColumn_total = 6 And sProject_Range <> "" Then
sProject_Range = Mid(sProject_Range, 2, Len(sProject_Range))
.SeriesCollection(4).Values = "=(" + sProject_Range + ")"
Else
.SeriesCollection(4).Values = "={0}"
End If
.HasLegend = False
.HasDataTable = False
sChartName = .Name
sChartName = Mid(sChartName, Len(Sheet_name) + 2, Len(sChartName))
'// Chart Area
.ChartArea.Border.LineStyle = 0
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9.25
End With
'// Plot Area
.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
'// Axis
With .Axes(xlValue).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
If ScaleMode = True Then
.Axes(xlValue).MaximumScale = 10
End If
'// Y - Axis
.Axes(xlCategory).TickLabelPosition = xlLow
With .Axes(xlCategory)
.Crosses = xlMaximum
.AxisBetweenCategories = True
.ReversePlotOrder = True
End With
With .Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.ColorIndex = 11
End With
.Axes(xlCategory).TickLabels.Offset = 0
'// Grid Lines
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 48
'// Data Series
.SeriesCollection(1).Border.LineStyle = xlNone
With .SeriesCollection(1).Interior
.ColorIndex = 11
.Pattern = xlSolid
End With
.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlNone, _
Type:=xlFixedValue, Amount:=1
.SeriesCollection(2).Border.LineStyle = xlNone
.SeriesCollection(2).Interior.ColorIndex = 17
.SeriesCollection(2).ErrorBar Direction:=xlY, Include:=xlNone, _
Type:=xlFixedValue, Amount:=1
.SeriesCollection(3).Border.LineStyle = xlNone
.SeriesCollection(3).Interior.ColorIndex = 44
.SeriesCollection(3).ErrorBar Direction:=xlY, Include:=xlNone, _
Type:=xlFixedValue, Amount:=1
If iColumn_total = 6 Then
.SeriesCollection(4).Border.LineStyle = xlNone
.SeriesCollection(4).Interior.ColorIndex = 43
.SeriesCollection(4).ErrorBar Direction:=xlY, Include:=xlNone, _
Type:=xlFixedValue, Amount:=1
End If
With .ChartGroups(1)
.Overlap = 0
.GapWidth = 50
.HasSeriesLines = False
End With
End With
End With
....................
.......
Note: I have not select the sheet or cell at any place in my work book. Why should i open the work book.? Can i generate the chart without open the excel book. Like file format.
Regards,
Ram..
Bookmarks