L
Luc Alquier
Client Context:
WinXP: 5.1.2600 Service Pack 1 Nu 2600
IE: 6.0.2800.1106 SP1
Excel 10.5815.4219 SP2
Server Context:
Win2000 Server+IIS
How to reproduce:
1/ From IE 6.0 Click an hyperlink (with query string part)
on an asp that returns an excel file containing a
PivotTable based on cell range.
2/ Then click on refresh from the PivotTable ToolBar.
This work fine with XL2000.
May be usefull
Asp Code:
<%@ Language=VBScript %>
<%
Response.Buffer = false
Response.ContentType = "application/vnd.ms-excel"
Const adTypeBinary = 1
Dim strFilePath
strFilePath = "D:\classeur2.xls"
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
%>
And here is the excel file:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-
comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-
comfficeffice">
<Author> </Author>
<LastAuthor> </LastAuthor>
<Created>2003-12-04T14:48:47Z</Created>
<LastSaved>2003-12-04T15:27:52Z</LastSaved>
<Company>DATASET</Company>
<Version>10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-
comfficeffice">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-
comffice:excel">
<WindowHeight>8070</WindowHeight>
<WindowWidth>13605</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>1230</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s23">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s24">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s25">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
<Style ss:ID="s26">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
</Styles>
<Names>
<NamedRange ss:Name="a" ss:RefersTo="=Feuil1!R1C1:R2C2"/>
</Names>
<Worksheet ss:Name="Feuil1">
<Table ss:ExpandedColumnCount="5"
ss:ExpandedRowCount="7" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60">
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="24"/>
<Column ss:AutoFitWidth="0" ss:Width="20.25"/>
<Row>
<Cell><Data ss:Type="String">a</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="String">b</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">1</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="Number">2</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row ss:Index="4">
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">Somme a</Data></Cell>
<Cell ss:StyleID="s23"/>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">b</Data></Cell>
<Cell ss:StyleID="s23"><Data
ss:Type="String">Somme</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="Number">2</Data></Cell>
<Cell ss:StyleID="s26"><Data
ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s24"><Data
ss:Type="String">Total</Data></Cell>
<Cell ss:StyleID="s25"><Data
ss:Type="Number">1</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<DisplayFormulas/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>4</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PivotTable xmlns="urn:schemas-microsoft-
comffice:excel">
<Name>pt1</Name>
<HideTotalsAnnotation/>
<Location>R4C4:R7C5</Location>
<DefaultVersion>0</DefaultVersion>
<PivotField>
<Name>a</Name>
<DataType>Integer</DataType>
</PivotField>
<PivotField>
<Name>b</Name>
<Orientation>Row</Orientation>
<Position>1</Position>
<DataType>Integer</DataType>
<PivotItem>
<Name>2</Name>
</PivotItem>
</PivotField>
<PivotField>
<DataField/>
<Name>Données</Name>
<Orientation>Row</Orientation>
<Position>-1</Position>
</PivotField>
<PivotField>
<Name>Somme a</Name>
<ParentField>a</ParentField>
<Orientation>Data</Orientation>
<Position>1</Position>
</PivotField>
<PTLineItems>
<PTLineItem>
<Item>0</Item>
</PTLineItem>
<PTLineItem>
<ItemType>Grand</ItemType>
<Item>0</Item>
</PTLineItem>
</PTLineItems>
<PTSource>
<CacheIndex>1</CacheIndex>
<RefreshName> </RefreshName>
<RefreshDate>2003-12-04T16:20:41</RefreshDate>
<RefreshDateCopy>2003-12-04T16:20:41</RefreshDateCopy>
<ConsolidationReference>
<FileName>a.xml</FileName>
<Name>a</Name>
</ConsolidationReference>
</PTSource>
</PivotTable>
</Worksheet>
<Worksheet ss:Name="Feuil2">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Feuil3">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<PivotCache xmlns="urn:schemas-microsoft-
comffice:excel">
<CacheIndex>1</CacheIndex>
<Schema s:id="RowsetSchema" xmlns="uuid:BDC6E3F0-6DA3-
11d1-A2A3-00AA00C14882">
<ElementType s:name="row" s:content="eltOnly">
<attribute s:type="Col1"/>
<attribute s:type="Col2"/>
<extends s:type="rs:rowbase"/>
</ElementType>
<AttributeType s:name="Col1" rs:name="a">
<datatype dt:type="int"/>
</AttributeType>
<AttributeType s:name="Col2" rs:name="b">
<datatype dt:type="int"/>
</AttributeType>
</Schema>
<data xmlns="urn:schemas-microsoft-com:rowset">
<row Col1="1" Col2="2" xmlns="#RowsetSchema"/>
</data>
</PivotCache>
</Workbook>
WinXP: 5.1.2600 Service Pack 1 Nu 2600
IE: 6.0.2800.1106 SP1
Excel 10.5815.4219 SP2
Server Context:
Win2000 Server+IIS
How to reproduce:
1/ From IE 6.0 Click an hyperlink (with query string part)
on an asp that returns an excel file containing a
PivotTable based on cell range.
2/ Then click on refresh from the PivotTable ToolBar.
This work fine with XL2000.
May be usefull
Asp Code:
<%@ Language=VBScript %>
<%
Response.Buffer = false
Response.ContentType = "application/vnd.ms-excel"
Const adTypeBinary = 1
Dim strFilePath
strFilePath = "D:\classeur2.xls"
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
%>
And here is the excel file:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-
comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-
comfficeffice">
<Author> </Author>
<LastAuthor> </LastAuthor>
<Created>2003-12-04T14:48:47Z</Created>
<LastSaved>2003-12-04T15:27:52Z</LastSaved>
<Company>DATASET</Company>
<Version>10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-
comfficeffice">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-
comffice:excel">
<WindowHeight>8070</WindowHeight>
<WindowWidth>13605</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>1230</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s23">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s24">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s25">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
<Style ss:ID="s26">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
</Styles>
<Names>
<NamedRange ss:Name="a" ss:RefersTo="=Feuil1!R1C1:R2C2"/>
</Names>
<Worksheet ss:Name="Feuil1">
<Table ss:ExpandedColumnCount="5"
ss:ExpandedRowCount="7" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60">
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="24"/>
<Column ss:AutoFitWidth="0" ss:Width="20.25"/>
<Row>
<Cell><Data ss:Type="String">a</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="String">b</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">1</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="Number">2</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row ss:Index="4">
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">Somme a</Data></Cell>
<Cell ss:StyleID="s23"/>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">b</Data></Cell>
<Cell ss:StyleID="s23"><Data
ss:Type="String">Somme</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="Number">2</Data></Cell>
<Cell ss:StyleID="s26"><Data
ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s24"><Data
ss:Type="String">Total</Data></Cell>
<Cell ss:StyleID="s25"><Data
ss:Type="Number">1</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<DisplayFormulas/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>4</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PivotTable xmlns="urn:schemas-microsoft-
comffice:excel">
<Name>pt1</Name>
<HideTotalsAnnotation/>
<Location>R4C4:R7C5</Location>
<DefaultVersion>0</DefaultVersion>
<PivotField>
<Name>a</Name>
<DataType>Integer</DataType>
</PivotField>
<PivotField>
<Name>b</Name>
<Orientation>Row</Orientation>
<Position>1</Position>
<DataType>Integer</DataType>
<PivotItem>
<Name>2</Name>
</PivotItem>
</PivotField>
<PivotField>
<DataField/>
<Name>Données</Name>
<Orientation>Row</Orientation>
<Position>-1</Position>
</PivotField>
<PivotField>
<Name>Somme a</Name>
<ParentField>a</ParentField>
<Orientation>Data</Orientation>
<Position>1</Position>
</PivotField>
<PTLineItems>
<PTLineItem>
<Item>0</Item>
</PTLineItem>
<PTLineItem>
<ItemType>Grand</ItemType>
<Item>0</Item>
</PTLineItem>
</PTLineItems>
<PTSource>
<CacheIndex>1</CacheIndex>
<RefreshName> </RefreshName>
<RefreshDate>2003-12-04T16:20:41</RefreshDate>
<RefreshDateCopy>2003-12-04T16:20:41</RefreshDateCopy>
<ConsolidationReference>
<FileName>a.xml</FileName>
<Name>a</Name>
</ConsolidationReference>
</PTSource>
</PivotTable>
</Worksheet>
<Worksheet ss:Name="Feuil2">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Feuil3">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<PivotCache xmlns="urn:schemas-microsoft-
comffice:excel">
<CacheIndex>1</CacheIndex>
<Schema s:id="RowsetSchema" xmlns="uuid:BDC6E3F0-6DA3-
11d1-A2A3-00AA00C14882">
<ElementType s:name="row" s:content="eltOnly">
<attribute s:type="Col1"/>
<attribute s:type="Col2"/>
<extends s:type="rs:rowbase"/>
</ElementType>
<AttributeType s:name="Col1" rs:name="a">
<datatype dt:type="int"/>
</AttributeType>
<AttributeType s:name="Col2" rs:name="b">
<datatype dt:type="int"/>
</AttributeType>
</Schema>
<data xmlns="urn:schemas-microsoft-com:rowset">
<row Col1="1" Col2="2" xmlns="#RowsetSchema"/>
</data>
</PivotCache>
</Workbook>