java将数据库数据导出为Excel文件

最新推荐文章于 2024-08-23 18:00:00 发布

herogus丶

最新推荐文章于 2024-08-23 18:00:00 发布

阅读量3.3k

点赞数 3

分类专栏： Java 文章标签：数据库 poi java excel

本文链接：https://blog.csdn.net/u013176571/article/details/61922379

版权

Java 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

本文介绍使用SSM框架实现从数据库导出Excel的具体步骤，包括UserVOMapper.xml配置、UserVOMapper.java定义、service层业务逻辑处理、controller层响应控制及前端页面交互。涵盖Apache POI组件使用技巧。

摘要由CSDN通过智能技术生成

一、开发环境

ssm框架；MySQL；JDK1.7；Tomcat7.0

Apache POI组件操作（自导jar包），官网下载地址：点击打开链接

二、具体内容

2.1、数据库数据

2.2、UserVOMapper.xml 核心代码

  <select id="queryExportUser" resultMap="BaseResultMap">
  	select * from user u
  </select>

2.3、UserVOMapper.java 核心代码

List<UserVO> queryExportUser();

2.4、service层代码（最重要）

package com.gusy.muban.service;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.gusy.muban.dao.UserVOMapper;
import com.gusy.muban.model.UserVO;

/**  
* @author gusy  
*/
@Service
public class UserService {
	@Autowired
	private UserVOMapper mapper;
	
	/**
	 * 导出excel
	 * @throws Exception 
	 */
	public Workbook queryExportUser() throws Exception{
		List<UserVO> list=mapper.queryExportUser();   //读取数据库数据
		String[] excelHeader={"账号ID","账号","用户名","密码"};  //设置Excel头部
		Workbook wb = new SXSSFWorkbook(1000);
		
		Sheet sheet = wb.createSheet("Sheet1");//创建工作表
		Row row = sheet.createRow(0);  //创建行，从0开始
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);  //对齐方式
		
		//导入头部
		for (int i = 0; i < excelHeader.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(excelHeader[i]);
			cell.setCellStyle(style);
			sheet.setColumnWidth(1, (short) 6000);  //设置列宽
		}
		UserVO user=null;
		//导入数据
		for(int i=0 ; i<list.size();i++){
			row = sheet.createRow(i + 1);  
			user=list.get(i);
			row.createCell(0).setCellValue(user.getId());   //设置单元格内容
			row.createCell(1).setCellValue(user.getAccount());
			row.createCell(2).setCellValue(user.getName());
			row.createCell(3).setCellValue(user.getPassword());
		}
		return wb;
	}
}

2.5 controller层代码

package com.gusy.muban.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Workbook;
import org.aspectj.apache.bcel.classfile.Constant;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gusy.common.utils.FileUtil;
import com.gusy.common.utils.ResultVO;
import com.gusy.muban.service.UserService;



/**  
* @author gusy 
*/
@Controller
@RequestMapping("/user")
public class UserController {
	
	private static Logger logger=Logger.getLogger(UserController.class);
	
	@Autowired
	private UserService userService;
	
	/**
	 * 导出excel
	 * @param response
	 * @param request
	 */
	@RequestMapping("/queryExportUser")
	public void queryExportUser(HttpServletResponse response,HttpServletRequest request){
		OutputStream ouputStream = null;
		try {
			Workbook wb = userService.queryExportUser();
			FileUtil.setResponse(request,response,"用户表_"+UUID.randomUUID()+".xlsx");		
			ouputStream = response.getOutputStream();
			wb.write(ouputStream);
			ouputStream.flush();
		} catch (Exception e) {
			logger.error("[queryExportUser]异常", e);
		} finally {
			if (ouputStream != null) {
				try {
					ouputStream.close();
				} catch (IOException e) {
					logger.error("[queryExportUser]输出流关闭异常", e);
				}
			}
		}
	}
}

2.6 网页代码

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
	String path = request.getContextPath();
	pageContext.setAttribute("path", path);
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!doctype html>
<html>

<head>
<meta charset="utf-8">
<title>主页</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport"
	content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="${path }/common/layui/css/layui.css"
	media="all">
<script src="${path }/common/layui/layui.js"></script>
<style type="text/css">
.main {
	text-align: center;
	margin: 200px 0;
}
</style>
</head>
<body>

	<div class="main">
		<a href="#">
			<button
				class="layui-btn layui-btn-big layui-btn-normal layui-btn-radius" οnclick="exportUser()">导出</button>
		</a>
	</div>

</body>
<script type="text/javascript">
	function exportUser(){
		var url="/muban/user/queryExportUser.do"
		window.location.href = url;
	}
</script>
</html>

三、运行结果

补充：Fileutil setResponse方法代码

	/**
	 * 设置excel文件响应（解决火狐中文名问题）
	 * @param request 请求对象
	 * @param response 响应对象
	 * @param filename 文件名
	 * @throws Exception
	 */
	public static void setResponse(HttpServletRequest request,HttpServletResponse response,String filename) throws Exception{
		String userAgent = request.getHeader("User-Agent");		
		if(userAgent.toLowerCase().contains("firefox")){
			filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
		}else{
			filename = URLEncoder.encode(filename, "UTF-8");
		}
		response.setContentType(request.getServletContext().getMimeType(filename)); 		
		response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", filename));	
	}

over!