由于公司大量的项目表汇总统计不方便，应公司领导要求能够将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;
}
}
1861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
