你的位置:首页 > Java教程

[Java教程]通过 java 把excel xls中数据转为可直接插入数据库的sql文件


package Tools;import java.io.BufferedWriter;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;import jxl.Sheet;import jxl.Workbook;import entity.Student;public class ConvertXMSToSQL {	/**	 * 从xls表格中获取数据,生成可执行的sql文件,用以插入数据库,注意需要引入jxl包!支持int,Integer,long,Long,String	 * ,可自行扩展	 * 	 * @param args	 * @throws IllegalArgumentException	 * @throws IllegalAccessException	 */	public static void main(String[] args) throws IllegalArgumentException,			IllegalAccessException {		// 得到表格中所有的数据		List<Student> listExcel = getAllByExcel("C:\\Users\\xxx\\Desktop\\zzz.xls");		try {			String path = "C:\\Users\\xxx\\Desktop\\convert.sql";// 文件保存路径、名字			File file = new File(path);			BufferedWriter ow = new BufferedWriter(new FileWriter(file));			for (Student c : listExcel) {				String sql = "insert into cfg_avatar values (" + outSql(c)						+ ")";				ow.write(sql + ";" + "\n");			}// 写入内容			ow.close();		} catch (IOException e) {			e.printStackTrace();		}	}	/**	 * 从xls中获取数据	 * 	 * @param file	 * @return	 */	public static List<Student> getAllByExcel(String file) {		List<Student> list = new ArrayList<Student>();		try {			Workbook rwb = Workbook.getWorkbook(new File(file));			Sheet rs = rwb.getSheet(0);			int clos = rs.getColumns();// 得到所有的列			int rows = rs.getRows();// 得到所有的行			// 样例中,数据从第三列第一行开始			for (int i = 2; i < rows; i++) {				// 取得的每一行的所有数据存入listString				List<String> listString = new ArrayList<String>();				for (int j = 0; j < clos; j++) {					String str = rs.getCell(j, i).getContents();					listString.add(str);				}				Student Student = (Student) newObject(new Student(), listString);				list.add(Student);			}		} catch (Exception e) {			e.printStackTrace();		}		return list;	}	/**	 * 使用反射设置数据。此例中可设置的数据类型有限,没有的请自己添加!!!	 * 	 * @param obj	 * @param list	 * @return	 * @throws IllegalArgumentException	 * @throws IllegalAccessException	 */	public static Object newObject(Object obj, List<String> list)			throws IllegalArgumentException, IllegalAccessException {		Field[] field = obj.getClass().getDeclaredFields();		for (int i = 0; i < field.length; i++) {			Field f = field[i];			f.setAccessible(true);			if (f.getType() == String.class) {				f.set(obj, list.get(i));			}			if (f.getType() == Integer.class) {				f.set(obj, Integer.parseInt(list.get(i)));			}			if (f.getType() == int.class) {				f.set(obj, Integer.parseInt(list.get(i)));			}			if (f.getType() == Long.class) {				f.set(obj, Long.parseLong(list.get(i)));			}			if (f.getType() == long.class) {				f.set(obj, Long.parseLong(list.get(i)));			}		}		return obj;	}	/**	 * 	 * @param obj	 * @return	 * @throws IllegalArgumentException	 * @throws IllegalAccessException	 */	public static String outSql(Object obj) throws IllegalArgumentException,			IllegalAccessException {		StringBuffer buffer = new StringBuffer();		Field[] field = obj.getClass().getDeclaredFields();		for (int i = 0; i < field.length; i++) {			Field f = field[i];			f.setAccessible(true);			if (f.getType() == String.class) {				buffer.append("'");			}			buffer.append(f.get(obj));			if (f.getType() == String.class) {				buffer.append("'");			}			if (i < field.length - 1) {				buffer.append(",");			}		}		return buffer.toString();	}}