Tutorial 24: How to create chart in a worksheet in ColdFusion

How to create chart in a worksheet in ColdFusion

EasyXLS Excel library can be used to export Excel files in ColdFusion. The library works without MS Excel installed.

Find more about Getting Started with EasyXLS

Step 1: Download EasyXLS Excel Library for Java

To download the trial version of EasyXLS Excel Library, press the below button:

If you already own a license key, you may login and download EasyXLS from your account.

Install the downloaded EasyXLS installer for v8.6 or earlier.

Step 2: License file setup

Step required for EasyXLS v9.0 or later.

If you are using a trial, generate a trial license file from EasyXLS trials page. The trial license is valid for 30-days.

If you own a license key, you may login to the account that purchased the license and generate the license file from:
https://www.easyxls.com/my-orders

Setup the license file into your project using these guidelines.

Step 3: Add EasyXLS library to CLASSPATH in ColdFusion Administator

EasyXLS.jar must be included to class path for Java runtime environment.

EasyXLS.jar can be found:
- Inside the downloaded archive at Step 1 for EasyXLS v9.0 or later
- Under installation path for EasyXLS v8.6 or earlier, in "Lib" folder.

Another solution is to add EasyXLS.jar to "lib" folder from ColdFusion installation path.

Step 4: Run ColdFusion code that creates a chart in a worksheet

Execute the following ColdFusion code that exports a chart to Excel and the chart is placed inside a worksheet.

View Excel file

Copy code

<!--
=================================================================
Tutorial 24

This tutorial shows how to create an Excel file in ColdFusion and
to create a chart in a worksheet.
=================================================================
-->

<!-- Constants Classes -->
<cfobject type="java" class="EasyXLS.Constants.Format" name="Format" action="CREATE">

Tutorial 24<br>
----------<br>

<!-- Create an instance of the class that exports Excel files -->
<cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE">

<!-- Create an worksheet -->
<cfset ret = workbook.easy_addWorksheet("SourceData")>

<!-- Get the table of data for the worksheet -->
<cfset xlsTab1 = workbook.easy_getSheet("SourceData")>
<cfset xlsTable1 = xlsTab1.easy_getExcelTable()>

<!-- Add data in cells for report header -->
<cfset xlsTable1.easy_getCell(0, 0).setValue("Show Date")>
<cfset xlsTable1.easy_getCell(0, 1).setValue("Available Places")>
<cfset xlsTable1.easy_getCell(0, 2).setValue("Available Tickets")>
<cfset xlsTable1.easy_getCell(0, 3).setValue("Sold Tickets")>

<!-- Add data in cells for chart report values -->
<cfset xlsTable1.easy_getCell(1, 0).setValue("03/13/2005 00:00:00")>
<cfset xlsTable1.easy_getCell(1, 0).setFormat(Format.FORMAT_DATE)>
<cfset xlsTable1.easy_getCell(2, 0).setValue("03/14/2005 00:00:00")>
<cfset xlsTable1.easy_getCell(2, 0).setFormat(Format.FORMAT_DATE)>
<cfset xlsTable1.easy_getCell(3, 0).setValue("03/15/2005 00:00:00")>
<cfset xlsTable1.easy_getCell(3, 0).setFormat(Format.FORMAT_DATE)>
<cfset xlsTable1.easy_getCell(4, 0).setValue("03/16/2005 00:00:00")>
<cfset xlsTable1.easy_getCell(4, 0).setFormat(Format.FORMAT_DATE)>

<cfset xlsTable1.easy_getCell(1, 1).setValue("10000")>
<cfset xlsTable1.easy_getCell(2, 1).setValue("5000")>
<cfset xlsTable1.easy_getCell(3, 1).setValue("8500")>
<cfset xlsTable1.easy_getCell(4, 1).setValue("1000")>

<cfset xlsTable1.easy_getCell(1, 2).setValue("8000")>
<cfset xlsTable1.easy_getCell(2, 2).setValue("4000")>
<cfset xlsTable1.easy_getCell(3, 2).setValue("6000")>
<cfset xlsTable1.easy_getCell(4, 2).setValue("1000")>

<cfset xlsTable1.easy_getCell(1, 3).setValue("920")>
<cfset xlsTable1.easy_getCell(2, 3).setValue("1005")>
<cfset xlsTable1.easy_getCell(3, 3).setValue("342")>
<cfset xlsTable1.easy_getCell(4, 3).setValue("967")>

<!-- Set column widths -->
<cfset xlsTable1.easy_getColumnAt(0).setWidth(100)>
<cfset xlsTable1.easy_getColumnAt(1).setWidth(100)>
<cfset xlsTable1.easy_getColumnAt(2).setWidth(100)>
<cfset xlsTable1.easy_getColumnAt(3).setWidth(100)>

<!-- Create a chart -->
<cfobject type="java" class="EasyXLS.Charts.ExcelChart" name="xlsChart" action="CREATE">
<cfset xlsChart.setLeftUpperCorner("A10")>
<cfset xlsChart.setSize(600, 300)>

<cfset xlsChart.easy_addSeries("=SourceData!$B$1", "=SourceData!$B$2:$B$5")>
<cfset xlsChart.easy_addSeries("=SourceData!$C$1", "=SourceData!$C$2:$C$5")>
<cfset xlsChart.easy_addSeries("=SourceData!$D$1", "=SourceData!$D$2:$D$5")>
<cfset xlsChart.easy_setCategoryXAxisLabels("=SourceData!$A$2:$A$5")>

<!-- Add the chart to the first worksheet -->
<cfset xlsWorksheet = workbook.easy_getSheet("SourceData")>
<cfset xlsWorksheet.easy_addChart(xlsChart)>

<!-- Export Excel file -->
Writing file C:\Samples\Tutorial24 - chart inside worksheet.xlsx<br>
<cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Tutorial24 - chart inside worksheet.xlsx")>

<!-- Confirm export of Excel file -->
<cfset sError = workbook.easy_getError()>
<cfif (sError is "")>
    <cfoutput>
        File successfully created.
    </cfoutput>
<cfelse>
    <cfoutput>
        Error encountered: #sError#
    </cfoutput>
</cfif>

<!-- Dispose memory -->
<cfset workbook.Dispose()>

EasyXLS Excel libraries:
Excel Library for ColdFusion
full Java version to import, export or convert Excel files
Excel Writer for ColdFusion
Java version to create and export Excel files

File formats:
MS Excel 97 - 2003 MS Excel 2007 - 2019 MS Excel 2021 Office 365

How to create chart in a worksheet in ColdFusion

Step 1: Download EasyXLS Excel Library for Java

Step 2: License file setup

Step 3: Add EasyXLS library to CLASSPATH in ColdFusion Administator

Step 4: Run ColdFusion code that creates a chart in a worksheet

Related sections

See also

Related methods

Related constants

EasyXLS Excel libraries:

File formats: