将数据导出成excel文件

最新推荐文章于 2025-01-16 12:16:55 发布

原创最新推荐文章于 2025-01-16 12:16:55 发布 · 257 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#导出

本文介绍如何利用Apache POI库实现Java程序导出Excel的功能。具体包括配置依赖、前端按钮触发导出操作、后端处理逻辑及样式设置等。通过示例代码展示了完整的导出流程。

1、jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8-beta5</version>
</dependency>

2、html代码

<input type="text" id="username"/> 
<input type="button" id="export" value="导出"/>

3、js代码

<script type="text/javascript" src="/resources/js/jquery-1.7.min.js"></script>
<script>
jQuery(function () {
	$("#export").click(function () {
		var value = $("#username").val();
		window.location.href="export?username"+value;
	})
})
</script>

4、后台代码

（1）、Util层

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;  
/**  
 * Excel工具类  
 */    
public class PoiUtil {
	  
   
    /**  
     * 创建单元格  
     * @param row  
     * @param num  
     * @param value  
     */  
    public static void createCell(Row row ,int num,int value,CellStyle style){  
        Cell cell = row.createCell(num);  
        cell.setCellValue(value);  
        cell.setCellStyle(style);  
    }  
      
    /**  
     * 创建单元格  
     * @param row  
     * @param num  
     * @param value  
     */  
    public static void createCell(Row row ,int num,double value,CellStyle style){  
        Cell cell = row.createCell(num);  
        cell.setCellValue(value);  
        cell.setCellStyle(style);  
    }  
      
    /**  
     * 创建单元格  
     * @param row  
     * @param num  
     * @param value  
     */  
    public static void createCell(Row row ,int num,String value,CellStyle style){  
        Cell cell = row.createCell(num);  
        cell.setCellValue(value);  
        cell.setCellStyle(style);  
    }  
   
    public static HSSFCellStyle createContentStyle(HSSFWorkbook wb){  
        //设置字体  
        HSSFFont font = wb.createFont();  
        font.setColor(HSSFColor.BLACK.index);  
        font.setFontHeightInPoints((short) 10);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
        style.setFont(font);  
        return style;  
    }  
    public static HSSFCellStyle createTitleStyle(HSSFWorkbook wb){  
        //设置字体  
        HSSFFont font = wb.createFont();  
        font.setColor(HSSFColor.VIOLET.index);  
        font.setFontHeightInPoints((short) 10);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
          
        //生成一个样式  
        HSSFCellStyle style = wb.createCellStyle();  
          
          
        style.setFillForegroundColor(HSSFColor.WHITE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        style.setFont(font);  
        return style;  
    }  
	
}

（2）、Controller层

package com.wenfeng.controller;

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

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.wenfeng.service.PoiService;
import com.wenfeng.service.UserService;

@Controller
public class PoiController {
	
	@Autowired
	private UserService userservice;		//用户
	
	@Autowired
	private PoiService<Object> poiService;		//poi导出
	
	@RequestMapping("/export")  
	public void exportPriceList(HttpServletRequest req, HttpServletResponse resp) throws Exception{  
	    
		resp.setContentType("text/plain; charset=UTF-8");
		Map<String,Object> queryMap = new HashMap<String,Object>();
		
		String username = req.getParameter("username");
		if(username != null && !"".equals(username)){
			if(username.indexOf("%")!=-1){
				username = URLDecoder.decode(username, "UTF-8");
			}
			queryMap.put("username", username);
		}
		
	    List<Map<String, Object>> listMap = userservice.listuser(queryMap);    //从数据库查询来的数据  
	    
	    try {  
             /**  
               * 生成excel  
               * 参数1：封装好的Map对象集合,对象中的字段类型不能有自定义类型的数据，否则无法解析生成excel  
               * 参数2：excel的标题行  
               * 参数3：Map对象中需要导出的字段  
               */  
	            HSSFWorkbook wb = poiService.exportMap(listMap,   
	            new String[]{"ID", "名称", "密码", "email"},          
	            new String[]{"userid", "username", "password", "useremail"});    
	            
	        
	        resp.setHeader("Content-disposition", "attachment; filename="   
	            + java.net.URLEncoder.encode("文件导出", "UTF-8")  
	            + ".xls");  
	        resp.setContentType("application/msexcel");  
	        resp.setCharacterEncoding("UTF-8");  
	        wb.write(resp.getOutputStream());  
	    } catch (Exception e) {  
	        e.printStackTrace();  
	    }  
	}
}

（3）、dao层（查询用户数据的service层就省略了）

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

import org.apache.ibatis.annotations.Param;

import com.wenfeng.bean.User;

public interface UserDao {

	List<User> userlist() throws Exception;
	/**
	 * 查询相关用户信息
	 * @param queryMap
	 * @return
	 * @throws Exception
	 */
	List<Map<String, Object>> listuser(@Param("params")Map<String, Object> queryMap) throws Exception;

}

（4）、mapper层

<?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.wenfeng.dao.UserDao">
	
	<sql id="Base_Column_List">
		user_id as userid,user_name as username,user_password as password,user_email as useremail 
	</sql>
	
	<select id="userlist" resultType="com.wenfeng.bean.User" >
		SELECT <include refid="Base_Column_List"/> FROM t_user
	</select>
	
