Wednesday, 5 September 2012

Reading an Excel and converting to key-value pair

Use Jxl jar file and File Util Jar file to run the following program
Create excel sheet in the following manner and read it and convert to string key value pair file




































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

import org.apache.commons.io.FileUtils;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

public class ReadExample {

    public static void main(String[] args) throws BiffException, IOException {
        String filename = "C:/Documents and Settings/sample/Desktop/Example/Book1.xls";
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("CP1252");

        Workbook workbook = Workbook.getWorkbook(new File(filename), ws);

        Sheet s = workbook.getSheet(0);
        System.out.println("Sheet Content::" + s.getName());
        System.out.println("Rows are " + s.getRows());
        // fetching heading

        int k = 0;
        // int j =1;
        String heading = getheading(s);
        StringBuffer buffer = new StringBuffer();
        for (int j = 1; j < s.getColumns(); j++) {
            buffer = new StringBuffer();
            Cell lan = null;
            for (int i = 1; i < s.getRows(); i++) {

                lan = s.getCell(j, 0);
                Cell key = s.getCell(0, i);
                Cell value = s.getCell(j, i);
                System.out.println("key value is " + key.getContents() + "="
                        + value.getContents());

                buffer.append(key.getContents() + "=" + value.getContents()
                        + "\n");

            }

            FileUtils.write(
                    new File(
                            "C:/Documents and Settings/krn4bmh/Desktop/Example/"
                                    + lan.getContents() + "/name.strings"),
                    buffer.toString(), "UTF-8");

        }

    }

    private static String getheading(Sheet s) {
        // TODO Auto-generated method stub
        // Cell lan = s.getCell(1,0);
        Cell lan = null;
        for (int col = 1; col < s.getColumns(); col++) {
            lan = s.getCell(col, 0);
            System.out.println("heading is " + lan.getContents());
        }
        return lan.toString();

    }

}

No comments:

Post a Comment

Pass a HashMap from Angular Client to Spring boot API

This example is for the case where fileData is very huge and in json format   let map = new Map<string, string>()      map.set(this.ge...