export PivotTable to static Excel

[Pedro Mendoza]

I am using OWC10 and OWC11 client-side (embedded in an HTML page) and
need to export the PivotTable data to an Excel file without
interactivity (simple static data)

Is there anyway to relate (always client-side) a PivotTable to an
Spreadsheet object and then use the Spreadsheet's CSVData property ?

Any other approach ?

AFAIK, it is possible to use the "Export To Excel" feature of the
PivotTable and then instruct the user to save (using Excel) the
document in CSV format, but I would prefer a "more automatic" solution

Thanks in advance,
Pedro

 

[SonOfXavier]

Here is some code that, if you can sort it out, does copy a static copy of
the OWC10 pivot table, opens a Excel sheet and paste a non-interactive copy
into the excel.

There is some extra code in this code, but if you can sort through it you
should ba able to get what you want.

It is a window VB.net code.

Gord

Private Sub btnExpotToExcel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExpotToExcel.Click

Dim strExcelFile As String = "C:\MyApp\MyExcel.xls"

'You need to clear a certain dimension and then go through that
dimension one by one to
'create the various reports. THe dimension should be flat and have
unique names.

'I am hardcoding this but I could make it so the user can select for
the dimension and field
Dim DimensionName As String
Dim i As Integer
Dim j As Integer
Dim K As Integer

Dim ptMembers As OWC10.PivotMember
DimensionName = "P_item_id"
Dim oChildren As OWC10.PivotMembers

With pt.ActiveView

oChildren =
..FilterAxis.FieldSets(DimensionName).Member.ChildMembers
For K = 0 To oChildren.Count - 1
Dim sMemberCaption As String
Dim sDirSave As String

.FilterAxis.FieldSets(i).AllIncludeExclude =
OWC10.PivotFieldSetAllIncludeExcludeEnum.plAllExclude
'MsgBox(oChildren(K).Caption)
sMemberCaption = oChildren(K).Caption
.FilterAxis.FieldSets(i).Fields(0).IncludedMembers =
..FilterAxis.FieldSets(i).FindMember(sMemberCaption)
If Not IO.File.Exists(strExcelFile) Then
MsgBox("Like, you know, this ain't gonna work for ya. Ya
need a template file named 'C:\MyApp\MyExcel.xls' ")
End If

MyExcel = New Excel.Application

sDirSave = "C:\MyApp\test-" + sMemberCaption + ".xls"


'If MyExcel Is Nothing Then
MyExcel.Visible = True
MyExcel.DisplayAlerts = False

MyExcel.WindowState = Excel.XlWindowState.xlMaximized

Dim strWorSheetName As String = "MyData"
Dim aWorkbook As Excel.Workbook
Dim aWorksheet As Excel.Worksheet

aWorkbook = MyExcel.Workbooks.Open(strExcelFile)
aWorksheet = CType(aWorkbook.Sheets("MyData"),
Excel.Worksheet)
aWorksheet.Activate()

pt.Copy(pt.ActiveView)

aWorksheet.Paste(aWorksheet.Range("a11"))

aWorksheet.UsedRange.Columns.AutoFit()
aWorksheet.UsedRange.Columns.Borders.LineStyle =
Excel.XlLineStyle.xlContinuous
aWorksheet.UsedRange.Columns.Borders.Weight =
Excel.XlBorderWeight.xlMedium

aWorksheet.SaveAs(sDirSave)

MyExcel.Quit()
'End If
Next
End With
End Sub