<%@ Page Language="C#" EnableViewState="false" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Stock Volume-Open-High-Low-Close Chart using ASP.NET and C# - Returns Open XML (XLSX) Workbook Using SpreadsheetGear 2010</title>
<meta content="Stock Volume-Open-High-Low-Close Chart with ASP.NET, C# and SpreadsheetGear 2010, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft .NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft .NET, ASP.NET, C#, VB.NET and Microsoft Office solutions. Integrates with Microsoft Visual Studio .NET, including IntelliSense and Dynamic Help." name="description" />
<script language="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
// Add a chart to the worksheet's shape collection.
// NOTE: Calculate the coordinates of the chart by converting row
// and column coordinates to points. Use fractional row
// and colum values to get coordinates anywhere in between
// row and column boundaries.
double left = 10;
double top = windowInfo.RowToPoints(0.25);
double right = windowInfo.ColumnToPoints(7.0) - 10;
double bottom = windowInfo.RowToPoints(15.75);
SpreadsheetGear.Charts.IChart chart =
worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart;
// Get the source data range from an existing XML File.
SpreadsheetGear.IRange source = GetSourceData(worksheet);
// Set the chart's source data range, plotting series in columns.
chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);
// Set the chart type to a volume-open-high-low-close stock chart.
chart.ChartType = SpreadsheetGear.Charts.ChartType.StockVOHLC;
// Get a reference to the first chart group used for volume series.
SpreadsheetGear.Charts.IChartGroup volumeGroup = chart.ChartGroups[0];
// Increase the volume series bar width by decreasing the gap width.
volumeGroup.GapWidth = 75;
// Get a reference to the second chart group used for stock values.
SpreadsheetGear.Charts.IChartGroup stockGroup = chart.ChartGroups[1];
// Change the fill color of the down bars.
stockGroup.DownBars.Format.Fill.ForeColor.RGB = System.Drawing.Color.Red;
// Change the fill color of the up bars.
stockGroup.UpBars.Format.Fill.ForeColor.RGB = System.Drawing.Color.Green;
// Get a reference to the primary category axis.
SpreadsheetGear.Charts.IAxis categoryAxis =
chart.Axes[SpreadsheetGear.Charts.AxisType.Category];
// Change the primary category axis to always use a category scale.
categoryAxis.CategoryType =
SpreadsheetGear.Charts.CategoryType.CategoryScale;
// Set the label frequency of the primary category axis.
categoryAxis.TickLabelSpacing = 7;
// Get a reference to the primary value axis.
SpreadsheetGear.Charts.IAxis valueAxis =
chart.Axes[SpreadsheetGear.Charts.AxisType.Value];
// Set the min, max, and major units of the primary value axis.
valueAxis.MinimumScale = 0;
valueAxis.MaximumScale = 130000;
valueAxis.MajorUnit = 26000;
// Hide the major gridlines on the primary value axis.
valueAxis.HasMajorGridlines = false;
// Get a reference to the secondary value axis.
valueAxis = chart.Axes[
SpreadsheetGear.Charts.AxisType.Value,
SpreadsheetGear.Charts.AxisGroup.Secondary];
// Set the min, max, and major units of the secondary value axis.
valueAxis.MinimumScale = 0;
valueAxis.MaximumScale = 130;
valueAxis.MajorUnit = 26;
// Hide the major gridlines on the secondary value axis.
valueAxis.HasMajorGridlines = false;
// Delete the legend.
chart.HasLegend = false;
// Add a chart title and change the font size.
chart.HasTitle = true;
chart.ChartTitle.Text = "ABC Company (Volume-Open-High-Low-Close)";
chart.ChartTitle.Font.Size = 10;
// Stream the Excel spreadsheet to the client in Excel 2007-2010 Open XML format.
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=report.xlsx");
workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
Response.End();
}
private SpreadsheetGear.IRange GetSourceData(SpreadsheetGear.IWorksheet worksheet)
{
// Load a data set from an XML file with sample stock data.
String xmlfile = Server.MapPath("files/stockvohlc.xml");
System.Data.DataSet dataset = new System.Data.DataSet();
dataset.ReadXml(xmlfile);
// Get a reference to the VOHLC data table.
System.Data.DataTable datatable = dataset.Tables["VOHLC"];
// Get a reference to a range matching the size of the data table.
int row1 = 0;
int col1 = 7;
int row2 = row1 + datatable.Rows.Count;
int col2 = col1 + datatable.Columns.Count - 1;
SpreadsheetGear.IRange source = worksheet.Cells[row1, col1, row2, col2];
// Copy the data table to the range.
source.CopyFromDataTable(
datatable, SpreadsheetGear.Data.SetDataFlags.None);
// return the range.
return source;
}
</script>
</head>
<body>
</body>
</html>