tdf#123341 XLSX: fix vertical alignment in comments

Import and export of VML element TextVAlign weren't
supported by Calc, losing vertical aligment of the
comments assigned to the spreadsheet cells.

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

4 files changed, 46 insertions, 0 deletions

diff --git a/sc/qa/unit/data/ods/CommentTextVAlign.ods b/sc/qa/unit/data/ods/CommentTextVAlign.ods
new file mode 100755
index 000000000000..a1cac792cbac
--- /dev/null
+++ b/sc/qa/unit/data/ods/CommentTextVAlign.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 30cb91116875..eaadde60f558 100755
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -222,6 +222,7 @@ public:
     void testTdf79972XLSX();
     void testTdf126024XLSX();
     void testTdf126177XLSX();
+    void testCommentTextVAlignment();
 
     void testXltxExport();
 
@@ -349,6 +350,7 @@ public:
     CPPUNIT_TEST(testTdf79972XLSX);
     CPPUNIT_TEST(testTdf126024XLSX);
     CPPUNIT_TEST(testTdf126177XLSX);
+    CPPUNIT_TEST(testCommentTextVAlignment);
 
     CPPUNIT_TEST(testXltxExport);
 
@@ -4486,6 +4488,22 @@ void ScExportTest::testTdf126177XLSX()
     assertXPath(pXmlRels, "/r:Relationships/r:Relationship", "TargetMode", "External");
 }
 
+void ScExportTest::testCommentTextVAlignment()
+{
+    // Testing comment text alignments.
+    ScDocShellRef xShell = loadDoc("CommentTextVAlign.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xShell.is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile
+        = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
+
+    const xmlDocPtr pVmlDrawing
+        = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/drawings/vmlDrawing1.vml");
+    CPPUNIT_ASSERT(pVmlDrawing);
+
+    assertXPathContent(pVmlDrawing, "/xml/v:shape/xx:ClientData/xx:TextVAlign", "Center");
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/oox/commentsbuffer.cxx b/sc/source/filter/oox/commentsbuffer.cxx
index aab70b07ae73..00045842c9fd 100644..100755
--- a/sc/source/filter/oox/commentsbuffer.cxx
+++ b/sc/source/filter/oox/commentsbuffer.cxx
@@ -64,10 +64,13 @@ static sal_Int32 lcl_ToVertAlign( sal_Int32 nAlign )
     switch( nAlign )
     {
         case XML_top:
+        case XML_Top:
             return SDRTEXTVERTADJUST_TOP;
         case XML_center:
+        case XML_Center:
             return SDRTEXTVERTADJUST_CENTER;
         case XML_bottom:
+        case XML_Bottom:
             return SDRTEXTVERTADJUST_BOTTOM;
         default:
             return SDRTEXTVERTADJUST_BLOCK;
@@ -164,6 +167,10 @@ void Comment::finalizeImport()
             pNoteShape->convertFormatting( xAnnoShape );
             // visibility
             bVisible = pNoteShape->getTypeModel().mbVisible;
+
+            // Setting comment text alignment
+            const ::oox::vml::ClientData* xClientData = pNoteShape->getClientData();
+            aCommentPr.setProperty(PROP_TextVerticalAdjust, lcl_ToVertAlign(xClientData->mnTextVAlign));
         }
         xAnno->setIsVisible( bVisible );
 
diff --git a/sc/source/filter/xcl97/xcl97rec.cxx b/sc/source/filter/xcl97/xcl97rec.cxx
index e2b8593c88f0..f750fdb9ad6c 100644..100755
--- a/sc/source/filter/xcl97/xcl97rec.cxx
+++ b/sc/source/filter/xcl97/xcl97rec.cxx
@@ -624,6 +624,22 @@ sal_Int32 VmlCommentExporter::StartShape()
     return nId;
 }
 
+static const char* lcl_GetVertAlignFromItemSetChar( const SfxItemSet& rItemSet )
+{
+    switch( rItemSet.Get( SDRATTR_TEXT_VERTADJUST ).GetValue() )
+    {
+        case SDRTEXTVERTADJUST_CENTER:
+            return "Center";
+        case SDRTEXTVERTADJUST_BOTTOM:
+            return "Bottom";
+        case SDRTEXTVERTADJUST_BLOCK:
+            return "Justify";
+        case SDRTEXTVERTADJUST_TOP:
+        default:
+            return "Top";
+    }
+}
+
 void VmlCommentExporter::EndShape( sal_Int32 nShapeElement )
 {
     char pAnchor[100];
@@ -632,11 +648,15 @@ void VmlCommentExporter::EndShape( sal_Int32 nShapeElement )
                   maFrom.Left(), maFrom.Top(), maFrom.Right(), maFrom.Bottom(),
                   maTo.Left(), maTo.Top(), maTo.Right(), maTo.Bottom() );
 
+    // Getting comment text alignments
+    const char* pVertAlign = lcl_GetVertAlignFromItemSetChar(mpCaption->GetMergedItemSet());
+
     pVmlDrawing->startElement(FSNS(XML_x, XML_ClientData), XML_ObjectType, "Note");
     pVmlDrawing->singleElement(FSNS(XML_x, XML_MoveWithCells));
     pVmlDrawing->singleElement(FSNS(XML_x, XML_SizeWithCells));
     XclXmlUtils::WriteElement( pVmlDrawing, FSNS( XML_x, XML_Anchor ), pAnchor );
     XclXmlUtils::WriteElement( pVmlDrawing, FSNS( XML_x, XML_AutoFill ), "False" );
+    XclXmlUtils::WriteElement( pVmlDrawing, FSNS( XML_x, XML_TextVAlign ), pVertAlign );
     XclXmlUtils::WriteElement( pVmlDrawing, FSNS( XML_x, XML_Row ), maScPos.Row() );
     XclXmlUtils::WriteElement( pVmlDrawing, FSNS(XML_x, XML_Column), sal_Int32(maScPos.Col()));
     if(mbVisible)
@@ -731,6 +751,7 @@ static sal_uInt8 lcl_GetVerAlignFromItemSet( const SfxItemSet& rItemSet )
         case SDRTEXTVERTADJUST_CENTER:  nVerAlign = EXC_OBJ_VER_CENTER;    break;
         case SDRTEXTVERTADJUST_BOTTOM:  nVerAlign = EXC_OBJ_VER_BOTTOM;    break;
         case SDRTEXTVERTADJUST_BLOCK:   nVerAlign = EXC_OBJ_VER_JUSTIFY;   break;
+        default:;
     }
     return nVerAlign;
 }