java操作txt或xls文件批量导入数据

我们经常碰到导入批量数据到数据库的，可以通过txt或者xls文件直接导入数据库，但是在程序中我们会有些需要自己处理的数据，在导入到数据库，这就要通过程序来控制了，现在是下面通过程序导入txt文件数据到数据库的一些步骤：
1、先读取txt文件的内容，文件内容可以按照一定的规律进行排列，这样程序读取就方便。
import java.io.IOException;
import java.io.RandomAccessFile;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.config.Constants;
import com.utils.UUIDUtil;

/**
*  txt文本数据 采集类
*
* @see
*/
public class UserDataGather {
public static final String  TXT_FILE_PATH = "D://testUser.txt";

public static final String openFileStyle = "r";

public static final String fieldLimitChar = ".";

public static final int fieldAllCount = 1;

public static final String default_password = "PTMD0309";

public Integer count = 0;
private String FltNum;
public String UUID;

/**
* 功能：解析文本文件
*/

public void loadFile() {

try {

RandomAccessFile raf = new RandomAccessFile(TXT_FILE_PATH, openFileStyle);

String line_record = raf.readLine();

while (line_record != null) {

// 解析每一条记录

parseRecord(line_record);

line_record = raf.readLine();
}

} catch (Exception e) {

e.printStackTrace();

}

}

/**
* 功能：具体解析每一条记录，这里可以增加很多对记录的解析判断条件，如是否为字母、
*/

@SuppressWarnings("static-access")
private void parseRecord(String line_record) throws Exception {

//拆分记录

//            String[] fields = line_record.split(fieldLimitChar);
//            System.out.println(tranStr(line_record)+"Ok");
String temp = line_record.substring(line_record.indexOf(fieldLimitChar, 0), line_record.indexOf(" ", line_record.indexOf(fieldLimitChar, 0)));
//            if (fields.length == fieldAllCount) {
//
FltNum = tranStr(temp).trim().replace(fieldLimitChar,"").replace(" ","");

//                System.out.println(FltNum);
if(FltNum.length()>=4){
if(!isNumeric(FltNum)){
//                         System.out.println(generateSql(FltNum)[0].toString());
//                         System.out.println(generateSql(FltNum)[1].toString());
count++;
String[] temp1 = generateSql(FltNum);
MyFile mf = new MyFile();
mf.creatTxtFile("insertPinTuUserSql");
mf.writeTxtFile(temp1[0].toString());
mf.creatTxtFile("UUID");
mf.writeTxtFile(temp1[1].toString()+",");
}
}else if(FltNum.length() ==2 || FltNum.length() ==3){
if(!isNumeric(FltNum)){
if(!isTwoCharacter(FltNum)){
//                         System.out.println(generateSql(FltNum)[0].toString());
//                         System.out.println(generateSql(FltNum)[1].toString());
count++;
String[] temp2 = generateSql(FltNum);
MyFile mf = new MyFile();
mf.creatTxtFile("insertPinTuUserSql");
mf.writeTxtFile(temp2[0].toString());
mf.creatTxtFile("UUID");
mf.writeTxtFile(temp2[1].toString()+",");
}
}
}

//                InsertDB db = new InsertDB();
//
//                db.insertDB(FltNum);

//            }

}

@SuppressWarnings("static-access")
public String[] generateSql(String userName) throws IOException{
StringBuffer sbf = new StringBuffer();
String[] str = new String[2];
String uuid = UUIDUtil.getUUID();
sbf.append("insert into user values('"+uuid+"','" + userName +"','"+default_password+"',"+Constants.ENABLED+","+Constants.NUllDELETE+","+Constants.AUDITING+",'"+uuid+"@164.com','"+formatDateTime()+"',"+Constants.REGEDIT_USER+");/n");
sbf.append("insert into users values('"+uuid+"',"+ null+","+Constants.MALE+","+null+","+null+",'60.176.36.250','"+formatDateTime()+"',"+null+","+null+","+null+","+null+","+null+","+null+",0,"+null+","+null+",0,0,0,'"+formatDateTime()+"','1036',0,"+null+","+null+","+null+","+null+","+null+",'11',"+null+","+null+","+null+","+null+","+null+");/n");
sbf.append("insert into user_user_group values('"+uuid+"','"+ uuid +"','"+Constants.PERSONAL_USER+"');/n");
UUID = uuid;
str[0]=sbf.toString();
str[1]=UUID;
return str;
}

public String formatDateTime(){
Date date = new Date();
/**
* 时间格式化2009-12-31 09:04:31
*/   
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(sdf.format(date));
return sdf.format(date);
}
private String tranStr(String oldstr) {

String newstr = "";

try {

newstr = new String(oldstr.getBytes("ISO-8859-1"), "utf-8");

} catch (UnsupportedEncodingException e) {

e.printStackTrace();

}

return newstr;

}

public static boolean isNumeric(String str){
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();  
}

public static boolean isTwoCharacter(String str){
String regEx="[a-zA-Z0-9]{2,3}";

Pattern p=Pattern.compile(regEx);
Matcher m=p.matcher(str);

return m.find();
}
}
2、连接数据库执行数据导入

