Results 1 to 11 of 11
Like Tree2Likes
  • 1 Post By Tolls
  • 1 Post By Tolls

Thread: Open/Launch an XML File in Excel from Java Application

  1. #1
    Redefine12 is offline Member
    Join Date
    Feb 2012
    Posts
    66
    Rep Power
    0

    Default Open/Launch an XML File in Excel from Java Application

    Hi all,

    I'm using Java to accomplish:

    1. Take row selections from JTable (JCheckBox)
    2. Write .xml file containing data from rows selected
    3. Open .xml file in MS Excel after file is written

    So far, steps 1 and 2 are complete. Now I have an .xml and as stated, am attempting to open the file in MS Excel for the user. (It can be done manually in Excel by File > Open > (path to file) > Open) but I want to do this for the user. I have a current method, but of course this opens the file in default web browser displaying the raw xml code, not in Excel as a spreadsheet. I'll post my mehtod snippet below. Any advice on how to accomplish without a bunch of extra libraries?

    Java Code:
    	public void launchXML(){
    		
    		try {
    			File xFile = null;
    			//File sourceFile2 = null;
    			
    			xFile = new File("C:\\PCQ\\Asset_Export.xml");
    			//sourceFile2 = new File(csvFile.getCanonicalPath().toString());
    			if (xFile.exists()) {
    
    				if (Desktop.isDesktopSupported()) {
    					Desktop.getDesktop().open(xFile);
    				} else {
    					// System.out.println("Awt Desktop is not supported!");
    				}
    
    			} else {
    				//System.out.println("File is not exists!");
    			}
    
    			//System.out.println("Done");
    
    		} catch (Exception ex) {
    			ex.printStackTrace();
    		}
    	}

  2. #2
    jhuber151 is offline Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    This should open the specific file on any platform using the default program in the system.
    Java Code:
    try {
               Process p = Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + "full\\path\\to\\file");
         } catch (IOException e) {
    	// TODO Auto-generated catch block
    	e.printStackTrace();
    	}
    If you wanted to use Desktop API the code would be
    Java Code:
    Desktop dt = Desktop.getDesktop();
    			dt.open(new File("full\\path\\to\\file"));
    Last edited by jhuber151; 07-18-2012 at 04:51 PM.

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default Re: Open/Launch an XML File in Excel from Java Application

    Which is what they're currently doing and, because XML directs to IE generally, it gets opened in IE.
    They want it opened in Excel.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #4
    jhuber151 is offline Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    Thats my fault. I didn't see that mine defaulted to Excel already. But one option you have is to translate the xml into a spreadsheet and then open that in excel
    Book excerpt: Converting XML to spreadsheet, and vice versa - JavaWorld
    This link shows how to do that step by step.
    Or in my case you can just set the default program to excel to have it opened there.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default Re: Open/Launch an XML File in Excel from Java Application

    You can probably fire excel off on the command line...maybe.
    You used to be able to anyway.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    Redefine12 is offline Member
    Join Date
    Feb 2012
    Posts
    66
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    Thanks for the link, jhuber151. I'll take a look and report back if I gain some experience that may be useful to others. After a cursory look, seems like the biggest hurdle will be getting Java to write the <TABLE>...

    EDIT: Darn, on closer look it looks like I need Apache POI HSSF package. Unfortunately, I don't have the ability to use libraries outside the standard Java API due to our enterprise's security posture on approved software.

    What I need now is a functional example of an excel spreadsheet created from xml in Java. I'll go hunting. I have found that I can create an xml file and name it as .xls. The only problem with this being the user has to click through 3 warning popups when the file is opened so a Schema is generated for the interpreted xml table by Excel..

    I'm wondering if there are tags I can add so that when I write to a foo.xml, Excel will see them and open without warnings. For clarity, reportData[][] is a 2d array which stores the selections from the JTable. Anyway, here's my rudimentary snippet for creating the xml body.

    Java Code:
    //create XML			
    	else{		
    			BufferedWriter xmOut = new BufferedWriter(new FileWriter("C:\\PCQ\\Asset_Export.xls"));
    
    			xmOut.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
    			xmOut.newLine();
    			xmOut.write("<?mso-application prgid=\"Excel.Sheet\"?>");
    			xmOut.newLine();
    			xmOut.write("<EXPORT>");
    			xmOut.newLine();
    			 
    				int ROWS2 = reportData.length;
    				int COLS2 = reportData[0].length;
    				
    				for (int L = 0; L < ROWS2; L++) {
    					
    					xmOut.write("<ASSET>");
    					xmOut.newLine();
    					
    					for (int M = 0; M < COLS2; M++) {
    						
    						xmOut.write("<"+colNames[M]+">" + reportData[L][M]+ "</"+colNames[M]+">");
    						xmOut.newLine();
    					}
    					
    					xmOut.write("</ASSET>");
    					xmOut.newLine();
    
    					 }
    				
    				xmOut.write("</EXPORT>");
    				xmOut.newLine();
    				xmOut.close();
    				xmOut = null;
    		}
    Last edited by Redefine12; 07-18-2012 at 10:33 PM.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default Re: Open/Launch an XML File in Excel from Java Application

    I know this won't help much, but I pointblank refuse to rewrite something that someone else has done (especially a long lived project from Apache with a decent track record) just because some twit has decided on a draconian approach to software approvals.

    Anyway, is the idea to produce a pretty output, or is it justa data dump?
    If the latter then why not simply csv it?
    Fubarable likes this.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    Redefine12 is offline Member
    Join Date
    Feb 2012
    Posts
    66
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    Quote Originally Posted by Tolls View Post
    ...I pointblank refuse to rewrite something that someone else has done (especially a long lived project from Apache with a decent track record) just because some twit has decided on a draconian approach to software approvals.

    Anyway, is the idea to produce a pretty output, or is it justa data dump?
    If the latter then why not simply csv it?
    Believe me, I hear you!

    I do have a good lead on this problem, however inflexable, without attempting to re-invent the legacy of above-mentioned Apache API.

    I created a standard .xls file as a template, then did SaveAs > XML Spreadsheet (*.xml). This generates xml code that when 'double-clicked' opens in an Excel spreadsheet with Styles, Data formatting, etc. So I'm having JAVA re-produce/write (new BufferedWriter(new FileWriter(foo.xml) all the .xml data (Schema, Styles, etc etc.) and then appending the
    <Worksheet>
    <Table>
    <Cell><Data>...
    into the body. Main drawback is inflexablilty-- changes to the data format (columns included) demand a re-write of how JAVA outputs the xml data, etc. Again, I know how non-OOP, kludge-ish, and 'hacky' this approach is, but given the 'draconian' limitations I'm under, I'm not so delusional as to think I could re-write anything close to Apache's legacy of code. I'll post my snippet once I get this to work.
    Last edited by Redefine12; 07-20-2012 at 01:06 AM.

  9. #9
    Redefine12 is offline Member
    Join Date
    Feb 2012
    Posts
    66
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    Well, my kludge-tastic implementation of XML Spreadsheet written from JAVA is working. I've satisfied requirement 3 in original post by writing to an .xml that contains all the tags necessary for the file to open in Excel. I would only recommend this approach as a last-ditch in the event that one is limited on both time and ability to import libraries.
    Process/Summary:
    1. Create sample standard .xls including desired format you want to output using sample data.
    2. Export as XML Spreasheet (*.xml)
    3. Use the raw .xml data as template and write it from JAVA Buffered Writer to an .xml file
    4. In the <Table></Table> block of the file use a nested loop to iterate over and write the data selected from the data-source.
    5. Make sure to nest int dataset.length; variables within the code to account for row size. Two places I ran in to needing to do this were:

    a. <Worksheet...<Table ss:ExpandedRowCount = *here*...</Worksheet> as well as
    b. within <x2:MapInfo> . .<x2:Range>*here*</x2:Range> ..</x2:MapInfo>

    I had NEVER worked with XML prior to this and would consider myself a JAVA novice, so it took a while to debug the verbose .xml syntax. The 'snippet' is literally hundreds of lines, again, due to the verbosity of .xml so I won't bog this page down with a bunch of code no one is going to use, but if anyone is in a similar boat please don't hesitate to get at me. Cheers and thanks for the help Tolls and jhuber151.
    Last edited by Redefine12; 07-20-2012 at 02:04 AM.

  10. #10
    ravi321 is offline Member
    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Default Re: Open/Launch an XML File in Excel from Java Application

    HI ,
    i need to export the xml data into xls please send the code......it's urgent

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,225
    Rep Power
    20

    Default Re: Open/Launch an XML File in Excel from Java Application

    Please don't request code here.
    We're not a code factory.
    Also, if you have a problem start your own thread.
    I'm locking this one.
    DarrylBurke likes this.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Cannot launch Java application
    By Coolcambo in forum New To Java
    Replies: 1
    Last Post: 11-24-2011, 03:00 PM
  2. Can I launch a program from a Java application?
    By kemiga in forum New To Java
    Replies: 5
    Last Post: 07-13-2011, 01:05 PM
  3. Java Application fails to launch without error
    By javaguy78 in forum AWT / Swing
    Replies: 9
    Last Post: 11-16-2010, 07:25 PM
  4. Replies: 1
    Last Post: 05-17-2010, 10:03 PM
  5. Replies: 4
    Last Post: 02-01-2010, 12:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •