Issue #60971, handle formula chart titles

implemented per issue, breaking out static text vs. formula based title getters and setters, with unit test updates and additions.

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1791025 13f79535-47bb-0310-9956-ffa450edef68

5 files changed, 127 insertions, 15 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFChart.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFChart.java
index 1bee1ad995..43e865989a 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFChart.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFChart.java
@@ -34,6 +34,7 @@ import org.apache.poi.ss.usermodel.charts.ChartAxis;
 import org.apache.poi.ss.usermodel.charts.ChartAxisFactory;
 import org.apache.poi.ss.usermodel.charts.ChartData;
 import org.apache.poi.util.Internal;
+import org.apache.poi.util.Removal;
 import org.apache.poi.xssf.usermodel.charts.XSSFCategoryAxis;
 import org.apache.poi.xssf.usermodel.charts.XSSFChartAxis;
 import org.apache.poi.xssf.usermodel.charts.XSSFChartDataFactory;
@@ -49,6 +50,7 @@ import org.openxmlformats.schemas.drawingml.x2006.chart.CTChartSpace;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTPageMargins;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTPrintSettings;
+import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTTitle;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTTx;
 import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
@@ -250,9 +252,27 @@ public final class XSSFChart extends POIXMLDocumentPart implements Chart, ChartA
 	}
 
 	/**
-	 * Returns the title, or null if none is set
+	 * Returns the title static text, or null if none is set.
+	 * Note that a title formula may be set instead.
+	 * @return static title text, if set
+	 * @deprecated POI 3.16, use {@link #getTitleText()} instead.
 	 */
+    @Deprecated
+    @Removal(version="4.0")
 	public XSSFRichTextString getTitle() {
+	    return getTitleText();
+	}
+	
+	/**
+     * Returns the title static text, or null if none is set.
+     * Note that a title formula may be set instead.
+     * Empty text result is for backward compatibility, and could mean the title text is empty or there is a formula instead.
+     * Check for a formula first, falling back on text for cleaner logic.
+     * @return static title text if set, 
+     *         null if there is no title, 
+     *         empty string if the title text is empty or the title uses a formula instead
+	 */
+	public XSSFRichTextString getTitleText() {
 		if(! chart.isSetTitle()) {
 			return null;
 		}
@@ -278,9 +298,21 @@ public final class XSSFChart extends POIXMLDocumentPart implements Chart, ChartA
 	}
 
 	/**
-	 * Sets the title text.
+	 * Sets the title text as a static string.
+	 * @param newTitle to use
+	 * @deprecated POI 3.16, use {@link #setTitleText(String)} instead.
 	 */
+    @Deprecated
+    @Removal(version="4.0")
 	public void setTitle(String newTitle) {
+	    
+	}
+	
+    /**
+     * Sets the title text as a static string.
+     * @param newTitle to use
+     */
+	public void setTitleText(String newTitle) {
 		CTTitle ctTitle;
 		if (chart.isSetTitle()) {
 			ctTitle = chart.getTitle();
@@ -325,6 +357,63 @@ public final class XSSFChart extends POIXMLDocumentPart implements Chart, ChartA
 			run.setT(newTitle);
 		}
 	}
+	
+	/**
+	 * Get the chart title formula expression if there is one
+	 * @return formula expression or null
+	 */
+	public String getTitleFormula() {
+	    if(! chart.isSetTitle()) {
+	        return null;
+	    }
+
+	    CTTitle title = chart.getTitle();
+	    
+	    if (! title.isSetTx()) {
+	        return null;
+	    }
+	    
+	    CTTx tx = title.getTx();
+	    
+	    if (! tx.isSetStrRef()) {
+	        return null;
+	    }
+	    
+	    return tx.getStrRef().getF();
+	}
+	
+	/**
+	 * Set the formula expression to use for the chart title
+	 * @param formula
+	 */
+	public void setTitleFormula(String formula) {
+	    CTTitle ctTitle;
+	    if (chart.isSetTitle()) {
+	        ctTitle = chart.getTitle();
+	    } else {
+	        ctTitle = chart.addNewTitle();
+	    }
+
+	    CTTx tx;
+	    if (ctTitle.isSetTx()) {
+	        tx = ctTitle.getTx();
+	    } else {
+	        tx = ctTitle.addNewTx();
+	    }
+
+	    if (tx.isSetRich()) {
+	        tx.unsetRich();
+	    }
+	    
+	    CTStrRef strRef;
+	    if (tx.isSetStrRef()) {
+	        strRef = tx.getStrRef();
+	    } else {
+	        strRef = tx.addNewStrRef();
+	    }
+	    
+	    strRef.setF(formula);
+	}
 
 	public XSSFChartLegend getOrCreateLegend() {
 		return new XSSFChartLegend(this);
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChart.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChart.java
index 1c8732402e..f033e8ed4e 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChart.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChart.java
@@ -49,13 +49,13 @@ public final class TestXSSFChart extends TestCase {
        
        // Check the titles
        XSSFChart chart = s2.createDrawingPatriarch().getCharts().get(0);
-       assertEquals(null, chart.getTitle());
+       assertEquals(null, chart.getTitleText());
        
        chart = s2.createDrawingPatriarch().getCharts().get(1);
-       assertEquals("Pie Chart Title Thingy", chart.getTitle().getString());
+       assertEquals("Pie Chart Title Thingy", chart.getTitleText().getString());
        
        chart = s3.createDrawingPatriarch().getCharts().get(0);
-       assertEquals("Sheet 3 Chart with Title", chart.getTitle().getString());
+       assertEquals("Sheet 3 Chart with Title", chart.getTitleText().getString());
        
        assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
     }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChartSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChartSheet.java
index 2eb4fc3e07..8cb76ac0fc 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChartSheet.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChartSheet.java
@@ -78,6 +78,6 @@ public final class TestXSSFChartSheet {
        assertEquals(1, cs.createDrawingPatriarch().getCharts().size());
        
        XSSFChart chart = cs.createDrawingPatriarch().getCharts().get(0);
-       assertNull(chart.getTitle());
+       assertNull(chart.getTitleText());
     }
 }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFChartTitle.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFChartTitle.java
