Open/Launch an XML File in Excel from Java Application

**Redefine12** · 07-17-2012, 11:19 PM

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();
		}
	}

**jhuber151** · 07-18-2012, 03:49 PM

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"));

**Tolls** · 07-18-2012, 04:43 PM

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.

**jhuber151** · 07-18-2012, 04:58 PM

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.

**Tolls** · 07-18-2012, 06:05 PM

You can probably fire excel off on the command line...maybe.
You used to be able to anyway.

**Redefine12** · 07-18-2012, 09:17 PM

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;
		}

**Tolls** · 07-19-2012, 09:49 AM

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?

**Redefine12** · 07-19-2012, 09:53 PM

Originally Posted by Tolls

...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.

**Redefine12** · 07-20-2012, 12:55 AM

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.