Java模块 -- MyBatis 查询数据库 导出Excel文件

国际惯例 先上代码结构图...

先从MyBatis 说起吧.... 一步步来...

mybatis_Config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<!-- 配置数据库连接信息 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/testmysql"/>
				<property name="username" value="root"/>
				<property name="password" value="1234"/>
			</dataSource>
		</environment>
	</environments>

	<!-- sql语句的配置文件 -->
	<mappers>
		<mapper resource="com/export/sqlconfig/Students.xml"/>
	</mappers>

</configuration> 

Students.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.export.dao.ExportExcelDaoImpl">

	<select id="getStudent" resultType="map">
		select * from students order by id
	</select>

	<select id="getStudentById" resultType="map" parameterType="map">
		select * from students where id = #{userId}
	</select>
	
	<delete id="deleteStuentById" parameterType="map">
		delete from students where id = #{userId}
	</delete>
	
	<insert id="insertStudent" parameterType="map">
		insert into students 
			(username,age,salary)
		values
			(
				#{username},
				#{age},
				#{salary}
			)
	</insert>
</mapper>

SqlSessionUtil

package com.export.util;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlSessionUtil {

	public SqlSession getSqlSession() {
		
		String configFile = "mybatis_Config.xml";
		// 使用类加载器 加载mybatis的配置文件,mybatis配置文件中配置了关联映射文件
		InputStream inputStream = SqlSessionUtil.class.getClassLoader().getResourceAsStream(configFile);
		// 构建sqlSession工厂
		SqlSessionFactory sqlsessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlsessionFactory.openSession();
		return sqlSession;
		
	}

}

ExportExcelDao

package com.export.dao;

import java.util.List;
import java.util.Map;

public interface ExportExcelDao {
	
	public List<Map> getStudentsList();
	public Map getStudentById(Map studentMap);
	public int deleteStudentById(Map studentMap);
	public int insertStudent(Map studentMap);
	
}

ExportExcelDaoImpl

package com.export.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.export.util.SqlSessionUtil;

public class ExportExcelDaoImpl implements ExportExcelDao {

	private static String className = ExportExcelDaoImpl.class.getName() + ".";
	private static SqlSessionUtil su = new SqlSessionUtil();
	private static SqlSession session = su.getSqlSession();

