Create Bar Chart

The following example shows you how add bar chart to a worksheet.
import com.independentsoft.office.Unit;
import com.independentsoft.office.UnitType;
import com.independentsoft.office.charts.AxisPosition;
import com.independentsoft.office.charts.BarChart;
import com.independentsoft.office.charts.BarChartSerie;
import com.independentsoft.office.charts.BarDirection;
import com.independentsoft.office.charts.BarGrouping;
import com.independentsoft.office.charts.CategoryAxis;
import com.independentsoft.office.charts.ChartCategoryAxis;
import com.independentsoft.office.charts.ChartSpace;
import com.independentsoft.office.charts.CrossBetween;
import com.independentsoft.office.charts.Crosses;
import com.independentsoft.office.charts.LabelAlignment;
import com.independentsoft.office.charts.Layout;
import com.independentsoft.office.charts.Legend;
import com.independentsoft.office.charts.LegendPosition;
import com.independentsoft.office.charts.MajorGridlines;
import com.independentsoft.office.charts.NumberCache;
import com.independentsoft.office.charts.NumberFormat;
import com.independentsoft.office.charts.NumberReference;
import com.independentsoft.office.charts.NumericPoint;
import com.independentsoft.office.charts.Orientation;
import com.independentsoft.office.charts.PlotArea;
import com.independentsoft.office.charts.Scaling;
import com.independentsoft.office.charts.SeriesText;
import com.independentsoft.office.charts.StringCache;
import com.independentsoft.office.charts.StringPoint;
import com.independentsoft.office.charts.StringReference;
import com.independentsoft.office.charts.TickLabelPosition;
import com.independentsoft.office.charts.ValueAxis;
import com.independentsoft.office.charts.Values;
import com.independentsoft.office.drawing.Extents;
import com.independentsoft.office.drawing.Offset;
import com.independentsoft.office.spreadsheet.Cell;
import com.independentsoft.office.spreadsheet.Workbook;
import com.independentsoft.office.spreadsheet.Worksheet;
import com.independentsoft.office.spreadsheet.drawing.ClientData;
import com.independentsoft.office.spreadsheet.drawing.DrawingObjects;
import com.independentsoft.office.spreadsheet.drawing.EndAnchorPoint;
import com.independentsoft.office.spreadsheet.drawing.GraphicFrame;
import com.independentsoft.office.spreadsheet.drawing.StartAnchorPoint;
import com.independentsoft.office.spreadsheet.drawing.Transform2D;
import com.independentsoft.office.spreadsheet.drawing.TwoCellAnchor;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            Workbook book = new Workbook();
            Worksheet sheet1 = new Worksheet();

            //set cell values
            sheet1.set("A2", new Cell("Category 1"));
            sheet1.set("A3", new Cell("Category 2"));
            sheet1.set("A4", new Cell("Category 3"));
            sheet1.set("A5", new Cell("Category 4"));

            sheet1.set("B1", new Cell("Series 1"));
            sheet1.set("B2", new Cell(4.3));
            sheet1.set("B3", new Cell(2.5));
            sheet1.set("B4", new Cell(3.5));
            sheet1.set("B5", new Cell(4.5));

            sheet1.set("C1", new Cell("Series 2"));
            sheet1.set("C2", new Cell(2.4));
            sheet1.set("C3", new Cell(4.4));
            sheet1.set("C4", new Cell(1.8));
            sheet1.set("C5", new Cell(2.8));

            sheet1.set("D1", new Cell("Series 3"));
            sheet1.set("D2", new Cell(2));
            sheet1.set("D3", new Cell(2));
            sheet1.set("D4", new Cell(3));
            sheet1.set("D5", new Cell(5));

            //create Bar Chart
            BarChart barChart = new BarChart();
            barChart.setDirection(BarDirection.COLUMN);
            barChart.setGrouping(BarGrouping.CLUSTERED);

            BarChartSerie serie1 = new BarChartSerie();
            serie1.setIndex(0);
            serie1.setOrder(0);
            
            serie1.setSeriesText(new SeriesText());
            serie1.getSeriesText().setStringReference(new StringReference());
            serie1.getSeriesText().getStringReference().setFormula("Sheet1!$B$1");
                        
            serie1.getSeriesText().getStringReference().setStringCache(new StringCache());

            StringPoint seriesTextPoint11 = new StringPoint(0, "Series 1");
            serie1.getSeriesText().getStringReference().getStringCache().getStringPoints().add(seriesTextPoint11);

            serie1.setCategoryAxis(new CategoryAxis());
            serie1.getCategoryAxis().setStringReference(new StringReference());
            serie1.getCategoryAxis().getStringReference().setFormula("Sheet1!$A$2:$A$5");

            serie1.getCategoryAxis().getStringReference().setStringCache(new StringCache());

            StringPoint categoryAxisPoint11 = new StringPoint(0, "Category 1");
            StringPoint categoryAxisPoint12 = new StringPoint(1, "Category 2");
            StringPoint categoryAxisPoint13 = new StringPoint(2, "Category 3");
            StringPoint categoryAxisPoint14 = new StringPoint(3, "Category 4");

            serie1.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint11);
            serie1.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint12);
            serie1.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint13);
            serie1.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint14);
            
            serie1.setValues(new Values());
            serie1.getValues().setNumberReference(new NumberReference());
            serie1.getValues().getNumberReference().setFormula("Sheet1!$B$2:$B$5");

            serie1.getValues().getNumberReference().setNumberCache(new NumberCache());
            serie1.getValues().getNumberReference().getNumberCache().setFormat("General");

            NumericPoint valuesPoint11 = new NumericPoint(0, "4.3");
            NumericPoint valuesPoint12 = new NumericPoint(1, "2.5");
            NumericPoint valuesPoint13 = new NumericPoint(2, "3.5");
            NumericPoint valuesPoint14 = new NumericPoint(3, "4.5");

            serie1.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint11);
            serie1.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint12);
            serie1.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint13);
            serie1.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint14);

            BarChartSerie serie2 = new BarChartSerie();
            serie2.setIndex(1);
            serie2.setOrder(1);

            serie2.setSeriesText(new SeriesText());
            serie2.getSeriesText().setStringReference(new StringReference());
            serie2.getSeriesText().getStringReference().setFormula("Sheet1!$C$1");

            serie2.getSeriesText().getStringReference().setStringCache(new StringCache());

            StringPoint seriesTextPoint21 = new StringPoint(0, "Series 2");
            serie2.getSeriesText().getStringReference().getStringCache().getStringPoints().add(seriesTextPoint21);

            serie2.setCategoryAxis(new CategoryAxis());
            serie2.getCategoryAxis().setStringReference(new StringReference());
            serie2.getCategoryAxis().getStringReference().setFormula("Sheet1!$A$2:$A$5");

            serie2.getCategoryAxis().getStringReference().setStringCache(new StringCache());

            StringPoint categoryAxisPoint21 = new StringPoint(0, "Category 1");
            StringPoint categoryAxisPoint22 = new StringPoint(1, "Category 2");
            StringPoint categoryAxisPoint23 = new StringPoint(2, "Category 3");
            StringPoint categoryAxisPoint24 = new StringPoint(3, "Category 4");

            serie2.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint21);
            serie2.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint22);
            serie2.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint23);
            serie2.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint24);

            serie2.setValues(new Values());
            serie2.getValues().setNumberReference(new NumberReference());
            serie2.getValues().getNumberReference().setFormula("Sheet1!$C$2:$C$5");

            serie2.getValues().getNumberReference().setNumberCache(new NumberCache());
            serie2.getValues().getNumberReference().getNumberCache().setFormat("General");

            NumericPoint valuesPoint21 = new NumericPoint(0, "2.4");
            NumericPoint valuesPoint22 = new NumericPoint(1, "4.4");
            NumericPoint valuesPoint23 = new NumericPoint(2, "1.8");
            NumericPoint valuesPoint24 = new NumericPoint(3, "2.8");

            serie2.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint21);
            serie2.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint22);
            serie2.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint23);
            serie2.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint24);
            
            BarChartSerie serie3 = new BarChartSerie();
            serie3.setIndex(2);
            serie3.setOrder(2);

            serie3.setSeriesText(new SeriesText());
            serie3.getSeriesText().setStringReference(new StringReference());
            serie3.getSeriesText().getStringReference().setFormula("Sheet1!$D$1");

            serie3.getSeriesText().getStringReference().setStringCache(new StringCache());

            StringPoint seriesTextPoint31 = new StringPoint(0, "Series 3");
            serie3.getSeriesText().getStringReference().getStringCache().getStringPoints().add(seriesTextPoint31);

            serie3.setCategoryAxis(new CategoryAxis());
            serie3.getCategoryAxis().setStringReference(new StringReference());
            serie3.getCategoryAxis().getStringReference().setFormula("Sheet1!$A$2:$A$5");

            serie3.getCategoryAxis().getStringReference().setStringCache(new StringCache());

            StringPoint categoryAxisPoint31 = new StringPoint(0, "Category 1");
            StringPoint categoryAxisPoint32 = new StringPoint(1, "Category 2");
            StringPoint categoryAxisPoint33 = new StringPoint(2, "Category 3");
            StringPoint categoryAxisPoint34 = new StringPoint(3, "Category 4");

            serie3.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint31);
            serie3.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint32);
            serie3.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint33);
            serie3.getCategoryAxis().getStringReference().getStringCache().getStringPoints().add(categoryAxisPoint34);

            serie3.setValues(new Values());
            serie3.getValues().setNumberReference(new NumberReference());
            serie3.getValues().getNumberReference().setFormula("Sheet1!$D$2:$D$5");

            serie3.getValues().getNumberReference().setNumberCache(new NumberCache());
            serie3.getValues().getNumberReference().getNumberCache().setFormat("General");

            NumericPoint valuesPoint31 = new NumericPoint(0, "2");
            NumericPoint valuesPoint32 = new NumericPoint(1, "2");
            NumericPoint valuesPoint33 = new NumericPoint(2, "3");
            NumericPoint valuesPoint34 = new NumericPoint(3, "5");

            serie3.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint31);
            serie3.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint32);
            serie3.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint33);
            serie3.getValues().getNumberReference().getNumberCache().getNumericPoints().add(valuesPoint34);

            barChart.getSeries().add(serie1);
            barChart.getSeries().add(serie2);
            barChart.getSeries().add(serie3);

            barChart.setFirstAxisID(1);
            barChart.setSecondAxisID(2);

            //create ChartSpace object and add barChart to the chartSpace
            ChartSpace chartSpace = new ChartSpace();
            chartSpace.setPlotArea(new PlotArea());
            chartSpace.getPlotArea().setLayout(new Layout());
            chartSpace.getPlotArea().getCharts().add(barChart);

            ChartCategoryAxis chartCategoryAxis = new ChartCategoryAxis();
            chartCategoryAxis.setID(1);
            chartCategoryAxis.setScaling(new Scaling());
            chartCategoryAxis.getScaling().setOrientation(Orientation.MINIMUM_TO_MAXIMUM);
            chartCategoryAxis.setPosition(AxisPosition.BOTTOM);
            chartCategoryAxis.setTickLabelPosition(TickLabelPosition.NEXT_TO);
            chartCategoryAxis.setCrossingAxisID(2);
            chartCategoryAxis.setCrosses(Crosses.ZERO);
            chartCategoryAxis.setAuto(true);
            chartCategoryAxis.setLabelAlignment(LabelAlignment.CENTER);
            chartCategoryAxis.setLabelOffset(100);

            ValueAxis valueAxis = new ValueAxis();
            valueAxis.setID(2);
            valueAxis.setScaling(new Scaling());
            valueAxis.getScaling().setOrientation(Orientation.MINIMUM_TO_MAXIMUM);
            valueAxis.setPosition(AxisPosition.LEFT);
            valueAxis.setMajorGridlines(new MajorGridlines());
            valueAxis.setNumberFormat(new NumberFormat());
            valueAxis.getNumberFormat().setFormat("General");
            valueAxis.getNumberFormat().setSourceLinked(true);
            valueAxis.setTickLabelPosition(TickLabelPosition.NEXT_TO);
            valueAxis.setCrossingAxisID(1);
            valueAxis.setCrosses(Crosses.ZERO);
            valueAxis.setCrossBetween(CrossBetween.BETWEEN);
        
            chartSpace.getPlotArea().getAxes().add(chartCategoryAxis);
            chartSpace.getPlotArea().getAxes().add(valueAxis);

            Legend legend = new Legend();
            legend.setPosition(LegendPosition.RIGHT);
            legend.setLayout(new Layout());

            chartSpace.setLegend(legend);
            chartSpace.setPlotVisibleOnly(true);
           
            //create anchor and add chart to the anchor
            TwoCellAnchor anchor = new TwoCellAnchor();
            
            StartAnchorPoint start = new StartAnchorPoint();
            start.setColumn(5);
            start.setColumnOffset(new Unit(1, UnitType.PIXEL));
            start.setRow(5);
            start.setRowOffset(new Unit(1, UnitType.PIXEL));

            EndAnchorPoint end = new EndAnchorPoint();
            end.setColumn(12);
            end.setColumnOffset(new Unit(33, UnitType.PIXEL));
            end.setRow(19);
            end.setRowOffset(new Unit(9, UnitType.PIXEL));

            anchor.setStart(start);
            anchor.setEnd(end);

            GraphicFrame graphicFrame = new GraphicFrame();
            graphicFrame.setID("1");
            graphicFrame.setName("Chart 1");
            graphicFrame.setGraphicObject(chartSpace);

            graphicFrame.setTransform2D(new Transform2D());
            graphicFrame.getTransform2D().setExtents(new Extents(0, 0));
            graphicFrame.getTransform2D().setOffset(new Offset(0, 0));

            anchor.setElement(graphicFrame);
            anchor.setClientData(new ClientData());

            DrawingObjects drawingObjects = new DrawingObjects();
            drawingObjects.getAnchors().add(anchor);

            sheet1.setDrawingObjects(drawingObjects);
            
            book.getSheets().add(sheet1);

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}
Independentsoft - any library, any programming language

JSpreadsheet > Tutorial > Create Bar Chart

Need help? Ask our developers:
