Working with large amounts of data can be slow going using Microsoft Excel’s internal lookup functions which is amplified if you are publishing lookup results on the internet dynamically.
In this example, I have created a large(ish) table of sales data that I want to be able to pick from using some specified criteria. All of the information in this table is dummy data and even the specified criteria are probably not what a sales manager would be looking for. The purpose of this data is to show that handling large data sets is best accomplished outside of Excel especially if you want to display it on the web.
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
The workbook has three sheets; “Inputs”, “Outputs” and “Tables”. The “Tables” sheet contains all of the 43,200 rows of sales data.
The “Inputs” sheet contains inputs for several criteria which broadly relate to the data held with two exceptions; the data is held monthly but the input is quarterly and the data is held in customer age bandings where the input asks for the average customer age.
The “Outputs” contains the formula results that are going to be displayed on the website.
I will not go into the formula in great detail but working out the age band is done using the INDEX function on a MATCH and the three months that make up the quarter are looked up separately using the SUMPRODUCT function with the month calculated by multiplying the quarter by 3 and then taking away a month or two when appropriate. You will also probably notice that I only use the minimum customer age band rather than both as they are fixed so if I know one the other will always be the same. Here are a couple of examples:
Customer Age Min
=INDEX({0,25,40,55,70},MATCH(Average_Customer_Age,{0,25,40,55,70},1))
Month 1
=SUMPRODUCT((Employee_Input=Employee)*(Year_Input=Year)*(((Quarter_Input*3)-2)=Month)*(Customer_Age_Min=MinAge)*(Range_Input=Range)*(Manufacturer_Input=Manufacturer),Amount)
When you change the inputs, even in Excel, it takes half a second or longer to fetch the results. Imagine if this sales data contained many more rows and even more columns than the eight we have, which wouldn’t be unusual. It would take a long time to get your results and it will take even longer when running it on the web with Excel as your backend engine, which you would need if you were going to use the results of the lookups further along in your workbook. Just to see it like that I will create a Visual Studio web application.
The ASP.NET websites
I have created three ASP.NET websites all with the same code but each with a slight difference. The first one uses the Excel SUMPRODUCT function to look up the monthly sales figures. The second uses OLEDB to connect to the spreadsheet and lookup the monthly sales figures and the last one removes values in that.
You can download all of the code of all three projects but I’ve also listed them at the bottom of the page. Each website has a set of inputs that are all the same.
I have kept the code behind each performing the same actions except in the way it fetches the data and I’ve used a stopwatch from System.Diagnostics to keep track of the calculation time.
Now for the experiment to see which method will work best. These have been run on my local 32bit machine so you might find you get better results or different results on other machines with other circumstances. The first method with everything in Excel is the slowest running at over ten seconds.
Keeping the data in Excel but using a database connection gets that same code down to three and a half seconds. This one sets Excel to Manual calculations rather than Automatic because we don’t want the formula to start searching for values before we replace them with our own values.
Taking the data out of Excel and putting it in a CSV file was by far the quickest method for me at just over a second but I have since run the second calculation again with the old 97-2003 Excel format and using JET 4.0 rather than ACE 12.0 and this improved the response time to almost as good as the CSV file. This final CSV example was set to Automatic calculations because the SUMPRODUCT formula for Months 1 to 3 was replaced with static values that the website overwrites. Using this method means you do not have a working model in Excel so setting Excel to manually calculate and keeping it all together might be more appropriate.
If the data was stored in a proper database it would probably be even quicker. So in conclusion, if you can separate your data then it makes sense to do so, but if not use a database connection and manual calculations rather than letting Excel do the large data lookups for you.
The code
Default.aspx Page
<%@ 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 Large Data Set Demo</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<h2>SpreadsheetGear Large Data Set Demo</h2>
<p>This example uses a spreadsheet with a fair amount of data to lookup based on several criteria</p>
<h4>Set Sales Data Criteria</h4>
<asp:Table ID=”Table1″ runat=”server”>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblEmployee” runat=”server” Text=”Employee: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selEmployee” runat=”server” Width=”100″>
<asp:ListItem Value=”Amy” Text=”Amy” />
<asp:ListItem Value=”Brian” Text=”Brian” />
<asp:ListItem Value=”Colin” Text=”Colin” />
<asp:ListItem Value=”Diane” Text=”Diane” />
<asp:ListItem Value=”Edward” Text=”Edward” />
<asp:ListItem Value=”Frank” Text=”Frank” />
<asp:ListItem Value=”Gary” Text=”Gary” />
<asp:ListItem Value=”Hannah” Text=”Hannah” />
<asp:ListItem Value=”Ian” Text=”Ian” />
<asp:ListItem Value=”Joanne” Text=”Joanne” />
<asp:ListItem Value=”Karen” Text=”Karen” />
<asp:ListItem Value=”Lee” Text=”Lee” />
<asp:ListItem Value=”Michelle” Text=”Michelle” />
<asp:ListItem Value=”Nick” Text=”Nick” />
<asp:ListItem Value=”Olivia” Text=”Olivia” />
<asp:ListItem Value=”Paul” Text=”Paul” />
<asp:ListItem Value=”Quintin” Text=”Quintin” />
<asp:ListItem Value=”Rose” Text=”Rose” />
<asp:ListItem Value=”Steve” Text=”Steve” />
<asp:ListItem Value=”Tony” Text=”Tony” />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblYear” runat=”server” Text=”Year: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selYear” runat=”server” Width=”100″>
<asp:ListItem Value=”2010″ Text=”2010″ />
<asp:ListItem Value=”2011″ Text=”2011″ />
<asp:ListItem Value=”2012″ Text=”2012″ />
<asp:ListItem Value=”2013″ Text=”2013″ />
<asp:ListItem Value=”2014″ Text=”2014″ />
<asp:ListItem Value=”2015″ Text=”2015″ />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblQuarter” runat=”server” Text=”Quarter: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selQuarter” runat=”server” Width=”100″>
<asp:ListItem Value=”1″ Text=”1″ />
<asp:ListItem Value=”2″ Text=”2″ />
<asp:ListItem Value=”3″ Text=”3″ />
<asp:ListItem Value=”4″ Text=”4″ />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblAge” runat=”server” Text=”Customer Age: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selAge” runat=”server” Width=”100″>
<asp:ListItem Value=”0″ Text=”Under 25″ />
<asp:ListItem Value=”25″ Text=”25 to 39″ />
<asp:ListItem Value=”40″ Text=”40 to 54″ />
<asp:ListItem Value=”55″ Text=”55 to 69″ />
<asp:ListItem Value=”70″ Text=”Over 69″ />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblRange” runat=”server” Text=”Product: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selRange” runat=”server” Width=”100″>
<asp:ListItem Value=”Product 1″ Text=”Product 1″ />
<asp:ListItem Value=”Product 2″ Text=”Product 2″ />
<asp:ListItem Value=”Product 3″ Text=”Product 3″ />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lblManufacturer” runat=”server” Text=”Manufacturer: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID=”selManufacturer” runat=”server” Width=”100″>
<asp:ListItem Value=”ABC Company” Text=”ABC Company” />
<asp:ListItem Value=”XYZ Company” Text=”XYZ Company” />
</asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
<h4>Get Sales Data Results – data in Excel, lookup in Excel</h4>
<asp:Button ID=”calcButton” runat=”server” Text=”Get Data” OnClick=”calcButton_Click” />
<asp:Table ID=”Table2″ runat=”server”>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lbl1″ runat=”server” Text=”Month 1: “></asp:Label><br />
<asp:Label ID=”lbl2″ runat=”server” Text=”Month 2: “></asp:Label><br />
<asp:Label ID=”lbl3″ runat=”server” Text=”Month 3: “></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:Label ID=”lblMonth1″ runat=”server” Text=””></asp:Label><br />
<asp:Label ID=”lblMonth2″ runat=”server” Text=””></asp:Label><br />
<asp:Label ID=”lblMonth3″ runat=”server” Text=””></asp:Label>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>
<asp:Label ID=”lbl0″ runat=”server” Text=”Total: ” Font-Bold=”True”></asp:Label>
</asp:TableCell>
<asp:TableCell>
<asp:Label ID=”lblTotal” runat=”server” Text=”” Font-Bold=”True”></asp:Label>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
<p><br /><asp:Label ID=”lblSec” runat=”server” Text=”Seconds To Run: “></asp:Label><asp:Label ID=”lblSeconds” runat=”server” Text=””></asp:Label></p>
</div>
</form>
</body>
</html>
Default.aspx Code – Sales Data 1
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Diagnostics;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void calcButton_Click(object sender, EventArgs e)
{
// Start stopwatch
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Open the workbook using the current culture (don’t want any defaults)
string baseDir = System.AppDomain.CurrentDomain.BaseDirectory;
string filePath = baseDir + “App_Data\SalesData.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”];
// Pass the web inputs to the Inputs sheet
InputWorksheet.Cells[“Employee_Input”].Range.Value = selEmployee.SelectedValue;
InputWorksheet.Cells[“Year_Input”].Range.Value = selYear.SelectedValue;
InputWorksheet.Cells[“Quarter_Input”].Range.Value = selQuarter.SelectedValue;
InputWorksheet.Cells[“Average_Customer_Age”].Range.Value = selAge.SelectedValue;
InputWorksheet.Cells[“Range_Input”].Range.Value = selRange.SelectedValue;
InputWorksheet.Cells[“Manufacturer_Input”].Range.Value = selManufacturer.SelectedValue;
// Get the web outputs from the Outputs sheet
lblMonth1.Text = OutputWorksheet.Cells[“Month_1”].Range.Text;
lblMonth2.Text = OutputWorksheet.Cells[“Month_2”].Range.Text;
lblMonth3.Text = OutputWorksheet.Cells[“Month_3”].Range.Text;
lblTotal.Text = OutputWorksheet.Cells[“Amount_Result”].Range.Text;
// Return the time taken in seconds
stopwatch.Stop();
lblSeconds.Text = ((double)stopwatch.ElapsedMilliseconds / 1000).ToString();
}
}
Default.aspx Code – Sales Data 2
In this example the workbook has been changed to an older version and has been set to calculate manually. The SQL Query might look complicated but it is not and just combines the 3 monthly lookups into one return set which we iterate through.
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Diagnostics;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void calcButton_Click(object sender, EventArgs e)
{
// Start stopwatch
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Open the workbook using the current culture (don’t want any defaults)
string baseDir = System.AppDomain.CurrentDomain.BaseDirectory;
string filePath = baseDir + “App_Data\SalesData.xlsx”;
SpreadsheetGear.IWorkbook WorkbookInterface = SpreadsheetGear.Factory.GetWorkbook(filePath, System.Globalization.CultureInfo.CurrentCulture);
// Set the workbook for manual caluclation rather than automatic
WorkbookInterface.WorkbookSet.Calculation = SpreadsheetGear.Calculation.Manual;
// Reference the Inputs and Outputs worksheets
SpreadsheetGear.IWorksheet InputWorksheet = WorkbookInterface.Worksheets[“Inputs”];
SpreadsheetGear.IWorksheet OutputWorksheet = WorkbookInterface.Worksheets[“Outputs”];
// Pass the web inputs to the Inputs sheet (not really needed in this example but could be used in other places in the workbook in other situations)
InputWorksheet.Cells[“Employee_Input”].Range.Value = selEmployee.SelectedValue;
InputWorksheet.Cells[“Year_Input”].Range.Value = selYear.SelectedValue;
InputWorksheet.Cells[“Quarter_Input”].Range.Value = selQuarter.SelectedValue;
InputWorksheet.Cells[“Average_Customer_Age”].Range.Value = selAge.SelectedValue;
InputWorksheet.Cells[“Range_Input”].Range.Value = selRange.SelectedValue;
InputWorksheet.Cells[“Manufacturer_Input”].Range.Value = selManufacturer.SelectedValue;
// Fetch the lookups using ODBC to Excel
string sqlQuery = “SELECT 1 AS ID,Amount FROM [Tables$] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3) – 2).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘” +
” UNION SELECT 2 AS ID,Amount FROM [Tables$] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3) – 1).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘” +
” UNION SELECT 3 AS ID,Amount FROM [Tables$] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3)).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘ ORDER BY ID ASC;”;
OleDbConnection con = new OleDbConnection();
string month1 = “0”, month2 = “0”, month3 = “0”;
try
{
// Read file into DataSet
String cmdText, conString;
// Use IMEX=1 when you want to treat all data in the file as text, overriding Excels guess what type of data is in the column.
string extension = filePath.Substring(filePath.Length – 3, 3);
if(extension == “xls”)
// Excel 97-2003
conString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source='” + filePath + “‘;Extended Properties=’Excel 8.0;HDR=YES;IMEX=1;'”;
else
// Excel 2007+
conString = @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source='” + filePath + “‘;Extended Properties=’Excel 12.0 Xml;HDR=YES;IMEX=1;'”;
cmdText = sqlQuery;
con.ConnectionString = conString;
con.Open();
OleDbCommand cmd = new OleDbCommand(cmdText, con);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
switch (reader[“ID”].ToString())
{
case “1”:
month1 = reader[“Amount”].ToString();
break;
case “2”:
month2 = reader[“Amount”].ToString();
break;
case “3”:
month3 = reader[“Amount”].ToString();
break;
}
}
reader.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
try
{
con.Close();
}
catch
{ }
}
// Replace the formula and values then calculate
OutputWorksheet.Cells[“Month_1”].Range.Formula = “”;
OutputWorksheet.Cells[“Month_2”].Range.Formula = “”;
OutputWorksheet.Cells[“Month_3”].Range.Formula = “”;
OutputWorksheet.Cells[“Month_1”].Range.Value = month1;
OutputWorksheet.Cells[“Month_2”].Range.Value = month2;
OutputWorksheet.Cells[“Month_3”].Range.Value = month3;
WorkbookInterface.WorkbookSet.CalculateFull();
// Get the month outputs from the Outputs sheet (not really needed in this example but could be returned with Excel formatting applied)
lblMonth1.Text = OutputWorksheet.Cells[“Month_1”].Range.Text;
lblMonth2.Text = OutputWorksheet.Cells[“Month_2”].Range.Text;
lblMonth3.Text = OutputWorksheet.Cells[“Month_3”].Range.Text;
// Get the total from the Outputs sheet
lblTotal.Text = OutputWorksheet.Cells[“Amount_Result”].Range.Text;
// Return the time taken in seconds
stopwatch.Stop();
lblSeconds.Text = ((double)stopwatch.ElapsedMilliseconds / 1000).ToString();
}
}
Default.aspx Code – Sales Data 3
In this example the workbook has been changed to remove the large data set which has been stored separately in a comma delimited text file but could easily have been a database. The workbook does not function properly with the data for the 3 months being supplied manually.
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Diagnostics;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void calcButton_Click(object sender, EventArgs e)
{
// Start stopwatch
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Open the workbook using the current culture (don’t want any defaults)
string baseDir = System.AppDomain.CurrentDomain.BaseDirectory;
string filePath = baseDir + “App_Data\SalesData.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”];
// Pass the web inputs to the Inputs sheet (not really needed in this example but could be used in other places in the workbook in other situations)
InputWorksheet.Cells[“Employee_Input”].Range.Value = selEmployee.SelectedValue;
InputWorksheet.Cells[“Year_Input”].Range.Value = selYear.SelectedValue;
InputWorksheet.Cells[“Quarter_Input”].Range.Value = selQuarter.SelectedValue;
InputWorksheet.Cells[“Average_Customer_Age”].Range.Value = selAge.SelectedValue;
InputWorksheet.Cells[“Range_Input”].Range.Value = selRange.SelectedValue;
InputWorksheet.Cells[“Manufacturer_Input”].Range.Value = selManufacturer.SelectedValue;
// Fetch the lookups using ODBC to Excel
string dataName = “SalesData.csv”;
string dataPath = baseDir + “App_Data\”;
string sqlQuery = “SELECT 1 AS ID,Amount FROM [” + dataName + “] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3) – 2).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘” +
” UNION SELECT 2 AS ID,Amount FROM [” + dataName + “] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3) – 1).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘” +
” UNION SELECT 3 AS ID,Amount FROM [” + dataName + “] WHERE Employee = ‘” + selEmployee.SelectedValue +
“‘ AND Year = ” + selYear.SelectedValue + ” AND Month = ” + ((Convert.ToInt32(selQuarter.SelectedValue) * 3)).ToString() +
” AND MinAge = ” + selAge.SelectedValue + ” AND Range = ‘” + selRange.SelectedValue +
“‘ AND Manufacturer = ‘” + selManufacturer.SelectedValue + “‘ ORDER BY ID ASC;”;
OleDbConnection con = new OleDbConnection();
string month1 = “0”, month2 = “0”, month3 = “0”;
try
{
// Read file into DataSet
String cmdText, conString;
// Use IMEX=1 when you want to treat all data in the file as text.
conString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source='” + dataPath + “‘;Extended Properties=’text;HDR=Yes;IMEX=1;FMT=Delimited’;”;
cmdText = sqlQuery;
con.ConnectionString = conString;
con.Open();
OleDbCommand cmd = new OleDbCommand(cmdText, con);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
switch (reader[“ID”].ToString())
{
case “1”:
month1 = reader[“Amount”].ToString();
break;
case “2”:
month2 = reader[“Amount”].ToString();
break;
case “3”:
month3 = reader[“Amount”].ToString();
break;
}
}
reader.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
try
{
con.Close();
}
catch
{ }
}
// Replace the formula with values and calculate
OutputWorksheet.Cells[“Month_1”].Range.Formula = “=” + month1;
OutputWorksheet.Cells[“Month_2”].Range.Formula = “=” + month2;
OutputWorksheet.Cells[“Month_3”].Range.Formula = “=” + month3;
WorkbookInterface.WorkbookSet.CalculateFull();
// Get the month outputs from the Outputs sheet (not really needed in this example but could be returned with Excel formatting applied)
lblMonth1.Text = OutputWorksheet.Cells[“Month_1”].Range.Text;
lblMonth2.Text = OutputWorksheet.Cells[“Month_2”].Range.Text;
lblMonth3.Text = OutputWorksheet.Cells[“Month_3”].Range.Text;
// Get the total from the Outputs sheet
lblTotal.Text = OutputWorksheet.Cells[“Amount_Result”].Range.Text;
// Return the time taken in seconds
stopwatch.Stop();
lblSeconds.Text = ((double)stopwatch.ElapsedMilliseconds / 1000).ToString();
}
}
The example links and code downloads
If you would like to view the website examples yourself please click the following links; 1. Data in Excel – Lookup in Excel, 2. Data in Excel – Lookup using OleDb or 3. Data in CSV – Lookup using OleDb.
You can download the original “Example Sales Data workbook” and create the websites yourself following the code above or download each of the website project examples (including the Workbooks but excluding the SpreadsheetGear DLL) here Sales 1 Project Files, Sales 2 Project Files and Sales 3 Project Files (they are all in Visual Studio 2008 so you can upgrade to your installed version).