3

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:

enter image description here

what i want is some thing like black one:

enter image description here

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);
            }


        }
    }

}
smac89
  • 39,374
  • 15
  • 132
  • 179
omid
  • 76
  • 5
  • 1
    My experience with Apache POI was, that it is much easier to create an Excel template, put it in my Jar file to open dynamically, fill with data and provide to user, than to do some coding magic. Can you use a template yourself? – Marek Puchalski Jun 14 '20 at 19:00

1 Answers1

2

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

omid
  • 76
  • 5