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

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+",'1 1',"+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(); } } }