Updating an excel document programmatically

Question

First for context, I am a placement student on the automation team of a software development company, so I help develop the automated test programs on our daily builds.

My line manager has produced an excel document which will be used to present the results from our test environments. Our tests run on virtual PC's on a hyper-V server.

I want to be able to update this excel document with the results of tests from each of the test environments, but I'm not sure how I should go about doing this.

Each of the virtual PC's runs an automation program (C# .NET 4.0), and currently copies the test logs and result files to a shared folder on our build machine, which is accessible by everyone.

So I want the automation program to run its tests, then when it has finished, I want it to write to the table in the excel document with the test results. Has anyone any experience with modifying excel documents programmatically? I should also note that the excel document will be stored in a shared folder on a remote virtual PC (accessible from the test environment)

The table will contain the build name/date, environment details, type of test and the test result etc.

I already collect this information and present it in HTML files - but there is a separate HTML file for each test environment. The goal of this is so that the results will all be in one place (The excel document will take care of presenting the data in the tables in the form of graphs and charts).

So really, all I need to know is how to write to a table in an excel document from a .NET program. There seems to be different ways of doing this (as I found when I researched it online), so I want to ask stackoverflow to see who knows the best way to do this).

Answer 1

I use and am happy with EPPlus http://epplus.codeplex.com/ for parsing, editing and creating xlsx files.

This question had helped me out: Create Excel (.XLS and .XLSX) file from C#

Answer 2

There are a lot of methods, indeed. You can use OLEDB, or you can use interop (look at get_Range() and ListObjects["table_name"].DataBodyRange).

When I was forced to create Excel files based on a template, I used XML maps -- it's very handy. After you bind an xsd to your workbook, you can just load an xml into it and Excel will place the data to the right cells. (If you need an example, just leave a comment and I'll update the answer with it.)

But personally I agree with mj82. If you can avoid writing to an Excel file -- avoid it. You can bind a table ('list' in 2003) to some external datasource and set the workbook to autoupdate on open. If it's possible in your environment that would be the best solution.

UPDATE: To use xml maps for pushing data into an Excel workbook, follow the next steps

Create an xml schema (xsd file). For example:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="BILLSTRINGS" targetNamespace="http://tempuri.org/BILLSTRINGS.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/BILLSTRINGS.xsd" xmlns:mstns="http://tempuri.org/BILLSTRINGS.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="BILLSTRINGS">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="STRINGS" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="ID_GOOD" type="xs:string" />
                            <xs:element name="NAME_GOOD" type="xs:string" />
                            <xs:element name="Quantity" type="xs:string" />
                            <xs:element name="Price" type="xs:string" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

Open the workbook, add your xsd as a map and map cells to elements as described in ms help.

Then in you code: Create a string with xml data. (I used typed DataSets, so I just called GetXml.) Open the workbook and push the data to Excel:

using OExcel = Microsoft.Office.Interop.Excel;
//...
OExcel.Application app = new OExcel.ApplicationClass(); 
OExcel.Workbook wb = app.Workbooks.Open(filepath, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.XmlMaps["MAPNAME"].ImportXml(string_with_xml);

And that's all. Hope this helps.

Answer 3

OfficeWriter is another 3rd party library for working programmatically with XLS/XLSX (and Word). It's mature, at version 8.0 now.

http://www.officewriter.com