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 <osl/diagnose.h>
  27 #include <oox/core/filterbase.hxx>
  28 #include <oox/helper/binaryinputstream.hxx>
  29 #include <oox/helper/attributelist.hxx>
  30 #include <oox/token/properties.hxx>
  31 #include <oox/token/tokens.hxx>
  32 #include <addressconverter.hxx>
  33 #include <biffhelper.hxx>
  34 #include <connectionsbuffer.hxx>
  35 #include <defnamesbuffer.hxx>
  36
  37 namespace oox {
  38 namespace 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.get(), "QueryTable::finalizeImport - missing connection object" );
 217     if( xConnection.get() && (xConnection->getConnectionType() == BIFF12_CONNECTION_HTML) )
 218     {
 219         // check that valid web query properties exist
 220         const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get();
 221         if( pWebPr && !pWebPr->mbXml )
 222         {
 223             OUString aFileUrl = getBaseFilter().getAbsoluteUrl( pWebPr->maUrl );
 224             if( !aFileUrl.isEmpty() )
 225             {
 226                 // resolve destination cell range (stored as defined name containing the range)
 227                 OUString aDefName = maModel.maDefName.replace( ' ', '_' ).replace( '-', '_' );
 228                 DefinedNameRef xDefName = getDefinedNames().getByModelName( aDefName, getSheetIndex() );
 229                 OSL_ENSURE( xDefName.get(), "QueryTable::finalizeImport - missing defined name" );
 230                 if( xDefName.get() )
 231                 {
 232                     ScRange aDestRange;
 233                     bool bIsRange = xDefName->getAbsoluteRange( aDestRange ) && (aDestRange.aStart.Tab() == getSheetIndex());
 234                     OSL_ENSURE( bIsRange, "QueryTable::finalizeImport - defined name does not contain valid cell range" );
 235                     if( bIsRange && getAddressConverter().checkCellRange( aDestRange, false, true ) )
 236                     {
 237                         // find tables mode: entire document, all tables, or specific tables
 238                         OUString aTables = pWebPr->mbHtmlTables ? lclBuildWebQueryTables( pWebPr->maTables ) : "HTML_all";
 239                         if( !aTables.isEmpty() ) try
 240                         {
 241                             PropertySet aDocProps( getDocument() );
 242                             Reference< XAreaLinks > xAreaLinks( aDocProps.getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW );
 243                             CellAddress aDestPos( aDestRange.aStart.Tab(), aDestRange.aStart.Col(), aDestRange.aStart.Row() );
 244                             OUString aFilterName = "calc_HTML_WebQuery";
 245                             OUString aFilterOptions;
 246                             xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName, aFilterOptions );
 247                             // set refresh interval (convert minutes to seconds)
 248                             sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60;
 249                             if( nRefreshPeriod > 0 )
 250                             {
 251                                 PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestRange.aStart, aFileUrl, aTables, aFilterName, aFilterOptions ) );
 252                                 aPropSet.setProperty( PROP_RefreshPeriod, nRefreshPeriod );
 253                             }
 254                         }
 255                         catch( Exception& )
 256                         {
 257                         }
 258                     }
 259                 }
 260             }
 261         }
 262     }
 263 }
 264
 265 QueryTableBuffer::QueryTableBuffer( const WorksheetHelper& rHelper ) :
 266     WorksheetHelper( rHelper )
 267 {
 268 }
 269
 270 QueryTable& QueryTableBuffer::createQueryTable()
 271 {
 272     QueryTableVector::value_type xQueryTable( new QueryTable( *this ) );
 273     maQueryTables.push_back( xQueryTable );
 274     return *xQueryTable;
 275 }
 276
 277 void QueryTableBuffer::finalizeImport()
 278 {
 279     maQueryTables.forEachMem( &QueryTable::finalizeImport );
 280 }
 281
 282 } // namespace xls
 283 } // namespace oox
 284
 285 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */