Excel 代码

package com.chinabase.common.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;//下载jxl包


/** 
 * 操作Exl 
 * @author zhujl 
 */  
public class WorkExlChinaBase {
    
    private String writePath = "e://";
    private String readPath = "e://";
    private String templatePath = "e://";
    
    /** 
     * 写入Exl
     * @param 表名 列名 字段名 
     * @author zhujl 
     */  
    public void writeExcel(String tableName, String[] names, String[] columnNames) throws Exception {
        OutputStream os = null;
        Connection conn = null;
        ResultSet rs = null;
        try {  
            // 构建Workbook对象  
            writePath += tableName + ".xls";
            os = new FileOutputStream(writePath);  
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            // 构建Excel sheet  
            WritableSheet sheet = wwb.createSheet(tableName, 0);
            Label name = null;
            //A1 = 00 B1 = 10
            //A2 = 01 B2 = 11
            for(int i=0;i<names.length;i++){
                name = new Label(i, 0, names[i]);
                sheet.addCell(name);
            }
            conn = DateBase.getConnection();
            //String sql = "select * from "+tableName+"";
            //System.out.println("sql:"+sql);
            String sql = "select * from client_info where  co_number = '001'";
            rs = DateBase.executeQuery(DateBase.getStmt(conn), sql);
            Integer j = 1;
            while(rs.next()){
                Label val = null;
                for(int i=0;i<columnNames.length;i++){ 
                    val = new Label(i, j, rs.getString(columnNames[i]));
                    sheet.addCell(val);
                }
                j++;
            }
             
            DateBase.closeResultSet(rs);
            DateBase.closeConn(conn);
           //先调用write();再调用close();
            wwb.write();  
            wwb.close();  
            os.close();  
         } catch (Exception e) {  
                e.printStackTrace();  
            } finally {
                DateBase.closeResultSet(rs);
                DateBase.closeConn(conn);
                if (null != os) {  
                    os.close();  
                }  
            } 
    }
    
