Introduction
ExcelCommunicator
is a component that makes it possible to pass input data to Excel and retrieve data from it (using as datasource) taking into account formulas or functions (Excel engine). It is possible to use in Windows and web applications.
Background
The idea comes from the need of a company to publish information stored in Excel, on the web with the interaction of the surfer (allow the surfer to specify an input that will modify the Excel output).
This company is composed of different product researchers with heterogeneous information about different products (DVDs, washing machines, etc…) stored in Excel files. In some cases, the Excel files contain comparison tables of different products, in other cases they contain spreadsheets with formulas, based on products' information.
As the Excel files are completely different in all aspects (data, structure Excel files), creating an intelligent system to export/import the data to relational database would take up too much time. For this reason, the best way I found to solve the problem was to use Excel as data source/engine.
Requirements
- The component must retrieve the resulting data (range of cell/s) based on input data.
- The component must use the Microsoft Office 10.0 Object Library.
- The component must be configurable taking into account:
- Where the Excel file path to pass/retrieve values is
- Where (Excel file, sheet, cells) the input values have to be placed
- What the data to retrieve is (from which Excel file, sheet, cells)
- What the Excel chart/s to retrieve are (from which Excel file, sheet, cells)
Configuration
The configuration of the component is based on an XML file with the following schema:
="1.0"="utf-8"
<Configuration>
<TemporaryFolder>C:\Inetpub\wwwroot\ExcelParser\WindowsClient\Configuration
</TemporaryFolder>
<ExcelFilePath>C:\Inetpub\wwwroot\ExcelParser\WindowsClient\ExcelSample\
ExcelSample.xls</ExcelFilePath>
<Inputs>
<Input>
<Cell>A1</Cell>
<SheetIndex>1</SheetIndex>
<DataType>Values</DataType>
</Input>
</Inputs>
<Outputs>
<Output>
<DataType>Values</DataType>
<Start>A1</Start>
<End>C30</End>
<SheetIndex>2</SheetIndex>
<FullName>Test1</FullName>
</Output>
<Output>
<DataType>Chart</DataType>
<SheetIndex>2</SheetIndex>
<ChartIndex>1</ChartIndex>
<FullName>Chart</FullName>
</Output>
</Outputs>
</Configuration>
Passing Values to Excel
To pass the values to Excel, the component analyzes the inputs declared in the configuration, gets the instance of the sheet specified, and sets the values to these declared input cells.
for (int i=0;i<this.Configuration.Inputs.Length;i++)
{
object value = inputs[i];
Input input = this.Configuration.Inputs[i];
sheet = (Worksheet)book.Worksheets[input.SheetIndex];
range = sheet.get_Range(input.Cell, input.Cell);
range.set_Value(Missing.Value, value);
ReleaseComObject(range);
ReleaseComObject(sheet);
}
Getting Data from Excel
In our configuration, we should have two types of data to retrieve from Excel file, value/s (ranges of cells) or chart/s.
First the component gets the sheet specified and parses the output declared on the configuration. The data to get is treated and added to the hashtable that contains all the data retrieved.
foreach (Output output in this.Configuration.Outputs)
{
sheet = (Worksheet)book.Worksheets[output.SheetIndex];
if(output.DataType == OutputDataType.Values)
{
range = sheet.get_Range(output.Start, output.End);
this.Output.Add(output.FullName, range.Value2);
ReleaseComObject(range);
}
else if(output.DataType == OutputDataType.Chart)
{
ChartObjects chartobjects=(ChartObjects)sheet.ChartObjects(Missing.Value);
string tempFileName = GetFileName();
chartobject = (ChartObject)
chartobjects.Item(output.ChartIndex);
chartobject.Chart.Export(tempFileName, "GIF", false);
AddChart(output,tempFileName);
ReleaseComObject(chartobject);
ReleaseComObject(chartobjects);
}
ReleaseComObject(sheet);
}
private void AddChart(Output output, string tempFileName)
{
FileStream fs = new FileStream(tempFileName, FileMode.Open);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
fs.Close();
FileInfo f = new FileInfo(tempFileName);
f.Delete();
this.Output.Add(output.FullName, buffer);
}
The Key: Release Com Object
ReleaseComObject
must be called to remove, from memory, instances of com
objects that will not be taken into account by the .NET Garbage collector. This method is used to explicitly control the lifetime of a COM
object used from managed code. Otherwise, some com
objects could remain in memory. In this case, the process EXCEL.exe wouldn't be killed after retrieving data.
DataTable is Comfortable
Natively, when we get ranges from Excel, the data retrieved comes as two dimensional arrays. This component will create a datatable, foreach ranges are declared in the configuration, and added to the output hashtable.
Using Excel Communicator to Pass/Get Data
This communication could be done using different overloads of the Parse
method:
- To retrieve values from Excel specifying input values to pass to Excel file:
Configuration config=Configuration.Read(this.textboxPath.Text);
Parser parser = new Parser();
parser.GetExcelOutputs (config,new object[]{this.textboxInput.Text});
- To retrieve values from Excel without specifying input values:
Parser parser = new Parser();
parser.GetExcelOutputs(pathtoxmlconfiguration);
- Another way to retrieve values from Excel without specifying input values:
Configuration config=Configuration.Read(this.textboxPath.Text);
Parser parser = new Parser();
parser.GetExcelOutputs (config);
To show data from the hashtable, there are two methods:
GetTable
– used to get a specific datatable from ExcelGetChart
– used to obtain a byte array with the data of the chart
Here is one example of binding a datatable into a DataGridView
, and put the Chart
image into a PictureBox
:
this.dataGridView1.DataSource=parser.GetTable(0);
pictureBox1.Image=Image.FromStream(new MemoryStream(parser.GetChart(1)));
What's in the Files to Download?
You'll find the Excel sheet, a configuration file, and the component inside the zip files in a folder called ExcelCommunicator and a test Windows application that uses the component and the Excel file.
I Want to Configure It On a IIS Web Server
If you want to use it on a web server, you will need to give permissions in the com
configuration console. Here a great article about how to do it : Configure Com+ console for web server.
Hope you like it.