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,"&quot;","\"");
s5=replaceString(s5,"&acute;","\'");
s5=replaceString(s5,"&#34;","\"");
s5=replaceString(s5,"&#39;","\'");
                        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,"&quot;","\"");
    s5=replaceString(s5,"&acute;","\'");
    s5=replaceString(s5,"&#34;","\"");
    s5=replaceString(s5,"&#39;","\'");
    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);
  }