• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

xls to xml conversion application

Hi,

I have a xls file with two sheets namely sheet1 and sheet2

 sheet 1 having 3 columns say

1. product 2. total 3. cost
has about 100 records


 sheet2 also  having same 3 columns say

1. product 2. total 3. cost
has about 10 records.

I need to convert above xls to xml with similar structure as follows


<sheet1>
 <product1>
    <product></product>
    <total></total>
    <cost></cost>
 </product1>
 <product2>
    <product></product>
    <total></total>
    <cost></cost>
 </product2>
.....more products
</sheet1>

<sheet2>
 <product1>
    <product></product>
    <total></total>
    <cost></cost>
 </product1>
 <product2>
    <product></product>
    <total></total>
    <cost></cost>
 </product2>
.....more products
</sheet2>

what is the best way to achieve it. Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
0
gudii9
Asked:
gudii9
  • 5
1 Solution
 
yatsCommented:
add the jxl.jar in classpath.
To read the Excel use the below code:
package xmlparser;

import java.io.File;
import jxl.Sheet;
import jxl.Workbook;

public class HelloWorld {
      public static void main(String[] args) {
       File excelFile = new File("Book1.xls");
          if (excelFile.exists()) {
              try {
                  Workbook workbook = Workbook.getWorkbook(excelFile);
                  Sheet sheet = workbook.getSheets()[0];
      
                  for (int row = 0; row < sheet.getRows(); row++) {
                      for (int column = 0; column < sheet.getColumns(); column++) {
                          String content = sheet.getCell(column, row).getContents();
                          System.out.println("content>>>>"+content);
                      }
                  }
              } catch (Exception e) {
                  e.printStackTrace();
              }
      
          } else {
              System.out.println("File does not exist");
          }

      }

}


to write it xml you can refer the below URL:
http://www.mkyong.com/java/how-to-create-xml-file-in-java-dom/
0
 
yatsCommented:
I think the XML you have paste is not valid xml, since in your xml root element is different(sheet1 and sheet2). But XML should always have one root element and inside you can have multiple elements.
You can create something like this <sheet name="1"><product name="1">
0
 
gudii9Author Commented:
>>>

http://www.mkyong.com/java/how-to-create-xml-file-in-java-dom/

I have multiple sheets in xls. How to deal with multiple sheets. How does xml looks like for multiple sheets. Also this example does not talk how to read multiple sheets in xls. Please advise
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
yatsCommented:
you can iterate the below line to work with multiple sheets in one excel
Sheet sheet = workbook.getSheets()[0];

try with the below code:
             
int noOfSheet = 2;
             try {
	            Workbook workbook = Workbook.getWorkbook(excelFile);
	            for(int i=0;i<noOfSheet;i++){
		            Sheet sheet = workbook.getSheets()[i];
		
		            for (int row = 0; row < sheet.getRows(); row++) {
		                for (int column = 0; column < sheet.getColumns(); column++) {
		                    String content = sheet.getCell(column, row).getContents();
		                    System.out.println("content>>>>"+content);
		                }
		            }
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        }

Open in new window

0
 
yatsCommented:
In XML there should be only one root element.
You can prepare your xml something like this:
<Excel>
      <sheet name="1">
            <products name="1">
                  <product></product>
                  <total></total>
                  <cost></cost>
            </products>
            <products name="2">
                  <product></product>
                  <total></total>
                  <cost></cost>
            </products>
      </sheet>
      <sheet name="2">
            <products name="1">
                  <product></product>
                  <total></total>
                  <cost></cost>
            </products>
            <products name="2">
                  <product></product>
                  <total></total>
                  <cost></cost>
            </products>
      </sheet>
</Excel>
0
 
yatsCommented:
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now