package com.company;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFChart;
import org.apache.poi.xslf.usermodel.XSLFSlide;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextBody;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextParagraph;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

public class Main {

    public static void main(String[] args) {
	    String inputPath = "excel_writing_bug.pptx";
	    String outputPath = "excel_writing_bug_output.pptx";
	    int slideNumber = 1;
	    String templateChartTitle = "Title";
	    String newChartTitle = "Title updated";
	    Map<String, Double> chartValues = new HashMap<>();
	    chartValues.put("Windows", 5d);
	    chartValues.put("Solaris", 2d);
	    chartValues.put("Unix", 8d);
	    chartValues.put("Linux", 5d);
	    try {
		    updateBarChartTemplate(inputPath, outputPath, slideNumber, templateChartTitle, newChartTitle, chartValues);
	    } catch (IOException e) {
		    e.printStackTrace();
	    }
    }

	public static void updateBarChartTemplate(String inputPath, String outputPath, int slideNumber, String templateChartTitle, String newChartTitle, Map<String, Double> chartValues)
		throws IllegalStateException, IOException{

		XMLSlideShow pptx = null;
		try {
			pptx = new XMLSlideShow(new FileInputStream(inputPath));
			XSLFChart chart = getChartFromTitle(inputPath, slideNumber, templateChartTitle);
			if (chart == null) throw new IllegalStateException("chart not found in the template");
			// embedded Excel workbook that holds the chart data
			POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
			XSSFWorkbook wb = new XSSFWorkbook();
			OutputStream xlsOut = null;
			try {
				XSSFSheet sheet = wb.createSheet();

				CTChart ctChart = chart.getCTChart();


				CTPlotArea plotArea = ctChart.getPlotArea();

				CTBarChart barChart = plotArea.getBarChartArray(0);
				//Bar Chart Series
				CTBarSer ser = barChart.getSerArray(0);

				// Series Text
				CTSerTx tx = ser.getTx();
				tx.getStrRef().getStrCache().getPtArray(0).setV(newChartTitle);
				sheet.createRow(0).createCell(1).setCellValue(newChartTitle);
				String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
				tx.getStrRef().setF(titleRef);

				// Category Axis Data
				CTAxDataSource cat = ser.getCat();
				CTStrData strData = cat.getStrRef().getStrCache();

				// Values
				CTNumDataSource val = ser.getVal();
				CTNumData numData = val.getNumRef().getNumCache();

				strData.setPtArray(null);  // unset old axis text
				numData.setPtArray(null);  // unset old values

				// set model
				int idx = 0;
				int rownum = 1;

				for (Map.Entry<String, Double> entry : chartValues.entrySet()) {
					String key = entry.getKey();
					String value = String.valueOf(entry.getValue());
					CTNumVal numVal = numData.addNewPt();
					numVal.setIdx(idx);
					numVal.setV(value);

					CTStrVal sVal = strData.addNewPt();
					sVal.setIdx(idx);
					sVal.setV(key);

					idx++;
					XSSFRow row = sheet.createRow(rownum++);
					row.createCell(0).setCellValue(key);
					row.createCell(1).setCellValue(Double.valueOf(value));
				}
				numData.getPtCount().setVal(idx);
				strData.getPtCount().setVal(idx);

				String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
				val.getNumRef().setF(numDataRange);
				String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
				cat.getStrRef().setF(axisDataRange);

				// updated the embedded workbook with the data
				xlsOut = xlsPart.getPackagePart().getOutputStream();
				try {
					wb.write(xlsOut);
				} finally {
					xlsOut.close();
				}
				FileOutputStream fos = new FileOutputStream(outputPath);
				try {
					pptx.write(fos);
				} finally {
					fos.close();
				}
			} finally {
				wb.close();
			}
		} finally {
			if (pptx != null) pptx.close();
		}
	}

	public static XSLFChart getChartFromTitle(String inputPath, int slideNumber, String templateChartTitle) throws IOException {
		XMLSlideShow pptx = null;
		try {
			pptx = new XMLSlideShow(new FileInputStream(inputPath));

			XSLFSlide slide = pptx.getSlides().get(slideNumber - 1);
			// find chart in the slide
			XSLFChart chart = null;
			for (POIXMLDocumentPart part : slide.getRelations()) {
				if (part instanceof XSLFChart) {
					chart = (XSLFChart) part;
					CTChart ctChart = chart.getCTChart();

					CTTitle ctTitle = ctChart.getTitle();
					CTTx titleTx = ctTitle.getTx();
					CTTextBody body = titleTx.getRich();
					CTTextParagraph paragraph = body.getPArray(0);
					String chartTitle = paragraph.getRArray(0).getT();

					if (chartTitle.equalsIgnoreCase(templateChartTitle)) {
						break;
					} else {
						chart = null;
					}
				}
			}
			return chart;
		} finally {
			if (pptx != null) pptx.close();
		}
	}
}