java 生成excel

java 生成excel （轉）
关键字: java 生成excel
1.类文件makexls
package com;
import java.io.File;
import java.sql.*;
import java.util.Calendar;
import com.custom.util.ConnectionCustom;

import jxl.Workbook;
import jxl.write.*;

// Referenced classes of package com:
// connection

public class makexls
{

public makexls()
{
DBconn = new ConnectionCustom();
conn = DBconn.getConnection();
stmt = null;
stmt1 = null;
rs = null;
rs1 = null;
cu = 0;
path1 = "";
bigstr = "";
}

public static String replaceString(String s, String s1, String s2)
{
byte byte0 = -1;
boolean flag = false;
int j = s1.length();
StringBuffer stringbuffer = new StringBuffer();
for(int i = s.indexOf(s1); i != -1; i = s.indexOf(s1))
{
stringbuffer.append(s.substring(0, i));
stringbuffer.append(s2);
s = s.substring(i + j);
}

stringbuffer.append(s);
return stringbuffer.toString();
}

public String nowServerTime()
{
Calendar calendar = Calendar.getInstance();
String s = String.valueOf(calendar.get(1));
String s1 = String.valueOf(calendar.get(2) + 1);
String s2 = String.valueOf(calendar.get(5) + 1);
String s3 = String.valueOf(calendar.get(10));
String s4 = String.valueOf(calendar.get(12));
String s5 = String.valueOf(calendar.get(13));
s1 = Integer.parseInt(s1) > 9 ? s1 : "0" + s1;
s2 = Integer.parseInt(s2) > 9 ? s2 : "0" + s2;
String s6 = String.valueOf((int)(Math.random() * 100000D));
String s7 = "_" + s6 + "_" + s + s1 + s2 + s3 + s4 + s5;
return s7;
}

public String makexls(String s, String s1, String s2, String as[])
{
try
{
String s3 = s;
File file = new File(s3);
String as1[] = file.list();
for(int i = 0; i < as1.length; i++)
if(as1[i].length() >= 21)
{
String s6 = as1[i].substring(13, 21);
Calendar calendar = Calendar.getInstance();
int j = calendar.get(5);
calendar.set(5, j - 3);
int k = calendar.get(1);
int l = calendar.get(2) + 1;
int j1 = calendar.get(5);
String s7 = String.valueOf(k) + (l > 9 ? String.valueOf(l) : "0" + String.valueOf(l)) + (j1 > 9 ? String.valueOf(j1) : "0" + String.valueOf(j1));
if(s6.compareTo(s7) <= 0)
{
File file1 = new File(s3 + "/" + as1[i]);
file1.delete();
}
}

}
catch(Exception exception) { }
String s4 = "";
try
{
Workbook workbook = Workbook.getWorkbook(new File(s1));
path1 = s + "result" + nowServerTime() + ".xls";
WritableWorkbook writableworkbook = Workbook.createWorkbook(new File(path1), workbook);
WritableSheet writablesheet = writableworkbook.getSheet(0);
jxl.write.WritableCell writablecell = writablesheet.getWritableCell(0, 0);
ResultSetMetaData resultsetmetadata = null;
stmt = conn.createStatement();
stmt1 = conn.createStatement();
try
{
//System.out.println("s2==sql==="+s2);
rs = stmt.executeQuery(s2);
if(rs.next())
resultsetmetadata = rs.getMetaData();
//System.out.println("resultsetmetadata========="+resultsetmetadata.getColumnCount());//得到结果集(rs)的结构信息，比如字段数、字段名等。
}
catch(Exception exception2)
{
rs = null;
}
boolean flag = false;
if(rs != null)
{
for(int i1 =1;i1 <= resultsetmetadata.getColumnCount(); i1++)
{
Label label = new Label(i1 - 1, 0, as[i1 - 1]);
writablesheet.addCell(label);
//rs.beforeFirst();
cu = 0;
Label label1;
int j=0;
rs1 = stmt1.executeQuery(s2);
for(; rs1.next(); writablesheet.addCell(label1))
{

//System.out.println(j);
cu = cu + 1;
String s5 = rs1.getString(resultsetmetadata.getColumnName(i1)) == null ? "" : rs1.getString(resultsetmetadata.getColumnName(i1));
s5=replaceString(s5,""","\"");
s5=replaceString(s5,"´","\'");
s5=replaceString(s5,""","\"");
s5=replaceString(s5,"'","\'");
label1 = new Label(i1 - 1, cu, s5);
j++;
}

}

}
rs1.close();
rs.close();
writableworkbook.write();
writableworkbook.close();
workbook.close();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
return path1;
}
public String makexlsWidthConn(String s, String s1, String s2, String as[],Connection conn1)
{
try
{
String s3 = s;
File file = new File(s3);
String as1[] = file.list();
for(int i = 0; i < as1.length; i++)
if(as1[i].length() >= 21)
{
String s6 = as1[i].substring(13, 21);
Calendar calendar = Calendar.getInstance();
int j = calendar.get(5);
calendar.set(5, j - 3);
int k = calendar.get(1);
int l = calendar.get(2) + 1;
int j1 = calendar.get(5);
String s7 = String.valueOf(k) + (l > 9 ? String.valueOf(l) : "0" + String.valueOf(l)) + (j1 > 9 ? String.valueOf(j1) : "0" + String.valueOf(j1));
if(s6.compareTo(s7) <= 0)
{
File file1 = new File(s3 + "/" + as1[i]);
file1.delete();
}
}

}
catch(Exception exception) { }
String s4 = "";
try
{
Workbook workbook = Workbook.getWorkbook(new File(s1));
path1 = s + "result" + nowServerTime() + ".xls";
WritableWorkbook writableworkbook = Workbook.createWorkbook(new File(path1), workbook);
WritableSheet writablesheet = writableworkbook.getSheet(0);
jxl.write.WritableCell writablecell = writablesheet.getWritableCell(0, 0);
ResultSetMetaData resultsetmetadata = null;
stmt = conn1.createStatement();
stmt1 = conn1.createStatement();
try
{
//System.out.println("s2==sql==="+s2);
rs = stmt.executeQuery(s2);
if(rs.next())
resultsetmetadata = rs.getMetaData();
//System.out.println("resultsetmetadata========="+resultsetmetadata.getColumnCount());//得到结果集(rs)的结构信息，比如字段数、字段名等。
}
catch(Exception exception2)
{
rs = null;
}
boolean flag = false;
if(rs != null)
{
for(int i1 =1;i1 <= resultsetmetadata.getColumnCount(); i1++)
{
Label label = new Label(i1 - 1, 0, as[i1 - 1]);
writablesheet.addCell(label);
//rs.beforeFirst();
cu = 0;
Label label1;
int j=0;
rs1 = stmt1.executeQuery(s2);
for(; rs1.next(); writablesheet.addCell(label1))
{

//System.out.println(j);
cu = cu + 1;
String s5 = rs1.getString(resultsetmetadata.getColumnName(i1)) == null ? "" : rs1.getString(resultsetmetadata.getColumnName(i1));
s5=replaceString(s5,""","\"");
s5=replaceString(s5,"´","\'");
s5=replaceString(s5,""","\"");
s5=replaceString(s5,"'","\'");
label1 = new Label(i1 - 1, cu, s5);
j++;
}

}

}
rs1.close();
rs.close();
writableworkbook.write();
writableworkbook.close();
workbook.close();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
return path1;
}

private ConnectionCustom DBconn;
private Connection conn;
Statement stmt;
Statement stmt1;
ResultSet rs;
ResultSet rs1;
int cu;
String path1;
String bigstr;
}


2.调用
String[] listTitle=new String[]{"産品編號","産品版本","客戶産品編號","倉點編號","入倉時間","入倉單號","單重 ","單價","本期結存"," ","0---30天"," ","","31---60天 "," ","","61---90天 "," ","","61---90天"," ","","121---150天 "," ","","151---180天 "," ","","181---210天 "," ","","211---240天"," ",""," 241---270天"," ","","271---300天 "," ","","301---330天 "," ","","331---360天 "," ","","361天以上 "," ","總結遇","總重","總價"};

String modefile="2005031017198.xls";//一个空excel文件
String path3=request.getRealPath("/report/template/");//空excel文件路径
path3=path3+"/";
String mopa=request.getRealPath("/report/report/");//生成excel保存路径
mopa=mopa+"/";
String mfile=path3+modefile;
// pathtemp=makexls(mopa,mfile,sql,listTitle,conn);
pathtemp=makexls(mopa,mfile,sql,listTitle);
if (!"".equals(pathtemp)){
int len=pathtemp.length();
int j=pathtemp.indexOf("result");
pathtemp=pathtemp.substring(j,len);
}