Product Structure Export to Excel

cadjett · ‎Sep 26, 2010

I might have added to a thread about this, but when not sure, try again.

Even this one has ptc support & R&D stumped, although you would think it be easy & OOTB.

From a wtpart detail/info page, you can select actions/export & when app opens, you can enter a xls file to export to.

But the excel/xls has OOTB attributes columns. You would think there would be a policy, rule, config properties, rbInfo or something file that specifies what the OOTB attribute columns are to be.

You would think you could then edit that file to allow you to generate an excel/xls with your own chosen attribute columns. Somewhere the system knows what attributes to report on.

But not only doesn't ptc support or R&B know how to do such a thing, they don't know even know which file controls what attributes are reported for OOTB.

From the Structure Tab & Multi-Level BOM report, you can change/customize the View & chose which attribute columns will show. But you cant do it for the Export to excel area.

Unless some guru here knows the way. Maybe what file controls the OOTB export attributes to report on. Or the method to control Export to Excel output.

Appreciate any info on this area.

L Jett
cadjett@aol.com;datajett@aol.com

AL_ANDERSON · ‎Sep 27, 2010

We have two approaches for this.

1) We have a SQL program that runs each night with a hard-coded config
spec to generate a cached multilevel BOM that allows other applications,
including reports that output Excel formats, to get multi-level BOM
outputs quickly. These reports also blend select information from our ERP
system for each part, as well. Data is up to 24 hours old and cannot take
any user-driven config spec.

2) We have an Excel macro that when a BOM is pasted in as text from the
OOTB HTML BOM Report will parse the information into a tab that is a
multi-level Excel version of the BOM, complete with intenture levels. It
is a little unfriendly to run the report, right-click, select all, copy.
go to Excel, right-click, paste special as Text, and then click a button
to run a macro - but it works. It isn't something I can share directly,
but he approach is fairly low tech, and you can do it with your own VB
programmer.

Al Anderson

Lawrence Jett <cadjett@aol.com>
09/26/2010 12:40 PM
Please respond to
Lawrence Jett <cadjett@aol.com>

To
-
cc

Subject
[solutions] - Product Structure Export to Excel

Caterpillar: Confidential Green Retain Until: 10/26/2010

Product Structure Export to Excel
I might have added to a thread about this, but when not sure, try again.
Even this one has ptc support & R&D stumped, although you would think it
be easy & OOTB.
From a wtpart detail/info page, you can select actions/export & when app
opens, you can enter a xls file to export to.
But the excel/xls has OOTB attributes columns. You would think there would
be a policy, rule, config properties, rbInfo or something file that
specifies what the OOTB attribute columns are to be.
You would think you could then edit that file to allow you to generate an
excel/xls with your own chosen attribute columns. Somewhere the system
knows what attributes to report on.
But not only doesn't ptc support or R&B know how to do such a thing, they
don't know even know which file controls what attributes are reported for
OOTB.
From the Structure Tab & Multi-Level BOM report, you can change/customize
the View & chose which attribute columns will show. But you cant do it for
the Export to excel area.
Unless some guru here knows the way. Maybe what file controls the OOTB
export attributes to report on. Or the method to control Export to Excel
output.
Appreciate any info on this area.
L Jett
cadjett@aol.com;datajett@aol.com

Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription
Use of this email content is governed by the terms of service at:

LiuLiang · ‎Sep 27, 2010

The Product Structure Export to Excel is a layer of wrapper of standard IXB import / export code. It gets the values from object's xml files that are in the exported jar file then transforms them into a xls excel spreadsheet. So basically all the values in objects' xml files in exported jar file (say all IBAs) are able to display in the exported excel somehow. But I don't see that there are configuration files or something that you can easily configured to achieve your goal. The OOTB exported columns/attributes are handled and hardcoded in %WC_HOME%\src\com\ptc\windchill\ixb\exporter\Xml2Xls.java so you will have to change Xml2Xls.java to add the extra attributes that you are trying to export. You will have to extract the Xml2Xls.class from %WC_HOME%\codebase\wncWeb.jar file first then de-compile the %WC_HOME%\codebase\com\ptc\windchill\ixb\exporter\Xml2Xls.class to get the Xml2Xls.java source code to modify on.

Attached are examples of adding some IBAs in Xml2Xls.java for exporting extra attributes into excel.

Also you can modify other related files to this piece of export to excel function as you wish :

%WC_HOME%\codebase\wt\ixb\clientAccess\StandardIXBService.class (Xml2Xls is called from here, you can call your own user friendly class here than customizing Xml2Xls)

%WC_HOME%\codebase\registry\ixb\object_set_handlers\product_struct_excel.xml (objects navigation rules are defined here)

%WC_HOME%\src\com\ptc\windchill\ixb\exporter\exportResource.rbInfo (error messages are defined here)

In Reply to Lawrence Jett:

Product Structure Export to Excel
I might have added to a thread about this, but when not sure, try again.
Even this one has ptc support & R&D stumped, although you would think it be easy & OOTB.
From a wtpart detail/info page, you can select actions/export & when app opens, you can enter a xls file to export to.
But the excel/xls has OOTB attributes columns. You would think there would be a policy, rule, config properties, rbInfo or something file that specifies what the OOTB attribute columns are to be.
You would think you could then edit that file to allow you to generate an excel/xls with your own chosen attribute columns. Somewhere the system knows what attributes to report on.
But not only doesn't ptc support or R&B know how to do such a thing, they don't know even know which file controls what attributes are reported for OOTB.
From the Structure Tab & Multi-Level BOM report, you can change/customize the View & chose which attribute columns will show. But you cant do it for the Export to excel area.
Unless some guru here knows the way. Maybe what file controls the OOTB export attributes to report on. Or the method to control Export to Excel output.
Appreciate any info on this area.
L Jett
cadjett@aol.com;datajett@aol.com

