Read an Excel file and return a string-Java

package beans;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

/**
*
* @author anjana
*/
import java.io.*;
import jxl.*;
import java.util.*;

import java.sql.*;

public class ConvertCSV {

public String str = "";
String shippingcompanyID = "1";
String Excelfilnename = "";

public String getExcelfilnename() {
return Excelfilnename;
}

public void setExcelfilnename(String Excelfilnename) {
this.Excelfilnename = Excelfilnename;
}

public String getShippingcompanyID() {
return shippingcompanyID;
}

public void setShippingcompanyID(String shippingcompanyID) {
this.shippingcompanyID = shippingcompanyID;
}

// public void readExcelFile() {
public static void main(String args[]){
try {
String str = "";
String shippingcompanyID = "1";
//File to store data in form of CSV
File f = new File("D://TestConsolidated.csv");
OutputStream os = (OutputStream) new FileOutputStream(f);
String encoding = "UTF8";
OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
BufferedWriter bw = new BufferedWriter(osw);
//Excel document to be imported
// String filename = getExcelfilnename();
String filename = "D://TestConsolidated.xls";
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
Workbook w = Workbook.getWorkbook(new File(filename), ws);

// Gets the sheets from workbook
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
Sheet s = w.getSheet(sheet);

bw.write(s.getName());
bw.newLine();

Cell[] row = null;

// Gets the cells from sheet
for (int i = 0; i < s.getRows(); i++) {
row = s.getRow(i);

if (row.length > 0) {
bw.write(row[0].getContents());
str = row[0].getContents();
for (int j = 1; j < 4; j++) {
// bw.write(',');
bw.write(row[j].getContents());

str = str + "," + "'"+row[j].getContents()+"'";

//System.out.println(","+row[j].getContents()+";");
}
}
str = str + "," + shippingcompanyID;
str="insert into tblshippingcost(Pincodes,Weight1,Weight2,Weight3,ShippingCompanyID) values(" + str + ");";
System.out.println("insert into tblshippingcost(Pincodes,Weight1,Weight2,Weight3,ShippingCompanyID) values(" + str + ");");
if(!str.equals("insert into tblshippingcost(Pincodes,Weight1,Weight2,Weight3,ShippingCompanyID) values(,'','','',2););")){
Connection con=null;
Statement pst=null;
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://192.168.10.6/greenmelaadbc?user=root&password=root");
pst=con.createStatement();
pst.executeUpdate(str);
}
bw.newLine();
}
}
bw.flush();
bw.close();
} catch (UnsupportedEncodingException e) {
System.err.println(e.toString());
} catch (IOException e) {
System.err.println(e.toString());
} catch (Exception e) {
System.err.println(e.toString());
}
}
}



Comments

Popular posts from this blog

defining functions clojure

Integrating Struts2 with Spring Security using Custom Login Form