1、导出类 =ExportIntoExcel.java package export; import java.io.File; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import utility.propertiesOperate; import jxl.Workbook; import db.DBConnManager; /** *
1、导出类
=>ExportIntoExcel.java
package export;
import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import utility.propertiesOperate;
import jxl.Workbook;
import db.DBConnManager;
/**
* 数据库导入导出程序_导出类
* @author 23_11
*/
public class ExportIntoExcel {
//导出文件夹路径
private static final String EXPORT_POSITION = System.getProperty("user.dir") + "\\db\\export";
// 配置文件<英文字段=字段中文说明>操作对象
propertiesOperate po = null;
/**
* 构造函数
* @param propurl_配置文件路径
*/
public ExportIntoExcel(String propurl){
po = new propertiesOperate(propurl);
}
/**
* 获取结果集
* @param dbname
* @param tablename
* @return rs
*/
private ResultSet getResultSet(String dbname, String tablename) {
ResultSet rs = null;
String qsql;
if(tablename !=null || !tablename.equals("")) {
qsql = "select * from " + tablename;
DBConnManager dbcm = new DBConnManager();;
Connection con = dbcm.getConnection("Access");;
try {
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(qsql);
}catch(Exception e) {
e.printStackTrace();
}finally{
dbcm.releaseConnection("Access", con);
}
}else {
System.out.println("ExportIntoExcel_构造函数_数据库表名称没指定；");
}
return rs;
}
/**
* 数据导出
* @param dbname
* @param tablename
* @param exporturl_导出到exporturl位置
* @return
*/
public boolean export(String dbname, String tablename, String exporturl) {
boolean flag = false;
ResultSet rs = getResultSet(dbname, tablename);
try{
/**判断结果集有无数据，如果没有数据，就不执行*/
if(!rs.last()){ // 没有数据；
System.out.println("ExportIntoExcel.export()_表中没有数据无法导出！");
return flag;
}else {
rs.beforeFirst();
}
/**导出路径处理*/
if(exporturl == null || exporturl.equals("") || exporturl.indexOf(".xls")==-1) {
exporturl = EXPORT_POSITION + "\\" + dbname + "_" + tablename + ".xls";
}
// 可写工作簿
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(exporturl));
// 可写工作表
jxl.write.WritableSheet ws = wwb.createSheet(tablename, 0);
/**
* 写入表头
*/
String[] headArray = po.getValues(dbname, tablename);
int collen = headArray.length; //列数；
for(int c=0; c<collen; c++) {
jxl.write.Label lbl = new jxl.write.Label(c, 0, headArray[c]);
ws.addCell(lbl);
}
/**
* 写入数据
*/
int row;
while(rs.next()) {
row = rs.getRow(); // 行数；
for(int i=0; i<collen; i++) {
if(i == 0) {
jxl.write.Number nlbl = new jxl.write.Number(i, row, rs.getInt("id"));
ws.addCell(nlbl);
}else {
jxl.write.Label slbl = new jxl.write.Label(i, row, rs.getString(i+1));
ws.addCell(slbl);
}
}
}
wwb.write();
wwb.close();
flag = true;
}catch(Exception e) {
e.printStackTrace();
}
return flag;
}
/**
* @param args
*/
public static void main(String[] args) {
System.out.println("=>ExportIntoExcel_EXPORT_POSITION: " + EXPORT_POSITION);
ExportIntoExcel eite = new ExportIntoExcel(null);
/**
* 导出
*/
eite.export("addressBook", "personTable", null);
}
}
2、导入类
ImportFromExcel.java
package importclass;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import db.DBConnManager;
import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Workbook;
import utility.propertiesOperate;
/**
* 数据库导入导出程序_导入类
* @author 23_11
*/
public class ImportFromExcel {
// 导入文件夹路径
private static final String IMPORT_FOLDER = System.getProperty("user.dir") + "
\\db\\import";
// 配置文件<英文字段=字段中文说明>操作对象
propertiesOperate po = null;
/**
* 构造函数
* @param propurl 配置文件路径
*/
public ImportFromExcel(String propurl) {
po = new propertiesOperate(propurl);
}
/**
* 导入方法
* @param dbname_数据库名称；
* @param tablename_数据库表名称；
* @param fileurl_导入文件路径；
* @return 是否成功导入，默认不成功；
*/
public boolean importdata(String dbname, String tablename, String fileurl) {
boolean flag = false;
/**导入文件路径*/
if(fileurl == null || fileurl.equals("")) {
fileurl = IMPORT_FOLDER + "\\" + dbname + "_" + tablename + ".xls";
}
/**后台操作类*/
DBConnManager dbcm = new DBConnManager();
Connection con = dbcm.getConnection("Access");
/**从excel表格读取数据*/
try{
// 只读工作簿
InputStream is = new FileInputStream(fileurl);
jxl.Workbook rwb = Workbook.getWorkbook(is);
// 只读工作表
jxl.Sheet rs = rwb.getSheet(0); // 默认只要第一张工作表；
int rows = rs.getRows(); // 行数；
/**判断导入表格是否有数据*/
if(rows > 1) {
int cols = rs.getColumns();
ArrayList<String> keys = new ArrayList<String>(); //表字段信息；
Cell cell; //单元格
String head = null;
String key = null;
/**组装添加语句_处理表头*/
for(int c=0; c<cols; c++) {
cell = rs.getCell(c, 0);
head = cell.getContents();
key = po.getKey(head, dbname, tablename);
keys.add(key.substring(key.lastIndexOf(".")+1));
}
String preSql = "insert into " + tablename + "(";
String lastSql = " values(";
for(int i=0; i<keys.toArray().length; i++) {
if(i == keys.toArray().length-1) {
preSql += keys.toArray()[i].toString() + ")";
lastSql += "?)";
}else {
preSql += keys.toArray()[i].toString() + ", ";
lastSql += "?, ";
}
}
String sql = preSql + lastSql; // 添加语句；
System.out.println("ImportFromExcel.importdata()_insertsql: " + sql);
PreparedStatement pstmt = con.prepareStatement(sql);
/**读取数据，添加数据*/
for(int r=1; r<rows; r++) {
for(int c=0; c<cols; c++) {
cell = rs.getCell(c, r);
if(cell.getType() == CellType.NUMBER) {
// 添加数字类型
NumberCell nc = (NumberCell)cell;
pstmt.setInt(c+1, (int)nc.getValue());
}else {
// 添加字符类型
pstmt.setString(c+1, cell.getContents());
}
}
pstmt.execute();
}
}else {
System.out.println("ImportFromExcel.importdata()_要导入文件没有数据；");
}
// 关闭对象
rwb.close();
con.close();
flag = true;
}catch(Exception e) {
e.printStackTrace();
}finally{
dbcm.releaseConnection("Access", con);
dbcm.closeConns();
}
return flag;
}
/**
* @param args
*/
public static void main(String[] args) {
/**
* 导入
*/
ImportFromExcel ipfe = new ImportFromExcel(null);
boolean isok = ipfe.importdata("addressBook", "personTable", null);
if(isok) {
System.out.println("导入成功!");
}else {
System.out.println("导入失败!");
}
}
}
3、启动类
import importclass.ImportFromExcel;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import export.ExportIntoExcel;
/**
* 数据库导入导出程序_开始类
* @author 23_11
*/
public class Start {
/**
* @param args
*/
public static void main(String[] args) {
/**
* 获取用户输入
*/
System.out.println("请输入操作类型(格式为：导出/导入 dbname tablename)：");
String strline = null;
try
{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
strline = br.readLine().trim();
}
catch(java.io.IOException ex)
{
System.out.println(ex.getMessage());
}
/**
* 处理用户输入
*/
if(strline.length() != 0) {
String[] stmp = strline.split(" ");
ArrayList<String> al = new ArrayList<String>();
for(int i=0; i<stmp.length; i++) {
if(!stmp[i].equals("")) {
al.add(stmp[i]);
}
}
if(al.toArray().length == 3) {
boolean isok = false;
String type = al.toArray()[0].toString();
if(type.equals("导入")) {
ImportFromExcel ipfe = new ImportFromExcel(null);
isok = ipfe.importdata(al.toArray()[1].toString(), al.toArray()[2].toString(), null);
}else if(type.equals("导出")){
ExportIntoExcel eite = new ExportIntoExcel(null);
isok = eite.export(al.toArray()[1].toString(), al.toArray()[2].toString(), null);
}else {
System.out.println("操作类型只有“导入”以及“导出”！");
}
if(isok) {
System.out.println(type + "成功！");
}else {
System.out.println(type + "失败！");
}
}else {
System.out.println("请输入符合格式要求的参数(格式为：导出/导入 dbname tablename)！");
}
}else {
System.out.println("请先输入操作类型再按enter键执行！");
}
}
}