Q: Converting data in excel file to xml using MS Excel 2007 I am trying to convert excel file directly to XML file. I already have xsd schema files and xml file that I can use to import in excel and then add data to those fields and generate an xml file. 
I am performing this task so that I can reduce extra task of creating a parser for parsing excel file, generating java objects from xml schema files to write data to them and generate xml file out of those object. Basically to avoid marshalling and un-marshalling process.The schema used is a complex schema including 3 other schema files.


*

*Is it possible to use this approach for creating xml directly from excel file.So,far I am not able to make it work. How can we import multiple schemas in MS Excel that refer to each other?

*Alternative approach to this would be marshalling/un-marshalling of objects which is time consuming as I again need to transform that xml file to json data.Ex: JAXB, JiBX Apis.

*Please suggest any alternatives to achieve this goal. For ex:Creating XML using Groovy's MarkupBuilder api.


A: See this tutorial here
That tutorial provides this example
try {
XmlGenner x = XMLGennerSingleton.getXmlGenner();
x.setCustomRows(1000);
x.setSubRootElementName("Student");
x.setProgressBar(jProgressBar1);
x.generateXML_Item("c:/temp/in.xls", "c:/temp/out.xml");

} catch (Exception ex) {
// handler
}

Which generates the xml file for the input xls.

A: Are you able to provide your users a "special" excel file?
If you save your Excel as XML (from within Excel), windows still recognizes it as Excel file and it behaves like an Excel file, but it is XML which you can easily read and modify.
You only have to be carefull with parsing empty cells - they are skipped in the XML format.

A: If you just want to read and write Excel files from a java/groovy application, you might want to use the jexel-api or Apache POI - both projects which have already solved this problem.

A: Use below code to convert your .xls or .xlsx file to xml. 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class ImportDataFromXLStoXML {

public ImportDataFromXLStoXML() {
}

public void displayFromExcel(String xlsPath) {
    InputStream inputStream = null;
    try {
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;

    try {
        // Initializing the XML document
        DocumentBuilderFactory factory = DocumentBuilderFactory
                .newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.newDocument();
        Element rootElement = document.createElement("categories");
        document.appendChild(rootElement);
        fileSystem = new POIFSFileSystem(inputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        Integer count = workBook.getNumberOfSheets();
        ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
        for (int i = 0; i < count; i++) {
            HSSFSheet sheet = workBook.getSheetAt(i);
            Iterator<?> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<?> cells = row.cellIterator();
                ArrayList<String> rowData = new ArrayList<String>();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    System.out.println("String: " + richTextString.getString());
                    rowData.add(richTextString.getString());
                } // end while
                data.add(rowData);
            } // end while
        }

        int numOfProduct = data.size();
        for (int i = 0; i < numOfProduct; i++) {
            Element productElement = document.createElement("categoryName");
            rootElement.appendChild(productElement);

            int index = 0;
            for (String s : data.get(i)) {
                String headerString = data.get(0).get(index);
                if (data.get(0).get(index).equals("image link")) {
                    headerString = "image_link";
                }

                if (data.get(0).get(index).equals("product type")) {
                    headerString = "product_type";
                }

                Element headerElement = document
                        .createElement(headerString);
                productElement.appendChild(headerElement);
                headerElement.appendChild(document.createTextNode(s));
                index++;
            }
        }

        TransformerFactory tFactory = TransformerFactory.newInstance();

        Transformer transformer = tFactory.newTransformer();
        // Add indentation to output
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty(
                "{http://xml.apache.org/xslt}indent-amount", "2");

        DOMSource source = new DOMSource(document);
        StreamResult result = new StreamResult(new File("products.xml"));
        // StreamResult result = new StreamResult(System.out);
        transformer.transform(source, result);

    } catch (IOException e) {
        System.out.println("IOException " + e.getMessage());
    } catch (ParserConfigurationException e) {
        System.out
                .println("ParserConfigurationException " + e.getMessage());
    } catch (TransformerConfigurationException e) {
        System.out.println("TransformerConfigurationException "
                + e.getMessage());
    } catch (TransformerException e) {
        System.out.println("TransformerException " + e.getMessage());
    }
}

public static void main(String[] args) {
    ImportDataFromXLStoXML poiExample = new ImportDataFromXLStoXML();
    String xlsPath = "/home/vpanchal/installApps/workspace/QTOExport/output/Ptest.xls";
    poiExample.displayFromExcel(xlsPath);
}

}
