Java利用Jxl向数据库上传下载Excel文件

由于公司大量的项目表汇总统计不方便，应公司领导要求能够将Excel项目统计表上传至数据库中，并且下载汇总的项目表。

1.程序主界面 

程序入口代码package cn.com.szzt;import javax.swing.JButton;import javax.swing.JOptionPane;public class JFrameMain {     public static void main(String[] args) {          start();    }     public static void start(){         Object[] possibleValues = {"上传Excel文件", "下载Excel文件" };          Object selectedValue =JOptionPane.showInputDialog(null, "请选择功能：",            "选择功能：", JOptionPane.INFORMATION_MESSAGE, null, possibleValues,            possibleValues[0]);          if("上传Excel文件".equals(selectedValue)){              new UpandDown().eventOnImport(new JButton());          }else if("下载Excel文件".equals(selectedValue)){              new UpandDown().eventOnExport(new JButton());          }     }    }

定义公共类start方法，在上传或下载结束时可以再次调用使得有不退出的效果

2.上传功能，选择上传Excel后跳转至上传方法进行处理，所有异常都需要抛出，最后返回对话框提示信息。

public  void eventOnImport(JButton developer){try{  JFileChooser chooser = new JFileChooser();  chooser.setMultiSelectionEnabled(true);  FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel文件","xls");  chooser.setFileFilter(filter);  int returnVal = chooser.showOpenDialog(developer);  if (returnVal == JFileChooser.APPROVE_OPTION) {   File[] arrfiles = chooser.getSelectedFiles();   if (arrfiles == null || arrfiles.length == 0) {    return;   }DBHepler db=new DBHepler();   for (File f : arrfiles) {List<Statistics> listExcel = StatisticsServices.getAllByExcel(f);            List<Statistics> stalist=StatisticsServices.getAllByDb();            List<Statistics> updateList = new ArrayList<Statistics>();            List<Statistics> addList = new ArrayList<Statistics>();            for(Statistics stuEntity : listExcel){            Boolean isexists = false;            for(Statistics stali : stalist){            if(stali.getTasj().equals(stuEntity.getTasj())&&stali.getKssj().equals(stuEntity.getKssj())&&stali.getKhmc().equals(stuEntity.getKhmc())&&stali.getXmmc().equals(stuEntity.getXmmc())){            updateList.add(stuEntity);            isexists = true;            break;            }            }            if(isexists.equals(false)){            addList.add(stuEntity);            }                        }for (Statistics stuEntity : addList) {            String sql="insert into projectstatics (tadh,tasj,kssj,jhjsrq,khmc,xmmc,jqxh,xmxq,rwfxsms,dq,bsc,syz,fzr,gjr,cdbm,zcfs,zclx,yxj,zt,jzqk,jhap,jssj,gzl,bz) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";            String[] str=new String[]{stuEntity.getTadh(),stuEntity.getTasj(),stuEntity.getKssj(),            stuEntity.getJhjsrq(),stuEntity.getKhmc(),stuEntity.getXmmc(),stuEntity.getJqxh(),            stuEntity.getXmxq(),stuEntity.getRwfxsms(),stuEntity.getDq(),stuEntity.getBsc(),            stuEntity.getSyz(),stuEntity.getFzr(),stuEntity.getGjr(),stuEntity.getCdbm(),            stuEntity.getZcfs(),stuEntity.getZclx(),stuEntity.getYxj(),stuEntity.getZt(),            stuEntity.getJzqk(),stuEntity.getJhap(),stuEntity.getJssj(),stuEntity.getGzl(),stuEntity.getBz()+""};            db.AddU(sql, str);    }DBHepler db2=new DBHepler();for (Statistics stuEntity : updateList) {            String sql="update projectstatics set tadh = ?,tasj= ?,kssj= ?,jhjsrq= ?,khmc= ?,xmmc= ?,jqxh= ?,xmxq= ?,rwfxsms= ?,dq= ?,bsc= ?,syz= ?,fzr= ?,gjr= ?,cdbm= ?,zcfs= ?,zclx= ?,yxj= ?,zt= ?,jzqk= ?,jhap= ?,jssj= ?,gzl= ?,bz= ? where tasj = ? and kssj = ? and khmc = ? and xmmc = ?";            String[] str=new String[]{stuEntity.getTadh(),stuEntity.getTasj(),stuEntity.getKssj(),            stuEntity.getJhjsrq(),stuEntity.getKhmc(),stuEntity.getXmmc(),stuEntity.getJqxh(),            stuEntity.getXmxq(),stuEntity.getRwfxsms(),stuEntity.getDq(),stuEntity.getBsc(),            stuEntity.getSyz(),stuEntity.getFzr(),stuEntity.getGjr(),stuEntity.getCdbm(),            stuEntity.getZcfs(),stuEntity.getZclx(),stuEntity.getYxj(),stuEntity.getZt(),            stuEntity.getJzqk(),stuEntity.getJhap(),stuEntity.getJssj(),stuEntity.getGzl(),stuEntity.getBz(),            stuEntity.getTasj(),stuEntity.getKssj(),stuEntity.getKhmc(),stuEntity.getXmmc()+""};            db2.AddU(sql, str);}   }}JOptionPane.showMessageDialog(null, "上传成功！", "提示",JOptionPane.INFORMATION_MESSAGE);}catch(SQLException e1){JOptionPane.showMessageDialog(null, e1.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e1.printStackTrace();}catch(ClassNotFoundException e2){JOptionPane.showMessageDialog(null, e2.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e2.printStackTrace();}catch(IOException e3){JOptionPane.showMessageDialog(null, e3.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e3.printStackTrace();}catch(Exception e4){JOptionPane.showMessageDialog(null, e4.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e4.printStackTrace();}JFrameMain.start();}

3.下载功能鉴于公司Excel文档列比较多此处省略一部分字段，根据方法调整成适应自己的格式即可。

public void eventOnExport(JButton developer) {String path; JFileChooser chooser = new JFileChooser();  FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel文件(*.xls)", "xls");  chooser.setFileFilter(filter);  int res =chooser.showSaveDialog(null);  if(res == JFileChooser.APPROVE_OPTION){  path = chooser.getSelectedFile().getAbsolutePath() + ".xls";// 获得保存路径try {            WritableWorkbook wwb = null;               File file=new File(path);               if (!file.exists()) {                   file.createNewFile();               }               //以fileName为文件名来创建一个Workbook               wwb = Workbook.createWorkbook(file);               List<String> daqu = new ArrayList<String>();               daqu.add("大客户部");daqu.add("区域营销中心");daqu.add("云贵分公司");daqu.add("华北分公司");               daqu.add("西北分公司");daqu.add("浙江分公司");daqu.add("河南办事处");daqu.add("安徽办事处");               daqu.add("江苏办事处");daqu.add("湖北办事处");daqu.add("山东办事处");daqu.add("黑吉办事处");               daqu.add("辽内办事处");daqu.add("行业销售部");               // 创建工作表               //查询数据库中所有的数据               List<Statistics> stalist=StatisticsServices.getAllByDb();               for(int i =0 ;i<daqu.size();i++){                   WritableSheet ws = wwb.createSheet(daqu.get(i), i);                 //要插入到的Excel表格的行号，默认从0开始                   Label labeltadh= new Label(0, 0, "提案单号");//表示第                   Label labeltasj= new Label(1, 0, "提案时间");//表示第                   ...                   ws.addCell(labeltadh);                   ws.addCell(labeltasj);                   ...                   int j = 0;                   for(Statistics sta:stalist){                   if(sta.getDq().equals(daqu.get(i))){                               Label labeltadh1= new Label(0, j+1, sta.getTadh()+"");//表示第                               Label labeltasj1= new Label(1, j+1, sta.getTasj()+"");//表示第                               ...                               ws.addCell(labeltadh1);                               ws.addCell(labeltasj1);                               ...                               j++;                        }                   }               }              //写进文档               wwb.write();              // 关闭Excel工作簿对象               wwb.close();        JOptionPane.showMessageDialog(null, "下载成功！", "提示",JOptionPane.INFORMATION_MESSAGE);}catch(SQLException e1){JOptionPane.showMessageDialog(null, e1.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e1.printStackTrace();}catch(ClassNotFoundException e2){JOptionPane.showMessageDialog(null, e2.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e2.printStackTrace();}catch(IOException e3){JOptionPane.showMessageDialog(null, e3.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e3.printStackTrace();}catch(Exception e4){JOptionPane.showMessageDialog(null, e4.getMessage(), "提示",JOptionPane.INFORMATION_MESSAGE);e4.printStackTrace();}}JFrameMain.start();}

4.下载时从数据库取出所有项目

public static List<Statistics> getAllByDb() throws SQLException, ClassNotFoundException{        List<Statistics> list=new ArrayList<Statistics>();            DBHepler db=new DBHepler();            String sql="select * from projectstatics";            ResultSet rs= db.Search(sql, null);            while (rs.next()) {            String tadh = rs.getString("tadh");            String tasj = rs.getString("tasj"); //提案时间             ...                list.add(new Statistics(tadh, tasj, kssj, jhjsrq, khmc, xmmc, jqxh, xmxq, rwfxsms, dq, bsc, syz, fzr, gjr, cdbm, zcfs, zclx, yxj, zt, jzqk, jhap, jssj, gzl, bz));            }            db.close();        return list;    }

5.上传时取出Excel表格中的数据，将其传至数据库，此处为公共方法

public static List<Statistics> getAllByExcel(File file) throws BiffException, IOException{        List<Statistics> list=new ArrayList<Statistics>();        InputStream is =  new FileInputStream(file);            Workbook rwb=Workbook.getWorkbook(is);                        int sheetNumbers = rwb.getNumberOfSheets();              System.out.println(sheetNumbers);            Sheet sheet = null;        for(int z = 0;z<sheetNumbers;z++){        sheet = rwb.getSheet(z);// 使用索引形式获取第一个工作表，也可以使用rwb.getSheet(sheetName);其中sheetName表示的是工作表的名称           int rows = sheet.getRows();//获取工作表中的总行数          int columns = sheet.getColumns();//获取工作表中的总列数          for (int i = 1; i < rows; i++) {              for (int j = 0; j < columns-1; j++) {              //第一个是列数，第二个是行数                    String tash =sheet.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++                    String kssj=sheet.getCell(j++, i).getContents();                    ...                                        String tadh = "";                    list.add(new Statistics(tadh, tash, kssj, jhjsrq, khmc, xmmc, jqxh, xmxq, rwfxsms, dq, bsc, syz, fzr, gjr, cdbm, zcfs, zclx, yxj, zt, jzqk, jhap, jssj, gzl, bz));            }          }        }        return list;    }

6.连接数据库的公共类

package cn.com.szzt.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBHepler {    /*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";    String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel";*/        String driver = "com.mysql.jdbc.Driver";    String url = "jdbc:mysql://127.0.0.1:3306/javaforexcel";            Connection con = null;    ResultSet res = null;    public void DataBase() throws ClassNotFoundException, SQLException {                Class.forName(driver);                con = DriverManager.getConnection(url, "root", "linuxandroid");    }   //关闭数据库连接公共方法  public void close(){    try {con.close();} catch (SQLException e) {e.printStackTrace();}    }    // 查询    public ResultSet  Search(String sql, String str[]) throws ClassNotFoundException, SQLException {        DataBase();            PreparedStatement pst =con.prepareStatement(sql);            if (str != null) {                for (int i = 0; i < str.length; i++) {                    pst.setString(i + 1, str[i]);                }            }            res = pst.executeQuery();                return res;    }    // 增删修改    public int AddU(String sql, String str[]) throws ClassNotFoundException, SQLException {        int a = 0;        DataBase();        PreparedStatement pst = con.prepareStatement(sql);        if (str != null) {            for (int i = 0; i < str.length; i++) {                pst.setString(i + 1, str[i]);            }        }        a = pst.executeUpdate();con.close();        return a;    }}