Conditional Formatting of Excel Cells Using NI LabVIEW

Updated Jun 4, 2020

Reported In

Software

  • LabVIEW
  • LabVIEW Report Generation Toolkit

Operating System

  • Windows

Issue Details

I generate an MS Excel report programmatically using NI LabVIEW. I want the format of each cell in the report (like font size, color etc.) to change automatically based on its contents. How can I achieve this ? 

Solution

Custom format for each cell can be specified either through Format Cells or through Conditional Formatting option within Excel. These functions can be implemented via LabVIEW as explained below :
 
  • Excel Set Cell Format VI takes in a reference to the report and sets format for the cells, based on the input given to the number format terminal. Excel Report - Conditionally Formatted Spreadsheet.vi is a shipping example in LabVIEW which demonstrates this method. 
  • Alternatively, you can use ActiveX interface in NI LabVIEW to emulate the functionality of Conditional Fomatting option in Excel. ActiveX reference to the worksheet has a method named Range. This method returns a reference to a range of cells within the worksheet as specified by Cell1 and Cell2 inputs of the method. This reference is actually an object of the Excel.Range class which has a property named FormatConditions. FormatConditions property returns an object of Excel.FormatConditions class. This object is a collection that represents all the conditional formats for the specified range. You can then invoke the methods of FormatConditions object to specify the format condition. For example, the below snippet changes the font color of a cell contents within the given range to blue, if the numeric value within the cell is less than -0.5.


Note: This image is a LabVIEW snippet, which includes LabVIEW code that you can reuse in your project. To use a snippet, right-click the image, save it to your computer, and drag the file onto your LabVIEW diagram. 

Additional Information

For the complete list of acceptable inputs to Operator terminal of FormatConditions and their corresponding values, refer to this document from Microsoft. 
Numerical values which are given as inputs to the ColorIndex, map to actual colors as shown here .