JAVA读取Excel数据写入Mysql数据库And取数据库数据写入Excel

一、创建数据库，创建数据库表，添加数据

    本文章数据库名为test，数据库表为student

二、创建MAVEN项目

   不细说省略

三、在pom文件添加所需jar包

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.41</version><!--$NO-MVN-MAN-VER$ -->
</dependency>

四、工具类

（1）JDBC数据库工具类（JdbcUtil）

package com.demo.ReadAndWriteExcel.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
	
	//连接数据库基本参数
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/test";
	private static final String NAME = "root";
	private static final String PASSWORD = "mysql";
	
	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet  rs;
	
	//static块加载驱动类,获取数据库连接对象Connection
	static {
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		} catch (Exception e) {
			 e.printStackTrace();
		}
	}
	
	/**
	 * 增、删、改
	 * @param sql
	 * @param paras
	 * @return
	 */
	public int dml(String sql, Object... paras) {
		try {
			ps = conn.prepareStatement(sql);
			if (paras != null && paras.length > 0) {
				int i = 1;
				for (Object p : paras) {
					ps.setObject(i, p);
					i++;
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(conn, ps, null);
		}
		return 0;
	}
	
	/**
	 * 查询
	 * @param sql
	 * @param paras
	 * @return
	 */
	public List<Map<String,Object>> dql(String sql, Object... paras) {
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			ps = conn.prepareStatement(sql);
			if (paras != null && paras.length > 0) {
				int i = 1;
				for (Object p : paras) {
					ps.setObject(i, p);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int j = 1; j <= columnCount; j++) {
					map.put(rsmd.getColumnName(j), rs.getObject(j));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeAll(conn, ps, rs);
		}
		return list;
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	private void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				if (ps != null) {
					try {
						ps.close();
					} catch (Exception e2) {
						e2.printStackTrace();
					}finally {
						if (conn != null) {
							try {
								conn.close();
							} catch (Exception e3) {
								e3.printStackTrace();
							}
						}
					}
				}
			}
		}
	}
}

（2）返回结果封装类（ReturnResult）

package com.demo.ReadAndWriteExcel.utils;

public class ReturnResult {

	private String code;
	private String message;
	private String status;
	private String data;

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getData() {
		return data;
	}

	public void setData(String data) {
		this.data = data;
	}

	@Override
	public String toString() {
		return "ReturnResult [code=" + code + ", message=" + message + ", status=" + status + ", data=" + data + "]";
	}
	
}

五、利用jdbc工具查询数据库表内数据并写入Excel

（1）查询数据及写入Excel代码

package com.demo.ReadAndWriteExcel.service;

import java.io.File;
import java.util.List;
import java.util.Map;

import com.demo.ReadAndWriteExcel.utils.JdbcUtil;
import com.demo.ReadAndWriteExcel.utils.ReturnResult;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class WriteExcelService {
	
	/**
	 * 查询数据库数据写入Excel
	 * @param fileName 文件路径 例：D:\\student.xls
	 * @return
	 */
	public ReturnResult writeExcel(String fileName) {
		ReturnResult rr = new ReturnResult();
		try {
			WritableWorkbook wwb = null;
			File file = new File(fileName);
			if (!file.exists()) {
				file.createNewFile();
			}
			// 以fileName为文件名来创建一个Workbook
			wwb = Workbook.createWorkbook(file);
			// 创建工作表
			WritableSheet ws = wwb.createSheet("Shee1", 0);
			// 查询数据库中所有的数据
			List<Map<String, Object>> list = new JdbcUtil().dql("select * from student");
			// 要插入到的Excel表格的行号，默认从0开始
			Label labelId = new Label(0, 0, "id");
			Label labelName = new Label(1, 0, "name");
			Label labelAge = new Label(2, 0, "age");
			Label labelBirthday = new Label(3, 0, "birthday");
			ws.addCell(labelId);
			ws.addCell(labelName);
			ws.addCell(labelAge);
			ws.addCell(labelBirthday);
			// 要插入到的Excel表格的数据
			for (int i = 0; i < list.size(); i++) {
				Label labelId_i = new Label(0, i + 1, list.get(i).get("id") + "");
				Label labelName_i = new Label(1, i + 1, list.get(i).get("name") + "");
				Label labelAge_i = new Label(2, i + 1, list.get(i).get("age") + "");
				Label labelBirthday_i = new Label(3, i + 1, list.get(i).get("birthday") + "");
				ws.addCell(labelId_i);
				ws.addCell(labelName_i);
				ws.addCell(labelAge_i);
				ws.addCell(labelBirthday_i);
			}
			// 写进文档
			wwb.write();
			// 关闭Excel工作簿对象
			wwb.close();
			//利用封装类返回成功结果
			rr.setCode("0");
			rr.setMessage("将"+list.size()+"数据写入Excel");
			rr.setStatus("success");
			rr.setData(list.toString());
			return rr;
		} catch (Exception e) {
			e.printStackTrace();
			//利用封装类返回失败结果
			rr.setCode("0");
			rr.setMessage("数据写入Excel失败");
			rr.setStatus("error");
			rr.setData("");
			return rr;
		}
	}

}

（2）利用main方法测试

package com.demo.ReadAndWriteExcel.controller;

import com.demo.ReadAndWriteExcel.service.WriteExcelService;

public class WriteExcelController {
	
	public static void main(String[] args) {
		System.out.println(new WriteExcelService().writeExcel("D:\\student.xls").toString());
	}
}

（3）测试结果

ReturnResult [
    code=0,
    message=将2数据写入Excel, 
    status=success, 
    data=[
        {birthday=2018-12-26, 
         name=111, id=1, age=1
        }, 
        {birthday=2018-12-26, 
         name=222, id=2, age=2
        }]]

六、将Excel数据写入到数据库

（1）读取Excel，写入数据到数据库代码

package com.demo.ReadAndWriteExcel.service;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.demo.ReadAndWriteExcel.utils.JdbcUtil;
import com.demo.ReadAndWriteExcel.utils.ReturnResult;

import jxl.Sheet;
import jxl.Workbook;

public class ReadExcelService {
	/**
	 * 读取Excel数据，将数据写入到Mysql数据库
	 * @param fileName Excel文件路径
	 * @return
	 */
	public ReturnResult readExcel(String fileName) {
		//返回结果封装类
		ReturnResult rr = new ReturnResult();
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			//获取文件
			Workbook wb = Workbook.getWorkbook(new File(fileName));
			Sheet rs = wb.getSheet(0);//获取文件的第一个sheet 0：标识第一个 以此后推
			int clos = rs.getColumns();// 得到所有的列
			int rows = rs.getRows();// 得到所有的行
			for (int i = 1; i < rows; i++) {
				for (int j = 0; j < clos; j++) {
					// 第一个是列数，第二个是行数
					String id = rs.getCell(j++, i).getContents();
					String name = rs.getCell(j++, i).getContents();
					String age = rs.getCell(j++, i).getContents();
					String birthday = rs.getCell(j++, i).getContents();
					//将数据写入数据库
					new JdbcUtil().dml("insert into student(`name`,`age`,`birthday`) values(?,?,?)", name, age,
							birthday);
					//将数据压入map，封装进list返回结果用
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("id", id);
					map.put("name", name);
					map.put("age", age);
					map.put("birthday", birthday);
					list.add(map);
				}
			}
			//返回成功结果
			rr.setCode("0");
			rr.setMessage("将" + list.size() + "数据写入数据库");
			rr.setStatus("success");
			rr.setData(list.toString());
			return rr;
		} catch (Exception e) {
			e.printStackTrace();
			//返回失败结果
			rr.setCode("0");
			rr.setMessage("数据写入数据库失败");
			rr.setStatus("error");
			rr.setData("");
			return rr;
		}
	}

}

(2) 返回结果

ReturnResult 
    [code=0, 
     message=将2数据写入数据库, 
     status=success, 
     data=[{
        birthday=2018-12-26, 
        name=111, 
        id=1, 
        age=1}, 
        {birthday=2018-12-26,
        name=222, 
        id=2, 
        age=2
        }]]

注：只支持xls格式