java导出只导一部分_Java_Excel表格导入导出_导出导入部分

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

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

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 keys = new ArrayList(); //表字段信息；

Cell cell; //单元格

String head = null;

String key = null;

/**组装添加语句_处理表头*/

for(int c=0; 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

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

for(int c=0; 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 al = new ArrayList();    for(int i=0; i