LibreOffice Module sc (master)  1
querytablebuffer.cxx
Go to the documentation of this file.
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 <com/sun/star/sheet/XSpreadsheetDocument.hpp>
27 #include <osl/diagnose.h>
28 #include <oox/core/filterbase.hxx>
31 #include <oox/token/properties.hxx>
32 #include <oox/token/tokens.hxx>
33 #include <addressconverter.hxx>
34 #include <biffhelper.hxx>
35 #include <connectionsbuffer.hxx>
36 #include <defnamesbuffer.hxx>
37 
38 namespace oox::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 
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 
151  WorksheetHelper( rHelper )
152 {
153 }
154 
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 
183 {
184  sal_uInt32 nFlags;
185  nFlags = rStrm.readuInt32();
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 
214 {
216  OSL_ENSURE( xConnection, "QueryTable::finalizeImport - missing connection object" );
217  if( !(xConnection && (xConnection->getConnectionType() == BIFF12_CONNECTION_HTML)) )
218  return;
219 
220  // check that valid web query properties exist
221  const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get();
222  if( !pWebPr || pWebPr->mbXml )
223  return;
224 
225  OUString aFileUrl = getBaseFilter().getAbsoluteUrl( pWebPr->maUrl );
226  if( aFileUrl.isEmpty() )
227  return;
228 
229  // resolve destination cell range (stored as defined name containing the range)
230  OUString aDefName = maModel.maDefName.replace( ' ', '_' ).replace( '-', '_' );
231  DefinedNameRef xDefName = getDefinedNames().getByModelName( aDefName, getSheetIndex() );
232  OSL_ENSURE( xDefName, "QueryTable::finalizeImport - missing defined name" );
233  if( !xDefName )
234  return;
235 
236  ScRange aDestRange;
237  bool bIsRange = xDefName->getAbsoluteRange( aDestRange ) && (aDestRange.aStart.Tab() == getSheetIndex());
238  OSL_ENSURE( bIsRange, "QueryTable::finalizeImport - defined name does not contain valid cell range" );
239  if( !(bIsRange && getAddressConverter().checkCellRange( aDestRange, false, true )) )
240  return;
241 
242  // find tables mode: entire document, all tables, or specific tables
243  OUString aTables = pWebPr->mbHtmlTables ? lclBuildWebQueryTables( pWebPr->maTables ) : "HTML_all";
244  if( !aTables.isEmpty() ) try
245  {
246  PropertySet aDocProps( getDocument() );
247  Reference< XAreaLinks > xAreaLinks( aDocProps.getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW );
248  CellAddress aDestPos( aDestRange.aStart.Tab(), aDestRange.aStart.Col(), aDestRange.aStart.Row() );
249  const OUString aFilterName = "calc_HTML_WebQuery";
250  xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName, /*aFilterOptions*/"" );
251  // set refresh interval (convert minutes to seconds)
252  sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60;
253  if( nRefreshPeriod > 0 )
254  {
255  PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestRange.aStart, aFileUrl, aTables, aFilterName, /*aFilterOptions*/"" ) );
256  aPropSet.setProperty( PROP_RefreshPeriod, nRefreshPeriod );
257  }
258  }
259  catch( Exception& )
260  {
261  }
262 }
263 
265  WorksheetHelper( rHelper )
266 {
267 }
268 
270 {
271  QueryTableVector::value_type xQueryTable = std::make_shared<QueryTable>( *this );
272  maQueryTables.push_back( xQueryTable );
273  return *xQueryTable;
274 }
275 
277 {
279 }
280 
281 } // namespace oox::xls
282 
283 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
bool mbApplyAlignment
True = apply font from autoformatting.
void finalizeImport()
Inserts a web query into the sheet.
ScAddress aStart
Definition: address.hxx:500
OptValue< bool > getBool(sal_Int32 nAttrToken) const
SCROW Row() const
Definition: address.hxx:262
bool mbRowNumbers
True = source data contains a header row.
OptValue< OUString > getXString(sal_Int32 nAttrToken) const
OptValue< sal_Int32 > getInteger(sal_Int32 nAttrToken) const
bool mbDisableRefresh
True = first column contains row numbers.
::std::vector< css::uno::Any > TablesVector
void importQueryTable(const AttributeList &rAttribs)
Imports query table settings from the queryTable element.
css::uno::Any getAnyProperty(sal_Int32 nPropId) const
bool mbAdjustColWidth
True = use existing formatting for new rows.
bool mbRefreshOnLoad
True = first background refresh not yet finished.
DefinedNameRef getByModelName(const OUString &rModelName, sal_Int16 nCalcSheet=-1) const
Returns a defined name by its model name.
void finalizeImport()
Inserts all web queries into the sheet.
sal_uInt32 readuInt32()
#define STATIC_ARRAY_SELECT(array, index, def)
bool mbHeaders
Behaviour when source data size changes.
void forEachMem(FuncType pFunc) const
QueryTable & createQueryTable()
Creates a new query table and stores it into the internal vector.
sal_uInt16 readuInt16()
::oox::core::FilterBase & getBaseFilter() const
Returns the base filter object (base class of all filters).
SCTAB Tab() const
Definition: address.hxx:271
bool mbHtmlTables
True = refreshed with Excel 2000 or newer.
QueryTableModel()
True = query table defined but not built yet.
bool getFlag(Type nBitField, Type nMask)
bool mbXml
Plain text, rich text, or HTML.
bool mbPreserveFormat
True = connection locked for editing.
std::shared_ptr< Connection > ConnectionRef
container_type::value_type value_type
const css::uno::Reference< css::sheet::XSpreadsheetDocument > & getDocument() const
Returns a reference to the source/target spreadsheet document model.
bool mbApplyFill
True = apply border from autoformatting.
bool mbApplyProtection
True = apply fill from autoformatting.
QueryTableBuffer(const WorksheetHelper &rHelper)
sal_Int32 mnConnId
Defined name containing the target cell range.
bool mbBackground
True = refreshing data disabled.
SCCOL Col() const
Definition: address.hxx:267
bool mbFirstBackground
True = refresh asynchronously.
Special properties for data connections representing web queries.
bool mbApplyBorder
True = apply alignment from autoformatting.
QueryTableModel maModel
OUString maUrl
Names or indexes of the web query tables.
std::shared_ptr< DefinedName > DefinedNameRef
sal_Int32 mnGrowShrinkType
Identifier of the external connection used to query the data.
SCTAB getSheetIndex() const
Returns the index of the current sheet.
QueryTable(const WorksheetHelper &rHelper)
bool mbRemoveDataOnSave
True = expand formulas next to range when source data grows.
bool mbApplyFont
True = apply number format from autoformatting.
ConnectionsBuffer & getConnections() const
Returns the collection of external data connections.
OUString getAbsoluteUrl(const OUString &rUrl) const
bool mbDisableEdit
True = remove querried data before saving.
bool mbApplyNumFmt
Index of predefined autoformatting.
ConnectionRef getConnection(sal_Int32 nConnId) const
Returns a data connection by its unique identifier.
DefinedNamesBuffer & getDefinedNames() const
Returns the defined names read from the workbook globals.
const sal_Int32 BIFF12_CONNECTION_HTML
bool setProperty(sal_Int32 nPropId, const Type &rValue)
bool mbIntermediate
True = adjust column widths after refresh.
OptValue< sal_Int32 > getToken(sal_Int32 nAttrToken) const
bool mbFillFormulas
True = refresh table after import.
AddressConverter & getAddressConverter() const
Returns the converter for string to cell address/range conversion.