index 08d1f179df..7282aa0f41 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFChartTitle.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFChartTitle.java
@@ -120,12 +120,20 @@ public class TestXSSFChartTitle {
         Workbook wb = createWorkbookWithChart();
         XSSFChart chart = getChartFromWorkbook(wb, "linechart");
         assertNotNull(chart);
-        assertNull(chart.getTitle());
+        assertNull(chart.getTitleText());
         final String myTitle = "My chart title";
-        chart.setTitle(myTitle);
-        XSSFRichTextString queryTitle = chart.getTitle();
+        chart.setTitleText(myTitle);
+        XSSFRichTextString queryTitle = chart.getTitleText();
         assertNotNull(queryTitle);
         assertEquals(myTitle, queryTitle.toString());
+        
+        final String myTitleFormula = "1 & \" and \" & 2";
+        chart.setTitleFormula(myTitleFormula);
+        // setting formula should unset text, but since there is a formula, returns an empty string
+        assertEquals("", chart.getTitleText().toString());
+        String titleFormula = chart.getTitleFormula();
+        assertNotNull(titleFormula);
+        assertEquals(myTitleFormula, titleFormula);
         wb.close();
     }
 
@@ -134,12 +142,12 @@ public class TestXSSFChartTitle {
         Workbook wb = XSSFTestDataSamples.openSampleWorkbook("chartTitle_withTitle.xlsx");
         XSSFChart chart = getChartFromWorkbook(wb, "Sheet1");
         assertNotNull(chart);
-        XSSFRichTextString originalTitle = chart.getTitle();
+        XSSFRichTextString originalTitle = chart.getTitleText();
         assertNotNull(originalTitle);
         final String myTitle = "My chart title";
         assertFalse(myTitle.equals(originalTitle.toString()));
-        chart.setTitle(myTitle);
-        XSSFRichTextString queryTitle = chart.getTitle();
+        chart.setTitleText(myTitle);
+        XSSFRichTextString queryTitle = chart.getTitleText();
         assertNotNull(queryTitle);
         assertEquals(myTitle, queryTitle.toString());
         wb.close();
@@ -150,12 +158,27 @@ public class TestXSSFChartTitle {
         Workbook wb = XSSFTestDataSamples.openSampleWorkbook("chartTitle_noTitle.xlsx");
         XSSFChart chart = getChartFromWorkbook(wb, "Sheet1");
         assertNotNull(chart);
-        assertNull(chart.getTitle());
+        assertNull(chart.getTitleText());
         final String myTitle = "My chart title";
-        chart.setTitle(myTitle);
-        XSSFRichTextString queryTitle = chart.getTitle();
+        chart.setTitleText(myTitle);
+        XSSFRichTextString queryTitle = chart.getTitleText();
         assertNotNull(queryTitle);
         assertEquals(myTitle, queryTitle.toString());
         wb.close();
     }
+
+    @Test
+    public void testExistingChartWithFormulaTitle() throws IOException {
+        Workbook wb = XSSFTestDataSamples.openSampleWorkbook("chartTitle_withTitleFormula.xlsx");
+        XSSFChart chart = getChartFromWorkbook(wb, "Sheet1");
+        assertNotNull(chart);
+        XSSFRichTextString originalTitle = chart.getTitleText();
+        assertNotNull(originalTitle);
+        assertEquals("", originalTitle.toString());
+        String formula = chart.getTitleFormula();
+        assertNotNull(formula);
+        assertEquals("Sheet1!$E$1", formula);
+        wb.close();
+    }
+
 }
diff --git a/test-data/spreadsheet/chartTitle_withTitleFormula.xlsx b/test-data/spreadsheet/chartTitle_withTitleFormula.xlsx
new file mode 100644
index 0000000000..0f609ca3b6
--- /dev/null
+++ b/test-data/spreadsheet/chartTitle_withTitleFormula.xlsx