xref: /aoo41x/main/oox/source/xls/querytablebuffer.cxx (revision ca5ec200)

/**************************************************************
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *************************************************************/



#include "oox/xls/querytablebuffer.hxx"

#include <com/sun/star/container/XEnumerationAccess.hpp>
#include <com/sun/star/sheet/XAreaLink.hpp>
#include <com/sun/star/sheet/XAreaLinks.hpp>
#include "oox/core/filterbase.hxx"
#include "oox/helper/attributelist.hxx"
#include "oox/xls/addressconverter.hxx"
#include "oox/xls/biffinputstream.hxx"
#include "oox/xls/connectionsbuffer.hxx"
#include "oox/xls/defnamesbuffer.hxx"

namespace oox {
namespace xls {

// ============================================================================

using namespace ::com::sun::star::container;
using namespace ::com::sun::star::sheet;
using namespace ::com::sun::star::table;
using namespace ::com::sun::star::uno;

using ::rtl::OUString;
using ::rtl::OUStringBuffer;

// ============================================================================

namespace {

const sal_uInt32 BIFF12_QUERYTABLE_HEADERS          = 0x00000001;
const sal_uInt32 BIFF12_QUERYTABLE_ROWNUMBERS       = 0x00000002;
const sal_uInt32 BIFF12_QUERYTABLE_DISABLEREFRESH   = 0x00000004;
const sal_uInt32 BIFF12_QUERYTABLE_BACKGROUND       = 0x00000008;
const sal_uInt32 BIFF12_QUERYTABLE_FIRSTBACKGROUND  = 0x00000010;
const sal_uInt32 BIFF12_QUERYTABLE_REFRESHONLOAD    = 0x00000020;
const sal_uInt32 BIFF12_QUERYTABLE_FILLFORMULAS     = 0x00000100;
const sal_uInt32 BIFF12_QUERYTABLE_SAVEDATA         = 0x00000200;
const sal_uInt32 BIFF12_QUERYTABLE_DISABLEEDIT      = 0x00000400;
const sal_uInt32 BIFF12_QUERYTABLE_PRESERVEFORMAT   = 0x00000800;
const sal_uInt32 BIFF12_QUERYTABLE_ADJUSTCOLWIDTH   = 0x00001000;
const sal_uInt32 BIFF12_QUERYTABLE_INTERMEDIATE     = 0x00002000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYNUMFMT      = 0x00004000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYFONT        = 0x00008000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYALIGNMENT   = 0x00010000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYBORDER      = 0x00020000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYFILL        = 0x00040000;
const sal_uInt32 BIFF12_QUERYTABLE_APPLYPROTECTION  = 0x00080000;

const sal_uInt16 BIFF_QUERYTABLE_HEADERS            = 0x0001;
const sal_uInt16 BIFF_QUERYTABLE_ROWNUMBERS         = 0x0002;
const sal_uInt16 BIFF_QUERYTABLE_DISABLEREFRESH     = 0x0004;
const sal_uInt16 BIFF_QUERYTABLE_BACKGROUND         = 0x0008;
const sal_uInt16 BIFF_QUERYTABLE_FIRSTBACKGROUND    = 0x0010;
const sal_uInt16 BIFF_QUERYTABLE_REFRESHONLOAD      = 0x0020;
const sal_uInt16 BIFF_QUERYTABLE_DELETEUNUSED       = 0x0040;
const sal_uInt16 BIFF_QUERYTABLE_FILLFORMULAS       = 0x0080;
const sal_uInt16 BIFF_QUERYTABLE_ADJUSTCOLWIDTH     = 0x0100;
const sal_uInt16 BIFF_QUERYTABLE_SAVEDATA           = 0x0200;
const sal_uInt16 BIFF_QUERYTABLE_DISABLEEDIT        = 0x0400;
const sal_uInt16 BIFF_QUERYTABLE_OVERWRITEEXISTING  = 0x2000;

const sal_uInt16 BIFF_QUERYTABLE_APPLYNUMFMT        = 0x0001;
const sal_uInt16 BIFF_QUERYTABLE_APPLYFONT          = 0x0002;
const sal_uInt16 BIFF_QUERYTABLE_APPLYALIGNMENT     = 0x0004;
const sal_uInt16 BIFF_QUERYTABLE_APPLYBORDER        = 0x0008;
const sal_uInt16 BIFF_QUERYTABLE_APPLYFILL          = 0x0010;
const sal_uInt16 BIFF_QUERYTABLE_APPLYPROTECTION    = 0x0020;

const sal_uInt32 BIFF_QTREFRESH_PRESERVEFORMAT      = 0x00000001;
const sal_uInt32 BIFF_QTREFRESH_ADJUSTCOLWIDTH      = 0x00000002;

// ----------------------------------------------------------------------------

void lclAppendWebQueryTableName( OUStringBuffer& rTables, const OUString& rTableName )
{
    if( rTableName.getLength() > 0 )
    {
        if( rTables.getLength() > 0 )
            rTables.append( sal_Unicode( ';' ) );
        rTables.appendAscii( RTL_CONSTASCII_STRINGPARAM( "HTML__" ) ).append( rTableName );
    }
}

void lclAppendWebQueryTableIndex( OUStringBuffer& rTables, sal_Int32 nTableIndex )
{
    if( nTableIndex > 0 )
    {
        if( rTables.getLength() > 0 )
            rTables.append( sal_Unicode( ';' ) );
        rTables.appendAscii( RTL_CONSTASCII_STRINGPARAM( "HTML_" ) ).append( nTableIndex );
    }
}

OUString lclBuildWebQueryTables( const WebPrModel::TablesVector& rTables )
{
    if( rTables.empty() )
        return CREATE_OUSTRING( "HTML_tables" );

    OUStringBuffer aTables;
    for( WebPrModel::TablesVector::const_iterator aIt = rTables.begin(), aEnd = rTables.end(); aIt != aEnd; ++aIt )
    {
        if( aIt->has< OUString >() )
            lclAppendWebQueryTableName( aTables, aIt->get< OUString >() );
        else if( aIt->has< sal_Int32 >() )
            lclAppendWebQueryTableIndex( aTables, aIt->get< sal_Int32 >() );
    }
    return aTables.makeStringAndClear();
}

Reference< XAreaLink > lclFindAreaLink(
        const Reference< XAreaLinks >& rxAreaLinks, const CellAddress& rDestPos,
        const OUString& rFileUrl, const OUString& rTables, const OUString& rFilterName, const OUString& rFilterOptions )
{
    try
    {
        Reference< XEnumerationAccess > xAreaLinksEA( rxAreaLinks, UNO_QUERY_THROW );
        Reference< XEnumeration > xAreaLinksEnum( xAreaLinksEA->createEnumeration(), UNO_SET_THROW );
        while( xAreaLinksEnum->hasMoreElements() )
        {
            Reference< XAreaLink > xAreaLink( xAreaLinksEnum->nextElement(), UNO_QUERY_THROW );
            PropertySet aPropSet( xAreaLink );
            CellRangeAddress aDestArea = xAreaLink->getDestArea();
            OUString aString;
            if( (rDestPos.Sheet == aDestArea.Sheet) && (rDestPos.Column == aDestArea.StartColumn) && (rDestPos.Row == aDestArea.StartRow) &&
                    (rTables == xAreaLink->getSourceArea()) &&
                    aPropSet.getProperty( aString, PROP_Url ) && (rFileUrl == aString) &&
                    aPropSet.getProperty( aString, PROP_Filter ) && (rFilterName == aString) &&
                    aPropSet.getProperty( aString, PROP_FilterOptions ) && (rFilterOptions == aString) )
                return xAreaLink;
        }
    }
    catch( Exception& )
    {
    }
    return Reference< XAreaLink >();
}

} // namespace

// ============================================================================

QueryTableModel::QueryTableModel() :
    mnConnId( -1 ),
    mnGrowShrinkType( XML_insertDelete ),
    mbHeaders( true ),
    mbRowNumbers( false ),
    mbDisableRefresh( false ),
    mbBackground( true ),
    mbFirstBackground( false ),
    mbRefreshOnLoad( false ),
    mbFillFormulas( false ),
    mbRemoveDataOnSave( false ),
    mbDisableEdit( false ),
    mbPreserveFormat( true ),
    mbAdjustColWidth( true ),
    mbIntermediate( false )
{
}

// ----------------------------------------------------------------------------

QueryTable::QueryTable( const WorksheetHelper& rHelper ) :
    WorksheetHelper( rHelper )
{
}

void QueryTable::importQueryTable( const AttributeList& rAttribs )
{
    maModel.maDefName          = rAttribs.getXString( XML_name, OUString() );
    maModel.mnConnId           = rAttribs.getInteger( XML_connectionId, -1 );
    maModel.mnGrowShrinkType   = rAttribs.getToken( XML_growShrinkType, XML_insertDelete );
    maModel.mnAutoFormatId     = rAttribs.getInteger( XML_autoFormatId, 0 );
    maModel.mbHeaders          = rAttribs.getBool( XML_headers, true );
    maModel.mbRowNumbers       = rAttribs.getBool( XML_rowNumbers, false );
    maModel.mbDisableRefresh   = rAttribs.getBool( XML_disableRefresh, false );
    maModel.mbBackground       = rAttribs.getBool( XML_backgroundRefresh, true );
    maModel.mbFirstBackground  = rAttribs.getBool( XML_firstBackgroundRefresh, false );
    maModel.mbRefreshOnLoad    = rAttribs.getBool( XML_refreshOnLoad, false );
    maModel.mbFillFormulas     = rAttribs.getBool( XML_fillFormulas, false );
    maModel.mbRemoveDataOnSave = rAttribs.getBool( XML_removeDataOnSave, false );
    maModel.mbDisableEdit      = rAttribs.getBool( XML_disableEdit, false );
    maModel.mbPreserveFormat   = rAttribs.getBool( XML_preserveFormatting, true );
    maModel.mbAdjustColWidth   = rAttribs.getBool( XML_adjustColumnWidth, true );
    maModel.mbIntermediate     = rAttribs.getBool( XML_intermediate, false );
    maModel.mbApplyNumFmt      = rAttribs.getBool( XML_applyNumberFormats, false );
    maModel.mbApplyFont        = rAttribs.getBool( XML_applyFontFormats, false );
    maModel.mbApplyAlignment   = rAttribs.getBool( XML_applyAlignmentFormats, false );
    maModel.mbApplyBorder      = rAttribs.getBool( XML_applyBorderFormats, false );
    maModel.mbApplyFill        = rAttribs.getBool( XML_applyPatternFormats, false );
    // OOXML and BIFF12 documentation differ: OOXML mentions width/height, BIFF12 mentions protection
    maModel.mbApplyProtection  = rAttribs.getBool( XML_applyWidthHeightFormats, false );
}

void QueryTable::importQueryTable( SequenceInputStream& rStrm )
{
    sal_uInt32 nFlags;
    rStrm >> nFlags;
    maModel.mnAutoFormatId = rStrm.readuInt16();
    rStrm >> maModel.mnConnId >> maModel.maDefName;

    static const sal_Int32 spnGrowShrinkTypes[] = { XML_insertClear, XML_insertDelete, XML_overwriteClear };
    maModel.mnGrowShrinkType = STATIC_ARRAY_SELECT( spnGrowShrinkTypes, extractValue< sal_uInt8 >( nFlags, 6, 2 ), XML_insertDelete );

    maModel.mbHeaders           = getFlag( nFlags, BIFF12_QUERYTABLE_HEADERS );
    maModel.mbRowNumbers        = getFlag( nFlags, BIFF12_QUERYTABLE_ROWNUMBERS );
    maModel.mbDisableRefresh    = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEREFRESH );
    maModel.mbBackground        = getFlag( nFlags, BIFF12_QUERYTABLE_BACKGROUND );
    maModel.mbFirstBackground   = getFlag( nFlags, BIFF12_QUERYTABLE_FIRSTBACKGROUND );
    maModel.mbRefreshOnLoad     = getFlag( nFlags, BIFF12_QUERYTABLE_REFRESHONLOAD );
    maModel.mbFillFormulas      = getFlag( nFlags, BIFF12_QUERYTABLE_FILLFORMULAS );
    maModel.mbRemoveDataOnSave  = !getFlag( nFlags, BIFF12_QUERYTABLE_SAVEDATA ); // flag negated in BIFF12
    maModel.mbDisableEdit       = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEEDIT );
    maModel.mbPreserveFormat    = getFlag( nFlags, BIFF12_QUERYTABLE_PRESERVEFORMAT );
    maModel.mbAdjustColWidth    = getFlag( nFlags, BIFF12_QUERYTABLE_ADJUSTCOLWIDTH );
    maModel.mbIntermediate      = getFlag( nFlags, BIFF12_QUERYTABLE_INTERMEDIATE );
    maModel.mbApplyNumFmt       = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYNUMFMT );
    maModel.mbApplyFont         = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFONT );
    maModel.mbApplyAlignment    = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYALIGNMENT );
    maModel.mbApplyBorder       = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYBORDER );
    maModel.mbApplyFill         = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFILL );
    maModel.mbApplyProtection   = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYPROTECTION );
}

