line chart design in excel using Apache poi

Question

I'm creating an Excel file with line chart. I've created chart and filled it with data but I cannot choose the design that i want. How to change my lines color,weight and design using Apache poi?

now is the white chart:

what i want is some thing like black one:

and this is my code input is something like this

people={100,200,150,140,130,120,110,100,90,85,80,75,70,65,60,55,50,45,40,35}

blueEye={10,110,140,135,130,120,110,100,90,85,80,75,70,65,60,55,50,45,40,35}

lineChart(people,blueEye);

package GeneticPropagation;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ApachePoiLineChart {

    public static void lineChart(int[] people,int[] blueEye) throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {

            String sheetName = "EyeLineChart";

            XSSFSheet sheet = wb.createSheet(sheetName);

            Row row = sheet.createRow((short) 0);
            Cell cell = row.createCell((short) 0);
            cell.setCellValue("Year");

            cell = row.createCell((short) 1);
            cell.setCellValue("people");

            cell = row.createCell((short) 2);
            cell.setCellValue("blue eye");

            for (int i = 0; i < people.length; i++) {
                row = sheet.createRow((short) i+1);
                cell = row.createCell((short) 0);
                cell.setCellValue(i);
                cell = row.createCell((short) 1);
                cell.setCellValue(people[i]);
                cell = row.createCell((short) 2);
                cell.setCellValue(blueEye[i]);
            }

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 0, 16, 16);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Population growth chart");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle("Century");
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle("Population");

            XDDFNumericalDataSource<Double> countries = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 20, 0, 0));

            XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 20, 1, 1));

            XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 20, 2, 2));

            XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

            XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
            series1.setTitle("all of Population", null);
            series1.setSmooth(false);
            series1.setMarkerStyle(MarkerStyle.STAR);

            XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
            series2.setTitle("blue eye Population", null);
            series2.setSmooth(true);
            series2.setMarkerSize((short) 6);
            series2.setMarkerStyle(MarkerStyle.SQUARE);

            chart.plot(data);

            // Write output to an excel file
            String filename = "line-chart-top-seven-countries.xlsx";
            try (FileOutputStream fileOut = new FileOutputStream(filename)) {
                wb.write(fileOut);
            }


        }
    }

}

Answer 1

i have changed my code and it works much better

and we can define color and can change everything using series....

for example:

XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, population);

color = "#0032FF";

lineSeriesColor(series1, XDDFColor.from(hex2Rgb(color)));

series1.setTitle("blue eye Population", null);

series1.setSmooth(true);

series1.setMarkerStyle(MarkerStyle.NONE);

series1.setMarkerStyle(...);

series1.setSmooth(..);

series1.getShapeProperties();

series1.setMarkerSize(...);

series1.setShapeProperties(...);

series1.getErrorBars();

series1.hasErrorBars();

series1.isSmooth();

series1.setTitle(...);

series1.plot();

...

public static void lineChart(int[] people, int[] blueEye) throws FileNotFoundException, IOException { try (XSSFWorkbook wb = new XSSFWorkbook()) {

        String sheetName = "EyeLineChart";

        XSSFSheet sheet = wb.createSheet(sheetName);

        Row row = sheet.createRow((short) 0);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue("Year");

        cell = row.createCell((short) 1);
        cell.setCellValue("people");

        cell = row.createCell((short) 2);
        cell.setCellValue("blue eye");

        for (int i = 0; i < people.length; i++) {
            row = sheet.createRow((short) i + 1);
            cell = row.createCell((short) 0);
            cell.setCellValue(i * 2000 / people.length);
            cell = row.createCell((short) 1);
            cell.setCellValue(people[i]);
            cell = row.createCell((short) 2);
            cell.setCellValue(blueEye[i]);
        }

        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 0, (people.length + 2000) / 100, 16);

        XSSFChart chart = drawing.createChart(anchor);
        chart.setTitleText("Population growth chart");
        chart.setTitleOverlay(false);

        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);

        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle("Year");
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle("Population");

        XDDFNumericalDataSource<Double> countries = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                new CellRangeAddress(1, people.length, 0, 0));

        XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                new CellRangeAddress(1, people.length, 1, 1));

        XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                new CellRangeAddress(1, people.length, 2, 2));

        XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

        XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
        String color = "#DEDEDE";
        lineSeriesColor(series1, XDDFColor.from(hex2Rgb(color)));
        series1.setTitle("all of Population", null);
        series1.setSmooth(true);
        series1.setMarkerStyle(MarkerStyle.DIAMOND);


        XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
        color = "#0032FF";
        lineSeriesColor(series2, XDDFColor.from(hex2Rgb(color)));
        series2.setTitle("blue eye Population", null);
        series2.setSmooth(true);
        series2.setMarkerStyle(MarkerStyle.NONE);


        chart.plot(data);

        // Write output to an excel file
        String filename = "chart.xlsx";
        try (FileOutputStream fileOut = new FileOutputStream(filename)) {
            wb.write(fileOut);
        }
    }
}

to change color should use:

private static void lineSeriesColor(XDDFChartData.Series series, XDDFColor color) {
    XDDFSolidFillProperties fill = new XDDFSolidFillProperties(color);
    XDDFLineProperties line = new XDDFLineProperties();
    line.setFillProperties(fill);
    XDDFShapeProperties properties = series.getShapeProperties();
    if (properties == null) {
        properties = new XDDFShapeProperties();
    }
    properties.setLineProperties(line);
    series.setShapeProperties(properties);
}

and there is a hex to RGB converter just in case of need:

private static byte[] hex2Rgb(String colorStr) {
    int r = Integer.valueOf(colorStr.substring(1, 3), 16);
    int g = Integer.valueOf(colorStr.substring(3, 5), 16);
    int b = Integer.valueOf(colorStr.substring(5, 7), 16);
    return new byte[]{(byte) r, (byte) g, (byte) b};
}

you give it 2 arrays and it gives you a chart