Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Nov 25, 2021 8:15 pm

Using spire.Office for JAVA 4.11.3 and Jdk13

When I use saveChartAsImage(), the chart (cf file as an attachment) is not generated.

NB. : it is working with ExcelChartType.PieExploded

The file as an attachment is saved for debugging before calling the function.
Attachments
a1fa448d-3f6e-4b95-94d7-88427c3bd86f_PIE.zip
(8.26 KiB) Downloaded 167 times

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Fri Nov 26, 2021 2:13 am

Hello Quentin,

Thank you for your inquiry.
I used saveChartAsImage() to test the Excel file you provided, but didn't find the behavior you mentioned. According to your description, you use our Spire.Office to create a chart and then save the chart as a picture, right? To help us further investigate your issue, please provide the following information. You could attach them here or send them to us via email (support@e-iceblue.com). Thanks in advance.
1) Your complete test code.
2) Your test environment, such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese).

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Nov 26, 2021 9:38 am

Here code used to create Chart

Code: Select all
    public BufferedImage createChart(Workbook workbook, ChartData data, Double width, Double height) throws Exception {
        if (workbook == null) {
            workbook = new Workbook();
        }
        final Worksheet sheet = workbook.createEmptySheet();
        this.addChartToWorksheet(sheet, data, width, height);

        workbook.saveToFile(UUID.randomUUID() + "_" + data.getType().name() + ".xlsx");

        return workbook.saveChartAsImage(sheet, 0);
    }

    protected void addChartToWorksheet(Worksheet sheet, ChartData data, Double width, Double height) throws Exception {

        final Chart chart = sheet.getCharts().add(formatExcelChartType(data.getType()));
        final DataTable dataTable = createChartDataTable(data);

        sheet.insertDataTable(dataTable, true, 1, 1);

        chart.getPlotArea().getFill().setVisible(false);

        final CellRange cellDataRange =
                sheet.getCellRange(1, 1, dataTable.getRows().size() + 1, dataTable.getColumns().size());

        chart.getPrimaryCategoryAxis().setCategoryLabels(
                sheet.getCellRange(1, data.getLegends() != null ? 2 : 1, 1, dataTable.getColumns().size()));

        //Set region of chart data
        chart.setDataRange(cellDataRange);
        chart.setSeriesDataFromRange(true);

        //Set position of chart
        chart.setLeft(1);
        chart.setTopRow(6);
        chart.setWidth((int) Math.round(width == null ? data.getW() : width));
        chart.setHeight((int) Math.round(height == null ? data.getH() : height));

        //Chart title
        chart.setChartTitle(data.getTitle());
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(10);
        chart.setWallsAndGridlines2D(true);

        // Text gray
        final Color textColor = new Color(89, 89, 89);

        chart.getPrimaryValueAxis().getBorder().setCustomLineWeight(0);
        chart.getPrimaryValueAxis().getBorder().setColor(Color.WHITE);
        chart.getPrimaryValueAxis().getFont().setColor(textColor);
        //chart.getPrimaryValueAxis().setAutoTickLabelSpacing(true);
        //chart.getPrimaryValueAxis().setAutoTickMarkSpacing(true);
        chart.getPrimaryValueAxis().getMajorGridLines().getBorder().setColor(new Color(217, 217, 217));
        chart.getPrimaryCategoryAxis().getFont().setColor(textColor);
        chart.getPrimaryCategoryAxis().getBorder().setCustomLineWeight(0);
        chart.getPrimaryCategoryAxis().getBorder().setColor(Color.WHITE);

        // Border
        chart.getChartArea().getBorder().setCustomLineWeight(0);
        chart.getChartArea().getBorder().setColor(new Color(255, 255, 255));

        if (data.getType() != ChartTypeEnum.PIE) {
            chart.hasLegend(data.getLegends() != null);
            chart.getLegend().setPosition(LegendPositionType.Bottom);
        } else {
            chart.hasLegend(true);
            chart.getLegend().setPosition(LegendPositionType.Right);
        }

        //Draw chart with data

        final AtomicInteger iSerie = new AtomicInteger();
        chart.getSeries().forEach(cs -> {
            final ChartSerie s = (ChartSerie) cs;
            final fr.exceliances.easy.service.generation.model.ChartSerie sourceSerie =
                    data.getSeries().get(iSerie.getAndIncrement());
            final Color color = sourceSerie.getColor();

            s.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(sourceSerie.isShowDataLabels());
            s.getFormat().getFormat3D().setBevelTopType(XLSXChartBevelType.SoftRound);
            s.getFormat().getFormat3D().setBevelTopWidth(12);
            s.getFormat().getFormat3D().setBevelTopHeight(4);

            if (sourceSerie.getNumberFormat() != null) {
                s.getValues().setNumberFormat(sourceSerie.getNumberFormat());
            } else {
                s.getValues().setNumberFormat("# ##0;-# ##0");
            }

            if (sourceSerie.getType() != null) {
                s.setSerieType(sourceSerie.getType());
            }

            if (sourceSerie.isSecondaryAxis()) {
                s.setUsePrimaryAxis(false);
            }

            if (data.getType() != ChartTypeEnum.PIE) {
                s.getFormat().setForeGroundColor(color == null ? CHART_COLORS[iSerie.get() - 1] : color);
                s.getFormat().getOptions().setOverlap(-10);
            }

            if (data.getType() == ChartTypeEnum.BAR_TREND || data.getType() == ChartTypeEnum.BAR) {
                s.getDataLabels().setTextRotationAngle(-45);
            }

            if (data.getType() == ChartTypeEnum.BAR_TREND) {
                final IChartTrendLine chartTrendLine = s.getTrendLines().add(TrendLineType.Linear);
                chartTrendLine.getBorder().setWeight(ChartLineWeightType.Narrow);
                chartTrendLine.getBorder().setColor(new Color(110, 100, 112));
            }

            for (int i = 0; i < s.getPointNumber(); i++) {
                final ChartDataPoint dp = chart.getSeries().get(0).getDataPoints().get(i++);
                dp.getDataLabels().setPosition(DataLabelPositionType.Automatic);
            }

        });

        if (chart.getSeries().size() == 1) {
            final ChartSerie chartSerie = chart.getSeries().get(0);
            final fr.exceliances.easy.service.generation.model.ChartSerie sourceSerie = data.getSeries().get(0);
            for (int i = 0; i < chartSerie.getPointNumber(); i++) {
                final ChartDataPoint dp = chartSerie.getDataPoints().get(i);
                final ChartValue<?> chartValue = sourceSerie.getValues().get(i);
                dp.getDataLabels().getFormat().setBackGroundColor(new Color(40, 40, 40));
                dp.getDataLabels().setColor(Color.DARK_GRAY);
                dp.getDataFormat().getFormat3D().setBevelTopType(XLSXChartBevelType.SoftRound);
                dp.getDataFormat().getFormat3D().setBevelTopWidth(12);
                dp.getDataFormat().getFormat3D().setBevelTopHeight(4);
                dp.getDataFormat()
                        .setForeGroundColor(chartValue.getColor() == null ? CHART_COLORS[i] : chartValue.getColor());
            }
        }
    }

    protected DataTable createChartDataTable(ChartData chartData) throws Exception {
        final DataTable dataTable = new DataTable();
        final DataRowCollection rows = new DataRowCollection(dataTable);

        if (chartData.getLegends() != null) {
            final DataColumn col = new DataColumn();
            col.setColumnName("");
            dataTable.getColumns().add(new DataColumn());
        }

        chartData.getLabels().forEach(l -> {
            final DataColumn col = new DataColumn();
            col.setColumnName(l);
            dataTable.getColumns().add(col);
        });

        final AtomicInteger iSerie = new AtomicInteger();
        for (fr.exceliances.easy.service.generation.model.ChartSerie cs : chartData.getSeries()) {
            final DataRow rowValue = dataTable.newRow();
            final AtomicInteger cellIndx = new AtomicInteger(0);

            if (chartData.getLegends() != null) {
                rowValue.setObject(cellIndx.getAndIncrement(), chartData.getLegends().get(iSerie.getAndIncrement()));
            }

            cs.getValues().forEach(cv -> {
                rowValue.setObject(cellIndx.getAndIncrement(), cv.getValue());
            });
            rows.add(rowValue);
        }
        dataTable.setRows(rows);
        return dataTable;
    }

    protected ExcelChartType formatExcelChartType(ChartTypeEnum type) {
        switch (type) {
            case PIE:
                return ExcelChartType.PieExploded;
            case LINE:
                return ExcelChartType.LineMarkers;
            case BAR:
            case BAR_TREND:
                return ExcelChartType.ColumnClustered;
            default:
                return ExcelChartType.Bar100PercentStacked;
        }
    }