void QueryTable::importQueryTable( BiffInputStream& rStrm )
{
    sal_uInt16 nFlags, nAutoFormatFlags;
    rStrm >> nFlags;
    maModel.mnAutoFormatId = rStrm.readuInt16();
    rStrm >> nAutoFormatFlags;
    rStrm.skip( 4 );
    maModel.maDefName = rStrm.readUniString();

    bool bDeleteUnused = getFlag( nFlags, BIFF_QUERYTABLE_DELETEUNUSED );
    bool bOverwriteExisting = getFlag( nFlags, BIFF_QUERYTABLE_OVERWRITEEXISTING );
    OSL_ENSURE( !bDeleteUnused || !bOverwriteExisting, "QueryTable::importQueryTable - invalid flags" );
    maModel.mnGrowShrinkType = bDeleteUnused ? XML_insertDelete : (bOverwriteExisting ? XML_overwriteClear : XML_insertClear);

    maModel.mbHeaders           = getFlag( nFlags, BIFF_QUERYTABLE_HEADERS );
    maModel.mbRowNumbers        = getFlag( nFlags, BIFF_QUERYTABLE_ROWNUMBERS );
    maModel.mbDisableRefresh    = getFlag( nFlags, BIFF_QUERYTABLE_DISABLEREFRESH );
    maModel.mbBackground        = getFlag( nFlags, BIFF_QUERYTABLE_BACKGROUND );
    maModel.mbFirstBackground   = getFlag( nFlags, BIFF_QUERYTABLE_FIRSTBACKGROUND );
    maModel.mbRefreshOnLoad     = getFlag( nFlags, BIFF_QUERYTABLE_REFRESHONLOAD );
    maModel.mbFillFormulas      = getFlag( nFlags, BIFF_QUERYTABLE_FILLFORMULAS );
    maModel.mbRemoveDataOnSave  = !getFlag( nFlags, BIFF_QUERYTABLE_SAVEDATA ); // flag negated in BIFF
    maModel.mbDisableEdit       = getFlag( nFlags, BIFF_QUERYTABLE_DISABLEEDIT );
    maModel.mbAdjustColWidth    = getFlag( nFlags, BIFF_QUERYTABLE_ADJUSTCOLWIDTH );
    maModel.mbApplyNumFmt       = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYNUMFMT );
    maModel.mbApplyFont         = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYFONT );
    maModel.mbApplyAlignment    = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYALIGNMENT );
    maModel.mbApplyBorder       = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYBORDER );
    maModel.mbApplyFill         = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYFILL );
    maModel.mbApplyProtection   = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYPROTECTION );

    // create a new connection object that will store settings from following records
    OSL_ENSURE( maModel.mnConnId == -1, "QueryTable::importQueryTable - multiple call" );
    Connection& rConnection = getConnections().createConnectionWithId();
    maModel.mnConnId = rConnection.getConnectionId();

    // a DBQUERY record with some PCITEM_STRING records must follow
    bool bHasDbQuery = (rStrm.getNextRecId() == BIFF_ID_DBQUERY) && rStrm.startNextRecord();
    OSL_ENSURE( bHasDbQuery, "QueryTable::importQueryTable - missing DBQUERY record" );
    if( bHasDbQuery )
        rConnection.importDbQuery( rStrm );
}