	<!-- 查询相关用户信息 -->
	<select id="listuser" resultType="java.util.Map">
		SELECT <include refid="Base_Column_List"/> FROM t_user WHERE 1=1
		<if test="params.username != null and params.username != '' ">
			AND user_name LIKE CONCAT('%', '${params.username}', '%')  
		</if>
		ORDER BY user_id DESC
	</select>
</mapper>

（5）、service层（导出）

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public interface PoiService<T> {

	/**
	 * 生成excel
	 * @param list		封装好的Map对象集合,对象中的字段类型不能有自定义类型的数据，否则无法解析生成excel
	 * @param titles	excel的标题行
	 * @param fields    Map对象中需要导出的字段
	 * @return
	 * @throws Exception
	 */
	public HSSFWorkbook exportMap(List<Map<String, Object>> list, String[] titles, String[] fields) throws Exception;

}

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.stereotype.Service;

import com.wenfeng.service.PoiService;
import com.wenfeng.util.PoiUtil;

@Service
public class PoiServiceImpl implements PoiService<Object> {

	public HSSFWorkbook exportMap(List<Map<String, Object>> objs, String[] titles, String[] fields) throws Exception {
		//行号
		int count = 0;
		//创建一个工作薄
		HSSFWorkbook wb = new HSSFWorkbook();
		//创建一个sheet
		HSSFSheet sheet = wb.createSheet();
		//创建一行
		HSSFRow row = sheet.createRow(count++); //这一行为第一行，即标题行
		
		//生成一个样式
		HSSFCellStyle style1 = PoiUtil.createContentStyle(wb);
		HSSFCellStyle style2 = PoiUtil.createTitleStyle(wb);
		HSSFCellStyle styleWrap = PoiUtil.createContentStyle(wb);
		styleWrap.setWrapText(true);
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		//为标题行生成单元格
		for (int i = 0; i < titles.length; i++) {
			PoiUtil.createCell(row, i, ""+titles[i]+"", style2);
		}
		
		// 红色字体
		HSSFFont fontRed = wb.createFont();
		fontRed.setColor(HSSFColor.RED.index);
		
		//生成表格内容
		/////////////////////////
		boolean hasFields = !(fields == null || fields.length == 0); // 是否指定导出字段
		Map<String, Object> map = null;
		Set<Map.Entry<String, Object>> setEntry = null;
		for (int i = 0; i < objs.size(); i++) { 
			map = objs.get(i);
			if (null == map) {
				continue;
			}
			row = sheet.createRow(count++);
			int col = 0;
			// 根据指定字段导出
			if(hasFields) {
				for(String f : fields) {
					if(f != null && !"".equals(f)) {
						continue;
					}
					if(f.startsWith("<RichText>")) { // 字段名开头包含<RichText>时，按富文本处理
						Object obj = map.get(f);
						HSSFRichTextString rts = new HSSFRichTextString(obj == null ? "" : String.valueOf(obj));
						// *开头的每行文本字体用红色，主要针对质检单描述信息
						Pattern pattern = Pattern.compile("\\*[^\r\n]+");
						Matcher m = pattern.matcher(rts.getString());
						while(m.find()) {
							rts.applyFont(m.start(), m.end(), fontRed);
						}
						Cell cell = row.createCell(col++);
						cell.setCellValue(rts);
						cell.setCellStyle(styleWrap);
					} else if(map.get(f) == null) {
						PoiUtil.createCell(row, col++, "", style1);
					} else if (map.get(f).getClass().getName().equals("java.util.Date")) {  //java.util.Date类型数据
						PoiUtil.createCell(row, col++, sdf.format(map.get(f)), style1);
					} else {
						PoiUtil.createCell(row, col++, String.valueOf(map.get(f)), style1);
					}
				}
			} else { // 导出所有字段
				setEntry = map.entrySet();
				for (Map.Entry<String, Object> e : setEntry) {
					if (null == e) {
						continue;
					}
					
					if(e.getValue() == null) {
						PoiUtil.createCell(row, col++, "", style1);
					} else if (e.getValue().getClass().getName().equals("java.util.Date")) {  //java.util.Date类型数据
						PoiUtil.createCell(row, col++, sdf.format(e.getValue()), style1);
					} else {
						PoiUtil.createCell(row, col++, String.valueOf(e.getValue()), style1);
					}
				}
			}
			objs.remove(i);
			i--;
			col = 0;
		}
		/////给每一列的单元格的size自适应，根据内容自动调整宽度和高度,但是对于中文的单元格无效，这个比较好性能
		for (int i = 0; i < titles.length; i++) {
			sheet.autoSizeColumn(i,true);	//让宽度根据内容长度自适应，中文情况下自适应不成立
		}
		
		for (int i = 0; i < titles.length; i++) {
			int  colWidth = sheet.getColumnWidth(i) / 256;		//得到单元格的实际宽度
			for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum ++) {
				if (null != sheet.getRow(rowNum)) {
					int length = sheet.getRow(rowNum).getCell(i).toString().getBytes().length;	//获取单元格内容的字节长度
					if (colWidth < length) {	//如果单元格的实际宽度小于单元格字节长度
						colWidth = length;		
					}
				}
			}
			
			sheet.setColumnWidth(i, 10*256);		//重新设置单元格宽度
			
		}
		
		return wb;
	}

}