import java.sql.DriverManager;
import java.sql.ResultSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

/**
* 插入数据库 生成sql语句
*
* created on Mar 8, 2010
* @see
*/
public class InsertDB {
private static final String user = "pintu";

private static final String pwd = "pintu";

private static final String url = "jdbc:mysql://192.168.10.6:3306/pintu";

private static final String driver = "com.mysql.jdbc.Driver";

public static Connection getCon() {

Connection con = null;

try {

Class.forName(driver).newInstance();

con = (Connection) DriverManager.getConnection(url, user, pwd);

if (con != null) {

System.out.println("你已连接到数据库：" + con.getCatalog());

}

} catch (Exception e) {

System.out.println("连接数据库失败！");

e.printStackTrace();

}

return con;

}

public boolean insertDB(String FltNum) {

Connection con = null;

Statement stm = null;

boolean flag = false;

String sql = "insert into t_FltPsgInfo values('" + FltNum +
//        "','"

//                + FltLine + "','" + FltDate + "','" + PsgName + "','" + PsgType
//
//                + "','" + PsgSex + "','" + PsgCab + "','" + PsgSeatNo + "','"
//
//                + PsgInfo +
"')";

try {

con = getCon();

stm = (Statement) con.createStatement();

int i = stm.executeUpdate(sql);

if (i > 0) {

flag = true;

System.out.println(flag + "插入数据成功！");

}

} catch (Exception e) {

flag = false;

e.printStackTrace();

} finally {

close(null, stm, con);

}

return flag;

}

//关闭相关连接

public void close(ResultSet rs, Statement stm, Connection con) {

if (rs != null)

try {

rs.close();

} catch (Exception e) {

e.printStackTrace();

}

if (stm != null)

try {

stm.close();

} catch (Exception e) {

e.printStackTrace();

}

if (con != null)

try {

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}
}
----2、或者也可以生成一个sql脚本导出到一个txt文件里，执行数据库脚本插入数据

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;

public class MyFile {
private static String path = "D:/";
private static String filenameTemp;

/**
* 创建文件
*
* @throws IOException
*/
public static boolean creatTxtFile(String name) throws IOException {
boolean flag = false;
filenameTemp = path + name + ".txt";
File filename = new File(filenameTemp);
if (!filename.exists()) {
filename.createNewFile();
flag = true;
}
return flag;
}

/**
* 写文件
*
* @param newStr
*            新内容
* @throws IOException
*/
public static boolean writeTxtFile (String newStr)
throws IOException {
// 先读取原有文件内容，然后进行写入操作
boolean flag = false;
String filein = newStr + "/r/n";
String temp = "";

FileInputStream fis = null;
InputStreamReader isr = null;
BufferedReader br = null;

FileOutputStream fos = null;
PrintWriter pw = null;
try {
// 文件路径
File file = new File(filenameTemp);
// 将文件读入输入流
fis = new FileInputStream(file);
isr = new InputStreamReader(fis);
br = new BufferedReader(isr);
StringBuffer buf = new StringBuffer();

// 保存该文件原有的内容
for (int j = 1; (temp = br.readLine()) != null; j++) {
buf = buf.append(temp);
// System.getProperty("line.separator")
// 行与行之间的分隔符 相当于“/n”
buf = buf.append(System.getProperty("line.separator"));
}
buf.append(filein);

fos = new FileOutputStream(file);
pw = new PrintWriter(fos);
pw.write(buf.toString().toCharArray());
pw.flush();
flag = true;
} catch (IOException e1) {
// TODO 自动生成 catch 块
throw e1;
} finally {
if (pw != null) {
pw.close();
}
if (fos != null) {
fos.close();
}
if (br != null) {
br.close();
}
if (isr != null) {
isr.close();
}
if (fis != null) {
fis.close();
}
}
return flag;
}
}
3、测试生成的文件，或者导入数据

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
* 测试 生成插入 构造user的sql语句 输出到txt文件里
*
* created on Mar 8, 2010
* @see
*/
public class TestGenerateUserInsertSql {

/**
* @param args
*/
public static void main(String[] args) {
//        String test  ="1.项 帅      {昊天} 总 盟      2748      6";
//        String temp = test.substring(test.indexOf(".", 0), test.indexOf("  ", test.indexOf(".", 0)));
//        String temp2 = temp.trim().replace(".","").replace(" ","");
//        System.out.println(temp2+"OK");
//        String regEx="[a-zA-Z]{2}";
//
//        Pattern p=Pattern.compile(regEx);
//        Matcher m=p.matcher("w的");
//
//        boolean rs=m.find();
//        System.out.println(rs);
//        UserDataGather gather = new UserDataGather ();
//        System.out.println(!gather.isNumeric("10086"));
try {

UserDataGather gather = new UserDataGather ();

gather.loadFile();
System.out.println("总共："+gather.count+"插入语句");
} catch (Exception e) {

e.printStackTrace();

}
}

}