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();

        }

    }

}