Component for Fast Pass/Retrieve Data from Excel

• Download source and test app - 1.24 MB

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:

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
  <!-- TemporaryFolder to store the optional charts retrieved -->
  <TemporaryFolder>C:\Inetpub\wwwroot\ExcelParser\WindowsClient\Configuration
	</TemporaryFolder>
  <!-- Path of the excel file to pass and retrieve values from it -->
  <ExcelFilePath>C:\Inetpub\wwwroot\ExcelParser\WindowsClient\ExcelSample\
	ExcelSample.xls</ExcelFilePath>
  <!-- Collection of inputs to pass to excel file -->
  <Inputs>
    <!-- This first input will be a range of one value that will be 
	placed on cell A1 on Sheet nº1 and is a "Values" type -->
    <Input>
      <Cell>A1</Cell>
      <SheetIndex>1</SheetIndex>
      <DataType>Values</DataType>
    </Input>
  </Inputs>
  <!--Collection of outputs retrieved from excel file -->
  <Outputs>
    <!-- This first output will be a range of values from A1 to C30 
	in the Sheet index nº2, and will be stored in the output hashtable 
	with key "Test1"-->
    <Output>
      <DataType>Values</DataType>
      <Start>A1</Start>
      <End>C30</End>
      <SheetIndex>2</SheetIndex>
      <FullName>Test1</FullName>
    </Output>
    <!-- This second output will be a Chart in Sheet index nº2, 
	and will be stored in the output hashtable with key "Chart"-->
    <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.

 //foreach input declared in the configuration file
for (int i=0;i<this.Configuration.Inputs.Length;i++)
{
    //get the value to pass to Excel
      object value = inputs[i];
    //Get the input from the configuration
       Input input = this.Configuration.Inputs[i];
    //set an instance of the declared Excel sheet
       sheet = (Worksheet)book.Worksheets[input.SheetIndex];
    //set the range (one or few cells) with the value to pass to Excel
       range = sheet.get_Range(input.Cell, input.Cell);
       range.set_Value(Missing.Value, value);
    //Free Com resources, necessary release com objects from memory.
       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)
{
 //get an instance of the selected sheet
 sheet = (Worksheet)book.Worksheets[output.SheetIndex];
 //add to the output hashtable the data from Excel taking into account 
 //the configuration outputdatatype (Values or Chart)
 //if the datatype of the current output is Values
 if(output.DataType == OutputDataType.Values)
 {
  range = sheet.get_Range(output.Start, output.End);
  this.Output.Add(output.FullName, range.Value2);
  ReleaseComObject(range);
 }
 //if the datatype of the current output is Chart
 else if(output.DataType == OutputDataType.Chart)
 {
  //get an instance of all chart objects
  ChartObjects chartobjects=(ChartObjects)sheet.ChartObjects(Missing.Value);
  //get a random filename for the chart that will be stored temporarily. 
  string tempFileName = GetFileName();
  chartobject = (ChartObject) 
  //get the chart object specified in the configuration
  chartobjects.Item(output.ChartIndex);
  //export the chart into a temporary gif file
  chartobject.Chart.Export(tempFileName, "GIF", false);
  //Add the chart as byte array and delete the file created
  AddChart(output,tempFileName);
  //free com resources
  ReleaseComObject(chartobject);
  ReleaseComObject(chartobjects);
 }
 ReleaseComObject(sheet);
} 
/// <summary>
/// Adds the byte[] representing the image data into the hashtable.
/// Deletes the temporary file previously created.
/// </summary>
private void AddChart(Output output, string tempFileName)
{
 //Read bytes
 FileStream fs = new FileStream(tempFileName, FileMode.Open);
 byte[] buffer = new byte[fs.Length];
 fs.Read(buffer, 0, buffer.Length);
 fs.Close();
 //Delete temporary file
 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:

1. GetTable – used to get a specific datatable from Excel
2. GetChart – 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.