使用POI将数据库数据导出到Excel中————TY

将MySQL中的数据导入到Excel中

 

1、创建数据库（poitest）及表（user），添加不同类型的列

 

2、配置database.properties属性文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/poitest?useUnicode=true&characterEncoding=utf-8
user=root
password=root

 

3、创建并编写BaseDao类

public class BaseDao {

	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	
	Statement st;
	PreparedStatement ps;
	Connection conn ;

    static {
		Properties properties = new Properties();
		String configFile = "database.properties";
		InputStream is =         BaseDaoOne.class.getClassLoader().getResourceAsStream(configFile);
		try {
			properties.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		driver = properties.getProperty("driver");
		url = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

    public Connection getConnection() {
		if(conn == null) {
			try {
				conn = DriverManager.getConnection(url, user, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return conn;
	}
	/**
	 * 关闭资源
	 * @param rs
	 */
	public void closeAll(ResultSet rs){
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
    /**
	 * 通用的查询方法
	 * @param sql
	 * @param objects
	 * @return
	 */
	public ResultSet executeQuery(String sql,Object...objects ) {
		conn = this.getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if(objects != null) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rs;
	}

 

4、写一个测试类

public class PoiTest {

	HSSFWorkbook hb;
    final String savePath = "D:\\poiJdbc.xlsx";

	@Test
	public void test() throws Exception {
		
		
		BaseDao baseDao = new BaseDao();
		ResultSet resultSet = baseDao.executeQuery("select * from user");
		
		
		//创建一个工作薄
		hb = new HSSFWorkbook();
		//创建一个sheet
		HSSFSheet sheetOne = hb.createSheet();
		//创建一行用来保存数据库的所有列名
		HSSFRow row = sheetOne.createRow(0);
		HSSFCell cell ;
		
		//获取列的个数
		int columnCount = resultSet.getMetaData().getColumnCount();
		for(int i = 0; i < columnCount ; i++) {
			String label = resultSet.getMetaData().getColumnLabel(i+1);
			cell = row.createCell(i);
			cell.setCellValue(label);
		}
		
		int count = 1;
		while(resultSet.next()) {
			try {
				row = sheetOne.createRow(count);
				for(int i = 0 ; i < columnCount ;i++) {
					Object object = resultSet.getObject(i+1);
					cell = row.createCell(i);
					setCellValueByType(object,cell,resultSet,i);
				}
			} catch (Exception e) {
				throw new Exception("导出数据发生异常");
			}
			count++;
		}
		
		resultSet.last();//将游标指向结果集末尾
		//resultSet.getRow()返回结果是当前数据集的行号，而不是结果的行数
		if(resultSet.getRow() == count-1) {
			System.out.println("成功导出数据");
		}
		FileOutputStream fos = new FileOutputStream(savePath);
		hb.write(fos);
		baseDao.closeAll(resultSet);
		fos.flush();
		fos.close();
	}


	/**
	 * 根据数据库列的类型给单元格设值
	 * @param object 获取当前行的某个列的值
	 * @param cell	当前列
	 * @param resultSet 数据集合
	 * @param i 下标
	 * @throws Exception
	 */
	private void setCellValueByType(Object object,HSSFCell cell,ResultSet resultSet,int i) throws Exception {
		if(object instanceof Integer) {
			cell.setCellValue(Integer.parseInt(object.toString()));
		}else if(object instanceof String) {
			cell.setCellValue(object.toString());
		}else if(object instanceof Date) {
			cell.setCellStyle(getCellStyle(hb));
			cell.setCellValue(resultSet.getDate(i+1));
		}else if(object instanceof BigDecimal) {
			cell.setCellValue(((BigDecimal) object).doubleValue());
		}
	}

	/**
	 * 设置单元格的日期格式
	 * @param hb
	 * @return
	 */
	private CellStyle getCellStyle(HSSFWorkbook hb) {
		
		CellStyle cellStyle = hb.createCellStyle();
		DataFormat format = hb.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
		return cellStyle;
	}

}