rdayalu · ‎Sep 27, 2010

Hello,

We use Windchill PDMLink 9.1 M010. We also felt challenged to send an
export of the Windchill BOM into an excel format easily.

The Multi-level export option does send out all the custom attributes
into an excel file.

It is a work-around that we are currently using to export BOM data into
an excel file. (to avoid customization)

(Please see attached) I created a Best Practice Guide to walk through
the steps. (I think this should really be a click of a button on
Windchill Product Structure interface and not so laborious)

Regards,
Rekha

ejhlti · ‎Sep 29, 2010

On page 4 of Rekha's presentation, the BOM report has full windows functionality like file > save as etc. On 9.0 all the BOM reports are justa basic popup with no options. Is the BOM report in Rekha's presentation added Windchill functionality or is it a newer version of Windchill ?

MikeLockwood · ‎Sep 29, 2010

Rekha, we're interested in your "magic" here as well.

rdayalu · ‎Sep 29, 2010

Mike, Ed,

We are using Windchill PDMLink 9.1 M010. It's Out-of-the-box
functionality in 9.1 (nothing special or magical really! 🙂 )

The IE browser version we use are both 6.0 and 7.0

Regards,

Rekha

MikeFoster · ‎Sep 29, 2010

In Intralink 9.1 M050 I do this as follows: (very similar to Rekha's process)

1. Start at the information page of an assembly
2. Select "Structure" from the left end of the middle menu bar
3. Select your desired table view (include all the attributes you want to see)
4. Set Configuration (if necessary)
5. Push the "Multi Level Report" button
6. Push the "Save" button accepting the default .htm file format
7. In Excel (2007) open a new worksheet
8. Select the "Data" tab on the ribbon
9. Push the "From Web" button (second from far left)
10. Paste the name of .htm file with full path; for example: C:\BOM data\BOM.htm
11. Push the "Go" button
12. At the bottom of the displayed web page push the "Import" button
13. Push the "OK" button in the "Import Data" dialog

Once you do a couple times it goes a lot faster than this looks.

Thanks,

Mike Foster
ATK

From: Dayalu, Rekha [

avillanueva · ‎Sep 29, 2010

Something that I've done is click Control-A and copy the entire report to Excel, paste. This will copy HTML formatting. From Excel, Select all rows, copy and paste to new sheet but paste special (only values). That strips out the HTML formatting.

kpietila · ‎Oct 06, 2010

Another way to do this... Follow Steps 1-6 as explained by Mike.

1. Start at the information page of an assembly
2. Select "Structure" from the left end of the middle menu bar
3. Select your desired table view (include all the attributes you want to see)
4. Set Configuration (if necessary)
5. Push the "Multi Level Report" button
6. Push the "Save" button accepting the default .htm file format

7. Highlight all of the information on the "Multi Level Report" that you want to copy using your LMB.
8. Select copy using your RMB
9. In Excel (2007) open a new worksheet and RMB paste the information into the spreadsheet.

Kevin

rleir · ‎Sep 19, 2023

@avillanueva @MikeFoster @rdayalu @kpietila @cadjett

team, It's 2023 and I am still looking for a way to automate the generation of multi-level BOM's. Even with a python script it looks difficult. Here is a sequence from the Apache log when doing reports->Export to xls report:

 "POST /Windchill/gwt/com.ptc.windchill.wncgwt.WncGWT/servlet/ops HTTP/1.1" 332
 "POST /Windchill/gwt/com.ptc.windchill.wncgwt.WncGWT/servlet/ops HTTP/1.1" 724
 "POST /Windchill/gwt/com.ptc.windchill.wncgwt.WncGWT/servlet/ops HTTP/1.1" 238
 "POST /Windchill/gwt/com.ptc.windchill.wncgwt.WncGWT/servlet/ops HTTP/1.1" 418
 "GET /Windchill/servlet/WindchillAuthGW/com.ptc.netmarkets.model.StandardNmObjectService/redirectRecipient/StructureReport~4199999999999design.xls?attach=true&sessId=H5qXSx999999999W2as.dxd&u8=1 HTTP/1.1" 40996

I need to know what is sent in those POSTs so I can automate this. Without the use of Burp Suite this is opaque.

RobotFramework, anyone?

HelesicPetr · ‎Sep 19, 2023

Oh @rleir

This is GWT google widget toolkit, and it is retally hardcoded.

I would say that you need to develop own multilevel report function to do so.

From my point of view it is easier then to try what GWT function does.

PetrH

avillanueva · ‎Sep 19, 2023

Where are you sending the data too?

HelesicPetr · ‎Sep 19, 2023

@avillanueva

I guess that the POSTs are from apache log, when the report is activated >D.

PetrH

avillanueva · ‎Sep 19, 2023

@HelesicPetr , I was just asking the use case. Where are they trying to get the multi-level BOMs exported to? Its a complicated problem since there are many variables like the config spec filter.

rleir · ‎Sep 20, 2023

Just need to provide the mlBOMs daily to colleagues who don't want to do the export process manually.