    /** 
     * 读入Exl
     * @param exl名 表名 字段名 
     * @author zhujl 
     */  
    public void readExcel(String exlName, String tableName, String[] columnNames) throws Exception {
        InputStream is = null;  
        Workbook workbook = null; 
        Connection conn = null;
        Statement stmt = null;
        try {
            //readPath += exlName + ".xls";
            System.out.println("exlName:"+exlName);
            is = new FileInputStream(exlName);  
            workbook = Workbook.getWorkbook(is);  
            // sheet row column 下标都是从0开始的  
            Sheet sheet = workbook.getSheet(0);  
            //int column = sheet.getColumns();  
            //int row = sheet.getRows();  
            //System.out.println("共有" + row + "行，" + column + "列数据");
            StringBuffer sql = new StringBuffer();
            //设置字段
            for(int j=1;j<sheet.getRows();j++){
                sql.append("insert into "+tableName+" (");
                for(int i=0;i<columnNames.length;i++){
                    sql.append(columnNames[i]);
                    if(i != columnNames.length-1){
                        sql.append(",");
                    }    
                }
                sql.append(") value (");
                Cell val = null;
                //设置值
                for(int i=0;i<columnNames.length;i++){
                    val = sheet.getCell(i, j);
                    sql.append("'");
                    sql.append(val.getContents());
                    sql.append("'");
                    if(i != columnNames.length-1){
                        sql.append(",");
                    }
                }
                sql.append(")");
                System.out.println(sql);
                //conn = DateBase.getConnection();
                //stmt = DateBase.getStmt(conn);
                //stmt.execute(sql.toString());
                //System.out.println("成功！");
                sql.delete(0, sql.length());
            }
                 
         // 操作完成时，关闭对象，释放占用的内存空间  
            DateBase.closeStmt(stmt);
            DateBase.closeConn(conn);
            workbook.close();  
            is.close();  
        } catch (Exception e) {  
            e.printStackTrace(System.out);  
        } finally {  
             DateBase.closeStmt(stmt);
             DateBase.closeConn(conn);
            if (is != null) {  
                is.close();  
            }  
        }
    }
    /** 
     * 读入Exl
     * @param exl名 表名 字段名 
     * @author xsc 
     */  
    /*public String readExcelProductPart(String path, String tableName, String[] columnNames, String id,User user) throws Exception {
        InputStream is = null;  
        Workbook workbook = null; 
        Connection conn = null;
        Statement stmt = null;
        String rd="";
        try {
            //readPath += exlName + ".xls";
            is = new FileInputStream(path);  
            workbook = Workbook.getWorkbook(is);  
            // sheet row column 下标都是从0开始的  
            Sheet sheet = workbook.getSheet(0);  
            //int column = sheet.getColumns();  
            //int row = sheet.getRows();  
            //System.out.println("共有" + row + "行，" + column + "列数据");
            StringBuffer sql = new StringBuffer();
            Boolean bl=true;
            //设置字段
            for(int j=1;j<sheet.getRows();j++){
                sql.append("insert into "+tableName+" (");
                for(int i=0;i<columnNames.length;i++){
                    sql.append(columnNames[i]);
                    if(i != columnNames.length-1){
                        sql.append(",");
                    }
                }
                sql.append(") value(");
                Cell val = null;
                  for(int i=0;i<columnNames.length-3;i++){
                      val = sheet.getCell(i, j);
                      String content=val.getContents().replaceAll(" ","");
                      String hql = "select * from Part where code='"+content+"'";
                      conn = DateBase.getConnection();
                      stmt = DateBase.getStmt(conn);
                      ResultSet resultSet=stmt.executeQuery(hql.toString());
                      Part part=new Part();
                      if(resultSet.next()){
                          part.setId(Long.parseLong(resultSet.getString(1)));
                      }else{
                        
                          rd+="第"+(val.getRow()+1)+"行,第"+(val.getColumn()+1)+"列,没有找到匹配零件.<br>";
                          bl=false;
                      }
                      sql.append("'");
                    sql.append(part.getId());
                    sql.append("'");
                
                }
                  sql.append(","+user.getId()+",now(),"+id+")");
                  //System.out.println("----------------"+rd);
                //System.out.println("+++++++++++"+sql);
                  if(bl){
                      conn = DateBase.getConnection();
                    stmt = DateBase.getStmt(conn);
                    stmt.execute(sql.toString());
                    //System.out.println("成功！");
                    sql.delete(0, sql.length());
                  }
                
            }
         // 操作完成时，关闭对象，释放占用的内存空间  
            DateBase.closeStmt(stmt);
            DateBase.closeConn(conn);
            workbook.close();  
            is.close();  
        } catch (Exception e) {  
            e.printStackTrace(System.out);  
        } finally {  
             DateBase.closeStmt(stmt);
             DateBase.closeConn(conn);
            if (is != null) {  
                is.close();  
            }  
        }
        return rd;
    }*/
    /** 
     * 读入Exl
     * @param exl名 表名 字段名 
     * @author zhujl 
     */  
    public void readExcelPart(String path, String tableName, String[] columnNames, String categoryID) throws Exception {
        InputStream is = null;  
        Workbook workbook = null; 
        Connection conn = null;
        Statement stmt = null;
        try {
            //readPath += exlName + ".xls";
            is = new FileInputStream(path);  
            workbook = Workbook.getWorkbook(is);  
            // sheet row column 下标都是从0开始的  
            Sheet sheet = workbook.getSheet(0);  
            //int column = sheet.getColumns();  
            //int row = sheet.getRows();  
            //System.out.println("共有" + row + "行，" + column + "列数据");
            StringBuffer sql = new StringBuffer();
            //设置字段
            for(int j=1;j<sheet.getRows();j++){
                sql.append("insert into "+tableName+" (");
                for(int i=0;i<columnNames.length;i++){
                    sql.append(columnNames[i]);
                    if(i != columnNames.length-1){
                        sql.append(",");
                    }    
                }
                sql.append(",created,updated,category_id) value (");
                Cell val = null;
                //设置值
                for(int i=0;i<columnNames.length;i++){
                    val = sheet.getCell(i, j);
                    sql.append("'");
                    sql.append(val.getContents());
                    sql.append("'");
                    if(i != columnNames.length-1){
                        sql.append(",");
                    }
                }
                sql.append(",now(),now(),"+categoryID+")");
                System.out.println(sql);
                conn = DateBase.getConnection();
                stmt = DateBase.getStmt(conn);
                stmt.execute(sql.toString());
                //System.out.println("成功！");
                sql.delete(0, sql.length());
            }
                 
         // 操作完成时，关闭对象，释放占用的内存空间  
            DateBase.closeStmt(stmt);
            DateBase.closeConn(conn);
            workbook.close();  
            is.close();  
        } catch (Exception e) {  
            e.printStackTrace(System.out);  
        } finally {  
             DateBase.closeStmt(stmt);
             DateBase.closeConn(conn);
            if (is != null) {  
                is.close();  
            }  
        }
    }
    
    
    /** 
     * 写入Exl模板
     * @param 表名 字段名 
     * @author zhujl 
     */ 
    public void templateExcel(String tableName, String[] names) throws Exception {
        OutputStream os = null;
        try {  
            // 构建Workbook对象  
            templatePath += tableName + ".xls";
            os = new FileOutputStream(templatePath);  
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            // 构建Excel sheet  
            WritableSheet sheet = wwb.createSheet(tableName, 0);
            Label name = null;
            //A1 = 00 B1 = 10
            //A2 = 01 B2 = 11
            for(int i=0;i<names.length;i++){
                name = new Label(i, 0, names[i]);
                sheet.addCell(name);
            }
           //先调用write();再调用close();
            wwb.write();  
            wwb.close();  
            os.close();  
         } catch (Exception e) {  
                e.printStackTrace();  
            } finally {
                if (null != os) {  
                    os.close();  
                }  
            } 
    }    
        
    public static void main(String[] args) {
        /*String a = "[LOG] 充值失败1:ORA-01403: 未找到任何数据\nORA-01403: 未找到任何数据\nORA-06512:在 'ETONE.TRIG_INSERT_ZFDATA', line 27\nORA-04088: 触发器 'ETONE.TRIG_INSERT_ZFDATA' 执行过程中出错\nORA-06512: 在 'ETONE.INSERTCZJL', line 51\nORA-06512: 在 line 1\n - 11544 ms";
        a = a.replace("'", "");
        System.out.println(a);*/
        WorkExlChinaBase workExl = new WorkExlChinaBase();
        String [] columnNames = {"client_name","leaguer_no","sale","save_money","saveg_money","work_money","workg_money"};
        String [] names = {"姓名","卡号","折扣","金额","赠送金额","工时金额","赠送工时金额"};
        //String [] columnNames = {"client_name","leaguer_no","save_money","saveg_money","work_money"};
        try {
            workExl.writeExcel("client_info", names, columnNames);
            //workExl.templateExcel("menu", names);
            //workExl.readExcel("e://client_info.xls","menu",columnNames);
            System.out.println("完成！");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}