And implementation to ChartData

Code: Select all

        final ChartData data = new ChartData(200, 250, 450, 250);
        final List<String> labels = new ArrayList<>();
        labels.add("SCI IR");
        labels.add("SCI IS");
        data.setLabels(labels);

        final List<String> legends = new ArrayList<>();
        legends.add("Flux de trésorerie nets sur la période (associé)");
        legends.add("VAN **");
        legends.add("TRI *");
        data.setLegends(legends);

        final List<ChartSerie> series = new ArrayList<>();
        ChartSerie serie = new ChartSerie(Arrays.asList(new ChartValue<>(664967.3000000002), new ChartValue<>(714024.6299999999)));
        series.add(serie);

        serie = new ChartSerie(Arrays.asList(new ChartValue<>(114992.18000000001), new ChartValue<>(220174.36)));
        serie.setColor(Color.MAGENTA);
        series.add(serie);

        serie = new ChartSerie(Arrays.asList(new ChartValue<>(0.17113181421731205), new ChartValue<>(0.39447897567122436)));
        serie.setType(ExcelChartType.LineMarkers);
        serie.setSecondaryAxis(true);
        serie.setNumberFormat("0.00%");
        series.add(serie);

        data.setSeries(series);
        data.setType(ChartTypeEnum.BAR);
        return data;


