Charts

  • Demo
  • Java
  • C# source
This demo shows you how to create chart in an excel workbook.
import com.spire.xls.*;

public class ChartDemo {
    public void chartDemo(String excelFile, ExcelChartType chartType, String resultFileName){
        Workbook workbook = new Workbook();
		workbook.loadFromFile(excelFile);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        setChart(worksheet,chartType);
        sheetStyle(workbook,worksheet);
        workbook.saveToFile(resultFileName+".xlsx",FileFormat.Version2013);

    }
    private void setChart(Worksheet sheet, ExcelChartType chartType){
        sheet.setName("Chart data");
        sheet.setGridLinesVisible(false);

        //Add a new  chart worsheet to workbook
        Chart chart = sheet.getCharts().add();
        chart.setChartType(chartType);

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C7"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(4);
        chart.setTopRow(2);
        chart.setRightColumn(12);
        chart.setBottomRow(22);

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        chart.getPrimarySerieAxis().setTitle("Country");
        chart.getPrimarySerieAxis().getFont().isBold(true);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPrimarySerieAxis().setTitle("Sales(in Dollars)");
        chart.getPrimarySerieAxis().hasMajorGridLines(false);
        chart.getPrimarySerieAxis().getTitleArea().setTextRotationAngle(90);
        chart.getPrimarySerieAxis().setMinValue(1000);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPlotArea().getFill().setFillType(ShapeFillType.SolidColor);
        chart.getPlotArea().getFill().setForeKnownColor(ExcelColors.White);

        for (int i = 0; i < chart.getSeries().getCount(); i++){
            chart.getSeries().get(i).getFormat().getOptions().isVaryColor(true);
            chart.getSeries().get(i).getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        chart.getLegend().setPosition(LegendPositionType.Top);
    }

    public static void sheetStyle(Workbook workbook, Worksheet sheet){
        CellStyle oddStyle = workbook.getStyles().addStyle("oddStyle");
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        oddStyle.setKnownColor(ExcelColors.LightGreen1);

        CellStyle evenStyle = workbook.getStyles().addStyle("evenStyle");
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        evenStyle.setKnownColor(ExcelColors.LightTurquoise);

        for (int i = 0; i < sheet.getAllocatedRange().getRows().length; i++) {
            CellRange[] ranges = sheet.getAllocatedRange().getRows();
            if (ranges[i].getRow() != 0){
                if (ranges[i].getRow() % 2 == 0)
                {
                    ranges[i].setCellStyleName(evenStyle.getName());
                }
                else
                {
                    ranges[i].setCellStyleName(oddStyle.getName());
                }
            }
        }

        //Sets header style
        CellStyle styleHeader = workbook.getStyles().addStyle("headerStyle");
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        styleHeader.setVerticalAlignment(VerticalAlignType.Center);
        styleHeader.setKnownColor(ExcelColors.Green);
        styleHeader.getFont().setKnownColor(ExcelColors.White);
        styleHeader.getFont().isBold(true);
        styleHeader.setHorizontalAlignment(HorizontalAlignType.Center);

        for (int i = 0; i < sheet.getRows()[0].getCount(); i++) {
            CellRange range = sheet.getRows()[0];
            range.setCellStyleName(styleHeader.getName());
        }

        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -1].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -2].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getRows()[0].getStyle().setNumberFormat("General");

        sheet.getAllocatedRange().autoFitColumns();
        sheet.getAllocatedRange().autoFitRows();

        sheet.getRows()[0].setRowHeight(20);
    }
}

No Matter How Big or Small Your Project is,
Any technical question related to our product, contact us at support@e-iceblue.com.
Any question related to the purchase of product, contact us at sales@e-iceblue.com.
If you don't find the function you want, please request a free demo from us.