62,615
社区成员
发帖
与我相关
我的任务
分享
import java.sql.*;
import java.util.*;
import java.io.*;
public class test extends ClassObj{
public static void main(String[] args) throws Exception {
try {
ArrayList<ClassObj> lst = test.getResultLst();
String contents = test.formatLst(lst);
String fileName = "C:\\result.txt";
test.saveFile(fileName, contents);
} catch (Exception e) {
e.printStackTrace();
}
}
static ArrayList<ClassObj> getResultLst() throws Exception {
String url = "jdbc:odbc:study";
String user = "sa";
String password = "123";
ArrayList <ClassObj> lst = new ArrayList <ClassObj>();
Connection con = null;
Connection con2 = null;
Connection con3 = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
String sql = "select 业务量 as A,单价 as B,员工编号 as C from 表1";
String sql2 = "select 业务量 as D,单价 as E,员工编号 as F,地区 as G from 表2";
String sql3 = "select 业务量 as H,单价 as I,员工编号 as J,经理 as K from 表3";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection(url,user,password);
pstmt = con.prepareStatement(sql);
pstmt2 = con.prepareStatement(sql2);
pstmt3 = con.prepareStatement(sql3);
rs = pstmt.executeQuery();
rs2 = pstmt2.executeQuery();
rs3 = pstmt3.executeQuery();
String fa = "组长1";
String fb = "组长2";
String fc = "组长3";
ClassObj c1=null;
while(rs.next()){ //怎样将fa,fb,fc跟相应的rs,rs2,rs3对应循环输出。
c1=new ClassObj(); //c1.setBand(rs.getString("fa"));?????
c1.setBuss(rs.getDouble("A"));
c1.setPrice(rs.getDouble("B"));
c1.setId(rs.getString("C"));
//怎么才能计算合并输出????
//c1.setId(rs.getDouble(A*B));
lst.add(c1);
}
while(rs2.next()){
c1=new ClassObj();
c1.setBuss(rs.getDouble("D"));
c1.setPrice(rs.getDouble("E"));
c1.setId(rs.getString("F"));
c1.setLand(rs.getString("G"));
lst.add(c1);
}
while(rs3.next()){
c1=new ClassObj();
c1.setBuss(rs.getDouble("H"));
c1.setPrice(rs.getDouble("I"));
c1.setId(rs.getString("J"));
c1.setMan(rs.getString("K"));
lst.add(c1);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
}catch(Exception e){}
}
}
static void saveFile(String fileName, String contents)
throws IOException {
File f = new File(fileName);
if (f.exists()){
throw new IOException("file exists...");
}
if (!f.createNewFile()) {
throw new IOException("file create failure...");
}
try {
BufferedWriter output = new BufferedWriter(new FileWriter(f));
output.write(contents);
output.close();
} catch (Exception e) {
throw new IOException(e);
}
}
static String formatLst(ArrayList <ClassObj> lst){
String contents = ""; //如何在这先循环第一行标题??
for(int i=0;i <lst.size();i++){
contents +=lst.get(i).getBand+"\t"+lst.get(i).getBuss+"\t"+lst.get(i).getPrice+"\t"+lst.get(i).getId+"\t"+lst.get(i).getLand+lst.get(i).getMan+"\t"+lst.get(i).getSum+"\n";
}
return contents;
}
}
class ClassObj {
private String Band;
private Double Buss;
private Double Price;
private String Id;
private String Land;
private String Man;
private Double Sum;
//get..set
public String toString() {
return "ClassObj{classBand=" + this.classBand +",classId="
+ this.classId + ",classGroup="
+ this.classGroup + ",classSum="
+ this.classSum + ",classNo=" + this.classNo +"}";
}
}
pstmt.setString(1, c[0]);
pstmt.setString(2, c[1]);
pstmt.setInt(3,Integer.parseInt(c[2]));//如果这项是合计,没有小数的情况是否这样写??
//如果小数,可用setBigDecimal(new BigDecimal(c[2]));具体应该怎么写??这里没明白。
while ((s = br.readLine()) != null) {
if (s.indexOf("合计") > 0) {
continue;
}
String[] c = s.split("\t");
printArray(c);
if (c.length == 3) {
//加入批量参数
pstmt.setString(1, c[0]);
pstmt.setString(2, c[1]);
setBigDecimal(new BigDecimal(c[2])); //是这个意思吗??
pstmt.addBatch();
}
}
class .....
{
public static void writeExcel(String efilePath)throws IOException{
String url = "jdbc:odbc:study11";
String user = "WSuser";
String password = "main@ws.com";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select 业务量 as A,单价 as B,员工编号 as C from 表1";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection(url,user,password);
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
OutputStream outf = new FileOutputStream(efilePath);
WritableWorkbook wwb = Workbook.createWorkbook(outf);
WritableSheet ws = wwb.createSheet("sheettest", 0);
WritableFont font1 = new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD,false,UnderlineStyle.DOUBLE);
WritableCellFormat format1=new WritableCellFormat(font1);
format1.setAlignment(jxl.format.Alignment.CENTRE);
format1.setBackground(Colour.PINK);
int i=0;
int j=0;
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k,0,rs.getMetaData().getColumnName(k+1),format1));
}
while(rs.next()){
System.out.println(rs.getMetaData().getColumnCount());
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
ws.addCell(new Label(k,j+i+1,rs.getString(k+1)));
}
i++;
}
wwb.write();
wwb.close();
} catch (Exception e) {
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
try {
JExcelOperate.writeExcel("C:\\new_excel.xls");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args)
{
.........
}
.........
String sql = "select 单价 as A,业务量 as B,员工编号 as C from 表1";
......
String fa = "";
ClassObj c1=null;
while(rs.next()){
fa = rs.getString("A");
c1=new ClassObj();
if(fa.indexOf(";") != -1){
double sum = 0;
String[] temp = fa.split(";");
for(int i = 0 ; i < temp.length; i ++){
sum += getPrice(temp[i]);
}
c1.setClassId(sum);
}else {
c1.setClassId(getPrice(fa));
}
c1.setClassGroup(rs.getString("B"));
c1.setClassNo(rs.getString("C"));
lst.add(c1);
}
。。。。。。
private static Map<String,Double> price = new HashMap<String,Double>(0);
static {
price.put("苹果", 1d);
price.put("桔子", 3d);
price.put("香蕉", 2.5);
price.put("西瓜", 1.6);
}
private static double getPrice(String name){
Double d = price.get(name);
return d == null ? 0 : d.doubleValue();
}
。。。。。。
static String formatLst(ArrayList<ClassObj> lst) {
String contents = "";
String total = "";
for (int i = 0; i < lst.size(); i++) {
Double hell=lst.get(i).getClassId();//从这开始就范糊涂了,
int song=lst.get(i).getClassGroup();
total = String.valueOf((hell).doubleValue*Integer.parseInt(song)); //???????帮忙解决
contents += lst.get(i).getClassId() +"\t"+lst.get(i).getClassGroup()
+"\t"+ lst.get(i).getClassNo() +"\n";
}
return contents;
}