Use Microsoft Excel as a report writer

Despite the many integration investments, a significant number of production reporting still occurs using spreadsheets printed on paper. Why?People will cite lots of reasons, but they all boil down to trust. Spreadsheets have been around so long that people have evolved their "favorite" spreadsheet-based reports.


Despite the many integration investments, a significant number of production reporting still occurs using spreadsheets printed on paper. Why?

People will cite lots of reasons, but they all boil down to trust . Spreadsheets have been around so long that people have evolved their 'favorite' spreadsheet-based reports. They can see and validate formulas, and they trust the results produced. After all, if you're sending a report with lots of 'crunched numbers' to your boss and/or your bosses' boss, you need to trust the underlying technology used to develop those reports.

Possibly the most-used spreadsheet today is Microsoft Excel. Excel's ability to manipulate, analyze, and report numerical data is unquestioned. Automatically getting data into Excel is achieved by connectivity technologies, such as Dynamic Data Exchange, Microsoft Query, Open Database Connectivity (ODBC), Object Linking and Embedding (OLE), Component Object Modeling (COM), and Distributed COM (DCOM), each with its own pros and cons.

SyTech (Walpole, Mass.) recognized and set out to address the challenges of using Excel as an integrated and automated report generation solution. The result is SyTech's XLReporter product.

Using COM technology, XLReporter pushes data into Excel cells with XLReporter controlling Excel as a 'behind-the-scenes' engine to duplicate workbooks, manipulate worksheets, update charts, recalculate formulas, print reports, and post web pages. (See XLReporter Architecture diagram.) (Note: A future release of XLReporter will also support ODBC.)

ABC's of report creation

Creating a report using XLReporter consists of:

  • Designing the report layout using Excel's cell and chart formatting tools, or if a 'favorite' report exists, preparing the report to use the XLReporter add-in;

  • Assigning worksheet cells to one of four data source types using XLReporter's Connection Configurator (See Connection configurator data types sidebar); and

  • Testing the report using XLReporter's Worksheet Execute tool to verify data values transferred to the worksheet and Excel formulas, charts, etc., produce desired results.

When/if results aren't what's expected, XLReporter's status log aids troubleshooting with a record of every event and when it occurred.

Report scheduling, publishing

Most industrial automation reports are generated based on time or events. Shift-end and daily production reports are examples of time-based reports; batch-end reports are examples of event driven reports.

Traditionally production reports have been printed, but one of Microsoft Office 2000's enhancements allows converting documents to web pages, opening up report publishing possibilities.

XLReporter's Schedule Designer tool defines when to collect and store data, and when, where, and how to publish reports.

SyTech's XLReporter is a product designed to fill the unglamorous yet important space of reporting. What makes SyTech's use of technology unique is that it creates those all-important reports using a product that people already trust-Microsoft Excel.

For more information go to or to down load a trial verison visit .

Author Information

Dave Harrold, senior editor

Connection Configurator Data Types

Real data come from real-time data servers.

History data originate from human-machine interface (HMI) software historical files.

Expression data are user configured expressions and may or may not include NameTypes, Excel formulas, or fixed text. Time and/or date stamps are examples of expression data.

Function data come from XLReporter's internal calculation database for long term and persistent time- or event-based statistic, difference, or profile results.

No comments
The Engineers' Choice Awards highlight some of the best new control, instrumentation and automation products as chosen by...
The System Integrator Giants program lists the top 100 system integrators among companies listed in CFE Media's Global System Integrator Database.
Each year, a panel of Control Engineering and Plant Engineering editors and industry expert judges select the System Integrator of the Year Award winners in three categories.
This eGuide illustrates solutions, applications and benefits of machine vision systems.
Learn how to increase device reliability in harsh environments and decrease unplanned system downtime.
This eGuide contains a series of articles and videos that considers theoretical and practical; immediate needs and a look into the future.
Controller programming; Safety networks; Enclosure design; Power quality; Safety integrity levels; Increasing process efficiency
Additive manufacturing benefits; HMI and sensor tips; System integrator advice; Innovations from the industry
Robotic safety, collaboration, standards; DCS migration tips; IT/OT convergence; 2017 Control Engineering Salary and Career Survey
Featured articles highlight technologies that enable the Industrial Internet of Things, IIoT-related products and strategies to get data more easily to the user.
This article collection contains several articles on how automation and controls are helping human-machine interface (HMI) hardware and software advance.
This digital report will explore several aspects of how IIoT will transform manufacturing in the coming years.

Find and connect with the most suitable service provider for your unique application. Start searching the Global System Integrator Database Now!

Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
Mobility as the means to offshore innovation; Preventing another Deepwater Horizon; ROVs as subsea robots; SCADA and the radio spectrum
Future of oil and gas projects; Reservoir models; The importance of SCADA to oil and gas
Automation Engineer; Wood Group
System Integrator; Cross Integrated Systems Group
Jose S. Vasquez, Jr.
Fire & Life Safety Engineer; Technip USA Inc.
This course focuses on climate analysis, appropriateness of cooling system selection, and combining cooling systems.
This course will help identify and reveal electrical hazards and identify the solutions to implementing and maintaining a safe work environment.
This course explains how maintaining power and communication systems through emergency power-generation systems is critical.
click me