void QueryTable::importQueryTableRefresh( BiffInputStream& rStrm )
{
    rStrm.skip( 4 );
    bool bPivot = rStrm.readuInt16() != 0;
    OSL_ENSURE( !bPivot, "QueryTable::importQueryTableRefresh - unexpected pivot flag" );
    if( !bPivot )
    {
        rStrm.skip( 2 );
        sal_uInt32 nFlags = rStrm.readuInt32();
        maModel.mbPreserveFormat = getFlag( nFlags, BIFF_QTREFRESH_PRESERVEFORMAT );
        maModel.mbAdjustColWidth = getFlag( nFlags, BIFF_QTREFRESH_ADJUSTCOLWIDTH );
    }
}

void QueryTable::importQueryTableSettings( BiffInputStream& rStrm )
{
    ConnectionRef xConnection = getConnections().getConnection( maModel.mnConnId );
    OSL_ENSURE( xConnection.get(), "QueryTable::importQueryTableSettings - missing connection object" );
    if( xConnection.get() )
        xConnection->importQueryTableSettings( rStrm );
}

void QueryTable::finalizeImport()
{
    ConnectionRef xConnection = getConnections().getConnection( maModel.mnConnId );
    OSL_ENSURE( xConnection.get(), "QueryTable::finalizeImport - missing connection object" );
    if( xConnection.get() && (xConnection->getConnectionType() == BIFF12_CONNECTION_HTML) )
    {
        // check that valid web query properties exist
        const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get();
        if( pWebPr && !pWebPr->mbXml )
        {
            OUString aFileUrl = getBaseFilter().getAbsoluteUrl( pWebPr->maUrl );
            if( aFileUrl.getLength() > 0 )
            {
                // resolve destination cell range (stored as defined name containing the range)
                OUString aDefName = maModel.maDefName.replace( ' ', '_' ).replace( '-', '_' );
                DefinedNameRef xDefName = getDefinedNames().getByModelName( aDefName, getSheetIndex() );
                OSL_ENSURE( xDefName.get(), "QueryTable::finalizeImport - missing defined name" );
                if( xDefName.get() )
                {
                    CellRangeAddress aDestRange;
                    bool bIsRange = xDefName->getAbsoluteRange( aDestRange ) && (aDestRange.Sheet == getSheetIndex());
                    OSL_ENSURE( bIsRange, "QueryTable::finalizeImport - defined name does not contain valid cell range" );
                    if( bIsRange && getAddressConverter().checkCellRange( aDestRange, false, true ) )
                    {
                        CellAddress aDestPos( aDestRange.Sheet, aDestRange.StartColumn, aDestRange.StartRow );
                        // find tables mode: entire document, all tables, or specific tables
                        OUString aTables = pWebPr->mbHtmlTables ? lclBuildWebQueryTables( pWebPr->maTables ) : CREATE_OUSTRING( "HTML_all" );
                        if( aTables.getLength() > 0 ) try
                        {
                            PropertySet aDocProps( getDocument() );
                            Reference< XAreaLinks > xAreaLinks( aDocProps.getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW );
                            OUString aFilterName = CREATE_OUSTRING( "calc_HTML_WebQuery" );
                            OUString aFilterOptions;
                            xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName, aFilterOptions );
                            // set refresh interval (convert minutes to seconds)
                            sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60;
                            if( nRefreshPeriod > 0 )
                            {
                                PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestPos, aFileUrl, aTables, aFilterName, aFilterOptions ) );
                                aPropSet.setProperty( PROP_RefreshPeriod, nRefreshPeriod );
                            }
                        }
                        catch( Exception& )
                        {
                        }
                    }
                }
            }
        }
    }
}

// ============================================================================

QueryTableBuffer::QueryTableBuffer( const WorksheetHelper& rHelper ) :
    WorksheetHelper( rHelper )
{
}

QueryTable& QueryTableBuffer::createQueryTable()
{
    QueryTableVector::value_type xQueryTable( new QueryTable( *this ) );
    maQueryTables.push_back( xQueryTable );
    return *xQueryTable;
}

void QueryTableBuffer::finalizeImport()
{
    maQueryTables.forEachMem( &QueryTable::finalizeImport );
}

// ============================================================================

} // namespace xls
} // namespace oox