Microsoft Excel provides easy to use and very good (in my opinion) functionality for most business applications and with its charting capabilities and extensibility through Visual Basic for Applications (VBA) and data links to other storage applications it gives you a powerful tool at your fingertips on your desktop or laptop.
That is great if you are with your client or don’t mind sending them your Workbook to use on their machines but it’s not very practical if you want to protect your intellectual property or view on different devices such as tablets or smart phones. Sharing your tools online legitimately has always been a challenge but using various components it can be done. This article shows you how to do exactly that; using an Excel workbook as the calculation engine for your dynamic ASP.NET website without installing Excel on your server or using SharePoint.
Microsoft Excel is a desktop application and is only licensed as such. It is of course possible to install it on a server but this is unlicensed for anyone to use online except the original individual that owns the license key. It is also slow to load all of Excel every time you access a webpage. SharePoint runs a cut down version of Excel if you want to share or edit data online in Excel format. there are plenty of component makers that also offer you cut-down Excel experiences that you can bring to webpages but my favourite and in my opinion the best is SpreadsheetGear.
The majority of examples you can find on SpreadsheetGear are for developing desktop applications (Windows Forms) and there is little documented for the web. In this article I will go through just the basics of setting up a website where a user can enter their date of birth and get their age in years months and days. In a later article I will cover macros, charts and moving large sets of data to a separate source to improve speed.
Getting started
You will need Microsoft Visual Studio (VS) to create an ASP.NET (VB.NET or C#) website and to work with SpreadsheetGear (SSG). I have SpreadsheetGear 2012 for .NET and Visual Studio Ultimate 2012 (but Pro and above should work just fine or even earlier version of Visual Studio as I also run SSG in Visual Studio Team System 2008 but I’m not sure about the Express editions which are not licensed for commercial use). Make sure you have both VS and SSG installed before you continue.
Create a new Visual Studio website. Here I’ve chosen C# empty website using .NET 2.0.
Then add a new Web Form and set it as the Start Page. Begin by right-clicking on the Project and selecting Add New Item. I’ve accepted the default as Default.aspx and then right-clicked on it to Set As Start Page.
Add a reference to SpreadsheetGear by right-clicking on the Project and selecting Add Reference.
You can either download my workbook example AgeCalcs.xlsx or create it yourself based on the screenshots below.
The Excel workbook will need to be in a location that is accessible to the website. In the example I am using the ASP.NET folder App_Data which you can get by adding a new ASP.NET folder to the project. Once created copy the Workbook into this location.
Now for the website code
The source of the Default.aspx page needs to have a text box for the date of birth entry and some labels added to show the results. The following code should be added between the DIV tags.
<div>
<p style=”vertical-align: middle;”>
<asp:Label runat=”server” ID=”dobLabel”>Date of birth: </asp:Label>
<asp:TextBox runat=”server” ID=”dobBox”>01/01/1980</asp:TextBox>
<asp:Button runat=”server” ID=”ageCalc” Text=”Calculate” OnClick=”ageCalc_Click”></asp:Button>
</p>
<p style=”vertical-align: middle;”>
<asp:Label runat=”server” ID=”todayLabel”>Date today is: </asp:Label>
<asp:Label runat=”server” ID=”todayResult” Text=””></asp:Label>
</p>
<p style=”vertical-align: middle;”>
<asp:Label runat=”server” ID=”ymLabel”>Your Y/M age is: </asp:Label>
<asp:Label runat=”server” ID=”ymResult” Text=””></asp:Label>
</p>
<p style=”vertical-align: middle;”>
<asp:Label runat=”server” ID=”ymdLabel”>Your Y/M/D age is: </asp:Label>
<asp:Label runat=”server” ID=”ymdResult” Text=””></asp:Label>
</p>
</div>
And the code behind
Then go to the code page, Default.aspx.cs and find the ageCalc_OnClick event to add the following code to.
protected void ageCalc_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\AgeCalcs.xlsx”;
SpreadsheetGear.IWorkbook WorkbookInterface = SpreadsheetGear.Factory.GetWorkbook(filePath, System.Globalization.CultureInfo.CurrentCulture);
// Refence the Inputs and Outputs worksheets
SpreadsheetGear.IWorksheet InputWorksheet = WorkbookInterface.Worksheets[“Inputs”];
SpreadsheetGear.IWorksheet OutputWorksheet = WorkbookInterface.Worksheets[“Outputs”];
// Get ranges from defined names.
SpreadsheetGear.IRange inputDOB = InputWorksheet.Cells[“DOB”].Range;
SpreadsheetGear.IRange outputDateToday = OutputWorksheet.Cells[“DateToday”].Range;
SpreadsheetGear.IRange outputAgeInYearsAndMonths = InputWorksheet.Cells[“AgeInYearsAndMonths”].Range;
SpreadsheetGear.IRange outputAgeInYearsAndMonthAndDays = InputWorksheet.Cells[“AgeInYearsAndMonthAndDays”].Range;
// Copy the values to the worksheet
inputDOB.Formula = dobBox.Text;
// Display the calculated result.
todayResult.Text = outputDateToday.Text;
ymResult.Text = outputAgeInYearsAndMonths.Text;
ymdResult.Text = outputAgeInYearsAndMonthAndDays.Text;
}
The code first of all establishes a connection through the SpreadsheetGear component to the Workbook.
Then it creates references to the Inputs and Outputs Worksheets. The calculations Worksheet is not needed as that does all the work behind the scenes.
You next need to reference some of the cells on those Worksheets. I have defined names for each of the cells that will be used but you could refer to them as B1, B2, etc…
You must then pass the value of the date of birth text box to the Inputs DOB cell. Lastly you assign the values on the results labels to the calculated results in the Output Worksheet.
And finally
That was a simple example but there will be more to follow in my next instalment. You can download the project code Age Calc Project Files (that file excludes the SSG DLL which you will have to include yourself) or view the age calculator results page age calculator results page.