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

How to retain the space of the td after hidding it in Jquery

Integrating Struts2 with Spring Security using Custom Login Form