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
lclAppendWebQueryTableName(OUStringBuffer & rTables,const OUString & rTableName)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
lclAppendWebQueryTableIndex(OUStringBuffer & rTables,sal_Int32 nTableIndex)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
lclBuildWebQueryTables(const WebPrModel::TablesVector & rTables)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
lclFindAreaLink(const Reference<XAreaLinks> & rxAreaLinks,const CellAddress & rDestPos,const OUString & rFileUrl,const OUString & rTables,const OUString & rFilterName,const OUString & rFilterOptions)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
QueryTableModel()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
QueryTable(const WorksheetHelper & rHelper)185 QueryTable::QueryTable( const WorksheetHelper& rHelper ) :
186 WorksheetHelper( rHelper )
187 {
188 }
189
importQueryTable(const AttributeList & rAttribs)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
importQueryTable(SequenceInputStream & rStrm)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
importQueryTable(BiffInputStream & rStrm)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
importQueryTableRefresh(BiffInputStream & rStrm)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
importQueryTableSettings(BiffInputStream & rStrm)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
finalizeImport()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
QueryTableBuffer(const WorksheetHelper & rHelper)366 QueryTableBuffer::QueryTableBuffer( const WorksheetHelper& rHelper ) :
367 WorksheetHelper( rHelper )
368 {
369 }
370
createQueryTable()371 QueryTable& QueryTableBuffer::createQueryTable()
372 {
373 QueryTableVector::value_type xQueryTable( new QueryTable( *this ) );
374 maQueryTables.push_back( xQueryTable );
375 return *xQueryTable;
376 }
377
finalizeImport()378 void QueryTableBuffer::finalizeImport()
379 {
380 maQueryTables.forEachMem( &QueryTable::finalizeImport );
381 }
382
383 // ============================================================================
384
385 } // namespace xls
386 } // namespace oox
387