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