tdf#115012 XLSX chart import: workaround for no gap

DispBlanksAs=gap mode of OOXML is different from
treat-empty-cells=leave-gap mode of OpenDocument,
because formulas with no numerical values and
strings are no gaps in OOXML line charts, but zeroes.

When the data source of the line charts contains
formulas with no numerical values or strings,
but it doesn't contain empty cells, as a workaround,
the charts will be imported with the
treat-empty-cells=use-zero setting to get the same
line chart as in MSO.

Note: now result of ScChart2DataSequence::getData(),
a sequence of Any values contains UNO void values for
empty cells instead empty strings, allowing the
distinction of the empty cells and cells with empty
string values.

Change-Id: If9a101d66b5b750051928fa7b10b05cea6040071
Reviewed-on: https://gerrit.libreoffice.org/69054
Tested-by: Jenkins
Reviewed-by: László Németh <nemeth@numbertext.org>

4 files changed, 104 insertions, 0 deletions

diff --git a/chart2/qa/extras/chart2export.cxx b/chart2/qa/extras/chart2export.cxx
index 8671a4771e21..f4d8f5e981a9 100644
--- a/chart2/qa/extras/chart2export.cxx
+++ b/chart2/qa/extras/chart2export.cxx
@@ -128,6 +128,7 @@ public:
     void testTdf108022();
     void testTdf121744();
     void testTdf122031();
+    void testTdf115012();
 
     CPPUNIT_TEST_SUITE(Chart2ExportTest);
     CPPUNIT_TEST(testErrorBarXLSX);
@@ -219,6 +220,7 @@ public:
     CPPUNIT_TEST(testTdf108022);
     CPPUNIT_TEST(testTdf121744);
     CPPUNIT_TEST(testTdf122031);
+    CPPUNIT_TEST(testTdf115012);
     CPPUNIT_TEST_SUITE_END();
 
 protected:
@@ -2046,6 +2048,15 @@ void Chart2ExportTest::testTdf122031()
     assertXPath(pXmlDoc, "/c:chartSpace/c:chart/c:plotArea/c:pieChart/c:ser/c:dLbls/c:dLbl[3]/c:numFmt", "formatCode", "0.000%");
 }
 