	public List<Map> getStudentsList() {
		List<Map> listMap = null;
		try{
			listMap = session.selectList(className + "getStudent");
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return listMap;
	}

	public Map getStudentById(Map studentMap) {
		Map map = null;
		try{
			map = session.selectOne(className + "getStudentById", studentMap);			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return map;
	}
	
	public int deleteStudentById(Map studentMap){
		int result = 0;
		try{
			result = session.delete(className+"deleteStuentById", studentMap);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return result;
	}
	
	public int insertStudent(Map studentMap){
		int result = 0;
		try{
			result = session.insert(className+"insertStudent", studentMap);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return result;
	}
}

ExportExcelService

package com.export.service;

import java.util.List;
import java.util.Map;

public interface ExportExcelService {
	public List<Map> getStudentsList();
	public Map getStudentById(Map studentMap);
	public int deleteById(Map studentMap);
	public int insertStuent(Map studentMap);
}

ExportExcelServiceImpl

package com.export.service;

import java.util.List;
import java.util.Map;

import com.export.dao.ExportExcelDao;
import com.export.dao.ExportExcelDaoImpl;

public class ExportExcelServiceImpl implements ExportExcelService{
	
	private static ExportExcelDao eedao = new ExportExcelDaoImpl();
	
	public List<Map> getStudentsList() {
		return eedao.getStudentsList();
	}

	public Map getStudentById(Map studentMap) {
		return eedao.getStudentById(studentMap);
	}
	
	public int deleteById(Map studentMap){
		int result = eedao.deleteStudentById(studentMap);
		return result;
	}
	
	public int insertStuent(Map studentMap){
		int result = eedao.insertStudent(studentMap);
		return result;
	}
	
}

下面 来介绍下 比较重要的ExcelUtil工具类...

ExportExcelUtil

package com.export.util;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ExportExcelUtil {

	private FileOutputStream fos;
	private static int NUM = 10000;// 一个sheet的记录数
	private static String level1 = "1";
	private static String level2 = "2";
	private static String level3 = "3";
	private static String level4 = "4";

	/**
	 * @param title
	 *            每个Sheet里的顶部大标题
	 * @param column
	 *            单个sheet里每行数据的列对应的对象属性名称 
	 *            column ="rule_name,cityName,specName,ivrName,contactGroup,specName,RulestCont".split(",");
	 * @param data
	 *            数据
	 * @param fileName
	 *            文件名
	 */
	public void getOutputFile(String[] title, String[] column,List<Map> data, String fileName) {
		if (title == null || title.equals("")) {
			System.out.println("Excel表格 标题(表头)为空");
		}
		if (column == null || column.equals("")) {
			System.out.println("没有定义取值字段集合");
		}
		if (data == null || data.equals("")) {
			System.out.println("没有定义导出数据集合");
		}
		if (fileName == null || fileName.equals("")) {
			System.out.println("没有定义输出文件名");
		}

		HSSFWorkbook workbook = null;

		try {

			fos = new FileOutputStream("D://" + fileName + ".xls");

			workbook = new HSSFWorkbook();// 创建Excel
			HSSFSheet sheet = null; // 工作表
			HSSFRow row = null; // 行
			HSSFCell cell = null; // 行--列

			Iterator it = data.iterator();
			int i = 0;

			// 字体
			HSSFFont font = workbook.createFont();
			font.setColor(HSSFColor.BLACK.index);
			font.setFontHeightInPoints((short) 10);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			// 父告警标题样式
			HSSFCellStyle pStyle = workbook.createCellStyle();
			pStyle.setFont(font);

			// 子告警标题样式
			// HSSFCellStyle sStyle = workbook.createCellStyle();
			// sStyle.setFont(font);
			// sStyle.setFillBackgroundColor((short) 0x3399CC);

			// 告警样式
			HSSFCellStyle level1Style = workbook.createCellStyle();
			HSSFPalette palette = workbook.getCustomPalette();
			palette.setColorAtIndex((short) 9, (byte) (0xFF), (byte) (0x00),
					(byte) (0x00));
			palette.setColorAtIndex((short) 10, (byte) (0xFF), (byte) (0xA5),
					(byte) (0x00));
			palette.setColorAtIndex((short) 11, (byte) (0xFF), (byte) (0xFF),
					(byte) (0x00));
			palette.setColorAtIndex((short) 12, (byte) (0x41), (byte) (0x69),
					(byte) (0xE1));
			level1Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			level1Style.setFillForegroundColor((short) 9);
			HSSFCellStyle level2Style = workbook.createCellStyle();
			level2Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			level2Style.setFillForegroundColor((short) 10);
			HSSFCellStyle level3Style = workbook.createCellStyle();
			level3Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			level3Style.setFillForegroundColor((short) 11);
			HSSFCellStyle level4Style = workbook.createCellStyle();
			level4Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			level4Style.setFillForegroundColor((short) 12);

			
			sheet = workbook.createSheet("Sheet0"); // 工作簿
			row = sheet.createRow(0);

			// 在每一页的第一行输入标题
			for (int j = 0; j < title.length; j++) {
				cell = row.createCell(j);
				cell.setCellValue(new HSSFRichTextString(title[j]));
				cell.setCellStyle(pStyle);
			}

			// 逐行添加数据
			int k = 0;
			while (it.hasNext()) {
				if (i / NUM > k) { // 每50000条记录分一页
					k = i / NUM;
					sheet = workbook.createSheet("Sheet" + k);
					row = sheet.createRow(0);
					for (int j = 0; j < title.length; j++) {
						cell = row.createCell(j);
						cell.setCellValue(new HSSFRichTextString(title[i]));
					}
				}

				Map dataMap = (Map) it.next();
				row = sheet.createRow(i - NUM * k + 1);

				// 输出数据
				for (int j = 0; j < column.length; j++) {
					cell = row.createCell(j);
					// 按字段取值
					String columnName = column[j];	//取值的key
					cell.setCellValue(new HSSFRichTextString(String.valueOf(dataMap.get(columnName))));
					String value = String.valueOf(dataMap.get(columnName));
					if (value.equalsIgnoreCase("null") && !value.equals("0")) {
						String level = String.valueOf(dataMap.get(columnName)
								+ "_level");
						if (!level.equalsIgnoreCase("null") && !level.equalsIgnoreCase("")) {
							if (level1.equals(level)) {
								cell.setCellStyle(level1Style);
							} else if (level2.equals(level)) {
								cell.setCellStyle(level2Style);
							} else if (level3.equals(level)) {
								cell.setCellStyle(level3Style);
							} else if (level4.equals(level)) {
								cell.setCellStyle(level4Style);
							}
						} else {
							cell.setCellStyle(level1Style);
						}
					}
				}
				i++;
			}
			// 写入流
			workbook.write(fos);
			fos.flush();
			fos.close();
			System.out.println("Excel 文件导出完成");
		} catch (Exception e) {
			System.out.println("Excel导出错误: ");
			e.printStackTrace();
		}

	}
}

TestExportExcelAction

package com.export.action;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.export.service.ExportExcelService;
import com.export.service.ExportExcelServiceImpl;
import com.export.util.ExportExcelUtil;

public class TestExportExcelAction {

	private static ExportExcelService ees = new ExportExcelServiceImpl();
	private static ExportExcelUtil eeu = new ExportExcelUtil();

	public static void main(String[] args) {
//		selectAll();
//		selectOneById();
//		deleteStudentById();
//		insertStudent();
				
		String[] title = "ID,姓名,年龄,工资".split(",");
		String[] column = "id,username,age,salary".split(",");
		List<Map> data = selectAll();
		String fileName = "Export Excel By MyBatis";
		eeu.getOutputFile(title, column, data, fileName);
		
		
	}

	@SuppressWarnings("rawtypes")
	public static List<Map> selectAll() {
		List<Map> studentList = ees.getStudentsList();
		System.out.println(studentList);
		return studentList;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void selectOneById() {
		Map studentInfoMap = new HashMap();
		studentInfoMap.put("userId", "12");
		Map studentMap = ees.getStudentById(studentInfoMap);
		System.out.println(studentMap);
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void deleteStudentById(){
		Map studentInfoMap = new HashMap();
		studentInfoMap.put("userId", "12");
		int result = ees.deleteById(studentInfoMap);
		System.out.println("删除"+result+"个学生");
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void insertStudent(){
		Map studentInfoMap = new HashMap();
		studentInfoMap.put("username", "jack");
		studentInfoMap.put("age", "26");
		studentInfoMap.put("salary", "6000");
		int result = ees.insertStuent(studentInfoMap);
		System.out.println("添加"+result+"个学生");
	}

}

然后直接运行就好了...