Personally, I think Microsoft Excel charts are powerful and look quite good so in this article, we will use SpreadsheetGear to connect to a workbook and fetch a chart as an image to display on an APS.NET web page.
Obviously websites could use a charting library or define their own chart like images using the data generated from Excel but in this example we will get the image of a chart that is generated in Excel.
This article is part of a set on using SpreadsheetGear to include the power of Microsoft Excel behind your website. This article does not cover the basics of using SpreadsheetGear so if you are unfamiliar with using SpreadsheetGear on the web then I would suggest looking How to build Microsoft Excel powered ASP.NET websites or there are some SpreadsheetGear ASP.NET examples on their website.
The Workbook
I was going to use the Budget Planner template that comes as part of Microsoft Excel 2013 but that had more information than I really needed for this example so I created my own shorter and simpler version.
It has an inputs sheet that actually sums the expenses too (row 13 is a sum of all the expenses on rows 3 to 12) and an outputs sheet that has a small table of data feeding a bar chart.
The ASP.NET Website
The website is straight forward like all the other websites created in these articles it has several inputs on a page and a button to run the calculations. The only difference is that this site has an extra page to handle an image with the SpreadsheetGear workbook object stored in session memory.
This is fine for small workbooks and works very quickly if you want to switch through multiple charts on your page without refreshing the data but I wouldn’t recommend it for large workbooks. There are more ways of handling charts on the page and this is just one of them. Some of the code for the GetImage.aspx page came from a SpreadsheetGear example for a Windows-based application. SpreadsheetGear do now have an ASP.NET example on their website.
Running the website gives a bunch of inputs and a chart because we have pre-filled the page with the variables and chart last used in the workbook when it was saved. You can change the values and click the Refresh button to view the revised totals and new chart.
The Website Code
Default.aspx Page
A typical page of inputs, with a button and some output labels and an image placeholder.
<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>
<!DOCTYPE html>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head id=”Head1″ runat=”server”>
<title>SpreadsheetGear Chart Demo</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table border=”0″ style=”border-collapse: collapse;”>
<tr>
<td> </td>
<td>Projected</td>
<td>Actual</td>
</tr>
<tr>
<td>Income</td>
<td><asp:TextBox ID=”projectedIncome” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualIncome” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Mortgage or rent</td>
<td><asp:TextBox ID=”projectedMortgage” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualMortgage” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Phone</td>
<td><asp:TextBox ID=”projectedPhone” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualPhone” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Electricity</td>
<td><asp:TextBox ID=”projectedElectricity” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualElectricity” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Gas</td>
<td><asp:TextBox ID=”projectedGas” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualGas” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Water and sewer</td>
<td><asp:TextBox ID=”projectedWater” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualWater” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>TV</td>
<td><asp:TextBox ID=”projectedTV” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualTV” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Car</td>
<td><asp:TextBox ID=”projectedCar” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualCar” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Maintenance or repairs </td>
<td><asp:TextBox ID=”projectedMaintenance” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualMaintenance” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Food or Supplies</td>
<td><asp:TextBox ID=”projectedFood” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualFood” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Other</td>
<td><asp:TextBox ID=”projectedOther” runat=”server” Text=””></asp:TextBox></td>
<td><asp:TextBox ID=”actualOther” runat=”server” Text=””></asp:TextBox></td>
</tr>
<tr>
<td>Expenses</td>
<td><asp:TextBox ID=”projectedExpenses” runat=”server” Text=”” ReadOnly=”true” Enabled=”False”></asp:TextBox></td>
<td><asp:TextBox ID=”actualExpenses” runat=”server” Text=”” ReadOnly=”true” Enabled=”False”></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Image ID=”Graph_Image” runat=”server” />
<p style=”vertical-align: middle;”>
<asp:Button runat=”server” ID=”refreshButton” Text=”Refresh” OnClick=”refreshButton_Click”></asp:Button>
</p>
</div>
</form>
</body>
</html>
Default.aspx.cs Code
This page works in the usual way but before calling for the chart image it stores a copy of the workbook state in memory. It adds a New GUID to the end of the request for the GetImage page to make sure it is refreshed and doesn’t use the cached image.
using System;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Open the workbook using the current culture (don’t want any defaults)
string baseDir = System.AppDomain.CurrentDomain.BaseDirectory;
string filePath = baseDir + “App_Data\BudgetPlanner.xlsx”;
SpreadsheetGear.IWorkbook WorkbookInterface = SpreadsheetGear.Factory.GetWorkbook(filePath, System.Globalization.CultureInfo.CurrentCulture);
// Reference the Inputs and Outputs worksheets
SpreadsheetGear.IWorksheet InputWorksheet = WorkbookInterface.Worksheets[“Inputs”];
SpreadsheetGear.IWorksheet OutputWorksheet = WorkbookInterface.Worksheets[“Outputs”];
// Add default values to text boxes on the web page
projectedIncome.Text = InputWorksheet.Cells[“B2”].Range.Text;
projectedMortgage.Text = InputWorksheet.Cells[“B3”].Range.Text;
projectedPhone.Text = InputWorksheet.Cells[“B4”].Range.Text;
projectedElectricity.Text = InputWorksheet.Cells[“B5”].Range.Text;
projectedGas.Text = InputWorksheet.Cells[“B6”].Range.Text;
projectedWater.Text = InputWorksheet.Cells[“B7”].Range.Text;
projectedTV.Text = InputWorksheet.Cells[“B8”].Range.Text;
projectedCar.Text = InputWorksheet.Cells[“B9”].Range.Text;
projectedMaintenance.Text = InputWorksheet.Cells[“B10”].Range.Text;
projectedFood.Text = InputWorksheet.Cells[“B11”].Range.Text;
projectedOther.Text = InputWorksheet.Cells[“B12”].Range.Text;
projectedExpenses.Text = InputWorksheet.Cells[“B13”].Range.Text;
actualIncome.Text = InputWorksheet.Cells[“C2”].Range.Text;
actualMortgage.Text = InputWorksheet.Cells[“C3”].Range.Text;
actualPhone.Text = InputWorksheet.Cells[“C4”].Range.Text;
actualElectricity.Text = InputWorksheet.Cells[“C5”].Range.Text;
actualGas.Text = InputWorksheet.Cells[“C6”].Range.Text;
actualWater.Text = InputWorksheet.Cells[“C7”].Range.Text;
actualTV.Text = InputWorksheet.Cells[“C8”].Range.Text;
actualCar.Text = InputWorksheet.Cells[“C9”].Range.Text;
actualMaintenance.Text = InputWorksheet.Cells[“C10”].Range.Text;
actualFood.Text = InputWorksheet.Cells[“C11”].Range.Text;
actualOther.Text = InputWorksheet.Cells[“C12”].Range.Text;
actualExpenses.Text = InputWorksheet.Cells[“C13”].Range.Text;
// Display the graph
Session[“workbook”] = WorkbookInterface;
Graph_Image.ImageUrl = “GetImage.aspx?ts=” + Guid.NewGuid().ToString(“N”);
Graph_Image.Visible = true;
}
}
protected void refreshButton_Click(object sender, EventArgs e)
{
// Open the workbook using the current culture (don’t want any defaults)
string baseDir = System.AppDomain.CurrentDomain.BaseDirectory;
string filePath = baseDir + “App_Data\BudgetPlanner.xlsx”;
SpreadsheetGear.IWorkbook WorkbookInterface = SpreadsheetGear.Factory.GetWorkbook(filePath, System.Globalization.CultureInfo.CurrentCulture);
// Reference the Inputs and Outputs worksheets
SpreadsheetGear.IWorksheet InputWorksheet = WorkbookInterface.Worksheets[“Inputs”];
SpreadsheetGear.IWorksheet OutputWorksheet = WorkbookInterface.Worksheets[“Outputs”];
// Post text box values to the workbook
InputWorksheet.Cells[“B2”].Range.Value = projectedIncome.Text;
InputWorksheet.Cells[“B3”].Range.Value = projectedMortgage.Text;
InputWorksheet.Cells[“B4”].Range.Value = projectedPhone.Text;
InputWorksheet.Cells[“B5”].Range.Value = projectedElectricity.Text;
InputWorksheet.Cells[“B6”].Range.Value = projectedGas.Text;
InputWorksheet.Cells[“B7”].Range.Value = projectedWater.Text;
InputWorksheet.Cells[“B8”].Range.Value = projectedTV.Text;
InputWorksheet.Cells[“B9”].Range.Value = projectedCar.Text;
InputWorksheet.Cells[“B10”].Range.Value = projectedMaintenance.Text;
InputWorksheet.Cells[“B11”].Range.Value = projectedFood.Text;
InputWorksheet.Cells[“B12”].Range.Value = projectedOther.Text;
InputWorksheet.Cells[“C2”].Range.Value = actualIncome.Text;
InputWorksheet.Cells[“C3”].Range.Value = actualMortgage.Text;
InputWorksheet.Cells[“C4”].Range.Value = actualPhone.Text;
InputWorksheet.Cells[“C5”].Range.Value = actualElectricity.Text;
InputWorksheet.Cells[“C6”].Range.Value = actualGas.Text;
InputWorksheet.Cells[“C7”].Range.Value = actualWater.Text;
InputWorksheet.Cells[“C8”].Range.Value = actualTV.Text;
InputWorksheet.Cells[“C9”].Range.Value = actualCar.Text;
InputWorksheet.Cells[“C10”].Range.Value = actualMaintenance.Text;
InputWorksheet.Cells[“C11”].Range.Value = actualFood.Text;
InputWorksheet.Cells[“C12”].Range.Value = actualOther.Text;
// Get expense values
projectedExpenses.Text = InputWorksheet.Cells[“B13”].Range.Text;
actualExpenses.Text = InputWorksheet.Cells[“C13”].Range.Text;
// Display the graph
Session[“workbook”] = WorkbookInterface;
Graph_Image.ImageUrl = “GetImage.aspx?ts=” + Guid.NewGuid().ToString(“N”);
Graph_Image.Visible = true;
}
}
GetImage.aspx Page
I have not separated the code as it’s mostly code and not much HTML. It’s been commented up so you can see that it first obtains a reference to the workbook stored in the session and then fetches Chart 1 from the workbook before writing it out as a bitmap PNG from a memory stream. The page returns an image and this is uses in the Image placeholder on the website.
<%@ Page Language=”C#” EnableViewState=”false” %>
<%@OutputCache Duration=”86400″ VaryByParam=”*” %>
<!DOCTYPE html>
<script language=”C#” runat=”server”>
void Page_Load(Object sender, EventArgs e)
{
// Open the workbook in the current session
SpreadsheetGear.IWorkbook workbook = (SpreadsheetGear.IWorkbook)Session[“workbook”];
// Reference the Outputs worksheet
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[“Outputs”];
// Create the Image renderer for the requested range or shape.
SpreadsheetGear.Drawing.Image image = null;
SpreadsheetGear.Shapes.IShape shape = worksheet.Shapes[“Chart 1”];
image = new SpreadsheetGear.Drawing.Image(shape);
// Check whether we have something valid to render…
if (image == null)
throw new InvalidOperationException(“Missing shape or range parameter.”);
// Get the size of the image to be generated.
System.Drawing.Size size = image.GetSize();
// Check the size of the image to see if it is reasonable.
if (size.Width <= 0 || size.Width > 2048 || size.Height <= 0 || size.Height > 2048)
throw new InvalidOperationException(“Invalid Image Size (probably specified too large of a range.”);
// Get a new bitmap image of the represented range or shape.
using (System.Drawing.Bitmap bitmap = image.GetBitmap())
{
// Stream the image to the client in PNG or GIF format.
Response.Clear();
// Save to a memory stream and write that to the response output stream.
Response.ContentType = “image/png”;
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
bitmap.Save(memoryStream, System.Drawing.Imaging.ImageFormat.Png);
memoryStream.WriteTo(Response.OutputStream);
}
}
</script>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head id=”Head1″ runat=”server”>
<title>SpreadsheetGear Excel Chart Image</title>
</head>
<body>
</body>
</html>
The example links and code downloads
If you would like to view the website example yourself please click the following links; Budget Planner with Chart.
You can download the Budget Planner Spreadsheet workbook and have a go at the above code yourself or you could download the website project example (including the Workbooks but excluding the SpreadsheetGear DLL) here Budget Planner Project Files (it is in Visual Studio 2008 ASP.NET C# and you will just need to add a reference to your installed version of SpreadsheetGear).