数据库导出excel表格

有时候我们的项目数据导出到excel表格，比如，ERP或者CRM系统就需要将网页上的table数据导出。

我们就需要使用到Apache的poi的jar包，下载该jar包，导入所需jar 包：

poi-3.10.1-20140818.jar
commons-codec-1.5.jar
commons-logging-1.1.jar
log4j-1.2.13.jar

 另外正好正在学习hibernate，所以就用了hibernate去操作数据库：

1.导入hibernate jar包：

antlr-2.7.6.jar
commons-collections-3.1.jar
dom4j-1.6.1.jarhibernate3.jar
hibernate-jpa-2.0-api-1.0.1.Final.jar
javassist-3.12.0.GA.jar
jta-1.1.jar
slf4j-api-1.6.1.jar

导入oracle驱动：ojdbc6.jar

sql:

create db2excel(
   keycode number(6) primary key,
   status  number(1) default 0,
   username  varchar2(10)
);

create sequence seq_db2excel start with 100000;

insert into db2excel values(seq_db2excel.nextval,1,'张三');
insert into db2excel values(seq_db2excel.nextval,0,'李四');
insert into db2excel values(seq_db2excel.nextval,1,'王五');

下面是实体类：

private int keyCode;
private int status;
private String username;

省略了get/set方法

Student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    
<hibernate-mapping package="com.niit.entity">
	<class name="ActiveStudent" table="db2excel">
		<id name="keyCode">
			<generator class="sequence">
				<param name="sequence">seq_db2excel</param>
			</generator>
		</id>
		<property name="status" />
		<property name="username" />
	</class>
</hibernate-mapping>

数据库操作类：

package com.niit.utils;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class HibernateUtils {
	private static SessionFactory factory;
	private static Session session;
	
	
	static{
		Configuration conf = new Configuration();
		conf.configure();
		factory = conf.buildSessionFactory();
		session = factory.getCurrentSession();
	}
	
	/**
	 * 获取session
	 * @return
	 */
	public static Session getSession(){
		if(session==null||!session.isOpen()){
			session = factory.getCurrentSession();
		}
		return session;
	}
	
	/**
	 * 根据hql查询获取数据，封装到Bean，然后在封装到List
	 * @param hql
	 * @return
	 */
	public static <T> List<T> findListBean(String hql){
		Transaction tx = session.beginTransaction();
		Query query = session.createQuery(hql);
		List<T> list = query.list();
		tx.commit();
		return list;
	}
	
	/**
	 * 根据hql查询获取数据，封装到数组，然后在封装到List
	 * @param hql
	 * @return
	 */
	public static List<Object[]> findListArray(String hql){
		Transaction tx = session.beginTransaction();
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();
		tx.commit();
		return list;
	}
	
}

demo:

package com.niit.test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.niit.entity.ActiveStudent;
import com.niit.utils.HibernateUtils;

public class Test {

	public static void main(String[] args) throws IOException {
		// 创建文件
		FileOutputStream out = new FileOutputStream("d:\\Student.xls");
		// 新建workbook
		Workbook wb = new HSSFWorkbook();
		// 创建sheet
		Sheet s = wb.createSheet();
		// 声明row和cell
		Row r = null;
		
		//从数据库中获取数据
		List<ActiveStudent> list = HibernateUtils.findListBean("from ActiveStudent");
		
		for (int rownum = 0; rownum < list.size(); rownum++) {
			// 创建一行
			r = s.createRow(rownum);
			ActiveStudent student = list.get(rownum);
			r.createCell(0).setCellValue(student.getKeyCode());
			r.createCell(2).setCellValue(student.getUsername());
			r.createCell(3).setCellValue(student.getStatus()==0?"InActive":"Active");
		}
		wb.write(out);
		out.close();

	}

}

基本上就这样，这只是一个简单的demo，只是从数据库中导出，没有具体去写界面，后期去完善吧。

fork吧