1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 28 #include "oox/xls/querytablebuffer.hxx" 29 30 #include <com/sun/star/container/XEnumerationAccess.hpp> 31 #include <com/sun/star/sheet/XAreaLink.hpp> 32 #include <com/sun/star/sheet/XAreaLinks.hpp> 33 #include "oox/core/filterbase.hxx" 34 #include "oox/helper/attributelist.hxx" 35 #include "oox/xls/addressconverter.hxx" 36 #include "oox/xls/biffinputstream.hxx" 37 #include "oox/xls/connectionsbuffer.hxx" 38 #include "oox/xls/defnamesbuffer.hxx" 39 40 namespace oox { 41 namespace xls { 42 43 // ============================================================================ 44 45 using namespace ::com::sun::star::container; 46 using namespace ::com::sun::star::sheet; 47 using namespace ::com::sun::star::table; 48 using namespace ::com::sun::star::uno; 49 50 using ::rtl::OUString; 51 using ::rtl::OUStringBuffer; 52 53 // ============================================================================ 54 55 namespace { 56 57 const sal_uInt32 BIFF12_QUERYTABLE_HEADERS = 0x00000001; 58 const sal_uInt32 BIFF12_QUERYTABLE_ROWNUMBERS = 0x00000002; 59 const sal_uInt32 BIFF12_QUERYTABLE_DISABLEREFRESH = 0x00000004; 60 const sal_uInt32 BIFF12_QUERYTABLE_BACKGROUND = 0x00000008; 61 const sal_uInt32 BIFF12_QUERYTABLE_FIRSTBACKGROUND = 0x00000010; 62 const sal_uInt32 BIFF12_QUERYTABLE_REFRESHONLOAD = 0x00000020; 63 const sal_uInt32 BIFF12_QUERYTABLE_FILLFORMULAS = 0x00000100; 64 const sal_uInt32 BIFF12_QUERYTABLE_SAVEDATA = 0x00000200; 65 const sal_uInt32 BIFF12_QUERYTABLE_DISABLEEDIT = 0x00000400; 66 const sal_uInt32 BIFF12_QUERYTABLE_PRESERVEFORMAT = 0x00000800; 67 const sal_uInt32 BIFF12_QUERYTABLE_ADJUSTCOLWIDTH = 0x00001000; 68 const sal_uInt32 BIFF12_QUERYTABLE_INTERMEDIATE = 0x00002000; 69 const sal_uInt32 BIFF12_QUERYTABLE_APPLYNUMFMT = 0x00004000; 70 const sal_uInt32 BIFF12_QUERYTABLE_APPLYFONT = 0x00008000; 71 const sal_uInt32 BIFF12_QUERYTABLE_APPLYALIGNMENT = 0x00010000; 72 const sal_uInt32 BIFF12_QUERYTABLE_APPLYBORDER = 0x00020000; 73 const sal_uInt32 BIFF12_QUERYTABLE_APPLYFILL = 0x00040000; 74 const sal_uInt32 BIFF12_QUERYTABLE_APPLYPROTECTION = 0x00080000; 75 76 const sal_uInt16 BIFF_QUERYTABLE_HEADERS = 0x0001; 77 const sal_uInt16 BIFF_QUERYTABLE_ROWNUMBERS = 0x0002; 78 const sal_uInt16 BIFF_QUERYTABLE_DISABLEREFRESH = 0x0004; 79 const sal_uInt16 BIFF_QUERYTABLE_BACKGROUND = 0x0008; 80 const sal_uInt16 BIFF_QUERYTABLE_FIRSTBACKGROUND = 0x0010; 81 const sal_uInt16 BIFF_QUERYTABLE_REFRESHONLOAD = 0x0020; 82 const sal_uInt16 BIFF_QUERYTABLE_DELETEUNUSED = 0x0040; 83 const sal_uInt16 BIFF_QUERYTABLE_FILLFORMULAS = 0x0080; 84 const sal_uInt16 BIFF_QUERYTABLE_ADJUSTCOLWIDTH = 0x0100; 85 const sal_uInt16 BIFF_QUERYTABLE_SAVEDATA = 0x0200; 86 const sal_uInt16 BIFF_QUERYTABLE_DISABLEEDIT = 0x0400; 87 const sal_uInt16 BIFF_QUERYTABLE_OVERWRITEEXISTING = 0x2000; 88 89 const sal_uInt16 BIFF_QUERYTABLE_APPLYNUMFMT = 0x0001; 90 const sal_uInt16 BIFF_QUERYTABLE_APPLYFONT = 0x0002; 91 const sal_uInt16 BIFF_QUERYTABLE_APPLYALIGNMENT = 0x0004; 92 const sal_uInt16 BIFF_QUERYTABLE_APPLYBORDER = 0x0008; 93 const sal_uInt16 BIFF_QUERYTABLE_APPLYFILL = 0x0010; 94 const sal_uInt16 BIFF_QUERYTABLE_APPLYPROTECTION = 0x0020; 95 96 const sal_uInt32 BIFF_QTREFRESH_PRESERVEFORMAT = 0x00000001; 97 const sal_uInt32 BIFF_QTREFRESH_ADJUSTCOLWIDTH = 0x00000002; 98 99 // ---------------------------------------------------------------------------- 100 101 void lclAppendWebQueryTableName( OUStringBuffer& rTables, const OUString& rTableName ) 102 { 103 if( rTableName.getLength() > 0 ) 104 { 105 if( rTables.getLength() > 0 ) 106 rTables.append( sal_Unicode( ';' ) ); 107 rTables.appendAscii( RTL_CONSTASCII_STRINGPARAM( "HTML__" ) ).append( rTableName ); 108 } 109 } 110 111 void lclAppendWebQueryTableIndex( OUStringBuffer& rTables, sal_Int32 nTableIndex ) 112 { 113 if( nTableIndex > 0 ) 114 { 115 if( rTables.getLength() > 0 ) 116 rTables.append( sal_Unicode( ';' ) ); 117 rTables.appendAscii( RTL_CONSTASCII_STRINGPARAM( "HTML_" ) ).append( nTableIndex ); 118 } 119 } 120 121 OUString lclBuildWebQueryTables( const WebPrModel::TablesVector& rTables ) 122 { 123 if( rTables.empty() ) 124 return CREATE_OUSTRING( "HTML_tables" ); 125 126 OUStringBuffer aTables; 127 for( WebPrModel::TablesVector::const_iterator aIt = rTables.begin(), aEnd = rTables.end(); aIt != aEnd; ++aIt ) 128 { 129 if( aIt->has< OUString >() ) 130 lclAppendWebQueryTableName( aTables, aIt->get< OUString >() ); 131 else if( aIt->has< sal_Int32 >() ) 132 lclAppendWebQueryTableIndex( aTables, aIt->get< sal_Int32 >() ); 133 } 134 return aTables.makeStringAndClear(); 135 } 136 137 Reference< XAreaLink > lclFindAreaLink( 138 const Reference< XAreaLinks >& rxAreaLinks, const CellAddress& rDestPos, 139 const OUString& rFileUrl, const OUString& rTables, const OUString& rFilterName, const OUString& rFilterOptions ) 140 { 141 try 142 { 143 Reference< XEnumerationAccess > xAreaLinksEA( rxAreaLinks, UNO_QUERY_THROW ); 144 Reference< XEnumeration > xAreaLinksEnum( xAreaLinksEA->createEnumeration(), UNO_SET_THROW ); 145 while( xAreaLinksEnum->hasMoreElements() ) 146 { 147 Reference< XAreaLink > xAreaLink( xAreaLinksEnum->nextElement(), UNO_QUERY_THROW ); 148 PropertySet aPropSet( xAreaLink ); 149 CellRangeAddress aDestArea = xAreaLink->getDestArea(); 150 OUString aString; 151 if( (rDestPos.Sheet == aDestArea.Sheet) && (rDestPos.Column == aDestArea.StartColumn) && (rDestPos.Row == aDestArea.StartRow) && 152 (rTables == xAreaLink->getSourceArea()) && 153 aPropSet.getProperty( aString, PROP_Url ) && (rFileUrl == aString) && 154 aPropSet.getProperty( aString, PROP_Filter ) && (rFilterName == aString) && 155 aPropSet.getProperty( aString, PROP_FilterOptions ) && (rFilterOptions == aString) ) 156 return xAreaLink; 157 } 158 } 159 catch( Exception& ) 160 { 161 } 162 return Reference< XAreaLink >(); 163 } 164 165 } // namespace 166 167 // ============================================================================ 168 169 QueryTableModel::QueryTableModel() : 170 mnConnId( -1 ), 171 mnGrowShrinkType( XML_insertDelete ), 172 mbHeaders( true ), 173 mbRowNumbers( false ), 174 mbDisableRefresh( false ), 175 mbBackground( true ), 176 mbFirstBackground( false ), 177 mbRefreshOnLoad( false ), 178 mbFillFormulas( false ), 179 mbRemoveDataOnSave( false ), 180 mbDisableEdit( false ), 181 mbPreserveFormat( true ), 182 mbAdjustColWidth( true ), 183 mbIntermediate( false ) 184 { 185 } 186 187 // ---------------------------------------------------------------------------- 188 189 QueryTable::QueryTable( const WorksheetHelper& rHelper ) : 190 WorksheetHelper( rHelper ) 191 { 192 } 193 194 void QueryTable::importQueryTable( const AttributeList& rAttribs ) 195 { 196 maModel.maDefName = rAttribs.getXString( XML_name, OUString() ); 197 maModel.mnConnId = rAttribs.getInteger( XML_connectionId, -1 ); 198 maModel.mnGrowShrinkType = rAttribs.getToken( XML_growShrinkType, XML_insertDelete ); 199 maModel.mnAutoFormatId = rAttribs.getInteger( XML_autoFormatId, 0 ); 200 maModel.mbHeaders = rAttribs.getBool( XML_headers, true ); 201 maModel.mbRowNumbers = rAttribs.getBool( XML_rowNumbers, false ); 202 maModel.mbDisableRefresh = rAttribs.getBool( XML_disableRefresh, false ); 203 maModel.mbBackground = rAttribs.getBool( XML_backgroundRefresh, true ); 204 maModel.mbFirstBackground = rAttribs.getBool( XML_firstBackgroundRefresh, false ); 205 maModel.mbRefreshOnLoad = rAttribs.getBool( XML_refreshOnLoad, false ); 206 maModel.mbFillFormulas = rAttribs.getBool( XML_fillFormulas, false ); 207 maModel.mbRemoveDataOnSave = rAttribs.getBool( XML_removeDataOnSave, false ); 208 maModel.mbDisableEdit = rAttribs.getBool( XML_disableEdit, false ); 209 maModel.mbPreserveFormat = rAttribs.getBool( XML_preserveFormatting, true ); 210 maModel.mbAdjustColWidth = rAttribs.getBool( XML_adjustColumnWidth, true ); 211 maModel.mbIntermediate = rAttribs.getBool( XML_intermediate, false ); 212 maModel.mbApplyNumFmt = rAttribs.getBool( XML_applyNumberFormats, false ); 213 maModel.mbApplyFont = rAttribs.getBool( XML_applyFontFormats, false ); 214 maModel.mbApplyAlignment = rAttribs.getBool( XML_applyAlignmentFormats, false ); 215 maModel.mbApplyBorder = rAttribs.getBool( XML_applyBorderFormats, false ); 216 maModel.mbApplyFill = rAttribs.getBool( XML_applyPatternFormats, false ); 217 // OOXML and BIFF12 documentation differ: OOXML mentions width/height, BIFF12 mentions protection 218 maModel.mbApplyProtection = rAttribs.getBool( XML_applyWidthHeightFormats, false ); 219 } 220 221 void QueryTable::importQueryTable( SequenceInputStream& rStrm ) 222 { 223 sal_uInt32 nFlags; 224 rStrm >> nFlags; 225 maModel.mnAutoFormatId = rStrm.readuInt16(); 226 rStrm >> maModel.mnConnId >> maModel.maDefName; 227 228 static const sal_Int32 spnGrowShrinkTypes[] = { XML_insertClear, XML_insertDelete, XML_overwriteClear }; 229 maModel.mnGrowShrinkType = STATIC_ARRAY_SELECT( spnGrowShrinkTypes, extractValue< sal_uInt8 >( nFlags, 6, 2 ), XML_insertDelete ); 230 231 maModel.mbHeaders = getFlag( nFlags, BIFF12_QUERYTABLE_HEADERS ); 232 maModel.mbRowNumbers = getFlag( nFlags, BIFF12_QUERYTABLE_ROWNUMBERS ); 233 maModel.mbDisableRefresh = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEREFRESH ); 234 maModel.mbBackground = getFlag( nFlags, BIFF12_QUERYTABLE_BACKGROUND ); 235 maModel.mbFirstBackground = getFlag( nFlags, BIFF12_QUERYTABLE_FIRSTBACKGROUND ); 236 maModel.mbRefreshOnLoad = getFlag( nFlags, BIFF12_QUERYTABLE_REFRESHONLOAD ); 237 maModel.mbFillFormulas = getFlag( nFlags, BIFF12_QUERYTABLE_FILLFORMULAS ); 238 maModel.mbRemoveDataOnSave = !getFlag( nFlags, BIFF12_QUERYTABLE_SAVEDATA ); // flag negated in BIFF12 239 maModel.mbDisableEdit = getFlag( nFlags, BIFF12_QUERYTABLE_DISABLEEDIT ); 240 maModel.mbPreserveFormat = getFlag( nFlags, BIFF12_QUERYTABLE_PRESERVEFORMAT ); 241 maModel.mbAdjustColWidth = getFlag( nFlags, BIFF12_QUERYTABLE_ADJUSTCOLWIDTH ); 242 maModel.mbIntermediate = getFlag( nFlags, BIFF12_QUERYTABLE_INTERMEDIATE ); 243 maModel.mbApplyNumFmt = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYNUMFMT ); 244 maModel.mbApplyFont = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFONT ); 245 maModel.mbApplyAlignment = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYALIGNMENT ); 246 maModel.mbApplyBorder = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYBORDER ); 247 maModel.mbApplyFill = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYFILL ); 248 maModel.mbApplyProtection = getFlag( nFlags, BIFF12_QUERYTABLE_APPLYPROTECTION ); 249 } 250 251 void QueryTable::importQueryTable( BiffInputStream& rStrm ) 252 { 253 sal_uInt16 nFlags, nAutoFormatFlags; 254 rStrm >> nFlags; 255 maModel.mnAutoFormatId = rStrm.readuInt16(); 256 rStrm >> nAutoFormatFlags; 257 rStrm.skip( 4 ); 258 maModel.maDefName = rStrm.readUniString(); 259 260 bool bDeleteUnused = getFlag( nFlags, BIFF_QUERYTABLE_DELETEUNUSED ); 261 bool bOverwriteExisting = getFlag( nFlags, BIFF_QUERYTABLE_OVERWRITEEXISTING ); 262 OSL_ENSURE( !bDeleteUnused || !bOverwriteExisting, "QueryTable::importQueryTable - invalid flags" ); 263 maModel.mnGrowShrinkType = bDeleteUnused ? XML_insertDelete : (bOverwriteExisting ? XML_overwriteClear : XML_insertClear); 264 265 maModel.mbHeaders = getFlag( nFlags, BIFF_QUERYTABLE_HEADERS ); 266 maModel.mbRowNumbers = getFlag( nFlags, BIFF_QUERYTABLE_ROWNUMBERS ); 267 maModel.mbDisableRefresh = getFlag( nFlags, BIFF_QUERYTABLE_DISABLEREFRESH ); 268 maModel.mbBackground = getFlag( nFlags, BIFF_QUERYTABLE_BACKGROUND ); 269 maModel.mbFirstBackground = getFlag( nFlags, BIFF_QUERYTABLE_FIRSTBACKGROUND ); 270 maModel.mbRefreshOnLoad = getFlag( nFlags, BIFF_QUERYTABLE_REFRESHONLOAD ); 271 maModel.mbFillFormulas = getFlag( nFlags, BIFF_QUERYTABLE_FILLFORMULAS ); 272 maModel.mbRemoveDataOnSave = !getFlag( nFlags, BIFF_QUERYTABLE_SAVEDATA ); // flag negated in BIFF 273 maModel.mbDisableEdit = getFlag( nFlags, BIFF_QUERYTABLE_DISABLEEDIT ); 274 maModel.mbAdjustColWidth = getFlag( nFlags, BIFF_QUERYTABLE_ADJUSTCOLWIDTH ); 275 maModel.mbApplyNumFmt = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYNUMFMT ); 276 maModel.mbApplyFont = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYFONT ); 277 maModel.mbApplyAlignment = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYALIGNMENT ); 278 maModel.mbApplyBorder = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYBORDER ); 279 maModel.mbApplyFill = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYFILL ); 280 maModel.mbApplyProtection = getFlag( nAutoFormatFlags, BIFF_QUERYTABLE_APPLYPROTECTION ); 281 282 // create a new connection object that will store settings from following records 283 OSL_ENSURE( maModel.mnConnId == -1, "QueryTable::importQueryTable - multiple call" ); 284 Connection& rConnection = getConnections().createConnectionWithId(); 285 maModel.mnConnId = rConnection.getConnectionId(); 286 287 // a DBQUERY record with some PCITEM_STRING records must follow 288 bool bHasDbQuery = (rStrm.getNextRecId() == BIFF_ID_DBQUERY) && rStrm.startNextRecord(); 289 OSL_ENSURE( bHasDbQuery, "QueryTable::importQueryTable - missing DBQUERY record" ); 290 if( bHasDbQuery ) 291 rConnection.importDbQuery( rStrm ); 292 } 293 294 void QueryTable::importQueryTableRefresh( BiffInputStream& rStrm ) 295 { 296 rStrm.skip( 4 ); 297 bool bPivot = rStrm.readuInt16() != 0; 298 OSL_ENSURE( !bPivot, "QueryTable::importQueryTableRefresh - unexpected pivot flag" ); 299 if( !bPivot ) 300 { 301 rStrm.skip( 2 ); 302 sal_uInt32 nFlags = rStrm.readuInt32(); 303 maModel.mbPreserveFormat = getFlag( nFlags, BIFF_QTREFRESH_PRESERVEFORMAT ); 304 maModel.mbAdjustColWidth = getFlag( nFlags, BIFF_QTREFRESH_ADJUSTCOLWIDTH ); 305 } 306 } 307 308 void QueryTable::importQueryTableSettings( BiffInputStream& rStrm ) 309 { 310 ConnectionRef xConnection = getConnections().getConnection( maModel.mnConnId ); 311 OSL_ENSURE( xConnection.get(), "QueryTable::importQueryTableSettings - missing connection object" ); 312 if( xConnection.get() ) 313 xConnection->importQueryTableSettings( rStrm ); 314 } 315 316 void QueryTable::finalizeImport() 317 { 318 ConnectionRef xConnection = getConnections().getConnection( maModel.mnConnId ); 319 OSL_ENSURE( xConnection.get(), "QueryTable::finalizeImport - missing connection object" ); 320 if( xConnection.get() && (xConnection->getConnectionType() == BIFF12_CONNECTION_HTML) ) 321 { 322 // check that valid web query properties exist 323 const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get(); 324 if( pWebPr && !pWebPr->mbXml ) 325 { 326 OUString aFileUrl = getBaseFilter().getAbsoluteUrl( pWebPr->maUrl ); 327 if( aFileUrl.getLength() > 0 ) 328 { 329 // resolve destination cell range (stored as defined name containing the range) 330 OUString aDefName = maModel.maDefName.replace( ' ', '_' ).replace( '-', '_' ); 331 DefinedNameRef xDefName = getDefinedNames().getByModelName( aDefName, getSheetIndex() ); 332 OSL_ENSURE( xDefName.get(), "QueryTable::finalizeImport - missing defined name" ); 333 if( xDefName.get() ) 334 { 335 CellRangeAddress aDestRange; 336 bool bIsRange = xDefName->getAbsoluteRange( aDestRange ) && (aDestRange.Sheet == getSheetIndex()); 337 OSL_ENSURE( bIsRange, "QueryTable::finalizeImport - defined name does not contain valid cell range" ); 338 if( bIsRange && getAddressConverter().checkCellRange( aDestRange, false, true ) ) 339 { 340 CellAddress aDestPos( aDestRange.Sheet, aDestRange.StartColumn, aDestRange.StartRow ); 341 // find tables mode: entire document, all tables, or specific tables 342 OUString aTables = pWebPr->mbHtmlTables ? lclBuildWebQueryTables( pWebPr->maTables ) : CREATE_OUSTRING( "HTML_all" ); 343 if( aTables.getLength() > 0 ) try 344 { 345 PropertySet aDocProps( getDocument() ); 346 Reference< XAreaLinks > xAreaLinks( aDocProps.getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW ); 347 OUString aFilterName = CREATE_OUSTRING( "calc_HTML_WebQuery" ); 348 OUString aFilterOptions; 349 xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName, aFilterOptions ); 350 // set refresh interval (convert minutes to seconds) 351 sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60; 352 if( nRefreshPeriod > 0 ) 353 { 354 PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestPos, aFileUrl, aTables, aFilterName, aFilterOptions ) ); 355 aPropSet.setProperty( PROP_RefreshPeriod, nRefreshPeriod ); 356 } 357 } 358 catch( Exception& ) 359 { 360 } 361 } 362 } 363 } 364 } 365 } 366 } 367 368 // ============================================================================ 369 370 QueryTableBuffer::QueryTableBuffer( const WorksheetHelper& rHelper ) : 371 WorksheetHelper( rHelper ) 372 { 373 } 374 375 QueryTable& QueryTableBuffer::createQueryTable() 376 { 377 QueryTableVector::value_type xQueryTable( new QueryTable( *this ) ); 378 maQueryTables.push_back( xQueryTable ); 379 return *xQueryTable; 380 } 381 382 void QueryTableBuffer::finalizeImport() 383 { 384 maQueryTables.forEachMem( &QueryTable::finalizeImport ); 385 } 386 387 // ============================================================================ 388 389 } // namespace xls 390 } // namespace oox 391