Friday, September 21, 2012

Java code for converting Excel file into XML file

package adf;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import java.util.Iterator;


import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.*;

public class Excel_To_Xml {
   public Excel_To_Xml() {
    super();
  }

  public static void main(String[] args) {

        Excel_To_Xml xml = new Excel_To_Xml();
     
      String filename = "file path";  //Ex:D:/sample.xsl
     xml.readAndConvertToXML(filename);
            
    }
public String readAndConvertToXML(String filepath) {

    FileInputStream fis = null;
    String out =
      "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n";

    try {

      fis = new FileInputStream(filepath);
      HSSFWorkbook workbook = new HSSFWorkbook(fis);
      HSSFSheet sheet = workbook.getSheetAt(0);
      Iterator rows = sheet.rowIterator();

      System.out.println(logger.getName());
      int j = 0;

      int k = 0;
      out += "<records>\n";
      while (rows.hasNext()) {

        HSSFRow row = (HSSFRow)rows.next();
        k = row.getPhysicalNumberOfCells();
      
        Iterator cells = row.cellIterator();
        System.out.println(k);
        String arr[] =
        { "a", "b", "c", "d", "e", "f" };
        int ca = 0, cb = 0;
        out += "\t<record>\n";
       
        for (j = 1; j <=k; j++) {
          cb = (j) % 6;
          ca = (j) / 6;
          String node = "";
          if (ca == 0)
            node = arr[cb - 1];
          else
            node = arr[ca - 1] + "" + arr[cb];
         
          out +=
              "\t\t<" + node + ">" + row.getCell(j - 1,Row.CREATE_NULL_AS_BLANK).toString() +
              "<\\" + node + ">\n";
        }
        out += "\t<\\record>\n";
     
      }
      out += "<\\records>";
      System.out.println(out);

    } catch (Exception e) {
      logger.log(Level.SEVERE, "File not uploaded"); //e.printStackTrace();
    }
    logger.fine("done");
    return out;
  }
  }