sc/source/filter/oox/querytablebuffer.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include <querytablebuffer.hxx>
  21
  22 #include <com/sun/star/beans/XPropertySet.hpp>
  23 #include <com/sun/star/container/XEnumerationAccess.hpp>
  24 #include <com/sun/star/sheet/XAreaLink.hpp>
  25 #include <com/sun/star/sheet/XAreaLinks.hpp>
  26 #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
  27 #include <osl/diagnose.h>
  28 #include <oox/core/filterbase.hxx>
  29 #include <oox/helper/binaryinputstream.hxx>
  30 #include <oox/helper/attributelist.hxx>
  31 #include <oox/token/properties.hxx>
  32 #include <oox/token/tokens.hxx>
  33 #include <addressconverter.hxx>
  34 #include <biffhelper.hxx>
  35 #include <connectionsbuffer.hxx>
  36 #include <defnamesbuffer.hxx>
  37 #include <docuno.hxx>
  38
  39 namespace oox::xls {
  40
  41 using namespace ::com::sun::star::container;
  42 using namespace ::com::sun::star::sheet;
  43 using namespace ::com::sun::star::table;
  44 using namespace ::com::sun::star::uno;
  45
  46 namespace {
  47
  48 const sal_uInt32 BIFF12_QUERYTABLE_HEADERS          = 0x00000001;
  49 const sal_uInt32 BIFF12_QUERYTABLE_ROWNUMBERS       = 0x00000002;
  50 const sal_uInt32 BIFF12_QUERYTABLE_DISABLEREFRESH   = 0x00000004;
  51 const sal_uInt32 BIFF12_QUERYTABLE_BACKGROUND       = 0x00000008;
  52 const sal_uInt32 BIFF12_QUERYTABLE_FIRSTBACKGROUND  = 0x00000010;
  53 const sal_uInt32 BIFF12_QUERYTABLE_REFRESHONLOAD    = 0x00000020;
  54 const sal_uInt32 BIFF12_QUERYTABLE_FILLFORMULAS     = 0x00000100;
  55 const sal_uInt32 BIFF12_QUERYTABLE_SAVEDATA         = 0x00000200;
  56 const sal_uInt32 BIFF12_QUERYTABLE_DISABLEEDIT      = 0x00000400;
  57 const sal_uInt32 BIFF12_QUERYTABLE_PRESERVEFORMAT   = 0x00000800;
  58 const sal_uInt32 BIFF12_QUERYTABLE_ADJUSTCOLWIDTH   = 0x00001000;
  59 const sal_uInt32 BIFF12_QUERYTABLE_INTERMEDIATE     = 0x00002000;
  60 const sal_uInt32 BIFF12_QUERYTABLE_APPLYNUMFMT      = 0x00004000;
  61 const sal_uInt32 BIFF12_QUERYTABLE_APPLYFONT        = 0x00008000;
  62 const sal_uInt32 BIFF12_QUERYTABLE_APPLYALIGNMENT   = 0x00010000;
  63 const sal_uInt32 BIFF12_QUERYTABLE_APPLYBORDER      = 0x00020000;
  64 const sal_uInt32 BIFF12_QUERYTABLE_APPLYFILL        = 0x00040000;
  65 const sal_uInt32 BIFF12_QUERYTABLE_APPLYPROTECTION  = 0x00080000;
  66
  67 void lclAppendWebQueryTableName( OUStringBuffer& rTables, std::u16string_view rTableName )
  68 {
  69     if( !rTableName.empty() )
  70     {
  71         if( !rTables.isEmpty() )
  72             rTables.append( ';' );
  73         rTables.append( OUString::Concat("HTML__") + rTableName );
  74     }
  75 }
  76
  77 void lclAppendWebQueryTableIndex( OUStringBuffer& rTables, sal_Int32 nTableIndex )
  78 {
  79     if( nTableIndex > 0 )
  80     {
  81         if( !rTables.isEmpty() )
  82             rTables.append( ';' );
  83         rTables.append( "HTML_" + OUString::number( nTableIndex ) );
  84     }
  85 }
  86
  87 OUString lclBuildWebQueryTables( const WebPrModel::TablesVector& rTables )
  88 {
  89     if( rTables.empty() )
  90         return u"HTML_tables"_ustr;
  91
  92     OUStringBuffer aTables;
  93     for( const auto& rTable : rTables )
  94     {
  95         if( rTable.has< OUString >() )
  96             lclAppendWebQueryTableName( aTables, rTable.get< OUString >() );
  97         else if( rTable.has< sal_Int32 >() )
  98             lclAppendWebQueryTableIndex( aTables, rTable.get< sal_Int32 >() );
  99     }
 100     return aTables.makeStringAndClear();
 101 }
 102
 103 Reference< XAreaLink > lclFindAreaLink(
 104         const Reference< XAreaLinks >& rxAreaLinks, const ScAddress& rDestPos,
 105         std::u16string_view rFileUrl, std::u16string_view rTables, std::u16string_view rFilterName, std::u16string_view rFilterOptions )
 106 {
 107     try
 108     {
 109         Reference< XEnumerationAccess > xAreaLinksEA( rxAreaLinks, UNO_QUERY_THROW );
 110         Reference< XEnumeration > xAreaLinksEnum( xAreaLinksEA->createEnumeration(), UNO_SET_THROW );
 111         while( xAreaLinksEnum->hasMoreElements() )
 112         {
 113             Reference< XAreaLink > xAreaLink( xAreaLinksEnum->nextElement(), UNO_QUERY_THROW );
 114             PropertySet aPropSet( xAreaLink );
 115             CellRangeAddress aDestArea = xAreaLink->getDestArea();
 116             OUString aString;
 117             if( (rDestPos.Tab() == aDestArea.Sheet) && (rDestPos.Col() == aDestArea.StartColumn) && (rDestPos.Row() == aDestArea.StartRow) &&
 118                     (rTables == xAreaLink->getSourceArea()) &&
 119                     aPropSet.getProperty( aString, PROP_Url ) && (rFileUrl == aString) &&
 120                     aPropSet.getProperty( aString, PROP_Filter ) && (rFilterName == aString) &&
 121                     aPropSet.getProperty( aString, PROP_FilterOptions ) && (rFilterOptions == aString) )
 122                 return xAreaLink;
 123         }
 124     }
 125     catch( Exception& )
 126     {
 127     }
 128     return Reference< XAreaLink >();
 129 }
 130
 131 } // namespace
 132
 133 QueryTableModel::QueryTableModel() :
 134     mnConnId( -1 ),
 135     mnGrowShrinkType( XML_insertDelete ),
 136     mbHeaders( true ),
 137     mbRowNumbers( false ),
 138     mbDisableRefresh( false ),
 139     mbBackground( true ),
 140     mbFirstBackground( false ),
 141     mbRefreshOnLoad( false ),
 142     mbFillFormulas( false ),
 143     mbRemoveDataOnSave( false ),
 144     mbDisableEdit( false ),
 145     mbPreserveFormat( true ),
 146     mbAdjustColWidth( true ),
 147     mbIntermediate( false )
 148 {
 149 }
 150
 151 QueryTable::QueryTable( const WorksheetHelper& rHelper ) :
 152     WorksheetHelper( rHelper )
 153 {
 154 }
 155
 156 void QueryTable::importQueryTable( const AttributeList& rAttribs )
 157 {
 158     maModel.maDefName          = rAttribs.getXString( XML_name, OUString() );
 159     maModel.mnConnId           = rAttribs.getInteger( XML_connectionId, -1 );
 160     maModel.mnGrowShrinkType   = rAttribs.getToken( XML_growShrinkType, XML_insertDelete );
 161     maModel.mnAutoFormatId     = rAttribs.getInteger( XML_autoFormatId, 0 );
 162     maModel.mbHeaders          = rAttribs.getBool( XML_headers, true );
 163     maModel.mbRowNumbers       = rAttribs.getBool( XML_rowNumbers, false );
 164     maModel.mbDisableRefresh   = rAttribs.getBool( XML_disableRefresh, false );
 165     maModel.mbBackground       = rAttribs.getBool( XML_backgroundRefresh, true );
 166     maModel.mbFirstBackground  = rAttribs.getBool( XML_firstBackgroundRefresh, false );
 167     maModel.mbRefreshOnLoad    = rAttribs.getBool( XML_refreshOnLoad, false );
 168     maModel.mbFillFormulas     = rAttribs.getBool( XML_fillFormulas, false );
 169     maModel.mbRemoveDataOnSave = rAttribs.getBool( XML_removeDataOnSave, false );
 170     maModel.mbDisableEdit      = rAttribs.getBool( XML_disableEdit, false );
 171     maModel.mbPreserveFormat   = rAttribs.getBool( XML_preserveFormatting, true );
 172     maModel.mbAdjustColWidth   = rAttribs.getBool( XML_adjustColumnWidth, true );
 173     maModel.mbIntermediate     = rAttribs.getBool( XML_intermediate, false );
 174     maModel.mbApplyNumFmt      = rAttribs.getBool( XML_applyNumberFormats, false );
 175     maModel.mbApplyFont        = rAttribs.getBool( XML_applyFontFormats, false );
 176     maModel.mbApplyAlignment   = rAttribs.getBool( XML_applyAlignmentFormats, false );
 177     maModel.mbApplyBorder      = rAttribs.getBool( XML_applyBorderFormats, false );
 178     maModel.mbApplyFill        = rAttribs.getBool( XML_applyPatternFormats, false );
 179     // OOXML and BIFF12 documentation differ: OOXML mentions width/height, BIFF12 mentions protection
 180     maModel.mbApplyProtection  = rAttribs.getBool( XML_applyWidthHeightFormats, false );
 181 }
 182
 183 void QueryTable::importQueryTable( SequenceInputStream& rStrm )
 184 {
 185     sal_uInt32 nFlags;
 186     nFlags = rStrm.readuInt32();
 187     maModel.mnAutoFormatId = rStrm.readuInt16();
 188     maModel.mnConnId = rStrm.readInt32();
 189     rStrm >> maModel.maDefName;
 190
 191     static const sal_Int32 spnGrowShrinkTypes[] = { XML_insertClear, XML_insertDelete, XML_overwriteClear };
 192     maModel.mnGrowShrinkType = STATIC_ARRAY_SELECT( spnGrowShrinkTypes, extractValue< sal_uInt8 >( nFlags, 6, 2 ), XML_insertDelete );
 193
 194     maModel.mbHeaders           = getFlag( nFlags, BIFF12_QUERYTABLE_HEADERS );
 195     maModel.mbRowNumbers        = getFlag( nFlags, BIFF12_QUERYTABLE_ROWNUMBERS );
 196     maModel.mbDisableRefresh    = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEREFRESH );
 197     maModel.mbBackground        = getFlag( nFlags, BIFF12_QUERYTABLE_BACKGROUND );
 198     maModel.mbFirstBackground   = getFlag( nFlags, BIFF12_QUERYTABLE_FIRSTBACKGROUND );
 199     maModel.mbRefreshOnLoad     = getFlag( nFlags, BIFF12_QUERYTABLE_REFRESHONLOAD );
 200     maModel.mbFillFormulas      = getFlag( nFlags, BIFF12_QUERYTABLE_FILLFORMULAS );
 201     maModel.mbRemoveDataOnSave  = !getFlag( nFlags, BIFF12_QUERYTABLE_SAVEDATA ); // flag negated in BIFF12
 202     maModel.mbDisableEdit       = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEEDIT );
 203     maModel.mbPreserveFormat    = getFlag( nFlags, BIFF12_QUERYTABLE_PRESERVEFORMAT );
 204     maModel.mbAdjustColWidth    = getFlag( nFlags, BIFF12_QUERYTABLE_ADJUSTCOLWIDTH );
 205     maModel.mbIntermediate      = getFlag( nFlags, BIFF12_QUERYTABLE_INTERMEDIATE );
 206     maModel.mbApplyNumFmt       = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYNUMFMT );
 207     maModel.mbApplyFont         = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFONT );
 208     maModel.mbApplyAlignment    = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYALIGNMENT );
 209     maModel.mbApplyBorder       = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYBORDER );
 210     maModel.mbApplyFill         = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFILL );
 211     maModel.mbApplyProtection   = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYPROTECTION );
 212 }
 213
 214 void QueryTable::finalizeImport()
 215 {
 216     ConnectionRef xConnection = getConnections().getConnection( maModel.mnConnId );
 217     OSL_ENSURE( xConnection, "QueryTable::finalizeImport - missing connection object" );
 218     if( !(xConnection && (xConnection->getConnectionType() == BIFF12_CONNECTION_HTML)) )
 219         return;
 220
 221     // check that valid web query properties exist
 222     const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get();
 223     if( !pWebPr || pWebPr->mbXml )
 224         return;
 225
 226     OUString aFileUrl = getBaseFilter().getAbsoluteUrl( pWebPr->maUrl );
 227     if( aFileUrl.isEmpty() )
 228         return;
 229
 230     // resolve destination cell range (stored as defined name containing the range)
 231     OUString aDefName = maModel.maDefName.replace( ' ', '_' ).replace( '-', '_' );
 232     DefinedNameRef xDefName = getDefinedNames().getByModelName( aDefName, getSheetIndex() );
 233     OSL_ENSURE( xDefName, "QueryTable::finalizeImport - missing defined name" );
 234     if( !xDefName )
 235         return;
 236
 237     ScRange aDestRange;
 238     bool bIsRange = xDefName->getAbsoluteRange( aDestRange ) && (aDestRange.aStart.Tab() == getSheetIndex());
 239     OSL_ENSURE( bIsRange, "QueryTable::finalizeImport - defined name does not contain valid cell range" );
 240     if( !(bIsRange && getAddressConverter().checkCellRange( aDestRange, false, true )) )
 241         return;
 242
 243     // find tables mode: entire document, all tables, or specific tables
 244     OUString aTables = pWebPr->mbHtmlTables ? lclBuildWebQueryTables( pWebPr->maTables ) : u"HTML_all"_ustr;
 245     if( aTables.isEmpty() )
 246         return;
 247
 248     try
 249     {
 250         PropertySet aDocProps(( Reference< css::beans::XPropertySet >(getDocument()) ));
 251         Reference< XAreaLinks > xAreaLinks( aDocProps.getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW );
 252         CellAddress aDestPos( aDestRange.aStart.Tab(), aDestRange.aStart.Col(), aDestRange.aStart.Row() );
 253         static constexpr OUString aFilterName = u"calc_HTML_WebQuery"_ustr;
 254         xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName, /*aFilterOptions*/u""_ustr );
 255         // set refresh interval (convert minutes to seconds)
 256         sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60;
 257         if( nRefreshPeriod > 0 )
 258         {
 259             PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestRange.aStart, aFileUrl, aTables, aFilterName, /*aFilterOptions*/u"" ) );
 260             aPropSet.setProperty( PROP_RefreshPeriod, nRefreshPeriod );
 261         }
 262     }
 263     catch( Exception& )
 264     {
 265     }
 266 }
 267
 268 QueryTableBuffer::QueryTableBuffer( const WorksheetHelper& rHelper ) :
 269     WorksheetHelper( rHelper )
 270 {
 271 }
 272
 273 QueryTable& QueryTableBuffer::createQueryTable()
 274 {
 275     QueryTableVector::value_type xQueryTable = std::make_shared<QueryTable>( *this );
 276     maQueryTables.push_back( xQueryTable );
 277     return *xQueryTable;
 278 }
 279
 280 void QueryTableBuffer::finalizeImport()
 281 {
 282     maQueryTables.forEachMem( &QueryTable::finalizeImport );
 283 }
 284
 285 } // namespace oox::xls
 286
 287 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */