excel导入导出实战

新建Java项目ImportExcelDemo,环境是eclipse+jdk1.8+mysql5.5,主要是完成一个单表,没有主外键关联的导入导出的功能.将excel中的数据,导入到数据库中;将数据库中的数据导出到excel中.

主要代码,先使用sql语句生成，t_student表.然后,建立student的entity类,属性包括主键,学号,姓名,性别,学院和专业.使用的DBHelper的jdbc原生sql语句处理,封装了2个简单的方法,查询和添加.

工具库帮助类

/**
 * 数据库帮助类,提供:连接数据库,增删改查功能
 * @author liuyanling
 *
 */
public class DBHelper {

	//驱动
	final String driver = "com.mysql.jdbc.Driver";

	//数据库连接
	final String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
	Connection connection = null;
	
	//查询结果集
	ResultSet resultSet = null;
	
	/**
	 * 实例化时,连接数据库
	 */
	public DBHelper() {
		// 1.加载驱动
		try {
			Class.forName(driver);
			// 2.连接
			connection = DriverManager.getConnection(url,"root","123456");
		} catch (ClassNotFoundException e) {
			System.err.println("装载 JDBC驱动程序失败。");
			e.printStackTrace();
		} catch (SQLException e) {
			System.err.println("无法连接数据库");
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询记录,根据sql语句和参数集
	 * @param sql
	 * @param strParamters
	 * @return
	 */
	public ResultSet query(String sql,String[] strParamters ) {
		try {
			//1.预编译
			PreparedStatement preStatement = connection.prepareStatement(sql);
			//2.添加参数
			if(strParamters != null) {
				for(int i = 0;i<strParamters.length;i++) {
					preStatement.setString(i+1, strParamters[i]);
				}
			}
			//3.执行查询,并返回结果
			resultSet = preStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSet;
	}
	
	/**
	 * 添加记录,根据sql语句和参数集
	 * @param sql
	 * @param strParamters
	 * @return
	 */
	public int add(String sql,String[] strParamters) {
		//受影响的条数
		int resultCount = 0;
		try {
			//1.预处理
			PreparedStatement preStatement = connection.prepareStatement(sql);
			//2.添加参数
			if(strParamters != null) {
				for(int i = 0;i<strParamters.length;i++) {
					preStatement.setString(i+1, strParamters[i]);
				}
			}
			//3.执行更新
			resultCount = preStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return resultCount;
	}
}

导入Excel

然后导入Excel的主要代码,就是将excel中的数据,读出,变成student的实体集合,最后更新这个实体集合就行.

在ImportExcel类的main方法中,调用studentService的静态方法getStudentByExcel.需要写一个地址,在该地址上放上这个文件.

		// 1.获取excel的数据,【要求在指定路径下，必须有这么一个文件】
		List<Student> listStudentFromExcel = StudentService
				.getStudentByExcel("D://student.xls");

文件中的内容,第一行为英文的表头,对应数据库中的字段名.但是实际上,用中文的表头才比较常见.

然后是getStudentByExcel()方法.

使用jxl.jar中的Workbook类,获取get指定excel的工作簿,然后工作簿获取Sheet工作表.然后将工作表中的数据,一行一行循环,将一行的单元格一个一个的取出,赋值到Student实体上.最后,循环掉一行,就是一个实体了.循环完一个工作表,就是一个实体集合了.而由于代码取第一列单元格是id,第二列是stuNo,所以对于excel模板不能任意换列.

	/**
	 * 将Excel文件中的数据,转换为Student的集合,并返回
	 * 
	 * @param ExcelFile
	 * @return
	 */
	public static List<Student> getStudentByExcel(String ExcelFile) {
		// excel中的数据转换为的student的集合
		List<Student> listStudent = new ArrayList<Student>();

		// 1.获取excel
		try {
			Workbook wBook = Workbook.getWorkbook(new File(ExcelFile));
			// 2.获取工作簿,sheet(工作表)
			Sheet sheet = wBook.getSheet(0);
			// 3.读取sheet中的数据,生成List<Student>，学生信息集合
			int colLength = sheet.getColumns();
			int rowLength = sheet.getRows();

			// 3.1.遍历行列,取出cell中的数据,放到Student对象中
			// 3.2.第一行是表头,所以从第二行(下标为1),开始遍历
			for (int i = 1; i < rowLength; i++) {
				// 3.3.列,从0开始，自增遍历
				int j = 0;
				// 3.4.取出cell,第i行和第j列,【要求excel模板中列的顺序必须为指定的顺序,否则取出数据会不对】
				String id = sheet.getCell(j++, i).getContents();
				String stuNo = sheet.getCell(j++, i).getContents();
				String stuName = sheet.getCell(j++, i).getContents();
				String stuSex = sheet.getCell(j++, i).getContents();
				String department = sheet.getCell(j++, i).getContents();
				String major = sheet.getCell(j++, i).getContents();

				// 3.5.添加student集合中
				Student student = new Student(id, stuNo, stuName, stuSex,
						department, major);
				listStudent.add(student);

				// 打印信息
				System.out
						.println("---excel中的学生-------: " + student.toString());
			}

		} catch (BiffException | IOException e) {
			e.printStackTrace();
		}

		// 4.返回学生信息集合,没有数据,则size为0
		return listStudent;
	}

然后更新到数据库中,就是一条记录一条记录的添加到数据库中.这里的问题是,I/O操作太多,严重影响效率.

DBHelper dbHelper = new DBHelper();
// 2.判断数据库中excel的数据是否存在;存在,更新;不存在,添加;
// 2.1.从Student中的集合取出student
for (Student stu : listStudentFromExcel) {
	// 获取主键id,用于判断数据是否存在
	String id = stu.getId();
	String sql = "";
	String[] strParamters = null;
	
	// 2.2.不存在，添加
	if (!StudentService.isExist(id)) {
		sql = "insert into t_student(id, stuNo, stuName, stuSex,department, major) values(?,?,?,?,?,?)";
		strParamters = new String[] { stu.getId(), stu.getStuNo(),
				stu.getStuName(), stu.getStuSex(),
				stu.getDepartment(), stu.getMajor() };
	} else {
		// 2.3.存在，更新
		sql = "update t_student set stuNo=?, stuName=?, stuSex=?,department=?, major=? where id=?";
		strParamters = new String[] {stu.getStuNo(),
				stu.getStuName(), stu.getStuSex(),
				stu.getDepartment(), stu.getMajor(), stu.getId()};
	}
	
	//3.执行
	dbHelper.add(sql,strParamters);
}

导出Excel

在ExportExcel的main方法中,也是指定好路径.没有这个文件就创建一下.若是web项目,也可以通过response将excel输出到界面上,用户下载即可.

ExportExcel.export("D://student2.xls");

导出的代码就是将就是将数据库中的数据取出,变成实体集合.

然后创建create工作簿和Sheet工作表,接着创建表头.然后将实体集合遍历,一个实体就是一行记录,将数据一个一个的赋值到单元格中.不过赋值不是直接添加的cell中,而是创建Label,指明label的列,行和label中的值.然后Sheet添加addCell(label).最后将所有数据写到工作簿中,关闭工作簿.

	/**
	 * 导出数据,到指定的Excel中
	 */
	public static void export(String ExcelFile) {
		// 1.新建导出的Excel文件
		File file = new File(ExcelFile);
		try {
			if (!file.exists()) {
				file.createNewFile();
			}

			// 1.1.建立工作簿和Sheet
			WritableWorkbook wwBook = Workbook.createWorkbook(file);
			WritableSheet wSheet = wwBook.createSheet("学生信息", 0);

			// 2.读取数据库中的数据
			List<Student> listStudentFromDB = StudentService.getStudentByDB();

			// 3.将数据写入到Excel文件中,cell中
			// 3.1.准备表头
			Label labelHeaderId = new Label(0, 0, "编号(id)");// 第1列，第1行，编号为id
			Label labelHeaderStuNo = new Label(1, 0, "学号(stuNo)");
			Label labelHeaderStuName = new Label(2, 0, "姓名(stuName)");
			Label labelHeaderStuSex = new Label(3, 0, "性别(stuSex)");
			Label labelHeaderDepartment = new Label(4, 0, "学院(department)");
			Label labelHeaderMajor = new Label(5, 0, "专业(major)");

			wSheet.addCell(labelHeaderId);
			wSheet.addCell(labelHeaderStuNo);
			wSheet.addCell(labelHeaderStuName);
			wSheet.addCell(labelHeaderStuSex);
			wSheet.addCell(labelHeaderDepartment);
			wSheet.addCell(labelHeaderMajor);

			// 3.2.准备表内容,从第二行开始填充
			for (int i = 0; i < listStudentFromDB.size(); i++) {
				Label labelId = new Label(0, i + 1, listStudentFromDB.get(i)
						.getId());
				Label labelStuNo = new Label(1, i + 1, listStudentFromDB.get(i)
						.getStuNo());
				Label labelStuName = new Label(2, i + 1, listStudentFromDB.get(
						i).getStuName());
				Label labelStuSex = new Label(3, i + 1, listStudentFromDB
						.get(i).getStuSex());
				Label labelDepartment = new Label(4, i + 1, listStudentFromDB
						.get(i).getDepartment());
				Label labelMajor = new Label(5, i + 1, listStudentFromDB.get(i)
						.getMajor());

				wSheet.addCell(labelId);
				wSheet.addCell(labelStuNo);
				wSheet.addCell(labelStuName);
				wSheet.addCell(labelStuSex);
				wSheet.addCell(labelDepartment);
				wSheet.addCell(labelMajor);
			}

			// 4.写进文档,关闭工作簿
			wwBook.write();
			wwBook.close();

		} catch (IOException | WriteException e) {
			e.printStackTrace();
		}

	}

而从数据库中获取所有记录的功能,也就是一个select查询,然后处理了一下resultSet的结果,变成list集合.

	/**
	 * 从数据库中查询所有的学生记录，返回
	 * 
	 * @return
	 */
	public static List<Student> getStudentByDB() {
		// 1.执行查询
		ResultSet rs = dbHelper.query("select * from t_student", null);
		List<Student> listStudent = new ArrayList<Student>();
		// 2.结果集变为学生集合
		try {
			while (rs != null && rs.next()) {
				//2.1取出数据
				String id = rs.getString("id");
				String stuNo = rs.getString("stuNo");
				String stuName = rs.getString("stuName");
				String stuSex = rs.getString("stuSex");
				String department = rs.getString("department");
				String major = rs.getString("major");
				
				//2.2添加到集合中
				Student student = new Student(id, stuNo, stuName, stuSex,
						department, major);
				listStudent.add(student);

				// 打印信息
				System.out.println("---数据库中的学生-------: " + student.toString());
			 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//3.返回学生列表
		return listStudent;
	} 

以上,就是一个excel简单的导入导出,尽管完成excel导入导出的功能没有问题.但是这个excel项目很多问题,写死了excel的地址,excel上传应该是通过文件上传工具,上传的.Excel的模板的列不能变,还是英文的表头,并且代码中根本没用表头,直接用的是excel的数据内容.并且插入或更新到数据库中的I/O操作太多,效率很低.所以还需要优化.

附注

项目下载：上传下载Excel的Demo