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