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