Custom classes are in attachment.
Attachments
classes.zip
Classes definition
(2.3 KiB) Downloaded 164 times

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Fri Nov 26, 2021 9:43 am

Sorry, mistake about de chart implementation. Here the implementation for PIE.


Code: Select all

 final AtomicReference<Double> rentsTotalAmount = new AtomicReference<>(0.0);

        final ChartData data = new ChartData(430, 320, 300, 200);
        final List<String> labels = new ArrayList<>();
        labels.add(i18n.translate("rents"));
        labels.add(i18n.translate("resale"));
        final List<ChartSerie> series = new ArrayList<>();

        final ChartSerie serie = new ChartSerie(Arrays.asList(
                new ChartValue<>(1559548).setPercent(0.58),
                new ChartValue<>(1125209).setPercent(0.42)));
        data.setLabels(labels);
        series.add(serie);
        data.setSeries(series);
        data.setType(ChartTypeEnum.PIE);
        return data;

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Fri Nov 26, 2021 9:44 am

Please notice to replace ExcelChartType.PieExploded by ExcelChartType.Pie to have the issue.

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Fri Nov 26, 2021 10:23 am

Hello,

Thank you for your sharing.
I will investigate that based on the information you provided and give you some feedback as soon as possible. Hope you can understand.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Nov 29, 2021 9:14 am

Hello,

Thanks for your patience, and sorry for the late reply as weekend.
I tested your case, but did not reproduce your issue. I have attached my test project(please see SaveChartAsImageNotWorkingWithPie.java in the attachment of https://www.e-iceblue.com/forum/savechartasimage-generate-infinite-loop-process-t10740.html), please download and test it on your side. If the problem still exists after the test, please provide your test environment (such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese)) for our reference. Thanks in advance.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Mon Nov 29, 2021 10:35 am

Hi,

I've downloaded your project and execute main() : I confirm that I've reproduced the issue (infinite process)

Test environment : Windows 10 Professionnel, version 21H1, 64 bits
Region setting : FRANCE
IntelliJ IDEA 2021 2.2
JDK 13

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Tue Nov 30, 2021 2:53 am

Hello,

Thank you for your feedback.
I tested it again in the same environment with the same settings as you, but still did not reproduce your issue. In addition, I also did a test on Windows Server2016, and there was still no problem. Do you directly open my projcet in IntelliJ IDEA 2021 and run it? Could you please test my project again on other computers and inform us of your test results? Looking forward to your test feedback.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Tue Nov 30, 2021 12:06 pm

Important

If I change de locale default as
Code: Select all
Locale.setDefault(Locale.CHINA);


I've no more issues (infinite process or image export).

So to reproduce my issues, you should add this :
Code: Select all
Locale.setDefault(Locale.FRANCE);

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Wed Dec 01, 2021 3:48 am

Hello,

Thank you for your feedback.
By adding "Locale.setDefault(Locale.FRANCE)", I did reproduce your issue(saveChartAsImage not working). I have logged the issue into our bug tracking system with the ticket number SPIREXLS-3564. Our development team will investigate and fix it. Once it is resolved, I will inform you in time. Sorry for the inconvenience caused.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Wed Dec 01, 2021 8:35 am

Hi,

Thank you : maybe a full control of the lib with this Locale is mandatory because there is probably other issues caused by this problem.

Please tell me when the new version with the fix is available, as soon as possible !

QuentinSup
 
Posts: 46
Joined: Mon Oct 18, 2021 9:18 am

Wed Dec 01, 2021 9:07 am

Hello,

Thank you for your further response.
Considering your state of emergency, I have raised the issue to the highest level. Our developers are working hard to solve it, if I get any update about the issue, I will inform you immediately.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Tue Dec 21, 2021 9:56 am

Hello,

Thank you for your patience.
Glad to inform that we just released Spire.Office for Java Version:4.12.2 which fixed the issue SPIREXLS-3564, please download it from the following links to test.
Website link: https://www.e-iceblue.com/Download/office-for-java.html
Maven link: https://repo.e-iceblue.com/nexus/conten ... ce/4.12.2/
In addition, please add the following code in pom.xml to avoid the project throwing exceptions.
Code: Select all
<dependency>
<groupId>org.glassfish.corba</groupId>
<artifactId>glassfish-corba-omgapi</artifactId>
<version>4.2.1</version>
</dependency>

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS