Create Bar Chart in Excel with Apache POI

11
votes

I'm trying to create a Bar Chart in an XLSX Spreadsheet using Apache POI, but Excel keeps saying that there's a problem with the content and deleting the chart when I try to open the file. Here's the full code of what I'm trying to do:

public static void createBarChart() {

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet2");
        Row row;
        Cell cell;

        row = sheet.createRow(0);
        row.createCell(0);
        row.createCell(1).setCellValue("HEADER 1");
        row.createCell(2).setCellValue("HEADER 2");
        row.createCell(3).setCellValue("HEADER 3");

        for (int r = 1; r < 5; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue("Serie " + r);
            cell = row.createCell(1);
            cell.setCellValue(123);
            cell = row.createCell(2);
            cell.setCellValue(456);
            cell = row.createCell(3);
            cell.setCellValue(789);
        }

        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 20);

        Chart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart) chart).getCTChart();

        ctChart.addNewTitle();

        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();

        ctBarChart.addNewBarDir().setVal(STBarDir.COL);
        ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);
        ctBarChart.addNewVaryColors().setVal(false);

        // First series
        CTBarSer ctBarSer = ctBarChart.addNewSer();

        ctBarSer.addNewIdx().setVal(0);
        ctBarSer.addNewOrder().setVal(0);

        // [01] Title
        CTSerTx ctSerTx = ctBarSer.addNewTx();
        CTStrRef ctSerTxStrRef = ctSerTx.addNewStrRef();
        ctSerTxStrRef.setF("Sheet2!$A$2");

        CTStrData strCache = ctSerTxStrRef.addNewStrCache();
        strCache.addNewPtCount().setVal(1);
        CTStrVal newPt = strCache.addNewPt();
        newPt.setV("Serie 1");
        newPt.setIdx(0);

        // [01] SpPr
        CTShapeProperties spPr = ctBarSer.addNewSpPr();
        CTSolidColorFillProperties solidFill = spPr.addNewSolidFill();
        solidFill.addNewSchemeClr().setVal(STSchemeColorValImpl.ACCENT_1);
        spPr.addNewLn().addNewNoFill();
        spPr.addNewEffectLst();

        // [01] Invert if negative
        ctBarSer.addNewInvertIfNegative().setVal(false);

        // [01] Series titles
        CTAxDataSource newCat = ctBarSer.addNewCat();
        CTStrRef addNewStrRef = newCat.addNewStrRef();
        addNewStrRef.setF("Sheet2!$B$1:$D$1");

        CTStrData addNewStrCache = addNewStrRef.addNewStrCache();
        addNewStrCache.addNewPtCount().setVal(3);

        CTStrVal pt1 = addNewStrCache.addNewPt();
        pt1.setIdx(0);
        pt1.setV(sheet.getRow(0).getCell(1).getStringCellValue());

        CTStrVal pt2 = addNewStrCache.addNewPt();
        pt2.setIdx(1);
        pt2.setV(sheet.getRow(0).getCell(2).getStringCellValue());

        CTStrVal pt3 = addNewStrCache.addNewPt();
        pt3.setIdx(2);
        pt3.setV(sheet.getRow(0).getCell(3).getStringCellValue());

        // [01] Series values
        CTNumDataSource newVal = ctBarSer.addNewVal();
        CTNumRef numRef = newVal.addNewNumRef();
        numRef.setF("Sheet2!$B$2:$D$2");
        CTNumData numCache = numRef.addNewNumCache();
        numCache.addNewPtCount().setVal(3);

        CTNumVal numpt1 = numCache.addNewPt();
        numpt1.setIdx(0);
        numpt1.setV(String.valueOf(sheet.getRow(1).getCell(1).getNumericCellValue()));

        CTNumVal numpt2 = numCache.addNewPt();
        numpt2.setIdx(1);
        numpt2.setV(String.valueOf(sheet.getRow(1).getCell(2).getNumericCellValue()));

        CTNumVal numpt3 = numCache.addNewPt();
        numpt3.setIdx(2);
        numpt3.setV(String.valueOf(sheet.getRow(1).getCell(3).getNumericCellValue()));

        // dLbls
        CTDLbls dLbls = ctBarChart.addNewDLbls();
        dLbls.addNewShowBubbleSize().setVal(false);
        dLbls.addNewShowLegendKey().setVal(false);
        dLbls.addNewShowCatName().setVal(false);
        dLbls.addNewShowSerName().setVal(false);
        dLbls.addNewShowPercent().setVal(false);
        dLbls.addNewShowVal().setVal(false);

        FileOutputStream fileOut = new FileOutputStream("barchart.xlsx");
        wb.write(fileOut);
        wb.close();
        fileOut.close();
    }

Can anyone help me find (and, well, solve) the issue? Thanks in advance!

3
javaexcelchartsapache-poi

3 Answers

24
votes

