Create Excel Chart within Excel File

Question

I am exporting some data from my system. I want to visualize these datasets in an excel chart. I have found and old, closed question, where the solution was missing. The charts should redraw when i change a datafield, this is excel standard, i guess.

I think it may work this way:

export Data
create manually a chart with MS-Excel
save and load this as a template in all other future exports

Do you know how to do it with POI using Java? Especially the import of the chart as template?

score 2 · Answer 1 · answered Mar 20 '14 at 13:55

POI doesn't give you that functionality but You can convert or copy charts(graphs) using J XL or Aspose Cells(Aspose is not free).

This is the code snippet to extract excel chart to image

public class ExportChartToImage
{
    public static void main(String[] args) throws Exception
    {
        //Start Excel
        Application excelApp = new Application();
        excelApp.setVisible(true);

        //Create test workbook
        Workbook workbook = excelApp.createWorkbook("/home/tejus/Desktop/Chart Test");

        //Get the first (and the only) worksheet
        final Worksheet worksheet1 = workbook.getWorksheet(1);

        //Fill-in the first worksheet with sample data
        worksheet1.getCell("A1").setValue("Date");
        worksheet1.getCell("A2").setValue("March 1");
        worksheet1.getCell("A3").setValue("March 8");
        worksheet1.getCell("A4").setValue("March 15");

        worksheet1.getCell("B1").setValue("Customer");
        worksheet1.getCell("B2").setValue("Smith");
        worksheet1.getCell("B3").setValue("Jones");
        worksheet1.getCell("B4").setValue("James");

        worksheet1.getCell("C1").setValue("Sales");
        worksheet1.getCell("C2").setValue("23");
        worksheet1.getCell("C3").setValue("17");
        worksheet1.getCell("C4").setValue("39");

        excelApp.getOleMessageLoop().doInvokeAndWait(new Runnable()
        {
            public void run()
            {
                final Variant unspecified = Variant.createUnspecifiedParameter();
                final Int32 localeID = new Int32(LocaleID.LOCALE_SYSTEM_DEFAULT);

                Range sourceDataNativePeer = worksheet1.getRange("A1:C4").getPeer();
                _Worksheet worksheetNativePeer = worksheet1.getPeer();

                IDispatch chartObjectDispatch = worksheetNativePeer.chartObjects(unspecified, localeID);

                ChartObjectsImpl chartObjects = new ChartObjectsImpl(chartObjectDispatch);
                ChartObject chartObject = chartObjects.add(new DoubleFloat(100), new DoubleFloat(150), new DoubleFloat(300), new DoubleFloat(225));

                _Chart chart = chartObject.getChart();
                chart.setSourceData(sourceDataNativePeer, new Variant(XlRowCol.xlRows));

                BStr fileName = new BStr("/home/tejus/Desktop/chart.gif");
                Variant filterName = new Variant("gif");
                Variant interactive = new Variant(false);

                chart.export(fileName, filterName, interactive);

                chart.setAutoDelete(false);
                chart.release();

                chartObject.setAutoDelete(false);
                chartObject.release();

                chartObjects.setAutoDelete(false);
                chartObjects.release();

                chartObjectDispatch.setAutoDelete(false);
                chartObjectDispatch.release();
            }
        });

        System.out.println("Press 'Enter' to terminate the application");
        System.in.read();

        //Close the MS Excel application.
        boolean saveChanges = false;
        workbook.close(saveChanges);
        boolean forceQuit = true;
        excelApp.close(forceQuit);
    }

}

i used J excel

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

Till now as the apache POI limitation is saying "You can not currently create charts. You can however create a chart in Excel, modify the chart data values using HSSF and write a new spreadsheet out. This is possible because POI attempts to keep existing records intact as far as possible".

However in my case, I have created a chart manually on excel sheet using Named Ranges, and using java, I am updating named ranges as per my requirement. Since the chart is based on named ranges so it also get updated.

For updation please check here

edited May 23 '17 at 11:43

Community

1
1

answered Jul 31 '13 at 13:32

Sankumarsingh

9,889
11
50
74

Thanks Sankumarsingh, i have always created the excel-docs from scratch with poi. Could you please explain, how to load an existing .xls-File ? – Stimpson Cat Jul 31 '13 at 13:51
Loading is as simple as creating. You can take help from http://stackoverflow.com/a/17709121/624003 – Sankumarsingh Jul 31 '13 at 14:11

Create Excel Chart within Excel File

2 Answers2