java+jfinal+poi导出excel

思路：后台获取要导出的信息（一般是获取id等，然后去数据库联合查询出结果），然后利用poi，设计excel格式，然后调出下载页面进行下载。

代码展示的是一个具体的事例，借鉴的话，根据自己的情况进行改进。

需要导入的包：

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

前台代码：

<a class="mini-button" iconCls="icon-download" οnclick="ExportExcel()">导出Excel</a>
<script type="text/javascript">
    mini.parse();
    var obj = mini.getParams();
    var task_grid = mini.get("task_grid");
    var btnYes = document.getElementById("yes");
	var btnNo = document.getElementById("no");	
	task_grid.load({"id":obj.id});
	
	function ExportExcel(){
		window.location.href="/random/export?id="+obj.id;
	}
	
	
</script>

java后台代码：

public void export() {		
		int task_id = getParaToInt("id");
		
	    Map<String, String> titleData = new LinkedHashMap<String, String>();//标题，后面用到   只有linkedMap有序
	    
	    titleData.put("kz_staff_id", "组长");	 
	    titleData.put("fkz_staff_id", "组员");
	    titleData.put("ky1_staff_id", "组员");
	    titleData.put("ky2_staff_id", "组员");
	    titleData.put("bul_name", "项目/工程");      
	    
	    String name = rtService.findbytask_id(task_id).get("task_name");
	    File file = new File(getTitle(name));
	        
		file = saveFile(titleData,file,task_id,name);
		this.renderFile(file);
	}
	private static final String FILEPATH = PathKit.getWebRootPath() + File.separator + "upload" + File.separator ;
	private static String getTitle(String name) {
		Date date = new Date();
		SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd"); 		
		
	     String title=FILEPATH+dateFormat.format(date) + ".xls";  
	     return title;
	}
	private   File saveFile(Map<String, String> headData, File file,int task_id,String name) {
		// 创建工作薄
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		// sheet:一张表的简称
		// row:表里的行
		// 创建工作薄中的工作表
		HSSFSheet hssfSheet = hssfWorkbook.createSheet();	
		hssfSheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
		hssfSheet.setColumnWidth(0, 3766);
		hssfSheet.setColumnWidth(1, 3766);
		hssfSheet.setColumnWidth(2, 3766);
		hssfSheet.setColumnWidth(3, 3766);
		hssfSheet.setColumnWidth(4, 6000);
		//创建单元格格式
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		HSSFCellStyle cellStyle1 = hssfWorkbook.createCellStyle();
		//水平居中
		HorizontalAlignment align = HorizontalAlignment.CENTER;
		cellStyle.setAlignment(align);
		cellStyle1.setAlignment(align);
		//垂直居中
		VerticalAlignment vertical = VerticalAlignment.CENTER;
		cellStyle.setVerticalAlignment(vertical);	
		cellStyle1.setVerticalAlignment(vertical);
		//设置字体
		HSSFFont font = hssfWorkbook.createFont();
		HSSFFont font1 = hssfWorkbook.createFont();
		font.setFontHeightInPoints((short)15);	
		font1.setFontHeightInPoints((short)10);
		font.setBold(true);
		font1.setBold(true);
		cellStyle.setFont(font);
		cellStyle1.setFont(font1);
		//创建headerrow
		HSSFRow headerRow = hssfSheet.createRow(0);
		hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
		HSSFCell headercell = headerRow.createCell(0);		
		headercell.setCellValue(name);			
		//将格式应用到具体单元格中
		headercell.setCellStyle(cellStyle);	
		//创建复杂表头
		HSSFRow initRow = hssfSheet.createRow(1);
		hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
		HSSFCell initcell = initRow.createCell(0);		
		initcell.setCellValue("巡查小组");
		initcell.setCellStyle(cellStyle1);	
		HSSFCell cell1 = initRow.createCell(4);	
		cell1.setCellValue("项目/工程");
		cell1.setCellStyle(cellStyle1);
		// 创建行
		HSSFRow row = hssfSheet.createRow(2);
		// 创建单元格，设置表头 创建列
		HSSFCell cell = null;
		// 初始化索引
		int rowIndex = 2;
		int cellIndex = 0;
		// 创建标题行
		row = hssfSheet.createRow(rowIndex);
		rowIndex++;
		// 遍历标题	
		for (String h : headData.keySet()) {
			//创建列
			cell = row.createCell(cellIndex);
			cell.setCellStyle(cellStyle1);
			//索引递增
			cellIndex++;
			//逐列插入标题
			cell.setCellValue(headData.get(h));
		}	
		// 得到所有记录 行：列
		int random_task = rtService.findTypeById(task_id).getInt("random_type");
		List<Record> list = rtService.exportList(task_id,random_task);
		Record record = null;
		if (list != null) {
			// 获取所有的记录 有多少条记录就创建多少行
			for (int i = 0; i < list.size(); i++) {
				row = hssfSheet.createRow(rowIndex);
				// 得到所有的行 一个record就代表 一行
				record = list.get(i);
				//下一行索引
				rowIndex++;
				//刷新新行索引
				cellIndex = 0;
				// 在有所有的记录基础之上，便利传入进来的表头,再创建N行
				for (String h : headData.keySet()) {
					cell = row.createCell(cellIndex);
					cellIndex++;
					//按照每条记录匹配数据
					cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
				}
			}
		}
		try {
			FileOutputStream fileOutputStreane = new FileOutputStream(file);
			hssfWorkbook.write(fileOutputStreane);
			fileOutputStreane.flush();
			fileOutputStreane.close();
			hssfWorkbook.close();		
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return file;
	}

读取数据库：

//export
	 public List<Record> exportList(int task_id,int random_task){
		 List<Record> list = new ArrayList<Record>();
		 if(random_task == 1){
				//单位工程
			String sql="select t.*,b.bul_name,sk.name as kz_staff_id,sf.name as fkz_staff_id,s1.name as ky1_staff_id,s2.name as ky2_staff_id from tbl_random_task t inner join tbl_random_relation r on t.global_no = r.global_no inner join tbl_staff sk on r.kz_staff_id = sk.id inner join tbl_staff sf on r.fkz_staff_id = sf.id inner join tbl_staff s1 on r.ky1_staff_id = s1.id inner join tbl_staff s2 on r.ky2_staff_id = s2.id inner join tbl_building b on r.unit_id = b.id  where t.status =1 and t.id = '"+task_id+"' ";				
			list = Db.find(sql);
			}else{
				//建设项目
				String sql="select t.*,p.pro_name as bul_name,sk.name as kz_staff_id,sf.name as fkz_staff_id,s1.name as ky1_staff_id,s2.name as ky2_staff_id from tbl_random_task t inner join tbl_random_relation r on t.global_no = r.global_no inner join tbl_staff sk on r.kz_staff_id = sk.id inner join tbl_staff sf on r.fkz_staff_id = sf.id inner join tbl_staff s1 on r.ky1_staff_id = s1.id inner join tbl_staff s2 on r.ky2_staff_id = s2.id inner join tbl_project p on r.unit_id = p.id where t.status =1 and t.id = '"+task_id+"' ";			
				list = Db.find(sql);
			}
		 return list;
	 }