Phew, difficult birth ;-). For users without knowledge of the background Apache POI supports only ScatterCharts and LineCharts why?. There is described how to proceed in principle.

As I said. Take the simplest possible bar chart for testing first. That is one series with two values. Then you would have seen that you have completely forgotten the axes in your code. A pie chart has no axes. Thats why there they are not necessary. But a bar chart will not work without axes.

Instead you can unscrupulously forget all things what is named "cache".

Whether other things are needed is determined by try and error. Also one could read the Office OpenXML recommendation. Probably the missing of all elements having minOccurs <> "0"` leads to deleting the Drawing while opening the Workbook.

Simple Example for a bar chart:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;

import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;

public class BarChart {

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");

        Row row;
        Cell cell;

        row = sheet.createRow(0);
        row.createCell(0);
        row.createCell(1).setCellValue("HEADER 1");
        row.createCell(2).setCellValue("HEADER 2");
        row.createCell(3).setCellValue("HEADER 3");

        for (int r = 1; r < 5; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue("Serie " + r);
            cell = row.createCell(1);
            cell.setCellValue(new java.util.Random().nextDouble());
            cell = row.createCell(2);
            cell.setCellValue(new java.util.Random().nextDouble());
            cell = row.createCell(3);
            cell.setCellValue(new java.util.Random().nextDouble());
        }

        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);

        Chart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

        for (int r = 2; r < 6; r++) {
           CTBarSer ctBarSer = ctBarChart.addNewSer();
           CTSerTx ctSerTx = ctBarSer.addNewTx();
           CTStrRef ctStrRef = ctSerTx.addNewStrRef();
           ctStrRef.setF("Sheet1!$A$" + r);
           ctBarSer.addNewIdx().setVal(r-2);  
           CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
           ctStrRef = cttAxDataSource.addNewStrRef();
           ctStrRef.setF("Sheet1!$B$1:$D$1"); 
           CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
           CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
           ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);

           //at least the border lines in Libreoffice Calc ;-)
           ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   

        } 

        //telling the BarChart that it has axes and giving them Ids
        ctBarChart.addNewAxId().setVal(123456);
        ctBarChart.addNewAxId().setVal(123457);

        //cat axis
        CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); 
        ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
        CTScaling ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewDelete().setVal(false);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //val axis
        CTValAx ctValAx = ctPlotArea.addNewValAx(); 
        ctValAx.addNewAxId().setVal(123457); //id of the val axis
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewDelete().setVal(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //legend
        CTLegend ctLegend = ctChart.addNewLegend();
        ctLegend.addNewLegendPos().setVal(STLegendPos.B);
        ctLegend.addNewOverlay().setVal(false);

System.out.println(ctChart);

        FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}

This example needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in the FAQ-N10025.

Above code has worked until apache poi 3.17.

Following code works using apache poi 4.1.0. It needs the full jar of all of the schemas ooxml-schemas-1.4.jar.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;

import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;

public class BarChart {

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");

        Row row;
        Cell cell;

        row = sheet.createRow(0);
        row.createCell(0);
        row.createCell(1).setCellValue("HEADER 1");
        row.createCell(2).setCellValue("HEADER 2");
        row.createCell(3).setCellValue("HEADER 3");

        for (int r = 1; r < 5; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue("Serie " + r);
            cell = row.createCell(1);
            cell.setCellValue(new java.util.Random().nextDouble());
            cell = row.createCell(2);
            cell.setCellValue(new java.util.Random().nextDouble());
            cell = row.createCell(3);
            cell.setCellValue(new java.util.Random().nextDouble());
        }

        XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);

        XSSFChart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

        for (int r = 2; r < 6; r++) {
           CTBarSer ctBarSer = ctBarChart.addNewSer();
           CTSerTx ctSerTx = ctBarSer.addNewTx();
           CTStrRef ctStrRef = ctSerTx.addNewStrRef();
           ctStrRef.setF("Sheet1!$A$" + r);
           ctBarSer.addNewIdx().setVal(r-2);  
           CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
           ctStrRef = cttAxDataSource.addNewStrRef();
           ctStrRef.setF("Sheet1!$B$1:$D$1"); 
           CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
           CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
           ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);

           //at least the border lines in Libreoffice Calc ;-)
           ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   

        } 

        //telling the BarChart that it has axes and giving them Ids
        ctBarChart.addNewAxId().setVal(123456);
        ctBarChart.addNewAxId().setVal(123457);

        //cat axis
        CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); 
        ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
        CTScaling ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewDelete().setVal(false);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //val axis
        CTValAx ctValAx = ctPlotArea.addNewValAx(); 
        ctValAx.addNewAxId().setVal(123457); //id of the val axis
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewDelete().setVal(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        //legend
        CTLegend ctLegend = ctChart.addNewLegend();
        ctLegend.addNewLegendPos().setVal(STLegendPos.B);
        ctLegend.addNewOverlay().setVal(false);

System.out.println(ctChart);

        FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}
4
votes

with Apache-Poi 4.0 we can achive it with minimal code. Below example is available with apache poi example. Now createChart Method is available with all format(Excel/DOCX/PPTX).

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.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
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.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 BarChart {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("barchart");
            final int NUM_OF_ROWS = 3;
            final int NUM_OF_COLUMNS = 10;

            // Create a row and put some cells in it. Rows are 0 based.
            Row row;
            Cell cell;
            for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
                row = sheet.createRow((short) rowIndex);
                for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
                    cell = row.createCell((short) colIndex);
                    cell.setCellValue(colIndex * (rowIndex + 1.0));
                }
            }

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("BarChart");
            chart.setTitleOverlay(false);
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            // Use a category axis for the bottom axis.
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle("x"); 
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle("f(x)");
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

            XDDFDataSource<Double> xs = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
            XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
            XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));

            XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
            XDDFChartData.Series series1 = data.addSeries(xs, ys1);
            series1.setTitle("2x", null);
            XDDFChartData.Series series2 = data.addSeries(xs, ys2);
            series2.setTitle("3x", null);
            chart.plot(data);

            // in order to transform a bar chart into a column chart, you just need to change the bar direction
            XDDFBarChartData bar = (XDDFBarChartData) data;
            bar.setBarDirection(BarDirection.COL);
            // looking for "Stacked Bar Chart"? uncomment the following line
            // bar.setBarGrouping(BarGrouping.STACKED);

            solidFillSeries(data, 0, PresetColor.CHARTREUSE);
            solidFillSeries(data, 1, PresetColor.TURQUOISE);

            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }

    private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFChartData.Series series = data.getSeries().get(index);
        XDDFShapeProperties properties = series.getShapeProperties();
        if (properties == null) {
            properties = new XDDFShapeProperties();
        }
        properties.setFillProperties(fill);
        series.setShapeProperties(properties);
    }
}
0
votes

This is the code for creating the bar chart (actually creating the column chart) in Excel using XSSF for Excel 2007 and above using the NPOI 2.4.1 nuget package.

private XSSFWorkbook createBarchart()
    { 
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("WorkForceAnalytics");

        XSSFCellStyle styleHeader = (XSSFCellStyle)workbook.CreateCellStyle();
        styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        styleHeader.SetFont(getNewXSSFFont(workbook, styleHeader));

        XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
        style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;


        XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
        row1.CreateCell(0).SetCellValue("");

        List<string> lstdatase = new List<string>(4);
        lstdatase.Add("A1");
        lstdatase.Add("B1");
        lstdatase.Add("C1");
        lstdatase.Add("D1");
        lstdatase.Add("E1");

        for (int i = 1; i < 5; i++)
        {
            row1.CreateCell(i).SetCellValue(lstdatase[i - 1].ToString());
            row1.GetCell(i).CellStyle = styleHeader;
        }

        int rowvalue = 1;
        List<string> lstdata = new List<string>(8);
        lstdata.Add("A");
        lstdata.Add("B");
        lstdata.Add("C");
        lstdata.Add("D");
        lstdata.Add("E");
        lstdata.Add("F");
        lstdata.Add("G");
        lstdata.Add("H");

        int d = 10;

        XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
        XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 6, 1, 15, 18);

        IChart chart = drawing.CreateChart(anchor); 
        IChartLegend legend = chart.GetOrCreateLegend();    
        legend.Position = (LegendPosition.TopRight);

        IBarChartData<string, double> data = chart.ChartDataFactory.CreateBarChartData<string, double>();   

        IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); 
        IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
        leftAxis.Crosses = AxisCrosses.AutoZero;
        leftAxis.SetCrossBetween(AxisCrossBetween.Between); 

        IChartDataSource<string> xs = DataSources.FromStringCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5-1));

        for (int ii = 0; ii < 8; ii++)
        {
            XSSFRow rownew = (XSSFRow)sheet.CreateRow(rowvalue);
            rownew.CreateCell(0).SetCellValue(lstdata[ii].ToString());

            for (int i = 1; i < 5; i++)
            {
                rownew.CreateCell(i).SetCellValue(d);
                d++;
                rownew.GetCell(i).CellStyle = style;
            }
            rowvalue++;


            IChartDataSource<double> ys = DataSources.FromNumericCellRange(sheet, new NPOI.SS.Util.CellRangeAddress(ii + 1, ii + 1, 1, 5 - 1));
            data.AddSeries(xs, ys).SetTitle(lstdata[ii].ToString()); 
        } 

        chart.Plot(data, bottomAxis, leftAxis);

        sheet.ForceFormulaRecalculation = true;

        string fileName = "WorkforceAnalyticsReport.xlsx";
        using (var exportData = new MemoryStream())
        {
            Response.Clear();
            workbook.Write(exportData);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            Response.BinaryWrite(exportData.ToArray());
            Response.End();
        }
        return workbook;
    }