Making Charts in MS Excel using JAVA

Probably the best part of using MS Excel would be the chart as it only allows you to track the record which you’ve entered efficiently, but also it helps you to project the data in a presentable way.

Jxcell provides a way to create charts in MS Excel using JAVA which takes the data from the MS Excel file and create the excel chart in a specific location.

Below is the code for creating charts in Excel using Jxcell.


import com.jxcell.GRChart;
import com.jxcell.RangeRef;
import com.jxcell.View;
import com.jxcell.chart.ChartFormat;

import java.awt.*;

public class ChartFormatTest {

private void createChart(){
View m_view = new View();
RangeRef newRange = null;
try {
m_view.getLock();
m_view.setEntry(1,2,"Jan");
m_view.setEntry(1,3,"Feb");
m_view.setEntry(1,4,"Mar");
m_view.setEntry(1,5,"Apr");
m_view.setEntry(2,1,"Bananas");
m_view.setEntry(3,1,"Papaya");
m_view.setEntry(4,1,"Mango");
m_view.setEntry(5,1,"Lilikoi");
m_view.setEntry(6,1,"Comfrey");
m_view.setEntry(7,1,"Total");
m_view.setEntry(1,6,"time");
m_view.setNumber(2,6,1);
m_view.setNumber(3,6,2);
m_view.setNumber(4,6,3);
m_view.setNumber(5,6,4);
m_view.setNumber(6,6,5);
m_view.setNumber(7,6,6);

for(int col = 2; col <= 5; col++)

for(int row = 2; row <= 7; row++)

m_view.setFormula(row, col, "rand()");

m_view.setFormula(7, 2, "SUM(C3:C7)");

m_view.setSelection("C8:F8");

m_view.editCopyRight();

com.jxcell.GRChart chart = (GRChart)m_view.addObject(com.jxcell.GRObject.eChart, 0, 9.1, 7, 20.4);

chart.setChartType(GRChart.eChartLine);

// chart.setLinkRange("Sheet1!$C$2", false);

chart.addSeries();

chart.setSeriesName(0, "Sheet1!$C$2");

chart.setSeriesYValueFormula(0, "Sheet1!$C$3:$C$7");

chart.setCategoryFormula("Sheet1!$B$3:$B$7");

chart.addSeries();

chart.setSeriesName(1, "Sheet1!$D$2");

chart.setSeriesYValueFormula(1, "Sheet1!$D$3:$D$7");

chart.addSeries();

chart.setSeriesName(2, "Sheet1!$E$2");

chart.setSeriesYValueFormula(2, "Sheet1!$E$3:$E$7");

chart.addSeries();

chart.setSeriesName(3, "Sheet1!$F$2");

chart.setSeriesYValueFormula(3, "Sheet1!$F$3:$F$7");

chart.getChart().validateData();

chart.setAxisTitle(GRChart.eXAxis, 0, "Category");

chart.setAxisTitle(GRChart.eYAxis, 0, "Amount");

// set fill color

ChartFormat cf = chart.getChartFormat();

cf.setPattern((short)1);

cf.setPatternFG(Color.LIGHT_GRAY);

chart.setChartFormat(cf);

cf = chart.getPlotFormat();

cf.setPattern((short)1);

cf.setPatternFG(new Color(204, 255, 255));

chart.setPlotFormat(cf);

cf = chart.getAxisFormat(GRChart.eXAxis, 0);

cf.setFontSizeInPoints(8.5);

chart.setAxisFormat(GRChart.eXAxis, 0, cf);

cf = chart.getAxisFormat(GRChart.eYAxis, 0);

cf.setFontSizeInPoints(8.5);

chart.setAxisFormat(GRChart.eYAxis, 0, cf);

cf = chart.getSeriesFormat(0);

cf.setLineStyle(1);

cf.setLineWeight(3*20);

cf.setLineColor((new Color(0, 0, 128)).getRGB());

cf.setMarkerAuto(false);

cf.setMarkerStyle((short)0);

chart.setSeriesFormat(0, cf);

cf = chart.getSeriesFormat(1);

cf.setLineStyle(1);

cf.setLineWeight(3*20);

cf.setLineColor((new Color(255, 0, 255)).getRGB());

cf.setMarkerAuto(false);

cf.setMarkerStyle((short)0);

chart.setSeriesFormat(1, cf);

cf = chart.getSeriesFormat(2);

cf.setLineStyle(1);

cf.setLineWeight(3*20);

cf.setLineColor((new Color(255, 255, 0)).getRGB());

cf.setMarkerAuto(false);

cf.setMarkerStyle((short)0);

chart.setSeriesFormat(2, cf);

cf = chart.getSeriesFormat(3);

cf.setLineStyle(1);

cf.setLineWeight(3*20);

cf.setLineColor((new Color(0, 255, 255)).getRGB());

cf.setMarkerAuto(false);

cf.setMarkerStyle((short)0);

chart.setSeriesFormat(3, cf);

cf = chart.getMajorGridFormat(GRChart.eYAxis, 0);

cf.setLineStyle(2);

cf.setLineColor((new Color(255, 0, 0)).getRGB());

cf.setLineAuto(false);

chart.setMajorGridFormat(GRChart.eYAxis, 0, cf);

chart.setLegendPos(ChartFormat.eLegendPlacementTop);

cf = chart.getLegendFormat();

cf.setFontBold(true);

cf.setFontSizeInPoints(8);

chart.setLegendFormat(cf);

m_view.write("C:\\out.xls", View.eFileExcel);

} catch (Exception e) {

System.out.println(e.getMessage());

} finally {

m_view.releaseLock();

}

}

public static void main(String args[]) {

ChartFormatTest cft = new ChartFormatTest(); cft.createChart();

}

}

Hope you all will find it useful.