+void Chart2ExportTest::testTdf115012()
+{
+    load("/chart2/qa/extras/data/xlsx/", "tdf115012.xlsx");
+    xmlDocPtr pXmlDoc = parseExport("xl/charts/chart","Calc Office Open XML");
+    CPPUNIT_ASSERT(pXmlDoc);
+    // workaround: use-zero instead of leave-gap to show the original line chart
+    assertXPath(pXmlDoc, "/c:chartSpace/c:chart/c:dispBlanksAs", "val", "zero");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(Chart2ExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/chart2/qa/extras/data/xlsx/tdf115012.xlsx b/chart2/qa/extras/data/xlsx/tdf115012.xlsx
new file mode 100644
index 000000000000..cf8ac7d81eac
--- /dev/null
+++ b/chart2/qa/extras/data/xlsx/tdf115012.xlsx
diff --git a/oox/source/drawingml/chart/chartspaceconverter.cxx b/oox/source/drawingml/chart/chartspaceconverter.cxx
index 6ddbb2ab76bb..2e2c8bc3885b 100644
--- a/oox/source/drawingml/chart/chartspaceconverter.cxx
+++ b/oox/source/drawingml/chart/chartspaceconverter.cxx
@@ -22,6 +22,10 @@
 #include <com/sun/star/chart/MissingValueTreatment.hpp>
 #include <com/sun/star/chart/XChartDocument.hpp>
 #include <com/sun/star/chart2/XChartDocument.hpp>
+#include <com/sun/star/chart2/XChartType.hpp>
+#include <com/sun/star/chart2/XChartTypeContainer.hpp>
+#include <com/sun/star/chart2/XCoordinateSystemContainer.hpp>
+#include <com/sun/star/chart2/XDataSeriesContainer.hpp>
 #include <com/sun/star/chart2/XTitled.hpp>
 #include <com/sun/star/drawing/XDrawPageSupplier.hpp>
 #include <com/sun/star/drawing/FillStyle.hpp>
@@ -67,6 +71,82 @@ ChartSpaceConverter::~ChartSpaceConverter()
 {
 }
 
+// Formulas with no numeric values and strings are zeroes in OOXML line charts also in the mode DispBlanksAs=gap,
+// unlike in OpenDocument LEAVE_GAP mode. As a workaround, we will use the OpenDocument mode USE_ZERO, if the OOXML
+// line chart has got formulas with no numeric values and strings, but it doesn't have empty cells, showing the
+// same chart as in MSO. (Empty cells need gaps, so in that case, we cannot use this workaround).
+static bool lcl_useWorkaroundForNoGapInOOXML( Reference< chart2::XChartDocument > const & xChartDoc)
+{
+    Reference <chart2::XDiagram > xDiagram = xChartDoc->getFirstDiagram();
+    if ( !xDiagram.is() )
+        return false;
+
+    Reference< chart2::XCoordinateSystemContainer > xCooSysContainer( xDiagram, UNO_QUERY_THROW );
+
+    Sequence< Reference< chart2::XCoordinateSystem > > xCooSysSequence( xCooSysContainer->getCoordinateSystems());
+    if ( xCooSysSequence.getLength() == 0 )
+        return false;
+
+    Reference< chart2::XChartTypeContainer > xChartTypeContainer( xCooSysSequence[0], UNO_QUERY_THROW );
+
+    Sequence< Reference< chart2::XChartType > > xChartTypeSequence( xChartTypeContainer->getChartTypes() );
+    if ( xChartTypeSequence.getLength() == 0 )
+        return false;
+
+    const Reference<chart2::XChartType>& xCT = xChartTypeSequence[0];
+
+    if ( xCT->getChartType() != "com.sun.star.chart2.LineChartType" )
+        return false;
+
+    Reference<chart2::XDataSeriesContainer> xDSCont(xCT, uno::UNO_QUERY);
+
+    if (!xDSCont.is())
+        return false;
+
+    Sequence<uno::Reference<chart2::XDataSeries> > aDataSeriesSeq = xDSCont->getDataSeries();
+
+    bool bHasNoGapBlankValue = false;
+    bool bHasEmptyCell = false;
+
+    for (sal_Int32 i = 0; i < aDataSeriesSeq.getLength(); ++i)
+    {
+        uno::Reference<chart2::data::XDataSource> xDSrc(aDataSeriesSeq[i], uno::UNO_QUERY);
+        if (!xDSrc.is())
+            return false;
+
+        uno::Sequence<Reference<chart2::data::XLabeledDataSequence> > aDataSeqs = xDSrc->getDataSequences();
+        for (sal_Int32 j = 0; j < aDataSeqs.getLength(); ++j)
+        {
+            Reference<chart2::data::XDataSequence> xValues = aDataSeqs[j]->getValues();
+            if(!xValues.is())
+                return false;
+            Reference<beans::XPropertySet> xPropSet(xValues, uno::UNO_QUERY);
+            if (!xPropSet.is())
+                continue;
+
+            OUString aRoleName;
+            xPropSet->getPropertyValue("Role") >>= aRoleName;
+            if (aRoleName == "values-y")
+            {
+                uno::Sequence<uno::Any> aData = xValues->getData();
+                for (sal_Int32 nVal = 0; nVal < aData.getLength(); ++nVal)
+                {
+                    double fVal;
+                    OUString sStr;
+                    if (aData[nVal] >>= fVal)
+                        continue;
+                    else if (aData[nVal] >>= sStr)
+                        bHasNoGapBlankValue = true;
+                    else
+                        bHasEmptyCell = true;
+                }
+            }
+        }
+    }
+
+    return bHasNoGapBlankValue && !bHasEmptyCell;
+}
+
 void ChartSpaceConverter::convertFromModel( const Reference< XShapes >& rxExternalPage, const awt::Point& rChartPos )
 {
     /*  create data provider (virtual function in the ChartConverter class,
@@ -132,6 +212,11 @@ void ChartSpaceConverter::convertFromModel( const Reference< XShapes >& rxExtern
             case XML_zero:  nMissingValues = USE_ZERO;  break;
             case XML_span:  nMissingValues = CONTINUE;  break;
         }
+
+        // use a workaround, if it's possible for the difference of OOXML and OpenDocument
+        if ( nMissingValues == LEAVE_GAP && lcl_useWorkaroundForNoGapInOOXML(getChartDocument()) )
+            nMissingValues = USE_ZERO;
+
         PropertySet aDiaProp( xDiagram );
         aDiaProp.setProperty( PROP_MissingValueTreatment, nMissingValues );
     }
diff --git a/sc/source/ui/unoobj/chart2uno.cxx b/sc/source/ui/unoobj/chart2uno.cxx
index cf4d62f96fc7..e859750f4fa1 100644
--- a/sc/source/ui/unoobj/chart2uno.cxx
+++ b/sc/source/ui/unoobj/chart2uno.cxx
@@ -2894,6 +2894,14 @@ uno::Sequence< uno::Any> SAL_CALL ScChart2DataSequence::getData()
         {
             if (rItem.mbIsValue)
                 *pArr <<= rItem.mfValue;
+            else if (rItem.maString.isEmpty())
+            {
+                ScRefCellValue aCell(*m_pDocument, rItem.mAddress);
+                if (aCell.isEmpty())
+                   *pArr = uno::Any();
+                else
+                   *pArr <<= rItem.maString;
+            }
             else
                 *pArr <<= rItem.maString;
             ++pArr;