import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;

import java.awt.*;

public class ChartWithNonContiguousData {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("NonContiguousData.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add a clustered column chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.ColumnClustered);
        chart.setSeriesDataFromRange(false);
        //Set chart position
        chart.setLeftColumn(1);
        chart.setTopRow(10);
        chart.setRightColumn(10);
        chart.setBottomRow(24);

        //Add a series to the chart
        ChartSerie cs1 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs1.setName(sheet.getCellRange("B1").getValue());
        //Set category labels for the series using non-contiguous data
        cs1.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs1.setValues(sheet.getCellRange("B2:B3").addCombinedRange(sheet.getCellRange("B5:B6"))
.addCombinedRange(sheet.getCellRange("B8:B9")));
        //Specify the series type
        cs1.setSerieType(ExcelChartType.ColumnClustered);

        //Add a series to the chart
        ChartSerie cs2 = (ChartSerie)chart.getSeries().add();
        //Set series name
        cs2.setName(sheet.getCellRange("C1").getValue());
        //Set category labels for the series using non-contiguous data
        cs2.setCategoryLabels(sheet.getCellRange("A2:A3").addCombinedRange(sheet.getCellRange("A5:A6"))
.addCombinedRange(sheet.getCellRange("A8:A9")));
        //Set values for the series using non-contiguous data
        cs2.setValues(sheet.getCellRange("C2:C3").addCombinedRange(sheet.getCellRange("C5:C6"))
.addCombinedRange(sheet.getCellRange("C8:C9")));
        //Specify the series type
        cs2.setSerieType(ExcelChartType.ColumnClustered);

        //Set chart title
        chart.setChartTitle("Chart");
        chart.getChartTitleArea().getFont().setSize(20);
        chart.getChartTitleArea().setColor(Color.black);
        
        chart.getPrimaryValueAxis().hasMajorGridLines(false);

        //Save the result file
        workbook.saveToFile("Chart.xlsx", ExcelVersion.Version2013);
    }
}