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>
31 #include <oox/token/properties.hxx>
32 #include <oox/token/tokens.hxx>
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;
66 void lclAppendWebQueryTableName( OUStringBuffer& rTables, std::u16string_view rTableName )
68 if( !rTableName.empty() )
70 if( !rTables.isEmpty() )
71 rTables.append(
';' );
72 rTables.append( OUString::Concat(
"HTML__") + rTableName );
76 void lclAppendWebQueryTableIndex( OUStringBuffer& rTables, sal_Int32 nTableIndex )
80 if( !rTables.isEmpty() )
81 rTables.append(
';' );
82 rTables.append(
"HTML_" + OUString::number( nTableIndex ) );
91 OUStringBuffer aTables;
92 for(
const auto& rTable : rTables )
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 >() );
99 return aTables.makeStringAndClear();
102 Reference< XAreaLink > lclFindAreaLink(
103 const Reference< XAreaLinks >& rxAreaLinks,
const ScAddress& rDestPos,
104 std::u16string_view rFileUrl, std::u16string_view rTables, std::u16string_view rFilterName, std::u16string_view rFilterOptions )
108 Reference< XEnumerationAccess > xAreaLinksEA( rxAreaLinks, UNO_QUERY_THROW );
109 Reference< XEnumeration > xAreaLinksEnum( xAreaLinksEA->createEnumeration(), UNO_SET_THROW );
110 while( xAreaLinksEnum->hasMoreElements() )
112 Reference< XAreaLink > xAreaLink( xAreaLinksEnum->nextElement(), UNO_QUERY_THROW );
113 PropertySet aPropSet( xAreaLink );
114 CellRangeAddress aDestArea = xAreaLink->getDestArea();
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) )
127 return Reference< XAreaLink >();
134 mnGrowShrinkType( XML_insertDelete ),
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 )
190 static const sal_Int32 spnGrowShrinkTypes[] = { XML_insertClear, XML_insertDelete, XML_overwriteClear };
216 OSL_ENSURE( xConnection,
"QueryTable::finalizeImport - missing connection object" );
221 const WebPrModel* pWebPr = xConnection->getModel().mxWebPr.get();
222 if( !pWebPr || pWebPr->
mbXml )
226 if( aFileUrl.isEmpty() )
230 OUString aDefName =
maModel.
maDefName.replace(
' ',
'_' ).replace(
'-',
'_' );
232 OSL_ENSURE( xDefName,
"QueryTable::finalizeImport - missing defined name" );
238 OSL_ENSURE( bIsRange,
"QueryTable::finalizeImport - defined name does not contain valid cell range" );
243 OUString aTables = pWebPr->
mbHtmlTables ? lclBuildWebQueryTables( pWebPr->
maTables ) :
"HTML_all";
244 if( aTables.isEmpty() )
250 Reference< XAreaLinks > xAreaLinks( aDocProps.
getAnyProperty( PROP_AreaLinks ), UNO_QUERY_THROW );
252 static const OUStringLiteral aFilterName =
u"calc_HTML_WebQuery";
253 xAreaLinks->insertAtPosition( aDestPos, aFileUrl, aTables, aFilterName,
"" );
255 sal_Int32 nRefreshPeriod = xConnection->getModel().mnInterval * 60;
256 if( nRefreshPeriod > 0 )
258 PropertySet aPropSet( lclFindAreaLink( xAreaLinks, aDestRange.
aStart, aFileUrl, aTables, aFilterName,
u"" ) );
259 aPropSet.
setProperty( PROP_RefreshPeriod, nRefreshPeriod );
void finalizeImport()
Inserts a web query into the sheet.
OptValue< bool > getBool(sal_Int32 nAttrToken) const
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.
QueryTableVector maQueryTables
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.
#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.
::oox::core::FilterBase & getBaseFilter() const
Returns the base filter object (base class of all filters).
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.
QueryTableBuffer(const WorksheetHelper &rHelper)
sal_Int32 mnConnId
Defined name containing the target cell range.
bool mbBackground
True = refreshing data disabled.
bool mbFirstBackground
True = refresh asynchronously.
Special properties for data connections representing web queries.
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.
ConnectionsBuffer & getConnections() const
Returns the collection of external data connections.
OUString getAbsoluteUrl(const OUString &rUrl) const
bool mbDisableEdit
True = remove querried data before saving.
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.
bool m_bDetectedRangeSegmentation false