通过 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();
	}

}

　　

转载于:https://www.cnblogs.com/lsyou/p/5303339.html